1. Overview

1.概述

In this quick tutorial, we’ll go through steps to use an SQLite database in a JPA-enabled Spring Boot application.

在这个快速教程中，我们将通过步骤在支持JPA的Spring Boot应用程序中使用SQLite>数据库。

Spring Boot supports a few well known in-memory databases out of the box, but SQLite requires a bit more from us.

Spring Boot 支持开箱即用的几个著名的内存数据库，但SQLite对我们的要求更高。

Let’s have a look at what it takes.

让我们来看看这需要什么。

2. Project Setup

2.项目设置

For our illustration, we’ll start with a Spring Data Rest app we’ve used in past tutorials.

对于我们的说明，我们将从Spring Data Rest应用程序开始，我们在过去的教程中已经使用过。

In the pom, we need to add the sqllite-jdbc dependency:

在pom中，我们需要添加sqllite-jdbc依赖项。

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.25.2</version>
</dependency>

This dependency gives us what we need to use JDBC to communicate with SQLite. But, if we are going to use an ORM, it’s not enough.

这一依赖关系为我们提供了使用JDBC与SQLite通信所需的条件。但是，如果我们要使用ORM，这还不够。。

3. SQLite Dialect

3.SQLite方言

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

看，Hibernate并没有提供Dialect的SQLite。我们需要自己创建一个。

3.1. Extending Dialect

3.1 扩展方言

Our first step is to extend org.hibernate.dialect.Dialect class to register the data types provided by SQLite:

我们的第一步是扩展org.hibernate.dialect.Dialect类来注册SQLite提供的数据类型。

public class SQLiteDialect extends Dialect {

    public SQLiteDialect() {
        registerColumnType(Types.BIT, "integer");
        registerColumnType(Types.TINYINT, "tinyint");
        registerColumnType(Types.SMALLINT, "smallint");
        registerColumnType(Types.INTEGER, "integer");
        // other data types
    }
}

There are several, so definitely check out the sample code for the rest.

有几个，所以一定要看一下其他的样本代码。

Next, we’ll need to override some default Dialect behaviors.

接下来，我们需要覆盖一些默认的Dialect行为。

3.2. Identity Column Support

3.2.身份栏支持

For example, we need to tell Hibernate how SQLite handles @Id columns, which we can do with a custom IdentityColumnSupport implementation:

例如，我们需要告诉Hibernate，SQLite如何处理@Id列，我们可以通过自定义IdentityColumnSupport实现来实现。

public class SQLiteIdentityColumnSupport extends IdentityColumnSupportImpl {

    @Override
    public boolean supportsIdentityColumns() {
        return true;
    }

    @Override
    public String getIdentitySelectString(String table, String column, int type) 
      throws MappingException {
        return "select last_insert_rowid()";
    }

    @Override
    public String getIdentityColumnString(int type) throws MappingException {
        return "integer";
    }
}

To keep things simple here, let’s keep the identity column type to Integer only. And to get the next available identity value, we’ll specify the appropriate mechanism.

为了保持简单，让我们保持身份列类型为Integer only。为了获得下一个可用的身份值，我们将指定适当的机制。

Then, we simply override the corresponding method in our growing SQLiteDialect class:

然后，我们简单地在我们成长中的SQLiteDialect类中覆盖相应的方法。

@Override
public IdentityColumnSupport getIdentityColumnSupport() {
    return new SQLiteIdentityColumnSupport();
}

3.3. Disable Constraints Handling

3.3.禁用约束条件处理

And, SQLite doesn’t have support for the database constraints, so we’ll need to disable those by again overriding the appropriate methods for both primary and foreign keys:

而且， SQLite不支持数据库约束，所以我们需要通过再次覆盖主键和外键的适当方法来禁用这些约束。

@Override
public boolean hasAlterTable() {
    return false;
}

@Override
public boolean dropConstraints() {
    return false;
}

@Override
public String getDropForeignKeyString() {
    return "";
}

@Override
public String getAddForeignKeyConstraintString(String cn, 
  String[] fk, String t, String[] pk, boolean rpk) {
    return "";
}

@Override
public String getAddPrimaryKeyConstraintString(String constraintName) {
    return "";
}

And, in just a moment, we’ll be able to reference this new dialect in our Spring Boot configuration.

稍后，我们就可以在Spring Boot的配置中引用这个新方言了。

4. DataSource Configuration

4.数据源配置

Also, since Spring Boot doesn’t provide configuration support for SQLite database out of the box, we also need to expose our own DataSource bean:

此外，由于Spring Boot没有为SQLite数据库提供开箱即用的配置支持，我们还需要公开我们自己的DataSourcebean。

@Autowired Environment env;

@Bean
public DataSource dataSource() {
    final DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(env.getProperty("driverClassName"));
    dataSource.setUrl(env.getProperty("url"));
    dataSource.setUsername(env.getProperty("user"));
    dataSource.setPassword(env.getProperty("password"));
    return dataSource;
}

And finally, we’ll configure the following properties in our persistence.properties file:

最后，我们将在我们的persistence.properties文件中配置以下属性。

driverClassName=org.sqlite.JDBC
url=jdbc:sqlite:memory:myDb?cache=shared
username=sa
password=sa
hibernate.dialect=com.baeldung.dialect.SQLiteDialect
hibernate.hbm2ddl.auto=create-drop
hibernate.show_sql=true

Note that we need to keep the cache as shared in order to keep the database updates visible across multiple database connections.

请注意，我们需要将缓存保持为shared，以保持数据库更新在多个数据库连接中可见。

So, with the above configurations, the app will start and will launch an in-memory database called myDb, which the remaining Spring Data Rest configuration can take up.

因此，通过上述配置，应用程序将启动并将启动一个名为myDb的内存数据库，其余的Spring Data Rest配置可以占用该数据库。

5. Conclusion

5.总结

In this article, we took a sample Spring Data Rest application and pointed it at an SQLite database. However, to do so, we had to create a custom Hibernate dialect.

在这篇文章中，我们采用了一个Spring Data Rest应用程序的样本，并将其指向了一个SQLite数据库。然而，要做到这一点，我们必须创建一个自定义的Hibernate方言。

Make sure to check out the application over on Github. Just run with mvn -Dspring.profiles.active=sqlite spring-boot:run and browse to http://localhost:8080.

请确保在Github上查看该应用程序over。只要用mvn -Dspring.profiles.active=sqlite spring-boot:run并浏览到http://localhost:8080。