我正在尝试使用SQLite与一个Sping Boot 应用程序。我知道在Spring Boot的真棒支持,例如MongoDB。但我找不到一种方法来使用Spring Boot与SQLite?任何建议,从哪里或如何开始使用Spring Boot和SQLite?
xbp102n01#
Boot 不能在SQLite中开箱即用(例如,它在H2、HSQL或ApacheDerby中也是如此--我建议您使用其中的任何一种来代替SQLite)。首先你需要覆盖数据源来指定你的SQLite数据源。在你的配置中使用下面的代码(使用Sping Boot 1.1.0.M2中引入的DataSourceBuilder)
DataSourceBuilder
@Bean public DataSource dataSource() { DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.driverClassName("org.sqlite.JDBC"); dataSourceBuilder.url("jdbc:sqlite:your.db"); return dataSourceBuilder.build(); }
然后您需要创建一个SQLiteDialect,因为Hibernate还没有(基于here中的代码,但适用于Hibernate 4)
package your.package 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; import org.hibernate.type.StringType; 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(StringType.INSTANCE, "", "||", "") ); registerFunction( "mod", new SQLFunctionTemplate( StringType.INSTANCE, "?1 % ?2" ) ); registerFunction( "substr", new StandardSQLFunction("substr", StringType.INSTANCE) ); registerFunction( "substring", new StandardSQLFunction( "substr", StringType.INSTANCE) ); } 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; } }
最后,在application.properties中覆盖以下设置
application.properties
spring.jpa.database-platform=your.package.SQLiteDialect spring.jpa.hibernate.ddl-auto=create-drop
为了让Sping Boot 通知Hibernate它应该使用上面创建的SQLiteDialect,需要第一个设置。
qvk1mo1f2#
除了@geoand提供的很好的答案之外,DataSourceBuilder还希望您的类路径上有以下类之一:
org.apache.tomcat.jdbc.pool.DataSource com.zaxxer.hikari.HikariDataSource org.apache.commons.dbcp.BasicDataSource
或者,您可以通过设置以下项来覆盖类搜索:dataSourceBuilder.type(org.sqlite.SQLiteDataSource.class);否则,将导致No supported DataSource type found错误。您可能还需要设置hibernate.dialect=your.package.SQLiteDialect
dataSourceBuilder.type(org.sqlite.SQLiteDataSource.class);
No supported DataSource type found
hibernate.dialect=your.package.SQLiteDialect
wgeznvg73#
添加依赖项:
<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc --> <dependency> <groupId>org.xerial</groupId> <artifactId>sqlite-jdbc</artifactId> <version>3.41.0.0</version> </dependency>
创建类SQLDialect
package <your package>; import org.hibernate.dialect.Dialect; public class SQLDialect extends Dialect { public SQLDialect() {} public boolean supportsIdentityColumns() { return true; } public boolean hasDataTypeInIdentityColumn() { return false; // As specify in NHibernate dialect } 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; } }
更新application.properties:
spring.datasource.url=jdbc:sqlite:<db-name>.db spring.datasource.driver-class-name=org.sqlite.JDBC spring.jpa.database-platform=<package>.SQLDialect spring.jpa.hibernate.ddl-auto=update
启动服务器,您应该能够执行数据库操作。数据库文件名.db将在根文件夹中创建。
3条答案
按热度按时间xbp102n01#
Boot 不能在SQLite中开箱即用(例如,它在H2、HSQL或ApacheDerby中也是如此--我建议您使用其中的任何一种来代替SQLite)。
首先你需要覆盖数据源来指定你的SQLite数据源。在你的配置中使用下面的代码(使用Sping Boot 1.1.0.M2中引入的
DataSourceBuilder
)然后您需要创建一个SQLiteDialect,因为Hibernate还没有(基于here中的代码,但适用于Hibernate 4)
最后,在
application.properties
中覆盖以下设置为了让Sping Boot 通知Hibernate它应该使用上面创建的SQLiteDialect,需要第一个设置。
qvk1mo1f2#
除了@geoand提供的很好的答案之外,DataSourceBuilder还希望您的类路径上有以下类之一:
或者,您可以通过设置以下项来覆盖类搜索:
dataSourceBuilder.type(org.sqlite.SQLiteDataSource.class);
否则,将导致
No supported DataSource type found
错误。您可能还需要设置
hibernate.dialect=your.package.SQLiteDialect
wgeznvg73#
添加依赖项:
创建类SQLDialect
更新application.properties:
启动服务器,您应该能够执行数据库操作。数据库文件名.db将在根文件夹中创建。