Integrate SQLite with Spring Boot

SQLite databases are compact, lightweight, file-centric databases frequently employed in embedded systems and modest applications. Spring Boot, a popular Java framework, provides easy integration with various databases, including SQLite. In this article, we'll explore how to connect SQLite with a Spring Boot application, including setting up dependencies, configuring the database connection, creating entities, and performing basic CRUD (Create, Read, Update, Delete) operations.

Create a Spring Boot Project

The first step is to create a new Spring Boot project. If you prefer, you can use Spring Initializr (https://start.spring.io/) or your preferred IDE.

Add SQLite Dependency

In your pom.xml file (for Maven), add the SQLite dependency:

<dependencies>
   <!-- SQLite JDBC Driver -->
   <dependency>
       <groupId>org.xerial</groupId>
       <artifactId>sqlite-jdbc</artifactId>
       <version>3.34.0</version>
   </dependency>

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

   <!-- Spring Boot Starter Data JPA -->
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-data-jpa</artifactId>
   </dependency>
</dependencies>

Configure SQLite Connection

Spring Boot allows us to configure the database connection in the application.properties file. Add the following properties for SQLite:

spring.application.name=sqlite
spring.datasource.url=jdbc:sqlite:mydatabase.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update

spring.datasource.url specifies the JDBC URL for the SQLite database. Here, mydatabase.db is the SQLite database file name.

spring.datasource.driver-class-name sets the driver class for SQLite.

spring.jpa.hibernate.ddl-auto configures Hibernate to automatically create or update the database schema.

spring.jpa.database-platform configure sqlite dialect for Hibernate

Create an Entity Class

Create a simple entity class representing a table in the SQLite database. For example, let's create a User class:

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class User {

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;

   private String firstName;
   private String lastName;

   // Getters and Setters
}

Create a Repository Interface

Create a repository interface that extends JpaRepository to perform CRUD operations on the User entity

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
}

Use UserRepository for CRUD Operations

Now, you can use the UserRepository to perform CRUD operations in your service or controller classes.

Example - Creating a User:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

   @Autowired
   private UserRepository userRepository;

   public User createUser(User user) {
       return userRepository.save(user);
   }
}

Example - Reading Users:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

   @Autowired
   private UserRepository userRepository;

   public List<User> getAllUsers() {
       return userRepository.findAll();
   }

   public User getUserById(Long id) {
       return userRepository.findById(id).orElse(null);
   }
}

Example - Updating a User:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

   @Autowired
   private UserRepository userRepository;

   public User updateUser(User updatedUser) {
       return userRepository.save(updatedUser);
   }
}

Example - Deleting a User:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

   @Autowired
   private UserRepository userRepository;

   public void deleteUser(Long id) {
       userRepository.deleteById(id);
   }
}

Run the Spring Boot Application

Now that everything is set up, you can run your Spring Boot application. Spring Boot will automatically connect to the SQLite database based on the configuration provided.

Source code available in Github

Conclusion

In this article, we've explored how to connect SQLite with a Spring Boot application. By adding the SQLite and Spring Boot Starter Data JPA dependencies, configuring the database connection in the application.properties file, creating an entity class (User), and using a JpaRepository (UserRepository), we can easily perform CRUD operations on SQLite tables. Spring Boot's auto-configuration simplifies the process of setting up the SQLite connection, allowing developers to focus on building the application's logic rather than handling the database setup. With this setup, it is easy to build Spring Boot applications that are SQLite-backed.