The pleasant power of JPA Query Language

July 15, 2011

This post is a minor celebration, I used some technology and it worked nicely. There’s also a reminder of how to enable OpenJPA tracing in a WebSphere environment. This allowed me to have alook at the SQL generated by JPA.

Setting the Scene

This example is based on some work I was doing in a financial application but I’ve reworked the problem in terms of health checks of some pieces of electrical equipment. The idea is that our system receives records recording the latest known "Health” of a piece of equipment. The records contain a time-stamp.

Equipment ID Date Health %
101 11th July 2011 98
101 12th July 2011 97
101 13th July 2011 98
351 11th July 2011 71
351 12th July 2011 47
351 13th July 2011 33

In the example table we see a piece of equipment, number 101, operating healthily,  whereas equipment number 351 is less healthy and its health is falling over time.

Now we might also have a table with more information about the Equipment, and so our health class might look like

   @Entity public class Health {
     public Date timestamp;
     public int healthPercent;

     @ManyToOne
     @JoinColumn(name="ID")
     public Equipment equipment;
  }

Simple JPA query

One thing we might reasonably do is implement a service to return the recent history of those records for a piece of equipment. Our RESTful service might have a URL like this

http://myhost/equipment/351/history

We would probably have some additional parameters to allow selection by date, but for the sake of simplicity let’s keep it to that.

In previous postings I’ve described how we can use JAX/RS to implement a service of this kind. Our JAX/RS implementation would probably call an EJB and eventually we’d end up invoking a JPA query

      SELECT h FROM Health s
                 WHERE h.equipment.id = :equipmentId

We could have then an EJB with and injected entity manager

    @Stateless
    @LocalBean
     public class RigEjb {

       @PersistenceContext
        private EntityManager m_em;

Then in the EJB a method to invoke the query

    public List<History> equipmentHistory(int equipmentId) {
         Query query = m_em.createNamedQuery(
                         "listHistoryForEquipment"); 
         query.setParameter("equipmentId", equipmentId);       
         return (List<History>) query.getResultList();
    }

All very simple written in a few tens of minutes and we get back a list of the history records for a piece of equipment, suitable for displaying in, for example, a graph. JPQL is doing well so far.

How Healthy Now?

Now Historic trends of Health are interesting, and indeed I’ve worked on systems where predicting the future health of equipment from such data is of considerable business value. However there’s probably a more important question to ask of this data: What’s the latest view about the health of each piece equipment?

For that we need to pick just one record for each piece of equipment, the latest one we have. Now when I first hit this problem I created a naive implementation. I just returned all the records to my Java application and iterated them identifying the latest record for each piece of equipment. This is not a scalable solution, with a large number of history records performance would not be good.

However JPQL is actually pretty powerful. And after some thought and a question on StackOverflow I came up with

  SELECT h FROM Health
  WHERE h.equipment.type like :type
  AND h.date = (
     SELECT MAX(hmax.date)
     FROM Health hmax WHERE
           hmax.equipment.id = h.equipment.id
    )

We’re here identify the record whose date matches the maximum date for this piece of equipment. I’m impressed that the OpenJPA iJPQL implementation delivered with WebSphere can deal with this and produced the desired answers.

However there’s even more we can accomplish. Let’s make the data a little more complicated, with multiple measurements on the same day, differentiated by a serial number.

 

Equipment ID Date Serial Health %
101 11th July 2011 1 98
101 12th July 2011 1 97
101 12th July 2011 2 98
351 11th July 2011 1 71
351 11th July 2011 2 47
351 11th July 2011 3 33
351 12th July 2011 1 29

Now this may seem a little contrived, but in fact the data now matches very closely the financial data I was working with in my real project. In that project the record with the highest serial number each day was deemed to have the most significant “health” value.

So I need to select these records:

 

Equipment ID Date Serial Health %
101 11th July 2011 1 98
101 12th July 2011 2 98
351 11th July 2011 3 33
351 12th July 2011 1 29

The query to do this is gratifyingly similar to our previous case

  SELECT s FROM State s
    WHERE s.equipment.id = :equipmentId
     AND s.id.serial = (
         SELECT MAX(smax.id.serial)
         FROM State smax WHERE
           smax.equipment.id  = s.equipment.id
                AND smax.id.date = s.id.date
         )

And this works very nicely. Out of curiosity I wanted to see what the actual SQL would be to implement this query, that led me to look at enabling OpenJPA trace in WebSphere.

OpenJPA Trace

In some environments OpenJPA trace is controlled by an entry in your peristence.xml, to enable SQL trace you would add the line:

<property name="openjpa.Log" value="SQL=TRACE"/>

In a WebSphere Application Server environment tracing is controlled through the RAS (Reliability, Availability Servicability) logging infrastructure. In my own code I use the java.util.logging APIs which are also integrated with WebSphere’s logging infrastructure.

Controlling this logging is a two step process. First you specify a destination for your trace and second you specify the logging levels for each module. One useful feature of WebSphere is that you can adjust logging levels dynamically at runtime.

I’ll describe doing this via the admin console, but you can also control logging via wsadmin scripts, and this is my preferred approach if I need to do much work with logging and tracing.

Logging Destinations

In the admin console select Troubleshooting, Logs and Trace, select your server and then Diagnostic Trace. This brings up the screen where you can specify the logging destination

image

In a high performance situation the use of a memory buffer which can then be seen in a core dump is useful, but in normal usage I use a file as show here.

Changes made to this specification do require a server restart, before doing that you may also want to change the default module logging levels. WebSphere allows you either to modify the logging levels temporarily (on the runtime tab) or to set the levels that take effect each time the server is started. I decided to make the change to those default settings and so selected Change Log Detail Levels.

Module Logging Levels

You can either specify a trace string directly or use a graphical UI.

The trace string can be entered directly

image

Here I set all modules to info, and the specifically the JPA SQL module to “all”, which is the highest volume setting.

If you don’t know the trace string, then it is best to use the UI module tree. I have found that it is best to make sure all modules are initialised before changing the logging levels through the UI module tree. So first I ran my test program which exercised JPA. Then expanded the tree to show the openjpa module

image

And then clicked the SQL module to bring up the available levels

image

Note that this UI is also available on the runtime tab.

Having saved the changes and restarted the server I reran my tests an could see the SQL in my trace file.

SELECT t0.date, t0.id, t0.serial, t2.id, t2.name, t3.id, t3.description, t3.field, t3.name, t2.type, t0.health
FROM OUI.State t0 LEFT OUTER JOIN OUI.Equipment t2 ON t0.id = t2.id
LEFT OUTER JOIN OUI.RIGS t3 ON t2.RIG_ID = t3.id
WHERE (t0.id = ?
AND t0.serial = (SELECT MAX(t1.serial)
FROM OUI.State t1 WHERE (t1.id = t0.id AND t1.date = t0.date) )) 

Advertisements

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

%d bloggers like this: