Monday, March 29, 2010

Using HQL Console for JPA facet in Intellij

You got a complicated HQL or JPAQL in your code that has some problems, you need to verify what exactly the query is doing, how should you go ahead to test run it in your database? One option is that you can read the HQL or JPAQL and manually translate it to SQL statement and run the sql statement in your Database, however, this method is error prone and tedious. Another option is use the HQL Console came with Intellij, I found this method is easy and more accurate comparing to the previous options. Here is how I did it:
  • Create a DataSource in Intellij, let's name it datasource_1
  • Go to the Project->View:J2EE structure->right click the module your pojos are located in, edit the JPA facet properties to set datasource_1 maps to the desired Persistent Unit, and set the default JPA provider to: Hibernate.
  • Project->View:J2EE structure->right click the Persistent Unit, you should be able to start HQL Console.
  • You can paste your HQL or JPAQL in the HQL Console and try it out, or you can generate SQL for it and run the SQL in your DB.
Update: In Intellj 9, there is a little bit change where you can find the Persistent Unit to start HQL Console, you should click the Persistence tab on the left corner of Intellij 9.