Hibernate 连接 SQLite (hibernate-sqlite) 分页bug的修复

众所周知, Hibernate 官方目前还不支持使用 SQLite 数据库, hibernate-sqlite 是一个让 Hibernate 可以支持 SQLite 数据库的项目, 这个项目提供了 Hibernate 的 SQLite 方言(Dialect) , 最后一次更新是 July 2010, 我在使用这个项目的时候发现在分页时有个 bug, 进行分页查询时, 不管怎么改变页码, 查询结果并不会改变.

SQLite 进行分页查询时的语句是:

1
SELECT xxx FROM xxx WHERE xxx LIMIT xxx

1
SELECT xxx FROM xxx WHERE xxx LIMIT xxx OFFSET xxx

使用中我发现用 hibernate-sqlite 提供的方言会将 setMaxResults(int) 设置的参数 bind 至 OFFSET , 使 setFirstResult(int) 设置的 OFFSET 失效, 也就是不管怎么改变页码, 查询结果并不会改变的原因.

仔细阅读 hibernate-sqlite 的 SQLiteDialect.java 和 org.hibernate.dialect.Dialect.java 后, 我发现 hibernate-sqlite 的方言没有重载 Dialect 的 bindLimitParametersInReverseOrder() 方法, 也就是反向捆绑 LIMIT 参数, 导致如上 bug.

修复这个 bug 的方法很简单, 就是重载 bindLimitParametersInReverseOrder() 方法, 修改返回值为 true, 如下:

1
2
3
public boolean bindLimitParametersInReverseOrder() {
return true;
}

完整的 SQLiteDialect.java 如下, 你可以直接替换掉 hibernate-sqlite 中的 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
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
package org.hibernate.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;
}
/**
* 反向捆绑 Limit 参数
* @return boolean
*/
public boolean bindLimitParametersInReverseOrder() {
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;
}
}

另外建议下载项目中的 HibernateSQLite_with_jar.zip 以防止Jar包版本不一致导致的异常.

使用中的问题欢迎大家在留言中与我讨论~