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

JPA: Small Mysteries

July 13, 2011

The Java Persistance API (POJO) handles the mapping between Java objects and data in a relational database. A few quick annotations of our Java class and the instances can be persisted to the database with a couple of lines of code. A couple of lines of Java Persistence Query Language and we can retrieve some of those instances with not a line of JDBC code in sight. All very good stuff, and there’s a great deal of cleverness down the in the various available  implementation layers to make this perform well. As we might expect there are a few wrinkles to hinder the unwary. This article lists a few mysterious error messages I encountered when using the OpenJPA implementation that caused much head-scratching when first seen and the annoyingly simple resolutions of these problems.

My development environment is Rational Application Developer 8.0.1, using a WebSphere 8 runtime and the OpenJPA implementation delivered with these products.

The RAD 8.0.1 tooling allows me to create the annotated Java classes corresponding to an existing database schema with a few mouse clicks. So developing the application took about an hour and then I hit a couple of problems, the first happened when I tried to run my app: I got a a complaint about a Connection Driver.

ConnectionDriverName

The error says 

A JDBC Driver or DataSource class name must be specified in the ConnectionDriverName property

The stack trace doesn’t give much more of a hint, we can see it’s when JPA is trying to get a connection to the database, but why is it failing?

[13/07/11 07:33:03:453 BST] 00000020 BusinessExcep E   CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "findRigByPK" on bean "BeanId(OuiServiceApp#OuiServiceWeb.war#RigEjb, null)". Exception data: <openjpa-2.1.0-SNAPSHOT-r422266:990238 fatal user error> org.apache.openjpa.persistence.ArgumentException: A JDBC Driver or DataSource class name must be specified in the ConnectionDriverName property.
    at org.apache.openjpa.jdbc.schema.DataSourceFactory.newDataSource(DataSourceFactory.java:76)
    at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.createConnectionFactory(JDBCConfigurationImpl.java:840)
    at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.getDBDictionaryInstance(JDBCConfigurationImpl.java:598)
    at org.apache.openjpa.jdbc.meta.MappingRepository.endConfiguration(MappingRepository.java:1486)

After some fruitless searching for where I might specify a JDBC Driver I thought to check my persistence.xml file. In there was the line

<jta-data-source>jdbc/myapp</jta-data-source>

and I had no corresponding JDBC datasource created in my WebSphere Application Server.

So, one quick trip to the WebSphere console, create the Data Source with the JNDI entry jdbc/myapp and everything works.

Or at least for a while, then we began to see a peculiar error concerning Enhancement.

My Entities Lack Enhancement

The symptom was seen when testing in the WebSphere 8 test environment in RAD 8.0.1, I make some changes, my revised application would be published to WebSphere and when I try to run I see an errror on the lines of:

The type "class Rig" has not been enhanced at org.apache.openjpa.meta.ClassMetaData.resolveMeta

The meaning of this is reasonably clear: we know that OpenJPA performs some interesting processing, or Enhancement, on the annotated Entity classes. Different JPA implementations do different things as described in this Enhancement discussion but OpenJPA does some “byte weaving”. And for my classes this hasn’t happened.

Now it seems that there are many way to control Enhancement explicitly, see This article for some explanation. But I’d never needed to do this before, and I really didn’t want to introduce needless complexity.

So being a software person (you all know the jokes about physicists, engineers and software chaps in road accidents?) my immediate reaction was “it’s broken, lets see if it happens again!”. And what do you know it didn’t!

So my recipe for recovering from this problem: in RAD, Server View, expand your server, select the application, and restart it. This seems to provoke enhancement. No compile or server restart needed. This recipe seems to work reliably.

I then proceeded to expand my database, adding a few new simple tables and did some manual mapping of those tables. All seemed pretty easy until I hit another mysterious error message:

Wrong result type column

The error showed up when I was trying to navigate a relationshiop between by two new tables. The error seems quite clear:

Error 500: <openjpa-2.1.0-SNAPSHOT-r422266:990238 fatal general error> org.apache.openjpa.persistence.PersistenceException: [jcc][t4][1092][11643][3.57.82] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815 FailedObject: com.ibm.helios.jpa.Transaction-21 [java.lang.String]

Caused by: com.ibm.db2.jcc.am.io: [jcc][t4][1092][11643][3.57.82] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815

    at com.ibm.db2.jcc.am.bd.a(bd.java:676)

    at com.ibm.db2.jcc.am.bd.a(bd.java:60)

    at com.ibm.db2.jcc.am.bd.a(bd.java:120)

    at com.ibm.db2.jcc.am.gc.L(gc.java:1589)

    at com.ibm.db2.jcc.am.zl.getBlob(zl.java:1218)

    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getBlob(WSJdbcResultSet.java:740)

And so I spent quite some time comparing my Java class attributes and the columns in the corresponding database. The actual problem transpired to be that I had forgotten to add my new classes to the persistence.xml file.