Get JPQL queries from Criteria API with OpenEJB and TomEE


Criteria API added in JPA2 is a great enhancement for dynamic queries (or some other cases ;)) but sometimes it can be useful to see how it is translated by the JPA provider. You think it is complicated? Read more to see it can be easy.

Often you simply add a sql proxy driver (like slf4j-jdbc, jamon, simon…) but since you are doing JPA, a JPQL tracer is probably more welcomed.

OpenJPA and Hibernate (understand not eclipselink) provide such information but it is not easily exportable.

Note: eclipselink provides the “query plan” which is not a jpql query but which is already interesting.

For those providers OpenEJB and TomEE now (on trunk) provide two new properties you can add to your persistence.xml (and like OpenEJB and TomEE are cool you can set these properties globally for the container too in conf/system.properties).

The first one is simply a boolean to activate the logging of jpql queries for criteria api queries and the second one is the level to use for the logging (default is info).

Here are the properties:

  • openejb.jpa.criteria.log.jpql: boolean activating or not the logging of jpql queries from criteria api queries
  • openejb.jpa.criteria.log.jpql.level: the logging level to use. Values are {debug, info, warn, error} (+ some aliases)

Note: if the level property is activated the boolean is not mandatory

Here a sample persistence.xm using this feature:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
             xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="
      http://java.sun.com/xml/ns/persistence
      http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="dynamic" transaction-type="JTA">
    <jta-data-source>jdbc/dynamicDB</jta-data-source>
    <class>org.superbiz.dynamic.User</class>
    <properties>
      <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(ForeignKeys=true)"/>
      <property name="openejb.jpa.criteria.log.jpql.level" value="info" />
    </properties>
  </persistence-unit>
</persistence>

Here a sample output on an openejb sample:

INFO - executing query 'SELECT u FROM User u WHERE u.id = 11'
INFO - executing query 'SELECT u FROM User u WHERE u.id = 1'
INFO - executing query 'SELECT u FROM User u'
INFO - executing query 'SELECT u FROM User u'
INFO - executing query 'SELECT u FROM User u'
INFO - executing query 'SELECT u FROM User u WHERE u.name LIKE 'foo''
INFO - executing query 'SELECT u FROM User u WHERE (u.name LIKE 'bar-1' AND u.age = 1)'

Note: cdi-query add some interesting feature on JPA and in particular some queryextractor to get the query string from a query.

Advertisements

2 thoughts on “Get JPQL queries from Criteria API with OpenEJB and TomEE

  1. mutya

    Thanks to this site, very informative. Got a question though, how do I set the above for log4j?

    Reply
    1. rmannibucau Post author

      Hmm, you are right, you have to set a system property, i think i’ll do another blog post today about it. Thanks to pointing it out!

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s