轻量数据库SQLite与H2发布于2023-01-15 01:50:34，更新于2025-03-05 14:04:38，标签：java sql 文章会持续修订，转载请注明来源地址：https://meethigher.top/blog

一、JDK版本管理

1.1 cmd配置代理

首先，按照我的习惯，依然是使用Qv2ray，参考Git配置代理，进行临时的cmd代理配置

set http_proxy=10.0.0.1:1081
set https_proxy=10.0.0.1:1081
curl -v https://google.com

Windows中临时配置环境变量是set

Linux中临时配置环境变量是export

执行结果如下

1.2 版本管理工具

该文章中使用的jvms版本工具(与jabba相比，最终还是选择了jvms，使用简单才是王道)，与创建Vue3+Vite单页面应用总结中提到的nvm，异曲同工。

有时间我也应该学一下go，作为c++的替代品

直接在windows cmd中执行如下命令

mkdir C:\Users\meeth\AppData\Roaming\jvms
cd C:\Users\meeth\AppData\Roaming\jvms
curl -L -x 10.0.0.1:1081 -o jvms.zip  "https://github.com/ystyle/jvms/releases/download/v2.1.1/jvms_v2.1.1_amd64.zip"

执行过程如图

之后，进行解压，放到jvms根目录即可。

执行命令下载jdk

jvms init
# 查看远程jdk版本
jvms rls
# 安装
jvms install 17.0.1
# 切换版本，win需要在管理员模式下
jvms switch 17.0.1

二、轻量数据库

2.1 SQLite

2.1.1 常规用法

安装命令

# jdbc:sqlite:D:/Desktop/count-page.db?journal_mode=off
# https://www.sqlite.org/download.html
# windows安装直接下载配置环境变量即可。https://www.sqlite.org/2025/sqlite-tools-win-x64-3490100.zip
# linux安装执行如下命令
yum -y install sqlite

运行命令时，可能会遇到问题“SQLite header and source version mismatch”（SQLite 头文件和源码版本不匹配），通常是由于 SQLite 的库文件（libsqlite3）与编译时的头文件版本不一致 造成的。

可以通过以下方式查看，是否存在已有可用的sqlite版本，然后通过软链接，将其定位到/usr/bin/sqlite3即可

查看已安装的sqlite包

rpm -qa | grep sqlite

检查sqlite库文件版本

# ldd查看程序的动态链接库依赖
ldd $(which sqlite3) | grep sqlite

连接数据库

若不存在则创建

# 连接数据库，若不存在则创建
sqlite3 target.db

多库操作-复制表结构及数据

-- 附加源数据库
attach database 'source1.db' AS source1;
attach database 'source2.db' AS source2;

-- 复制表结构
create table target_table1 AS SELECT * FROM source1.table1 WHERE 1=0;
create table target_table2 AS SELECT * FROM source2.table2 WHERE 1=0;

-- 复制数据
insert into target_table1 SELECT * FROM source1.table1;
insert into target_table2 SELECT * FROM source2.table2;

-- 分离源数据库
detach database source1;
detach database source2;

表列表及表结构

-- 查询当前库里拥有的表
.tables

-- 查询出所有的表
SELECT * FROM sqlite_master WHERE type='table';

-- 查看某张表的表结构
pragma table_info("202402.csv");

导入导出SQL

命令行式

-- 命令行导出sql .dump会导出结构和数据
sqlite3 my_database.db .dump > backup.sql
-- 命令行导入sql
sqlite3 my_database.db < backup.sql

导入导出CSV

命令行式

-- 命令行将数据导出为csv
sqlite3 -header -csv example.db "SELECT * FROM users;" > users.csv
-- 命令行导入csv，不会自动创建表。注意csv不要带标题头
sqlite3 my_database.db ".mode csv" ".import data.csv my_table"

交互式

# 交互式导出为csv
sqlite3 example.db
sqlite> .mode csv
sqlite> .headers on
sqlite> .output users.csv
sqlite> SELECT * FROM users;
sqlite> .output stdout
sqlite> .exit
# 交互式导入csv，不会自动创建表。注意csv不要带标题头
sqlite3 example.db
sqlite> .mode csv
sqlite> .import my_data.csv my_table
sqlite> .output stdout
sqlite> .exit

