Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring boot and SQLite

I am trying to use SQLite with a Spring Boot app. I am aware of the awesome support in Spring Boot with for example MongoDB. But i cannot find a way to use Spring Boot with SQLite? Any suggestion where or how to start with using Spring Boot and SQLite??

like image 829
Marco Avatar asked Jun 15 '14 18:06

Marco


People also ask

Can you use SQLite in spring boot?

Spring Boot doesn't work out of the box with SQLite (as it does for example with H2, HSQL or Apache Derby - any of which which I would suggest you use instead of SQLite). The first setting is needed in order for Spring Boot to inform Hibernate that it should use the SQLiteDialect that was created above.

Does Hibernate support SQLite?

See, Hibernate doesn't ship with a Dialect for SQLite. We need to create one ourselves.

What is spring boot data JPA?

Spring Boot JPA is a Java specification for managing relational data in Java applications. It allows us to access and persist data between Java object/ class and relational database. JPA follows Object-Relation Mapping (ORM). It is a set of interfaces.


2 Answers

Spring Boot doesn't work out of the box with SQLite (as it does for example with H2, HSQL or Apache Derby - any of which which I would suggest you use instead of SQLite).

First of all you need to override the data source to specify you SQLite data souce. Use the following code in your configuration (uses DataSourceBuilder which was introduced in Spring Boot 1.1.0.M2)

@Bean public DataSource dataSource() {         DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();         dataSourceBuilder.driverClassName("org.sqlite.JDBC");         dataSourceBuilder.url("jdbc:sqlite:your.db");         return dataSourceBuilder.build();    } 

Then you need to create an SQLiteDialect because Hibernate does not already have one (based on the code from here but adapted for 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;     } } 

Finally in application.properties override the following settings

spring.jpa.database-platform=your.package.SQLiteDialect spring.jpa.hibernate.ddl-auto=create-drop 

The first setting is needed in order for Spring Boot to inform Hibernate that it should use the SQLiteDialect that was created above.

like image 185
geoand Avatar answered Sep 21 '22 22:09

geoand


In addition to the great answer provided by @geoand, the DataSourceBuilder expects one of the following classes to be on your class path:

org.apache.tomcat.jdbc.pool.DataSource com.zaxxer.hikari.HikariDataSource org.apache.commons.dbcp.BasicDataSource 

Or you can override the class search by setting: dataSourceBuilder.type(org.sqlite.SQLiteDataSource.class);

If you don't, it results in the No supported DataSource type found error.

You might also need to set hibernate.dialect=your.package.SQLiteDialect

like image 36
adkisson Avatar answered Sep 22 '22 22:09

adkisson