Testing JPQL queries straight from Intellij

In my current project, most of the queries are written in JPQL (Java Persistence Query Language). As with any xxx-QL (that eventually gets translated to SQL), it’s cumbersome to do the translation of the xxx-QL to SQL and back. This translation is generally done when you’re creating a new query or trying to debug an existing query. It would be great to be able to send JPQL queries directly to the DB.

One way to do this is to configure the JPA console in IntelliJ IDEA. Note that this feature is only available in the paid Edition.

For those that are in a rush, this is the minimal configuration needed to get the JPA console going. To keep things simple, let’s assume that you have a single module project, called test-jpa:

  1. Add a new data source to the project (View | Tool Windows | Database). This data source should point to the same DB that your entities use.
  2. Add “JavaEE Persistence” framework support to test-jpa (right click module | Add framework support…). Click OK
  3. Open the Persistence Window (View | Tool Windows | Persistence)
  4. In this window, you will assign a data source to test-jpa (right click module | Assign data sources)
  5. In the Assign Data Sources window, you will see a line with the value “Entities” which points to an empty Data Source field. Click on this field and select the data source from step 1. Click OK.
  6. In the Persistence Window, expand the module and right click on Entities | Console. You have a choice between JPA and Hibernate Console.

Some cool features that both Consoles support:

  • Navigating to the declaration of a class or field
  • Auto-completion
  • Parameterized queries

Reference: https://www.jetbrains.com/help/idea/using-jpa-console.html

One last thing: I’m new at writing posts. If you have any suggestions to make this post more clear, please write a comment.