I’m starting to get tired of OpenJPA/HSQLDB setup that I’ve trying: it seems to give more problems than it solves. Now I discovered that the HSQL db wasn’t been properly shutdown and for reasons that I’m investigating, if the HSQL db is not propertly closed (SHUTDOWN command) it’s impossible to reopen it again. It fails with a IndexOutOfBoundsException.

Caused by: org.hsqldb.HsqlException: error in script file line: 33 java.io.IOException: java.lang.IndexOutOfBoundsException in statement [SET TABLE PUBLIC.NOTES INDEX '2883070']
at org.hsqldb.Error.error(Error.java:111)
at org.hsqldb.scriptio.ScriptReaderText.readDDL(ScriptReaderText.java:132)
at org.hsqldb.scriptio.ScriptReaderBase.readAll(ScriptReaderBase.java:88)
at org.hsqldb.persist.Log.processScript(Log.java:721)
at org.hsqldb.persist.Log.open(Log.java:187)
at org.hsqldb.persist.Logger.openPersistence(Logger.java:209)
at org.hsqldb.Database.reopen(Database.java:265)
at org.hsqldb.Database.open(Database.java:235)
at org.hsqldb.DatabaseManager.getDatabase(DatabaseManager.java:222)
at org.hsqldb.DatabaseManager.newSession(DatabaseManager.java:145)
at org.hsqldb.jdbc.JDBCConnection.<init>(JDBCConnection.java:3219)
... 59 more

Annoying. This is not OpenJPA fault, but it gets complicated to actually solve it.

The obvious solution was to add a “;shutdown=true” to the openjpa.ConnectionURL property. That works but it makes the database access unbearable slow. Presumably because it makes HSQL to perform a SHUTDOWN each time OpenJPA closes the connection, and I guess that OpenJPA is closing the connection every couple of INSERTs. This is a guess, I couldn’t confirm it but I see a lot of GC (garbage collection) going on between INSERT and INSERT and I though that I could be that.

UPDATE: Now I’m sure that OpenJPA is closing the connection, I can see it in the HSQL logs. There is a way to enable connection pooling in OpenJPA although is non-trivial.

So “;shutdown=true” is not feasible right now, unless there is something that I could do to raise HSQLDB performance to an acceptable level while using this option.

I was forced to issue the SHUTDOWN myself. In my case in close() in a Netbean’s ModuleInstaller. Which I still don’t like because if the application is not propertly closed itself, the DB won’t be SHUTDOWN and the database will be unreadable again.

public class Installer extends ModuleInstall {

  private static EntityManagerFactory EMF = null;
  private static EntityManager EM = null;
  private static String connectionURL = null;
  private static final Logger LOG = Logger.getLogger(Installer.class.getName());

  @Override
  public void close() {
    if (null != EM) {
      //things get cumbersome. We must syncronize access to EM.
      //here and anywhere else where it's used
      synchronized (EM) {
        EM.close(); //we know that there is no active transaction
        //running because we synchronized the access
        //and all the other synchonized blocks
        //never leave the transaction open
        LOG.info("closed EntityManager " + EM);

        //Issue a SHUTDOWN command via JPA Native Query
        final EntityManager emanager = EMF.createEntityManager();
        LOG.info("JPA Native Query shutdown");
        emanager.getTransaction().begin();
        emanager.createNativeQuery("SHUTDOWN").executeUpdate();
        emanager.getTransaction().commit();
        emanager.close();
      }
    }
    if (null != EMF) {
      LOG.info("closing EntityManagerFactory " + EMF);
      EMF.close();
    }

  }

  public static EntityManager getEntityManager() {
    if (EMF == null) {
      Map properties = new HashMap();
      connectionURL = "jdbc:hsqldb:file:" + System.getProperty("netbeans.user") + "/en4j/db";
      properties.put("openjpa.ConnectionURL", connectionURL);
      EMF = javax.persistence.Persistence.createEntityManagerFactory("JpaEntitiesClassLibraryPU", properties);
    }

    if (null == EM) {
      EM = EMF.createEntityManager();
    }
    synchronized (EM) {
      return java.beans.Beans.isDesignTime() ? null : EM;
    }
  }
}

As I said the everything gets more complicated because know the entityManager could be accessed simultaneosly by two different threads. The ModuleInstall.close() get invoked from a different thread and suddenly we have to use synchronize blocks arround the EntityManager and check that the EntityManager is still open (with EntityManager.isOpen()) everywhere.