Testing JPQL queries straight from Intellij

In my current project, most of the queries are written in Java Persistence Query Language (JPQL). As with any *QL (that eventually gets translated to SQL), it’s cumbersome to do the translation of the *QL to SQL and vice-versa. 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 Ultimate Edition (paid).

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