[RSS] [quick] Home » Hibernate with SQLite

Hibernate with SQLite

By qless on Tuesday 24 November 2009 16:56 - Comments (16)
Categories: Java, Programming, Views: 9.877

Hibernate is a very use-full framework, but sometimes you don't want to install a full-blown database, but for some reason hibernate does not default support SQLite. With some searching and extending however its possible. Here is a working solution I've made :)

The most important file, and only file you'll extra need is SQLiteDialect:

Java: SQLiteDialect.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
import java.sql.Types;
import org.hibernate.Hibernate;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;

/**
 *
 * @author Arno Raps
 */

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.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 hasDataTypeInIdentityColumn() {
        return false;
    }

    public String getIdentityColumnString() {
        return "integer";
    }

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

    public boolean supportsLimit() {
        return true;
    }

    protected 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;
    }

    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;
    }
}


And in your hibernate config:

Java:
1
cfg.setProperty("hibernate.dialect", "package.SQLiteDialect");

or in config file:

XML:
1
<property name="dialect">package.SQLiteDialect</property>

Volgende: Provinciaal ziekenhuis 11-'09 Provinciaal ziekenhuis
Volgende: Wifi & wifi 11-'09 Wifi & wifi

Comments

By T.net user YopY, Tuesday 24 November 2009 19:40

Neat, and simple. In fact, it's so simple, I don't understand why it's not there by default - did you double check that? Is there any particular reason they wouldn't add this?

By T.net user qless, Wednesday 25 November 2009 09:30

Don't know...but I found it very strange that nHibernate (.net framework) did support SQLite and the hibernate framework didn't.

By T.net user Snake, Wednesday 25 November 2009 15:00

I can get so angry at framework developers.

Why do you need to specify your package twice? There is no purpose in that.

By T.net user qless, Wednesday 25 November 2009 15:07

What do you mean with twice?

By Seb, Monday 18 January 2010 15:22

Hi,
I'm new in hibernate framework.
Can you send your 'hibernate.cfg.xml' to have a complete example
tank you for your light :)

By Rainer, Sunday 05 September 2010 15:11

Many thanks for this article,

trying this dialect for SQLite works fine, until I try to use types like "numeric" or "decimal". Setting a breakpoint within the constructor of SQLiteDialect shows, that the "org.hibernate.MappingException: Could not determine type for: numeric, at..." exception is thrown before the constructor is called. After I change the column type to "integer" everything is fine until I try to run a query over that table.

I'm using xerial sqlite-jdbc 3.6.0 with hibernate 3.2.6.
What's wrong? Any hints?

By Rainer, Monday 06 September 2010 21:53

I've found my fault. I was using a wrong type "decimal" or "numeric", which must be called "big_decimal" instead. Thats fine so far. No I try to use a patch from "http://github.com/gwenn/sqlitejdbc" to support BigDecimal.

By Taygun Kekec, Sunday 17 October 2010 20:23

Thank you very much, it works perfectly.

If you do need to use a full hibernate config file, you can have a look at how to use properties from http://code.google.com/p/hibernate-sqlite/ In this project there is hibernate xml config file for sqlite.

By Oleg Yakovenko, Monday 10 January 2011 22:11

Dialect is gr8 but i still get the error :
528 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - could not complete schema update
org.hibernate.exception.GenericJDBCException: could not get table metadata: %TableName%

If no table exists in sqlite database SchemaUpdate works fine. But if table exists an error occurs. How to fix it for sqlite? What else should i add to the config file except
<property name="hbm2ddl.auto">update</property>

By T.net user qless, Tuesday 11 January 2011 10:11

Update isn't supported, change too:

code:
1

<property name="hbm2ddl.auto">create</property>


That should work

By Oleg Yakovenko, Tuesday 11 January 2011 17:54

Thx for responding.
If it is not supported, then why it creates tables (when update value is specified) but only when tables don't exist?

By T.net user qless, Wednesday 12 January 2011 08:44

basic function is to create (and drop) tables, altering would require some extensions to the sqlite driver.

By Otto, Saturday 05 March 2011 02:32

What are the username, password and url?

By T.net user qless, Sunday 06 March 2011 21:30

Example:

code:
1
2
3
4
5
6
7
8
9

            Configuration cfg = new Configuration();
            cfg.setProperty("hibernate.show_sql", "true");
            cfg.setProperty("hibernate.format_sql", "true");
            cfg.setProperty("hibernate.dialect", "your.packageSQLiteDialect");
            cfg.setProperty("hibernate.connection.driver_class", "org.sqlite.JDBC");
            cfg.setProperty("hibernate.connection.url", "jdbc:sqlite:"+file.toString());
             if (create) cfg.setProperty("hibernate.hbm2ddl.auto", "create");
            cfg.addClass(....class);
            sessionFactory = cfg.buildSessionFactory();

By sylvie, Monday 20 June 2011 10:25

Thank you very much for the SQLiteDialect class, it was very helpful!

I think I found a bug when working with setFirstResult and setMaxResults:
The values for setFirstResult and setMaxResults are used in the wrong order, the query starts always at the same offset and the size of the resultset is increasing:
  • Query with setFirstResult=200 and setMaxResults=100 queries the database with limit=200 and offset =100.
  • Query with setFirstResult=300 and setMaxResults=100 queries the database with limit=300 and offset =100.
The reason for this behavior is:
SQLite supports two different ways for specifying limit and offset where the parameters are used in reverse order:

http://stackoverflow.com/...-limit-offset-query-doubt
http://dev.mysql.com/doc/refman/5.1/en/select.html

The syntax LIMIT <count> OFFSET <skip> is the same as used in PostgreSQLDialect, but the class PostgreSQLDialect contains the following method to indicate the reverse order:


code:
1
2
3

    public boolean bindLimitParametersInReverseOrder() {
        return true;
    }


The syntax LIMIT <skip>, <count> is the same as used in MySQL, but the class MySQLDialect does not override the bindLimitParametersInReverseOrder method.

The bug can be fixed with overriding the method bindLimitParametersInReverseOrder the same way as in the PostgreSQLDialect.
Comment form
(required)
(required, but will not be displayed)
(optional)

Please enter the code from the image below:

UBB faq

Recent posts

Categories

Archives

About

My attempt to blog something usefull.

I have a portfolio website too:
arnoraps.com

and a website too:
hierzo.net

and even flickr:
flickr