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.

Inspecting in-mem HSQLDB

Sometimes, for automatic testing of your Java application, you need to configure a DB connection. Most of the times the decision is to go for an in-memory database and HSQLDB is a prime candidate.

And some other times your tests will be failing and it would be great to see the DB status before the failure. I already knew of the option of running HSQLDB as a server, but a colleague showed me a simpler way, with less configuration.

Simply add the following lines to the beginning or the test:

System.setProperty(“java.awt.headless”, “false”); // to prevent an exception related to awt running headless
DatabaseManagerSwing.main(new String[]{“--url”, “jdbc:hsqldb:mem:testdb”, “--user”, “sa”, “--password”, “”}); //to launch a separate a simple DB-inspector window

Normally, you’re going to put a breakpoint in your test right before the failing assertions. If the DB-inspector window freezes it’s because the breakpoint is configured to stop all threads. You will need to configure your IDE to not stop the DB-inspector thread (on IntelliJ IDEA, right click on the breakpoint and you’ll get the menu that allows you to change this).

References:

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

Excluding Hibernate entities from auto-generation

Today I had to do something completely new with Hibernate, within the scope of a unit test. I had a group of entities and wanted to exclude one of them from the schema auto-generation (hibernate.hbm2ddl.auto=create).

After spending a couple of hours trying out different things and reading Stackoverflow answers, I hit jackpot with this cool solution:

  • implement the SchemaFilterProvider and the SchemaFilter interfaces
  • in the SchemaFilter implementation, add an if condition to includeTable so that it returns false for the table that you don’t want to create
  • add hibernate.properties to the classpath and define hibernate.hbm2ddl.schema_filter_provider to point to the SchemaFilterProvider implementation

What follows is an example of an implementation. In my case, I want to exclude all entities that would create a table that has namespace in its name.

https://gist.github.com/treaz/4c576f6701385a727a6df4cd479089d5https://gist.github.com/treaz/3cd4ec457eefbf2b79ce01a527022160

Reference: https://stackoverflow.com/questions/6212144/how-to-disable-schema-validation-in-hibernate-for-certain-entities

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