Spring Boot 集成 SQLite3 与 MyBatis-Plus 的轻量级数据服务实践
Maven 依赖声明
通过继承 Spring Boot 父工程统一管理组件版本，引入 Web 模块、SQLite JDBC 驱动、MyBatis-Plus Starter 以及 Fastjson 用于序列化输出。构建阶段需开启 fork 属性以确保打包环境隔离。
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/>
</parent>
<properties>
<java.version>1.8</java.version>
<mybatis-spring-boot.version>2.0.0</mybatis-spring-boot.version>
<mybatis-plus.version>3.0.6</mybatis-plus.version>
<sqlite-jdbc.version>3.27.2.1</sqlite-jdbc.version>
<fastjson.version>1.2.73</fastjson.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>${sqlite-jdbc.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-spring-boot.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
</configuration>
</plugin>
</plugins>
</build>应用环境配置
SQLite 支持将数据库文件打包至 classpath 中运行。当 db 文件存放于 resources/sqlite 目录下时，连接字符串需采用 jdbc:sqlite::resource:sqlite/xxx.db 协议格式，以便框架在打包后仍能正确定位资源路径。
server:
port: 8190
spring:
datasource:
driver-class-name: org.sqlite.JDBC
# 引用外部绝对路径
# url: jdbc:sqlite:/data/app/demo.db
# 引用 classpath 内嵌文件（推荐）
url: jdbc:sqlite::resource:sqlite/demo.db
username: root
password: root
logging:
level:
root: debug
com.example.sqlite: debug
file: logs/app.log领域模型定义
使用 MyBatis-Plus 注解建立表与 POJO 的双向映射，指定主键策略并保留标准 Setter/Getter 方法供框架反射调用。
package com.example.sqlite.domain;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
@TableName("t_data_record")
public class DataRecord {
@TableId("record_id")
private Integer recordId;
private String subject;
private String detail;
private String category;
// Getters & Setters 省略
}持久层接口
继承 BaseMapper 获得内置单表操作能力，无需编写额外 SQL 即可满足常规增删改查需求。
package com.example.sqlite.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.sqlite.domain.DataRecord;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface RecordMapper extends BaseMapper<DataRecord> {
}
服务层逻辑实现
基于 ServiceImpl 封装通用业务，使用 LambdaQueryWrapper 和 LambdaUpdateWrapper 保证字段类型安全，避免硬编码列名导致的运行时异常。ID 生成采用时间戳截断策略模拟自增效果。
package com.example.sqlite.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.sqlite.domain.DataRecord;
import com.example.sqlite.mapper.RecordMapper;
import com.example.sqlite.service.RecordService;
import org.springframework.stereotype.Service;
@Service
public class RecordServiceImpl extends ServiceImpl<RecordMapper, DataRecord> implements RecordService {
@Override
public DataRecord queryBySubject(String subject) {
LambdaQueryWrapper<DataRecord> query = new LambdaQueryWrapper<>();
query.eq(DataRecord::getSubject, subject);
return baseMapper.selectOne(query);
}
@Override
public int insertRecord(DataRecord record) {
long timeMillis = System.currentTimeMillis();
record.setRecordId((int) (timeMillis % 100_000));
return baseMapper.insert(record);
}
@Override
public int modifyRecord(DataRecord record) {
LambdaUpdateWrapper<DataRecord> update = new LambdaUpdateWrapper<>();
update.eq(DataRecord::getSubject, record.getSubject());
return baseMapper.update(record, update);
}
}
REST 控制层
暴露标准化 HTTP 端点，接收前端请求参数并返回 JSON 响应日志记录采用 SLF4J 规范，便于后续对接集中式日志系统。
package com.example.sqlite.controller;
import com.alibaba.fastjson.JSON;
import com.example.sqlite.domain.DataRecord;
import com.example.sqlite.service.RecordService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/api/v1")
public class DataController {
private static final Logger log = LoggerFactory.getLogger(DataController.class);
@Autowired
private RecordService recordService;
@GetMapping("/fetch")
public String fetch(@RequestParam String subject) {
log.info("查询主题: {}", subject);
DataRecord data = (subject != null) ? recordService.queryBySubject(subject) : null;
return (data != null) ? JSON.toJSONString(data) : "条件参数缺失";
}
@PostMapping("/create")
public String create(@RequestBody DataRecord payload) {
log.info("新增数据: {}", JSON.toJSONString(payload));
int affected = (payload != null) ? recordService.insertRecord(payload) : 0;
return (affected > 0) ? "写入成功" : "写入失败";
}
@PutMapping("/update")
public String update(@RequestBody DataRecord payload) {
log.info("更新数据: {}", JSON.toJSONString(payload));
int affected = (payload != null) ? recordService.modifyRecord(payload) : 0;
return (affected > 0) ? "修订成功" : "修订失败";
}
}
启动入口
标注@SpringBootApplication 激活自动装配机制，启动过程打印关键状态日志辅助运维排查。
package com.example.sqlite;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SqliteDemoApplication {
private static final Logger log = LoggerFactory.getLogger(SqliteDemoApplication.class);
public static void main(String[] args) {
log.info("服务初始化中...");
SpringApplication.run(SqliteDemoApplication.class, args);
log.info("服务已就绪");
}
}
底层 sqlite 存储文件可通过官方工具或第三方图形化管理客户端手动初始化建表，亦可借助 flyway 等迁移插件实现 schema 自动化版本控制。
