Problems with dialect SQLite 3 with Hibernate 5

java hibernate sqlite maven jpa

15,813

I am using SQLite 3.10 with Hibernate 5.2 with the following configuration:

pom.xml:

<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.20.1</version>
    <scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.zsoltfabok/sqlite-dialect -->
<dependency>
    <groupId>com.zsoltfabok</groupId>
    <artifactId>sqlite-dialect</artifactId>
    <version>1.0</version>
</dependency>

persistence.xml:

<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="first_test" transaction-type="RESOURCE_LOCAL">
        <properties>
            <property name="hibernate.archive.autodetection" value="class" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" />
            <property name="hibernate.connection.driver_class" value="org.sqlite.JDBC" />
            <property name="hibernate.connection.username" value="" />
            <property name="hibernate.connection.password" value="" />
            <property name="hibernate.connection.user" value="" />
            <property name="hibernate.connection.autocommit" value="true"/>
            <property name="hibernate.connection.url" value="jdbc:sqlite:sqlite.db"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.flushMode" value="ALWAYS" />
            <property name="hibernate.cache.use_second_level_cache" value="false" />
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />

            <!-- create https://docs.jboss.org/hibernate/orm/5.0/manual/en-US/html/ch03.html -->
            <property name="hibernate.hbm2ddl.auto" value="validate or create" />
        </properties>
    </persistence-unit>

</persistence>

Of course you have to change the value of hibernate.connection.url and hibernate.hbm2ddl.auto and maybe other properties to your needs.

15,813

Author by

Iraponti

Updated on June 18, 2022

Comments

Iraponti almost 2 years

I'm facing some problems with Hibernate-JPA-Maven

It's the first time I use JPA and hibernate and I have some problems : I have a database that I have created with JPA and I would like to make some CRUD tests on it with JUnit5. With the pom.xml I add dependency to work with a SQLite dialect and hibernate, and I have a persistence.xml file. I use Eclipse to run the project to see if the first simple test works and i have this problem :

oct. 28, 2017 2:42:32 PM org.hibernate.jpa.internal.util.LogHelper logPersistenceUnitInformation INFO: HHH000204: Processing PersistenceUnitInfo [ name: Bla ...] oct. 28, 2017 2:42:32 PM org.hibernate.Version logVersion INFO: HHH000412: Hibernate Core {5.2.12.Final} oct. 28, 2017 2:42:32 PM org.hibernate.cfg.Environment INFO: HHH000206: hibernate.properties not found oct. 28, 2017 2:42:32 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager INFO: HCANN000001: Hibernate Commons Annotations {5.0.1.Final} oct. 28, 2017 2:42:33 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!) oct. 28, 2017 2:42:33 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator INFO: HHH10001005: using driver [org.sqlite.JDBC] at URL [jdbc:sqlite::memory:] oct. 28, 2017 2:42:33 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator INFO: HHH10001001: Connection properties: {charSet=UTF-8, password=****, user=test} oct. 28, 2017 2:42:33 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator INFO: HHH10001003: Autocommit mode: false oct. 28, 2017 2:42:33 PM org.hibernate.engine.jdbc.connections.internal.PooledConnections INFO: HHH000115: Hibernate connection pool size: 20 (min=1) oct. 28, 2017 2:42:33 PM org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator initiateService WARN: HHH000342: Could not obtain connection to query metadata : Unable to determine Dialect to use [name=SQLite, majorVersion=3]; user must register resolver or explicitly set 'hibernate.dialect' Exception in thread "main" org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] at . . . Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set at . . . ... 14 more

So I have i try to find out why I have :

WARN: HHH000342: Could not obtain connection to query metadata : Unable to determine Dialect to use [name=SQLite, majorVersion=3]; user must register resolver or explicitly set 'hibernate.dialect'

Apparently this is because SQLite 3 and Hibernate 5 are not compatible, so I try this : Does Hibernate Fully Support SQLite but it was not working for me.

Then I try to take a lower version of hibernate and modify in my pom.xml the hibernate version and hibernate-core versions to 4.3.11.Final but same, it doesn't solved my problems.

After many hours on that, I can't figure it out if the problems comes from the version of hibernate and SQLite, or if this is a problem of the configuration in my pom.xml (a missing dependency ?) or it's my persistance.xml, or this comes from my class Test.

File tree of my project

Please let me know if you need more details. Thank you in advance for your help.

My pom.xml file :

