SQLite - Java

Modern SQLite with Java

This guide demonstrates the modern, best-practice approach to using SQLite in Java applications, focusing on dependency management, resource safety, and secure coding.

Project Setup with a Build Tool

Manually downloading JAR files is an outdated and error-prone practice. Modern Java projects use build automation tools like Maven or Gradle to manage dependencies.

Maven Dependency

Add the following dependency to your pom.xml file. This will automatically download the SQLite JDBC driver.

<!-- pom.xml -->
<dependencies>
    <dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.45.1.0</version> <!-- Check for the latest version -->
    </dependency>
</dependencies>

Gradle Dependency

For Gradle projects, add this to your build.gradle file:

build.gradle

dependencies {
    implementation 'org.xerial:sqlite-jdbc:3.45.1.0' // Check for the latest version
}

Connecting to the Database

With modern JDBC 4.0+ drivers, you no longer need Class.forName(). The driver is detected automatically from the classpath. A robust connection method should be centralized.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseManager {
    private static final String DB_URL = "jdbc:sqlite:company.db";

    public static Connection connect() throws SQLException {
        return DriverManager.getConnection(DB_URL);
    }
}

Best Practices: try-with-resources and PreparedStatement

To prevent resource leaks, always use the try-with-resources statement. To prevent SQL injection vulnerabilities, always use PreparedStatement for queries with parameters.

Complete CRUD Example with a DAO

Let’s structure our code using a Data Access Object (DAO) pattern. This separates database operations from our main application logic, making the code cleaner and easier to test.

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// A simple data class for our Employee
record Employee(int id, String name, int age, String address, double salary) {}

// The DAO class handling all database operations
class EmployeeDAO {

    public void createTable() {
        String sql = "CREATE TABLE IF NOT EXISTS employees (\n"
                + " id INTEGER PRIMARY KEY,\n"
                + " name TEXT NOT NULL,\n"
                + " age INTEGER NOT NULL,\n"
                + " address TEXT,\n"
                + " salary REAL\n"
                + ");";

        try (Connection conn = DatabaseManager.connect();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("Table 'employees' is ready.");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public void insert(Employee employee) {
        String sql = "INSERT INTO employees(name, age, address, salary) VALUES(?,?,?,?)";

        try (Connection conn = DatabaseManager.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, employee.name());
            pstmt.setInt(2, employee.age());
            pstmt.setString(3, employee.address());
            pstmt.setDouble(4, employee.salary());
            pstmt.executeUpdate();
            System.out.println(employee.name() + " inserted.");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public List<Employee> selectAll() {
        String sql = "SELECT id, name, age, address, salary FROM employees";
        List<Employee> employees = new ArrayList<>();

        try (Connection conn = DatabaseManager.connect();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                employees.add(new Employee(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getInt("age"),
                    rs.getString("address"),
                    rs.getDouble("salary"))
                );
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        return employees;
    }

    public void updateSalary(int id, double newSalary) {
        String sql = "UPDATE employees SET salary = ? WHERE id = ?";

        try (Connection conn = DatabaseManager.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, newSalary);
            pstmt.setInt(2, id);
            int affectedRows = pstmt.executeUpdate();
            System.out.println(affectedRows + " record(s) updated.");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }

    public void delete(int id) {
        String sql = "DELETE FROM employees WHERE id = ?";

        try (Connection conn = DatabaseManager.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            int affectedRows = pstmt.executeUpdate();
            System.out.println(affectedRows + " record(s) deleted.");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
    }
}

// Main application class to run the demo
public class Main {
    public static void main(String[] args) {
        EmployeeDAO dao = new EmployeeDAO();

        // 1. Setup
        dao.createTable();

        // 2. INSERT (Create)
        dao.insert(new Employee(0, "Paul", 32, "California", 20000.00));
        dao.insert(new Employee(0, "Allen", 25, "Texas", 15000.00));
        dao.insert(new Employee(0, "Teddy", 23, "Norway", 20000.00));

        // 3. SELECT (Read)
        System.out.println("\n--- Current Employees ---");
        dao.selectAll().forEach(System.out::println);

        // 4. UPDATE
        System.out.println("\n--- Updating Paul's Salary ---");
        dao.updateSalary(1, 25000.00);
        dao.selectAll().forEach(System.out::println);

        // 5. DELETE
        System.out.println("\n--- Deleting Allen ---");
        dao.delete(2);
        dao.selectAll().forEach(System.out::println);
    }
}

Testing Strategy

To test the EmployeeDAO, you can use a testing framework like JUnit 5. A common practice is to configure the JDBC URL to use an in-memory database (jdbc:sqlite::memory:) for tests. This ensures your tests are fast, isolated, and don’t leave artifacts on the filesystem.