Does Hibernate Fully Support SQLite

Question

Jboss Hibernate doesn't say anything about the support for SQLite -

https://community.jboss.org/wiki/SupportedDatabases2

And same is mentioned in below SO:

Hibernate+SQLite+Netbeans

Can you please highlight about this. I want to use embedded SQLite with hibernate for swing desktop application.

I am also evaluating Derby (JavaDB) as it also can be embedded and is part of the jdk.

Answer 1

There are several SQLite dialects out there.

Hibernate 3:

https://github.com/kemitix/sqlite-dialect

<dependency>
    <groupId>net.kemitix</groupId>
    <artifactId>sqlite-dialect</artifactId>
    <version>0.1.0</version>
</dependency>  

Hibernate configuration:

hibernate.dialect = org.hibernate.dialect.SQLiteDialect

Hibernate 4:

https://github.com/EnigmaBridge/hibernate4-sqlite-dialect

<dependency>
    <groupId>com.enigmabridge</groupId>
    <artifactId>hibernate4-sqlite-dialect</artifactId>
    <version>0.1.2</version>
</dependency>

Hibernate configuration:

hibernate.dialect = com.enigmabridge.hibernate.dialect.SQLiteDialect

Note: I am author of this repository. Based on the gwenn repo.

Hibernate 5:

https://github.com/gwenn/sqlite-dialect/

Author worked with Hibernate team to integrate it to the Hibernate directly. It is tracked in this issue.

Add this dependency:

    <dependency>
        <groupId>com.github.gwenn</groupId>
        <artifactId>sqlite-dialect</artifactId>
        <version>0.1.2</version>
    </dependency>

Hibernate configuration:

hibernate.dialect = org.sqlite.hibernate.dialect.SQLiteDialect

Answer 2

Since SQLite is an embedded database for C-like environments, written in C and thus compiled to native code, changes that Hibernate (or any ORM) will support aren't really high. Java is cross-platform and it would be a bit weird to have a platform-dependent dependency. On Android, SQLite is used, but there the platform supplies a JDBC driver for it.

Usually, Windows binaries are compatible over different Windows versions - as long as the architecture stays the same. If you look at the SQLite download page you'll notice there's a 32-bit pre-built Windows binary. This one can be used on almost any Windows version (except Windows RT, maybe), but you cannot use it on Linux or OS X. In order to use SQLite from Java, you would need to include the correct binary for the specific OS / architecture, effectively making a Java application platform-dependent. That is something you usually don't want.

If you're building a desktop application in Swing and you want to use an embedded database, my suggestion would be to use a Java embedded database, like H2, HSQL or Derby. The latter is also shipped with Oracle Java as JavaDB. All are supported as hibernate dialects (For a full list of dialects, see the dialect classes: https://github.com/hibernate/hibernate-orm/tree/master/hibernate-core/src/main/java/org/hibernate/dialect)

As noted below by @akostadinov, there may be other factors you want to consider, for example the fact that SQLite is written in native code and hence may have better performance. In the end, the only one who can decide which database is best is the one who is building the system.

Answer 3

If you need to use SQLite with Hibernate for whatever reason then you need a custom dialect. It looks like there are a couple of implementations kicking around on the web.

I tried this one: https://github.com/gwenn/sqlite-dialect which worked for me with Hibernate 3 (I believe that it needs some updates for Hibernate 4). Note that you'll have to compile the code yourself and then set the hibernate.dialect configuration property to be org.hibernate.dialect.SQLiteDialect.

Other implementations that I found: http://code.google.com/p/hibernate-sqlite/ and https://gist.github.com/virasak/54436.

Answer 4

SQLite is very forgiving about SQL syntax as far as I have experience with so it may work. I have not used SQLite extensively with hibernate but tried to use it with infinispan with some success. The stock SQLite jdbc driver that I found as the best recommended, lacks some of JDBC standard's methods so I had to patch it to have it working.

I think that you can try it with hibernate and see if it works. It may help you to see what I did to have it working with infinispan: https://issues.jboss.org/browse/ISPN-2980

I know this is not the exact answer you would like to see but I decided to answer because I think there is little chance anybody else to answer in a more helpful way. At least when I was searching for more information at the time I couldn't find.