Spring Boot 和 SQLite

Spring boot and SQLite

提问by Marco

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

我正在尝试将 SQLite 与 Spring Boot 应用程序一起使用。我知道 Spring Boot 中的强大支持，例如 MongoDB。但是我找不到在 SQLite 中使用 Spring Boot 的方法？任何建议从何处或如何开始使用 Spring Boot 和 SQLite？

回答by geoand

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).

Spring Boot 不适用于 SQLite（就像 H2、HSQL 或 Apache Derby 一样，我建议您使用其中的任何一种来代替 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 DataSourceBuilderwhich was introduced in Spring Boot 1.1.0.M2)

首先，您需要覆盖数据源以指定您的 SQLite 数据源。在您的配置中使用以下代码（使用DataSourceBuilder在 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 herebut adapted for Hibernate 4)

然后你需要创建一个 SQLiteDialect 因为 Hibernate 还没有一个（基于这里的代码但适用于 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.propertiesoverride the following settings

最后在application.properties覆盖以下设置

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.

为了让 Spring Boot 通知 Hibernate 它应该使用上面创建的 SQLiteDialect，需要第一个设置。

回答by adkisson

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

除了@geoand 提供的出色答案之外，DataSourceBuilder 还希望您的类路径中包含以下类之一：

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);

或者您可以通过设置覆盖类搜索： dataSourceBuilder.type(org.sqlite.SQLiteDataSource.class);

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

如果不这样做，则会导致No supported DataSource type found错误。

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

您可能还需要设置 hibernate.dialect=your.package.SQLiteDialect