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.

Advertisement