在本地使用SQLite

有没有同学有这样的需求，我想把数据存储到本地，还要求本地的数据能像DB一样查询出来，我的项目到哪都能跑起来，但又不想部署一个MySQL。

SQLite

官网

下面介绍如何在Java中内置使用SQLite

1.引入依赖

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

2.准备db文件存储位置，创建连接

public Connection getConn() throws ClassNotFoundException, IOException, SQLException {
    // 1. sqlite数据库文件存储位置
    String path = "D:\\code\\demo\\sqlite_demo";
    Path dir = Paths.get(path);
    if (!Files.exists(dir)) {
        Files.createDirectories(dir);
    }

    // 2. 创建连接
    String url = "jdbc:sqlite:" + path + "/inner.db";
    Connection conn = null;
    Class.forName("org.sqlite.JDBC");
    conn = DriverManager.getConnection(url);
    return conn;
}

3. 创建表

public void init() throws SQLException, IOException, ClassNotFoundException {
    try (Connection conn = getConn(); Statement stmt = conn.createStatement()) {
        // 创建表
        stmt.execute("CREATE TABLE IF NOT EXISTS test(t_id INTEGER PRIMARY KEY AUTOINCREMENT,t_name TEXT(255) NOT NULL)");
    }
}

4. 插入表记录

public void testInsert(long id, String name) throws SQLException, IOException, ClassNotFoundException {
    String sql = "insert into test(t_id,t_name) values (?,?)";
    try (Connection conn = getConn(); PreparedStatement psmt = conn.prepareStatement(sql)) {
        psmt.setLong(1, id);
        psmt.setString(2, name);
        psmt.execute();
    }
}

5. 查询表记录

public String testQuery(long id) throws IOException, ClassNotFoundException, SQLException {
    String tName = "";
    String sql = "select t_id,t_name from test where t_id=?";

    try (Connection conn = getConn(); PreparedStatement psmt = conn.prepareStatement(sql)) {
        psmt.setLong(1, id);
        try (ResultSet rs = psmt.executeQuery()) {
            while (rs.next()) {
                tName = rs.getString("t_name");
            }
        }
    }
    return tName;
}

代码汇总

package com.mkii.sqlite_demo;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;

@SpringBootTest
class SqliteDemoApplicationTests {

    @Test
    void contextLoads() throws IOException, ClassNotFoundException, SQLException {
        init();
        testInsert(1001, "mkii");
        String name = testQuery(1001);
        System.out.println(name);
    }

    public Connection getConn() throws ClassNotFoundException, IOException, SQLException {
        // 1. sqlite数据库文件存储位置
        String path = "D:\\code\\demo\\sqlite_demo";
        Path dir = Paths.get(path);
        if (!Files.exists(dir)) {
            Files.createDirectories(dir);
        }

        // 2. 创建连接
        String url = "jdbc:sqlite:" + path + "/inner.db";
        Connection conn = null;
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection(url);
        return conn;
    }

    public void init() throws SQLException, IOException, ClassNotFoundException {
        try (Connection conn = getConn(); Statement stmt = conn.createStatement()) {
            // 创建表
            stmt.execute("CREATE TABLE IF NOT EXISTS test(t_id INTEGER PRIMARY KEY AUTOINCREMENT,t_name TEXT(255) NOT NULL)");
        }
    }

    public void testInsert(long id, String name) throws SQLException, IOException, ClassNotFoundException {
        String sql = "insert into test(t_id,t_name) values (?,?)";
        try (Connection conn = getConn(); PreparedStatement psmt = conn.prepareStatement(sql)) {
            psmt.setLong(1, id);
            psmt.setString(2, name);
            psmt.execute();
        }
    }

    public String testQuery(long id) throws IOException, ClassNotFoundException, SQLException {
        String tName = "";
        String sql = "select t_id,t_name from test where t_id=?";

        try (Connection conn = getConn(); PreparedStatement psmt = conn.prepareStatement(sql)) {
            psmt.setLong(1, id);
            try (ResultSet rs = psmt.executeQuery()) {
                while (rs.next()) {
                    tName = rs.getString("t_name");
                }
            }
        }
        return tName;
    }


}

运行

这样我们就可以愉快地使用“本地DB”了