阅读基础：掌握 lombok、MyBatis Plus、Spring Data JPA

代码仓库：Springboot-SQLite-Tutorial

创建 sqlite 数据库

sqlite3 tutorial.db
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .tables
sqlite> create table if not exists user(id integer primary key AUTOINCREMENT, name text);
sqlite> .tables
user
sqlite> insert into user(`name`) values ('James'),('Hopbourn');
sqlite> select * from user;
1|James
2|Hopbourn
sqlite> .headers on
sqlite> .mode column
sqlite> select * from user;
id  name
--  --------
1   James
2   Hopbourn
sqlite> .quit

编辑 sqlite 配置文件

cat > ~/.sqliterc << EOF
.headers on
.mode column
EOF

sqlite3 tutorial.db
-- Loading resources from /Users/james/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> select * from user;
id  name
--  --------
1   James
2   Hopbourn
sqlite> .quit

安装 sqlite 可视化客户端，方便以后直接查看数据，使用 Homebrew 安装 DB Browser for SQLite。

brew install db-browser-for-sqlite
==> Downloading https://github.com/sqlitebrowser/sqlitebrowser/releases/download/v3.12.2/DB.Browser.for.SQLite-arm64-3.12.2.dmg
==> Downloading from https://objects.githubusercontent.com/github-production-release-asset-2e65be/19416551/b759779e-15b1-4971-bdb1-fdff42e60592?X-
######################################################################## 100.0%
==> Installing Cask db-browser-for-sqlite
==> Moving App 'DB Browser for SQLite.app' to '/Applications/DB Browser for SQLite.app'
🍺  db-browser-for-sqlite was successfully installed!

参考链接

Change SQLite default settings - Stack Overflow

demo01 resource 读写，整合 MyBatis Plus

创建基础的 sqlite 读取项目

创建 Springboot 项目，不选择依赖直接创建 demo01 项目，进入项目之后添加如下依赖：

lombok：快速生成实体类方法

sqlite-jdbc：sqlite 数据库连接驱动

mybatis-plus-boot-starter：CRUD 框架

如果是 M 系列芯片需要使用最新版 3.32.3.3 驱动程序，否则会报错。

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

修改配置文件的后缀为 yml，配置内容如下所示。驱动程序名为 org.sqlite.JDBC，url 指向了 resource 目录下的 tutorial.db 文件。将刚才创建的 tutorial.db 文件拷贝到 resources 目录下即可。

spring:
  datasource:
    url: jdbc:sqlite::resource:tutorial.db
    driver-class-name: org.sqlite.JDBC

此时的 resources 目录结构如下所示

└── resources
    ├── application.yml
    └── tutorial.db

创建 User 实体类，创建 mapper，创建 mapperTest，使用 list 方法查看表里的数据。

import lombok.Data;

@Data
public class User {
    private Long id;
    private String name;
}

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

@SpringBootTest
class UserMapperTest {
    @Resource
    private UserMapper userMapper;

    @Test
    void list(){
        userMapper.selectList(null).forEach(System.out::println);
    }
}

输出结果

User(id=1, name=James)
User(id=2, name=Hopbourn)

插入新的数据

返回值为 1 说明插入数据成功

@Test
void insert(){
    User user = new User();
    user.setId(3L);
    user.setName("Hello");
    int insert = userMapper.insert(user);
    System.out.println(insert);
}

但是使用 sqlite3 查询数据库却没有发现新插入的数据

sqlite3 tutorial.db 
-- Loading resources from /Users/james/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> select * from user;
id  name    
--  --------
1   James   
2   Hopbourn
sqlite> 

这是因为 resource 文件夹下的文件在应用程序运行时是只读的。为了能够向 SQLite 数据库中写入数据，需要将数据库文件放在其他的可写位置，例如和 pom.xml 同级的目录之下。接下来就开始修改存储位置，使得程序可以向 sqlite 文件中写入数据。

参考链接

java - How to include SQLite database in executable Jar? - Stack Overflow

spring boot - java.sql.SQLException: Error opening connection - Stack Overflow

java - Spring boot application not able to detect table defined in sqlite db file - Stack Overflow

demo02 根目录读写

将 tutorial.db 移动到和 pom.xml 同级的目录之下，然后修改 application.yml 中的 url 配置，修改之后的配置文件内容如下所示：

spring:
  datasource:
    url: jdbc:sqlite:tutorial.db
    driver-class-name: org.sqlite.JDBC

