-
-
Save virasak/54436 to your computer and use it in GitHub Desktop.
/* | |
* 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. | |
* | |
*/ | |
package org.hibernate.dialect; | |
import java.sql.Types; | |
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() { | |
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; | |
} | |
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; // 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; | |
} | |
} |
For anybody interested, this Dialect that user virasak has posted is actually the first commit of user gwenn's repository https://github.com/gwenn/sqlite-dialect/ which dates back to at least January 2009, if not earlier on. (Gwenn has put a line in the README.md file that says the repository was first posted in March 2008.) Gwenn has been licensing this Dialect under the Unlicense http://unlicense.org/
I wonder why the developers of Hibernate haven't implemented this Dialect...
I found a bug when using org.springframework.data.jpa.repository.JpaRepository.findAll(Pageable)
.
So I fixed the getLimitString
method.
Before:
protected String getLimitString(String query, boolean hasOffset) {
return new StringBuffer(query.length()+20).
append(query).
append(hasOffset ? " limit ? offset ?" : " limit ?").
toString();
}
After:
protected String getLimitString(String query, boolean hasOffset) {
return new StringBuffer(query.length()+20).
append(query).
append(hasOffset ? " limit ?, ?" : " limit ?").
toString();
}
I found a bug when using
org.springframework.data.jpa.repository.JpaRepository.findAll(Pageable)
.
So I fixed thegetLimitString
method.Before:
protected String getLimitString(String query, boolean hasOffset) { return new StringBuffer(query.length()+20). append(query). append(hasOffset ? " limit ? offset ?" : " limit ?"). toString(); }After:
protected String getLimitString(String query, boolean hasOffset) { return new StringBuffer(query.length()+20). append(query). append(hasOffset ? " limit ?, ?" : " limit ?"). toString(); }
You can also add:
@Override
public boolean bindLimitParametersInReverseOrder() {
return true;
}
Small review on SQLite dialects:
Hibernate 3
https://github.com/kemitix/sqlite-dialect
Hibernate 4
https://github.com/EnigmaBridge/hibernate4-sqlite-dialect
Note: Mine repo. Based on the gwenn repo.
Hibernate 5
https://github.com/gwenn/sqlite-dialect/
Not yet added to the maven central repository. Author works with Hibernate team to integrate it to the Hibernate directly.