【五一创作】嵌入式Sqlite数据库【基本语法、Sqlite-JDBC、嵌入到Java程序】

目录

前言 

基本介绍

Sqlite 对比 MySQL

字段类型

语法

创建表

插入数据

更新数据

查询数据

删除数据

查看建表语句

Sqlite-JDBC

嵌入到Java程序

---

前言 

        最近在用JavaFX做一个桌面软件需要用到数据库，但MySQL这种数据库明显只能本地访问，把软件移植到别的电脑上就不能运行了，即使能够嵌入到程序中也太大了（MySQL8.x占用内存大约700MB）。所以，嵌入式轻量级的数据库应运而生，但这种轻量级数据库不只有Sqlite，还有H2数据库等，但网上资料比较少，Sqlite用的人可能更多。

基本介绍

SQLite是一种轻量级的关系型数据库管理系统，它以库文件的形式存储数据，没有独立的服务器进程或配置需求，可以在各种操作系统（包括Windows、Linux、macOS等）上运行。SQLite的设计目标是嵌入式数据库，它占用资源少、体积小、速度快，常用于移动设备、嵌入式设备等场景。

Sqlite 对比 MySQL

1. 轻量级：SQLite没有独立的服务器进程，整个数据库管理系统就是一个库文件，非常轻便，可以很方便地嵌入到其他应用程序中。
2. 快速：SQLite的设计目标是快速读写，它采用了许多优化策略，如内存缓存、页缓存、预编译语句等，可以实现高性能的数据读写操作。
3. 稳定可靠：SQLite的设计目标是可靠性，它采用了ACID事务模型，能够保证数据的一致性和完整性。
4. 跨平台：SQLite可以在各种操作系统上运行，包括Windows、Linux、macOS等，同时还支持多种编程语言，如C/C++、Java、Python等。
5. 简单易用：SQLite的语法类似于标准的SQL语法，易于学习和使用，同时还提供了多种API和命令行工具，方便用户进行数据库操作。

此外，Mysql是支持多线程操作（多读多写）的，但是Sqlite是单线程的（仅允许一个线程进行写操作，但允许多个线程进行读操作）。所以用Sqlite数据库就会深刻理解JDBC中关闭资源(ResultSet、Connection、Statement)的重要性，因为只要你的代码中有多个地方需要与数据库交互，如果资源未关闭，就会报错 'database is locked...'

字段类型

• NULL：空值
• INTEGER：整型
• REAL：浮点型
• TEXT：文本
• BLOB：二进制数据

语法

Sqlite的语法与MySQL基本一致，所以有MySQL的基础很容易上手。

创建表

CREATE TABLE student(
                    id integer primary key autoincrement ,
                    student_id text not null ,
                    name text not null ,
                    sex text not null
);

插入数据

INSERT INTO student (student_id,name,sex)
VALUES ('2023001','燕双鹰','男');

更新数据

UPDATE student
SET student='2023002', name='李大喜',
WHERE name='燕双鹰';

查询数据
 

-- 查询满足条件的记录
SELECT *
FROM student
WHERE name = '李大喜';
-- 查询所有记录
SELECT * FROM student;

删除数据

DELETE FROM student 
WHERE id = 1;

查看建表语句

-- 查看sqlite数据库中student表的建表语句
SELECT sql FROM sqlite_master WHERE type='table' AND name='student';

Sqlite-JDBC

1. 加载SQLite-JDBC驱动程序：
2. 连接SQLite数据库：
3. 进行数据库操作：使用Connection对象进行数据库操作，可以执行SQL语句、查询数据、更新数据等操作。
4. 关闭资源

下面是简单的Sqlite工具类：

public class DBUtils {
    private static Connection con = null;
    private static PreparedStatement stm = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;

    /**
     * 获取数据库连接对象
     * @return 连接
     * @throws Exception 异常
     */
    public static Connection getConnection() throws Exception{
        //通过反射加载驱动
        Class.forName("org.sqlite.JDBC");
        con = DriverManager.getConnection("jdbc:sqlite:test.db");
        return con;
    }

    /**
     * 执行SQL语句
     * @param sql sql语句
     * @return 返回查询结果集
     */
    public static ResultSet executeQuery(String sql){
        // SQLite连接串，指向本地的test.db文件
        try {
            // 建立连接
            con = getConnection();
            // 创建Statement对象
            stmt = con.createStatement();
            // 执行SELECT语句
            rs = stmt.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 执行增删改操作
     * @param sql SQL语句
     * @param params 参数数组
     * @return 返回操作的表的行数
     */
    public static int executeUpdate(String sql,String[] params){
//        DBUtils.close();
        int count = 0;
        try {
            con = getConnection();
            stm = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                stm.setString(i+1,params[i]);
            }
            count = stm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return count;
    }

    /**
     * 执行增删改操作
     * @param sql SQL语句
     * @param params 参数数组
     * @return 返回操作的表的行数
     */
    public static int executeUpdate(String sql,String[] params,byte[] face_feature){
        int count = 0;
        try {
            con = getConnection();
            stm = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                stm.setString((i+1),params[i]);
            }
            stm.setBytes(7,face_feature);//默认第7个参数位置是特征值
            count = stm.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            close();
        }

        return count;
    }

    /**
     * 关闭所有资源
     */
    public static void close(){
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stm!=null){
            try {
                stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

嵌入到Java程序

不需要下载Sqlite数据库到本地，只需要安装sqlite的jar包到java项目

 <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.41.2.1</version>
        </dependency>

不需要配置用户名密码、可直接配置