Spring boot and SQLite

Question

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??

Baeldung posted [a guide about Spring Boot and SQLite](https://www.baeldung.com/spring-boot-sqlite) by now. — cheffe, Dec 10 '18 at 10:15

geoand · Accepted Answer · 2014-06-15T20:19:41.247

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.

I did not consider the use of H2 for example. I wanted to go for a database with a low memory footprint because it has to run on a embedded device. But you remarks triggered me to look at H2 also. — Marco, Jun 16 '14 at 06:17
@Marco I can't really tell you which embedded DB would be best for your environment, but Spring Boot will make it to change between them and find out :) — geoand, Jun 16 '14 at 06:19
I get the error java.lang.IllegalStateException: No supported DataSource type found when configuring the Data Source — Cenobyte321, Dec 26 '14 at 03:33
how do you know with which DB's spring boot work out of the box? — PowerFlower, May 27 '17 at 14:07
The constraining factor here is the underlying JPA implementation (mostlikely Hibernate). Check out [this](https://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/dialect/package-summary.html) — geoand, May 29 '17 at 07:44
SQLite does actually support foreign keys, it just has to be enabled via a PRAGMA statement. — Andrew Lalis, Aug 21 '19 at 05:16
Just in case anyone has the problem that data is gone once the app shuts down, use 'spring.jpa.hibernate.ddl-auto=none' to persist the data. (Or else dont define at all) — Austin, Jan 20 '20 at 12:09

score 11 · Answer 2 · edited May 31 '16 at 04:17

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

score 0 · Answer 3 · answered Mar 12 '23 at 04:43

Add dependency:

<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.41.0.0</version>
</dependency>

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

Update 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

Start the server and you should be able to do DB operations. THe DB file name .db will be created on the root folder.

Spring boot and SQLite

3 Answers3

Linked