将 demo01 中的 User、UserMapper、UserMapperTest 复制到 demo02 对应的目录下，运行 UserMapperTest 然后后查询数据库中的记录，此时可以正常插入数据。

 sqlite3 tutorial.db 
-- Loading resources from /Users/james/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> select * from user;
id  name    
--  --------
1   James   
2   Hopbourn
3   Hello   
sqlite> 

demo03 JPA 自动建库（动态数据库名）

Spring Data JPA 提供了可以使用注解直接创建数据表的方式，继续整合 Spring Data JPA 进行自动创建数据库。

引入相关的依赖，Spring Data JPA 和 SQLite dialect，相关的依赖坐标如下：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.github.gwenn</groupId>
    <artifactId>sqlite-dialect</artifactId>
    <version>0.1.1</version>
</dependency>

创建 User 实体类，使用 JPA 和 MyBatis Plus 的相关注解进行定义

import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

import javax.persistence.*;

@Data
@Entity
public class User {
    @Id
    @TableId
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "admin_id", nullable = false, columnDefinition = "integer(20)")
    private Long adminId;

    @Column(name = "user_name", nullable = false, columnDefinition = "text(20)")
    private String userName;
}

在 application.yml 下面增加 JPA 的相关配置：

spring:
  datasource:
    url: jdbc:sqlite:demo03/tutorial03.db
    driver-class-name: org.sqlite.JDBC
  jpa:
    properties:
      hibernate:
        dialect: org.sqlite.hibernate.dialect.SQLiteDialect
        format_sql: true
    show-sql: true
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

运行起项目，即可看到 pom.xml 同级目录下多了一个 tutorial.db 文件，使用 sqlite3 查询表结构，也和 User 实体类里定义的一致。

sqlite3 tutorial03.db
-- Loading resources from /Users/james/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> .table
User
sqlite> .schema User
CREATE TABLE User (
       admin_id  integer,
        user_name text(20) not null,
        primary key (admin_id)
    );
sqlite>

参考链接

wildfly - Unable to resolve name org.hibernate.dialect.SQLiteDialect - Stack Overflow

SpringBoot系列教材 （十八）- CRUD+分页 - SPRINGBOOT 基于SQLITE 和JPA 方式

demo03 JPA 自动插入数据

修改 demo03 的 application，增加 data 和 initialization-mode 两个配置项，修改后的内容如下所示：

spring:
  datasource:
    url: jdbc:sqlite:demo03/tutorial03.db
    data: classpath:data.sql
    initialization-mode: always
    driver-class-name: org.sqlite.JDBC
  jpa:
    properties:
      hibernate:
        dialect: org.sqlite.hibernate.dialect.SQLiteDialect
        format_sql: true
    show-sql: true
    hibernate:
      ddl-auto: update
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

在 resources 目录下创建 data.sql 文件，并写入以下内容：

insert into user (`user_name`) values ('James'),('Hopbourn');

运行项目，启动后使用 sqlite3 查看数据库 user 表内容，可以看到 data.sql 的数据已经成功插入表中：

sqlite3 tutorial03.db
-- Loading resources from /Users/james/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> select * from user;
admin_id  user_name
--------  ---------
1         James    
2         Hopbourn 
sqlite> 

demo04 CSV 数据导入

使用 JPA 自动建库建表，在 demo04 下创建 user.cvs 文件，使用 sqlite3 进行导入数据

sqlite3 tutorial04.db
-- Loading resources from /Users/james/.sqliterc
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> select * from user;
sqlite> .mode csv
sqlite> .import user.csv user
sqlite> select * from user;
admin_id,user_name
1,"张云"
2,"刘阳"
3,"麦淑华"
4,"梁玉华"
5,"徐慧"
6,"卜雪梅"
7,"周宇"
8,"巴秀芳"
9,"陈淑华"
10,"毛英"
sqlite> 

编写测试方法查询数据，返回结果符合预期

User(adminId=1, userName=张云)
User(adminId=2, userName=刘阳)
User(adminId=3, userName=麦淑华)
User(adminId=4, userName=梁玉华)
User(adminId=5, userName=徐慧)
User(adminId=6, userName=卜雪梅)
User(adminId=7, userName=周宇)
User(adminId=8, userName=巴秀芳)
User(adminId=9, userName=陈淑华)
User(adminId=10, userName=毛英)

参考链接

Import CSV to SQLite

Export and import SQLite

参考文章

SpringBoot集成内存数据库Sqlite

[译] 抛弃你的成见，SQLite 不是一个玩具数据库 - 掘金