<project
    xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>fr.umlv.orthopro</groupId>
  <artifactId>OrthoPro_brain</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>OrthoPro_brain</name>
  <url>http://maven.apache.org</url>

  <properties>
     <java.version>9</java.version>
     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
     <junit.jupiter.version>5.0.1</junit.jupiter.version>
     <junit.platform.version>1.0.1</junit.platform.version>
     <hibernate.version>5.2.12.Final</hibernate.version>
     <sqlite.version>3.20.1</sqlite.version>
  </properties>

  <build>
     <plugins>
         <plugin>
             <groupId>org.apache.maven.plugins</groupId>
             <artifactId>maven-compiler-plugin</artifactId>
             <version>3.7.0</version>
             <configuration>
                 <source>${java.version}</source>
                 <target>${java.version}</target>
                 <showWarnings>true</showWarnings>
                 <showDeprecation>true</showDeprecation>
             </configuration>
         </plugin>
         <plugin>
             <groupId>org.apache.maven.plugins</groupId>
             <artifactId>maven-javadoc-plugin</artifactId>
             <version>3.0.0-M1</version>
             <configuration>
                 <reportOutputDirectory>${project.build.directory}/javadoc</reportOutputDirectory>
                 <destDir>javadoc</destDir>
                 <nohelp>true</nohelp>
             </configuration>
         </plugin>
     </plugins>
  </build>
  <dependencies>
     <dependency>
         <groupId>org.junit.jupiter</groupId>
         <artifactId>junit-jupiter-engine</artifactId>
         <version>${junit.jupiter.version}</version>
         <scope>test</scope>
     </dependency>
     <dependency>
         <groupId>org.junit.platform</groupId>
         <artifactId>junit-platform-runner</artifactId>
         <version>${junit.platform.version}</version>
         <scope>test</scope>
     </dependency>
     <dependency>
         <groupId>org.hibernate</groupId>
         <artifactId>hibernate-core</artifactId>
         <version>${hibernate.version}</version>
     </dependency>
     <dependency>
         <groupId>org.xerial</groupId>
         <artifactId>sqlite-jdbc</artifactId>
         <version>${sqlite.version}</version>
         <!-- <scope>test</scope> -->
     </dependency>
    <dependency>
        <groupId>org.hibernate.javax.persistence</groupId>
        <artifactId>hibernate-jpa-2.1-api</artifactId>
        <version>1.0.0.Final</version>
    </dependency>
    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>org.eclipse.persistence.jpa</artifactId>
        <version>2.7.0</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>5.2.12.Final</version>
    </dependency>
    <dependency>
        <groupId>javax.xml.bind</groupId>
        <artifactId>jaxb-api</artifactId>
        <version>2.3.0</version>
    </dependency>

  </dependencies>
 </project>

My persistence.xml file :

 <persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
                 http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
                 version="2.1">

        <persistence-unit name="first_test" transaction-type="RESOURCE_LOCAL">
          <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
           <class>fr.umlv.orthpro.db.User</class>
           <class>fr.umlv.orthpro.db.Rule</class>
           <class>fr.umlv.orthpro.db.UserRule</class>
           <class>fr.umlv.orthpro.db.Sentence</class>

            <properties>
                <property name="dialect" value="org.hibernate.dialect.SQLiteDialect" />
                <property name="javax.persistence.jdbc.driver" value="org.sqlite.JDBC" />
                <property name="javax.persistence.jdbc.url" value="jdbc:sqlite::memory:" />
                <property name="javax.persistence.jdbc.user" value="test" />
                <property name="javax.persistence.jdbc.password" value="test" />
                <property name="hibernate.show_sql" value="true" />
                <property name="format_sql" value="true" />
                <property name="hibernate.connection.charSet" value="UTF-8" />
                <property name="hibernate.hbm2ddl.auto" value="create" />
            </properties>
        </persistence-unit>
    </persistence>

This is my class to run test on the database :

package fr.umlv.orthopro.db;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public class Test {
      EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "first_test" );

      EntityManager entitymanager = emfactory.createEntityManager( );
      entitymanager.getTransaction( ).begin( );

      User quentin = new User( ); 
      quentin.setId(1201);
      quentin.setAdmin(false);

      entitymanager.persist( quentin );
      entitymanager.getTransaction( ).commit( );
      entitymanager.close( );
      emfactory.close( );
    }
}

EDIT : I change
<property name="dialect" value="org.hibernate.dialect.SQLiteDialect" /> to
<property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect" /> and now I have this :