SQLite 安装和 Java 使用教程

SQLite是一个C语言库，它实现了一个小型、快速、自包含、高可靠性、功能齐全的SQL数据库引擎。SQLite是世界上使用最多的数据库引擎。SQLite内置于所有手机和大多数计算机中，并捆绑在人们每天使用的无数其他应用程序中。

SQLite文件格式稳定、跨平台、向后兼容，开发人员承诺在2050年保持这种格式。SQLite数据库文件通常用作在系统之间传输丰富内容的容器，以及数据的长期存档格式。目前有超过1万亿个SQLite数据库在积极使用

SQLite 官网：https://www.sqlite.org/index.html

目录

1、安装 SQLite

2、新建数据库

3、新建表

4、Java 操作 SQLite

4.1、普通类型存储

4.2、二进制类型存储

---

1、安装 SQLite

官网下载 SQLite

下载地址：https://www.sqlite.org/download.html

 根据自己的电脑系统选择

下载后将其解压

解压

2、新建数据库

双击打开 sqlite3.exe

弹出命令行窗体

新建数据库命令

.open test.db

新建名称是 test 的数据库

新建完成后，会在当前目录下生成 test.db 的文件，数据库创建完成

3、新建表

新建表可以使用命令

CREATE TABLE user (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

 新建 user 表，有 id 和 name 字段，id是主键自增，name是 TEXT 字符串文本类型

SQLite 的类型有

 除此之外可以使用 Navicat 连接建表

选择 SQLite

连接名随意

选择数据库文件，点击确定即可

新建表

新建表，有3个字段， id， name，price

新建完成

4、Java 操作 SQLite

新建 maven 项目 sqlite-learn

引入 sqlite-jdbc 依赖

<dependency>
    <groupId>org.xerialgroupId>
    <artifactId>sqlite-jdbcartifactId>
    <version>3.41.2.1version>
dependency>

sqlite-learn 项目 pom 文件

"1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>
 
    <groupId>com.wsjzzcbqgroupId>
    <artifactId>sqlite-learnartifactId>
    <version>1.0-SNAPSHOTversion>
 
    <dependencies>
        <dependency>
            <groupId>org.xerialgroupId>
            <artifactId>sqlite-jdbcartifactId>
            <version>3.41.2.1version>
        dependency>
    dependencies>
 
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-pluginartifactId>
                <configuration>
                    <source>11source>
                    <target>11target>
                configuration>
            plugin>
        plugins>
    build>
 
project>

4.1、普通类型存储

增删改查代码

package com.wsjzzcbq;
 
import java.sql.*;
 
/**
 * SqliteDemo
 *
 * @author wsjz
 * @date 2023/11/17
 */
public class SqliteDemo {
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        //SQLite 数据库文件
        String dbFile = "D:\\tmp\\sql\\sqlite-tools-win-x64-3440000\\test.db";
        String url = "jdbc:sqlite:" + dbFile;
        Connection conn = DriverManager.getConnection(url);
 
        //添加
        insert(conn);
        //查询
        select(conn);
        //修改
        update(conn);
        //删除
        delete(conn);
 
        conn.close();
    }
 
    private static void select(Connection connection) throws SQLException {
        String sql = "select * from user";
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
 
            System.out.println(id);
            System.out.println(name);
        }
        rs.close();
        statement.close();
    }
 
    private static void insert(Connection connection) throws SQLException {
        String sql = "insert into user( name) values('小丽')";
        Statement stat = connection.createStatement();
        stat.executeUpdate(sql);
        stat.close();
    }
 
    private static void update(Connection connection) throws SQLException {
        String sql = "update user set name = ? where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, "小雪花");
        ps.setObject(2, 1);
        ps.executeUpdate();
        ps.close();
    }
 
    private static void delete(Connection connection) throws SQLException {
        String sql = "delete from user where id = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, 1);
        ps.executeUpdate();
        ps.close();
    }
 
}

运行测试

测试添加

测试修改

测试删除

shop 表代码

package com.wsjzzcbq;
 
import java.sql.*;
 
/**
 * SqliteDemo2
 *
 * @author wsjz
 * @date 2023/11/17
 */
public class SqliteDemo2 {
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        //SQLite 数据库文件
        String dbFile = "D:\\tmp\\sql\\sqlite-tools-win-x64-3440000\\test.db";
        String url = "jdbc:sqlite:" + dbFile;
        Connection conn = DriverManager.getConnection(url);
 
        //添加
        insert(conn);
        //查询
        select(conn);
 
 
        conn.close();
    }
 
    private static void insert(Connection connection) throws SQLException {
        String sql = "insert into shop(name, price) values('水浒传', 20)";
        Statement stat = connection.createStatement();
        stat.executeUpdate(sql);
        stat.close();
    }
 
    private static void select(Connection connection) throws SQLException {
        String sql = "select * from shop";
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double price = rs.getDouble("price");
 
            System.out.println(id);
            System.out.println(name);
            System.out.println(price);
        }
        rs.close();
        statement.close();
    }
 
}

运行测试

4.2、二进制类型存储

储存二进制图片文件

package com.wsjzzcbq;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
 
/**
 * SqliteDemo3
 *
 * @author wsjz
 * @date 2023/11/17
 */
public class SqliteDemo3 {
 
    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
        Class.forName("org.sqlite.JDBC");
        //SQLite 数据库文件
        String dbFile = "D:\\tmp\\sql\\sqlite-tools-win-x64-3440000\\test.db";
        String url = "jdbc:sqlite:" + dbFile;
        Connection conn = DriverManager.getConnection(url);
 
        //新建表
        createTable(conn);
        //添加
        insert(conn);
        //查询
        select(conn);
 
 
        conn.close();
    }
 
    private static void createTable(Connection connection) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS  img (name TEXT, image BLOB)";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
 
    private static void insert(Connection connection) throws SQLException, IOException {
        String sql = "insert into img(name, image) values(?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, "怀素自叙帖");
 
        String filePath = "D:\\tmp\\img\\huaisu.png";
        byte[] bytes = Files.readAllBytes(Paths.get(filePath));
        InputStream inputStream = Files.newInputStream(Paths.get(filePath));
        //添加图片文件
        ps.setBinaryStream(2, inputStream, bytes.length);
 
        ps.executeUpdate();
        ps.close();
    }
 
    private static void select(Connection connection) throws SQLException, IOException {
        String sql = "select * from img";
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()) {
            //获取name
            String name = rs.getString("name");
            System.out.println(name);
 
            //获取图片文件
            InputStream inputStream = rs.getBinaryStream("image");
            String filePath = "D:\\tmp\\img\\huaisu2.png";
            Files.copy(inputStream, Paths.get(filePath));
        }
        rs.close();
        statement.close();
    }
}

测试运行

至此完