使用SQLite的Spring Boot

1. Overview

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

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

Let’s have a look at what it takes.

2. Project Setup

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

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

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

3. SQLite Dialect

From Hibernate 6, SQLite dialect is supported.

We have to import in our pom.xml:

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
</dependency>

And in our application properties:

spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect

4. DataSource Configuration

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:

@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:

driverClassName=org.sqlite.JDBC
url=jdbc:sqlite:memory:myDb?cache=shared 
username=sa 
password=sa
spring.jpa.database-platform=org.hibernate.community.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.

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.

5. Conclusion

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.

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