JPA+SQLite problem

Question

I'm configuring JPA to work with SQLite db, with hibernate as the provider. I map a single pojo to a table, no fk's,just plain simple for now. I'm using a SQLDialect I found on the internet.I get this stacktrace, when I try to initialize my application:

java.sql.SQLException: SQLite supports only TRANSACTION_SERIALIZABLE
    at org.sqlite.Conn.setTransactionIsolation(Conn.java:131)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.setTransactionIsolation(NewProxyConnection.java:701)
    at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:58)
    at org.hibernate.tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.prepare(SuppliedConnectionProviderConnectionHelper.java:27)
    at org.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:127)
    at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:314)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1300)
    at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:915)
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:730)
    at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:121)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:51)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:33)
    at hr.loggingserver.listeners.ApplicationListener.contextInitialized(ApplicationListener.java:27)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3843)
    at org.apache.catalina.core.StandardContext.start(StandardContext.java:4342)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
    at org.apache.catalina.core.StandardHost.start(StandardHost.java:719)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
    at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
    at org.apache.catalina.core.StandardService.start(StandardService.java:516)
    at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:578)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:288)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:413)

here is my persistence.xml:

<persistence 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_1_0.xsd"
    version="1.0">
    <persistence-unit name="loggingServerDB" transaction-type="RESOURCE_LOCAL">
     <provider>org.hibernate.ejb.HibernatePersistence</provider>

     <class>hr.loggingserver.model.Type</class>
     <class>hr.loggingserver.model.LogSpec</class>

     <properties>
      <property name="hibernate.bytecode.use_reflection_optimizer" value="true"/>
      <property name="hibernate.default_batch_fetch_size" value="100"/>
      <property name="hibernate.cache.use_second_level_cache" value="true"/>
<!--     <property name="hibernate.cache.use_query_cache" value="true"/>  -->
      <property name="hibernate.cache.provider_class" value="net.sf.ehcache.hibernate.EhCacheProvider"/>

      <property name="hibernate.archive.autodetection" value="class, hbm"/>
      <property name="hibernate.show_sql" value="true"/>
      <property name="hibernate.format_sql" value="true"/>
      <property name="hibernate.hbm2ddl.auto" value="update"/>
      <property name="hibernate.dialect" value="hr.loggingserver.model.dao.dialect.SQLiteDialect"/>
      <!--  Set isolation level to repeatable_read (read_commited je 2) -->
      <property name="hibernate.connection.isolation" value="4"/>
      <property name="hibernate.connection.release_mode" value="after_transaction"/>

      <property name="hibernate.connection.driver_class" value="org.sqlite.JDBC"/>
      <property name="hibernate.connection.url" value="jdbc:sqlite:C:\work1\logging-server2\db1"/>
      <!--<property name="hibernate.connection.username" value="labosi"/>
      <property name="hibernate.connection.password" value="s%aHb31J7"/>-->
      <property name="hibernate.c3p0.min_size" value="5"/>
      <property name="hibernate.c3p0.max_size" value="50"/>
      <property name="hibernate.c3p0.timeout" value="300"/>
      <property name="hibernate.c3p0.max_statements" value="50"/>
      <property name="hibernate.c3p0.idle_test_period" value="600"/>

     </properties>
    </persistence-unit>
</persistence>

Here is the SQLDialect class I'm using:

package hr.loggingserver.model.dao.dialect;
/*
 * The author disclaims copyright to this source code. In place of
 * a legal notice, here is a blessing:
 * 
 * May you do good and not evil.
 * May you find forgiveness for yourself and forgive others.
 * May you share freely, never taking more than you give.
 *
 */
import java.sql.Types;

import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.Hibernate;