bash遍历导出csv

#!/bin/bash
set -e

# SQLite数据库文件
DB_FILE="sf2403.db"
# 表名
TABLE_NAME="sf2403"
# 每个文件导出的记录数
LIMIT=100000
# 查询的起始 ID
OFFSET=10
# 遍历的次数
TOTAL=132



# 日志打印函数
function log() {
  echo "$(date +"%Y-%m-%d %H:%M:%S"): $1"
}


# 手写式循环
# for i in {1..20}

# 变量式循环
for i in $(seq 1 $TOTAL)
do
  # 计算文件名
  FILE_NAME=$(printf "part%03d.csv" $OFFSET)


  log "开始导出 ($((OFFSET*LIMIT)),$(($((OFFSET+1))*LIMIT))] 至 $FILE_NAME"

  # 执行查询并将结果输出到文件
  sqlite3 -header -csv $DB_FILE \
    "select id,address_std from $TABLE_NAME where (id+0) > ($OFFSET*$LIMIT) order by (id+0) asc limit $LIMIT;" > $FILE_NAME

  # 更新 OFFSET，为下一次查询设置新的起始 ID
  OFFSET=$((OFFSET + 1))

  log "导出 $FILE_NAME 完成"
  echo "===================="
done

解析JSON

-- 解析json
-- 官方文档https://www.sqlitetutorial.net/sqlite-json-functions/sqlite-json_extract-function/
select json_extract('{"id":1,"job":[{"name":"job1"},{"name":"job2"}],"name":"test","prop":{"age":10,"level":"high"}}','$.name') name;
select json_extract('{"id":1,"job":[{"name":"job1"},{"name":"job2"}],"name":"test","prop":{"age":10,"level":"high"}}','$.prop.age') name;
select json_extract('{"id":1,"job":[{"name":"job1"},{"name":"job2"}],"name":"test","prop":{"age":10,"level":"high"}}','$.job') name;
select json_extract('{"id":1,"job":[{"name":"job1"},{"name":"job2"}],"name":"test","prop":{"age":10,"level":"high"}}','$.job[0]') name;

类型转换

SQLite 只支持以下五种基本存储类（数据类型）：

存储类	说明
NULL	空值（NULL）
INTEGER	整数（有符号，1~8 字节）
REAL	浮点数（8 字节，IEEE 754）
TEXT	文本字符串（UTF-8、UTF-16）
BLOB	二进制数据（Blob）

在sqlite中，即使定义了varchar，本质上也是使用的text

显示转换

select cast('123' as integer);  -- 返回 123
select cast('3.14' as real);    -- 返回 3.14

隐式转换

select '123' + 0;   -- 返回 123
select '3.14' * 1;  -- 返回 3.14

2.1.2 综合示例

-- 查询当前事务日志模式。类型有delete、truncate、persist、memory、wal、off。
PRAGMA journal_mode 
-- 查询当前写入数据到磁盘时的同步性级别。类型有0-off 1-normal 2-full 3-extra
PRAGMA synchronous 


DROP TABLE location ;
CREATE TABLE location (
id int4,
name varchar,
lon float8,
lat float8,
gpstime varchar
)
-- 清空数据
truncate TABLE location ;

-- 生成某天开始后的14天的随机时间
-- sqlite中datetime函数接收的是秒。unixepoch表示前面这个数值看成unix时间戳
SELECT datetime((1727712000000+abs(random()%1209600000)+1) / 1000, 'unixepoch','localtime') AS formatted_date

-- 模拟2000w条定位轨迹。其中固定人员为2w个，模拟的时间为2024-10-01到2024-10-14
WITH RECURSIVE numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM numbers WHERE num <= 20000000
)
INSERT INTO location(id, name, lon, lat, gpstime)
SELECT
	 num,
    'Name' || ABS(RANDOM() % 20000),
    (RANDOM() % 90), 
    (RANDOM() % 90),
    datetime((1727712000000+abs(random()%1209600000)+1) / 1000, 'unixepoch','localtime') from numbers; 


