Hibernate 5.0.2 with Sqlite -- cannot get it working due to errors

Question

While investigating Hibernate / JPA / ORM I found a HibernateHelloWorld Java application from the net.

Via Maven I use these libs:

   <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.0.2.Final</version>
    </dependency>
    <dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.15.0</version>
    </dependency>

[1] When running the app, my first issue was that no table could be created. OK, I created the table.

[2] Second issue was that the commit could not be done.

[3] Third issue is that the database keeps on being locked.

The hibernate config file is:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" 
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="dialect">org.hibernate.dialect.SQLiteDialect</property>
        <property name="connection.driver_class">org.sqlite.JDBC</property>
        <property name="connection.url">jdbc:sqlite:mydb.db</property>
        <property name="connection.username"></property>
        <property name="connection.password"></property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <mapping class="nl.deholtmans.HibernateHelloWorld.Contact"/>
    </session-factory>
</hibernate-configuration>

The POJO with annotations is:

@Entity
@Table(name = "contact")
public class Contact {
    private Integer id;
    private String name;
    private String email;

    public Contact() {

    }

    public Contact(Integer id, String name, String email) {
        this.id = id;
        this.name = name;
        this.email = email;
    }

    @Id
    public Integer getId() {
        return this.id;
    }
    // etc. 

The simple HibernateHelloWorld app is this:

public class App {
    private static SessionFactory sessionFactory = null;  
    private static SessionFactory configureSessionFactory() throws HibernateException {  
        sessionFactory = new Configuration()
            .configure() 
            .buildSessionFactory();
        return sessionFactory;
    }

    public static void main(String[] args) {
        configureSessionFactory();
        Session session = null;
        Transaction tx=null;
        try {
            session = sessionFactory.openSession();
            tx = session.beginTransaction();

            Contact myContact = new Contact(202, "My Name", "my_email@email.com");
            Contact yourContact = new Contact(203, "Your Name", "your_email@email.com");

            session.save(myContact);
            session.save(yourContact);
            session.flush();
            tx.commit();

            List<Contact> contactList = session.createQuery("from Contact").list();
            for (Contact contact : contactList) {
                System.out.println("Id: " + contact.getId() + " | Name:"  + contact.getName() + " | Email:" + contact.getEmail());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            tx.rollback();
        } finally{
            if(session != null) {
                session.close();
            }
        }
    }
}

Answer 1

To get Hibernate working in combination with Sqlite, I finally found:

[1] I used a newer version of hibernate-core: 5.1.2.

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>5.1.2.Final</version>
    </dependency>

[2] I used the following dialect and identity files:

https://github.com/gwenn/sqlite-dialect/tree/master/src/main/java/org/hibernate/dialect

[3] And I added column names to the POJO. I guess this was not the main break through. So, for example:

@Entity
@Table(name = "contact")
public class Contact {

    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "email")
    private String email;

    public Contact() {
    }
    // etc. 

==> I hope the creators of Hibernate can add a simple and good tutorial on integrating Hibernate with Sqlite.

Answer 2

your database seems to be locked. I found this topic:

Getting [SQLITE_BUSY] database file is locked with select statements

Hope this helps.