public class SQLiteDialect extends Dialect {
    public SQLiteDialect() {
     super();
     registerColumnType(Types.BIT, "integer");
     registerColumnType(Types.TINYINT, "tinyint");
     registerColumnType(Types.SMALLINT, "smallint");
     registerColumnType(Types.INTEGER, "integer");
     registerColumnType(Types.BIGINT, "bigint");
     registerColumnType(Types.FLOAT, "float");
     registerColumnType(Types.REAL, "real");
     registerColumnType(Types.DOUBLE, "double");
     registerColumnType(Types.NUMERIC, "numeric");
     registerColumnType(Types.DECIMAL, "decimal");
     registerColumnType(Types.CHAR, "char");
     registerColumnType(Types.VARCHAR, "varchar");
     registerColumnType(Types.LONGVARCHAR, "longvarchar");
     registerColumnType(Types.DATE, "date");
     registerColumnType(Types.TIME, "time");
     registerColumnType(Types.TIMESTAMP, "timestamp");
     registerColumnType(Types.BINARY, "blob");
     registerColumnType(Types.VARBINARY, "blob");
     registerColumnType(Types.LONGVARBINARY, "blob");
     // registerColumnType(Types.NULL, "null");
     registerColumnType(Types.BLOB, "blob");
     registerColumnType(Types.CLOB, "clob");
     registerColumnType(Types.BOOLEAN, "integer");

     registerFunction("concat", new VarArgsSQLFunction(Hibernate.STRING, "",
       "||", ""));
     registerFunction("mod", new SQLFunctionTemplate(Hibernate.INTEGER,
       "?1 % ?2"));
     registerFunction("substr", new StandardSQLFunction("substr",
       Hibernate.STRING));
     registerFunction("substring", new StandardSQLFunction("substr",
       Hibernate.STRING));
    }

    public boolean supportsIdentityColumns() {
     return true;
    }

    /*
     public boolean supportsInsertSelectIdentity() {
     return true; // As specify in NHibernate dialect
     }
     */

    public boolean hasDataTypeInIdentityColumn() {
     return false; // As specify in NHibernate dialect
    }

    /*
     public String appendIdentitySelectToInsert(String insertString) {
     return new StringBuffer(insertString.length()+30). // As specify in NHibernate dialect
     append(insertString).
     append("; ").append(getIdentitySelectString()).
     toString();
     }
     */

    public String getIdentityColumnString() {
     // return "integer primary key autoincrement";
     return "integer";
    }

    public String getIdentitySelectString() {
     return "select last_insert_rowid()";
    }

    public boolean supportsLimit() {
     return true;
    }

    public String getLimitString(String query, boolean hasOffset) {
     return new StringBuffer(query.length() + 20).append(query).append(
       hasOffset ? " limit ? offset ?" : " limit ?").toString();
    }

    public boolean supportsTemporaryTables() {
     return true;
    }

    public String getCreateTemporaryTableString() {
     return "create temporary table if not exists";
    }

    public boolean dropTemporaryTableAfterUse() {
     return false;
    }

    public boolean supportsCurrentTimestampSelection() {
     return true;
    }

    public boolean isCurrentTimestampSelectStringCallable() {
     return false;
    }

    public String getCurrentTimestampSelectString() {
     return "select current_timestamp";
    }

    public boolean supportsUnionAll() {
     return true;
    }

    public boolean hasAlterTable() {
     return false; // As specify in NHibernate dialect
    }

    public boolean dropConstraints() {
     return false;
    }

    public String getAddColumnString() {
     return "add column";
    }

    public String getForUpdateString() {
     return "";
    }

    public boolean supportsOuterJoinForUpdate() {
     return false;
    }

    public String getDropForeignKeyString() {
     throw new UnsupportedOperationException(
       "No drop foreign key syntax supported by SQLiteDialect");
    }

    public String getAddForeignKeyConstraintString(String constraintName,
      String[] foreignKey, String referencedTable, String[] primaryKey,
      boolean referencesPrimaryKey) {
     throw new UnsupportedOperationException(
       "No add foreign key syntax supported by SQLiteDialect");
    }

    public String getAddPrimaryKeyConstraintString(String constraintName) {
     throw new UnsupportedOperationException(
       "No add primary key syntax supported by SQLiteDialect");
    }

    public boolean supportsIfExistsBeforeTableName() {
     return true;
    }

    public boolean supportsCascadeDelete() {
     return false;
    }
}

Does anyone know what I'm doing wrong? Thank you.

Answer 1

The cause of the SQLException is that the SQLite JDBC driver you are using does not support the transaction isolation level you are specifying.

In your persistance.xml, hibernate.connection.isolation is set to 4 (TRANSACTION_REPEATABLE_READ). The SQLException is saying that the driver only supports TRANSACTION_SERIALIZABLE. This would be equivalent to setting hibernate.connection.isolation to 8. You will need to change this value to use the driver.