-- 创建索引
DROP INDEX LOCATION_NAME_IDX;
DROP INDEX LOCATION_GPSTIME_IDX;
CREATE INDEX LOCATION_NAME_IDX ON location (name);
CREATE INDEX LOCATION_GPSTIME_IDX ON location (gpstime);


-- 开始测试索引效率
SELECT name,count(*) FROM location GROUP BY name ORDER BY 2 DESC;

SELECT * FROM location WHERE name='Name606' AND gpstime >= '2024-10-07 00:00:00' AND gpstime <= '2024-10-07 23:59:59' ORDER BY gpstime DESC;

2.2 H2

2.2.1 综合示例

-- h2数据库中，如果字符串没有用双引号，则默认转换为大写。如需禁用该功能，需要在jdbcurl中添加参数DATABASE_TO_UPPER=false
-- 如jdbc:h2:~/test;DATABASE_TO_UPPER=false
-- h2数据库中，当数据量过大时，会出现连接不上或者查询慢的问题，可以通过调整CACHE_SIZE=2048解决

-- 查询出所有的表
select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='PUBLIC'
DROP TABLE location ;
CREATE TABLE location (
id int4,
name varchar,
lon float8,
lat float8,
gpstime varchar
)

-- 清空数据
truncate TABLE location ;

-- 生成近14天的随机时间
SELECT FORMATDATETIME(
    DATEADD(MILLISECOND, FLOOR(rand()*1209600000), '2024-10-01 00:00:00'),  -- 将毫秒时间戳加到1970年
    'yyyy-MM-dd HH:mm:ss'
) AS formatted_date;

-- 模拟2000w条定位轨迹。其中固定人员为2w个，模拟的时间为2024-10-01到2024-10-14
INSERT INTO location(id, name, lon, lat, gpstime)
SELECT X,'Name' || floor(rand()*2000), FLOOR(RAND() * 100),FLOOR(RAND() * 100), FORMATDATETIME(
    DATEADD(MILLISECOND, FLOOR(rand()*1209600000), '2024-10-01 00:00:00'),  -- 将毫秒时间戳加到1970年
    'yyyy-MM-dd HH:mm:ss'
)
FROM SYSTEM_RANGE(1, 60000000);

-- 创建索引
DROP INDEX LOCATION_NAME_IDX;
DROP INDEX LOCATION_GPSTIME_IDX;
CREATE INDEX LOCATION_NAME_IDX ON location (name);
CREATE INDEX LOCATION_GPSTIME_IDX ON location (gpstime);

-- 开始测试索引效率
SELECT name,count(*) FROM location GROUP BY name ORDER BY 2 DESC;

SELECT * FROM location WHERE name='Name606.0' AND gpstime >= '2024-10-07 00:00:00' AND gpstime <= '2024-10-07 23:59:59' ORDER BY gpstime DESC;

2.2.2 H2运行模式

内嵌模式

本地文件连接

内嵌模式下的本地文件连接URL的格式是：

jdbc:h2:[file:][<path>]<databaseName>

其中前缀file:是可选的。如果没有设置路径或者只使用了相对路径，则当前工作目录将被作为起点使用。路径和数据库名称的大小写敏感取决于操作系统， 不过推荐只使用小写字母。数据库名称必须最少三个字母（File.createTempFile的限制）。数据库名字不容许包含分号;。

可以使用~/来指向当前用户home目录，例如jdbc:h2:~/test。

可以使用./来指向项目当前目录，例如jdbc:h2:./test

例如：

jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample (仅仅用于Windows)

内存数据库连接

对于特殊使用场景(例如：快速原型开发，测试，高性能操作，只读数据库)，可能不需要持久化数据或数据的改变。H2数据库支持内存模式，数据不被持久化。

内存数据库资料存储有两种方式：

1. private/私有

   在一些场景中，要求仅仅有一个到内存数据库的连接。这意味着被开启的数据库是私有的(private)。在这个场景中，数据库URL是jdbc:h2:mem:。在同一个虚拟机中开启两个连接意味着打开两个不同的（私有）数据库。

2. named/命名： 其他应用可以通过使用命令来访问

   有时需要到同一个内存数据库的多个连接，在这个场景中，数据库URL必须包含一个名字。例如：jdbc:h2:mem:db1。仅在同一个虚拟机和class loader下可以通过这个URL访问到同样的数据库。

