Thursday, August 07, 2008

Testing OpenJPA SQL statements using a JDBCListener

I am currently designing something around FetchGroups in OpenJPA, and being a good Agile / Test Driven architect, I wanted to write a JUnit which asserts the actual SQL statement generated by this ORM (the purpose is to test the FROM/WHERE, the SQL should not include unncessary JOIN and columns, if that framework I'm building around FetchGroups works).

Initially I went off looking into e.g. good ol' P6spy (that's STILL around?). Then I mused around some (Spring?) AOP Interceptor for a JDBC Connection, this article on DW seemed a good starting point... But finally I realized that the following (OpenJPA specific; this is obviously not standard JPA) does the job perfectly (and may be useful to others, so posted here):
public class RememberingLastStatementJDBCListener extends AbstractJDBCListener {

private String lastSQL;

protected void eventOccurred(JDBCEvent event) {
if (event.getSQL() != null) {
// Note: This will be called several times, for different event.getType(); but it doesn't matter for this use.
this.lastSQL = event.getSQL();

public String getLastSQL() {
return this.lastSQL;

and then in the JUnit you can do like:

RememberingLastStatementJDBCListener rememberingLastStatementJDBCListener;

openJPAConnectionProperties.setProperty("openjpa.jdbc.JDBCListeners", RememberingLastStatementJDBCListener.class.getName());

OpenJPAEntityManager oem = OpenJPAPersistence.cast(em);
OpenJPAEntityManagerSPI oems = (OpenJPAEntityManagerSPI) oem;
OpenJPAConfiguration configuration = oems.getConfiguration();
JDBCConfiguration jdbcConfiguration = (JDBCConfiguration)configuration;
JDBCListener[] jdbcListeners = jdbcConfiguration.getJDBCListenerInstances();
for (JDBCListener listener : jdbcListeners) {
if (listener instanceof RememberingLastStatementJDBCListener) {
rememberingLastStatementJDBCListener = (RememberingLastStatementJDBCListener) listener;

SomeEntity e = new SomeEntity();
e.setName("Hallo World!");

// We *HAVE* to flush() at this point, otherwise the Listener won't (yet) have seen the Statement(s)

Assert.assertEquals("INSERT INTO APP.SOMEENTITY (ID, NAME) VALUES (?, ?)",

Don't you love Open Source frameworks, where you can dig into the code and figure this kind of stuff out? ;-)


Anonymous Anonymous said...

This comment has been removed by a blog administrator.

13 April, 2009 09:00  

Post a Comment

<< Home