对应的内存数据库连接URL的格式是：

• 私有格式：jdbc:h2:mem:
• 命名格式：jdbc:h2:mem:< databaseName >

命名格式的例子如下：

jdbc:h2:mem:test

默认，最后一个连接到数据库的连接关闭时就会关闭数据库。对于一个内存数据库，这意味着内容将会丢失。为了保持数据库开启，可以添加DB_CLOSE_DELAY=-1到数据库URL中。为了让内存数据库的数据在虚拟机运行时始终存在，请使用jdbc:h2:mem:test;DB_CLOSE_DELAY=-1。

服务器模式

为了从其他进程或者其他机器访问一个内存数据库，需要在创建内存数据库的进程中启动一个TCP服务器。其他进程就需要通过TCP/IP 或者 TLS来访问数据库，使用URL类似jdbc:h2:tcp://localhost/mem:db1

H2支持两种连接方式

1. TCP
2. TLS

使用TCP

格式如下，通过指定server和port连接到以内嵌模式运行的H2服务器，参数path和databaseName对应该内嵌数据库启动时的参数：

jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName>

范例：

jdbc:h2:tcp://localhost/~/test		（对应内嵌模式下的jdbc:h2:~/test)
jdbc:h2:tcp://dbserv:8084/~/sample （对应内嵌模式下的jdbc:h2:~/sample)
jdbc:h2:tcp://localhost/mem:test	（对应内嵌模式下的jdbc:h2:mem:test)

使用TLS

可以通过采用SSL在传输层对数据库访问内容做加密，URL格式和TCP相同，只是TCP替换为ssl：

jdbc:h2:ssl://<server>[:<port>]/<databaseName>

范例：

jdbc:h2:ssl://localhost:8085/~/sample;

混合模式

通过设置AUTO_SERVER=TRUE可以开启自动混合模式：

jdbc:h2:<url>;AUTO_SERVER=TRUE

范例：

jdbc:h2:~/test;AUTO_SERVER=TRUE

2.3 SQLite与H2对比

轻量数据库的对比demo，放置在简单对比sqlite与h2的配置方式

两者对比，直接用一个图来比较吧。

简而言之，就并发查询来看，是sqlite性能更高。

同样实现40GB数据文件存储，查询速度快慢比较 postgresql>sqlite>h2，其中h2随着数据量增大，连接和查询速度急剧下降，sqlite也存在该问题，不过总体比h2要好。

高并发插入时，sqlite会存在锁表的情况，我的解决办法是配置数据源为单连接。而h2在并发下表现就很好。

因为h2支持并发插入，采用了多版本并发控制MVCC，这就导致随着数据量变大，查询效率急剧下降，并且需要配置CACHE_SIZE才能得到勉强能用的效果。

sqlite不支持并发插入，不过查询效率受文件大小的影响较小。

不过针对大数据的查询和存储，两者性能均不如postgresql。

再有一点，h2支持sqlite的文件模式，也支持内存模式。同时，还能开启远程服务。

H2，设计初衷是面向Java应用，虽然其性能在非并发场景下的表现不如SQLite显著，但是其多运行模式、对并发场景的良好支持，使其更适合于轻量级的Web应用或服务。

SQLite，对多种开发语言有着良好的支持，其非并发场景下的显著性能，更适合于手机App的内部数据存储与管理。

2.4 同时连接多数据库

源码JPA同时连接不同数据库

运行结果直接上图

可以通过注解实现切换数据源，源码参考自定义注解实现动态切换数据源

动态数据源的思路，可以用于多数据库时，配置轮询，进行请求分发。

三、致谢参考

windows 的cmd设置代理方法_Sherlock　Salvatore的博客-CSDN博客_cmd 设置代理

自定义注解实现动态切换数据源

SpringBoot JPA 配置多个数据库_长不大的大灰狼的博客-CSDN博客_jpa连接多个数据库

springboot Jpa多数据源（不同库）配置_Mr-Wanter的博客-CSDN博客_springboot 不同数据库配置

【分享吧】嵌入式数据库 H2 Database vs SQLite

H2介绍 · leaning-h2