Sqlite： Java使用、sqlite-devel

一、简介

1. 非常轻量级，都没有服务器进程（mysql必须要有mysqld.service 3306）
2. 一个.db或.sqlite文件就是一个数据库, 非常方便备份和传输，只要复制文件就可以

• sqlite 是本地数据库，不能远程。安全！
• SQLite 在任何时刻只允许一个写入操作执行，其他写入操作需要排队
• 数据库就是一个文件，这个文件可以在任意位置，任意后缀名，建议用.db 或者 .sqlite 作为后缀

二、使用

1. Java项目中

（1）引入驱动

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

（2）工具类

• SqlLiteHelper

package sample.common.sqlLite;import sample.common.utils.LogUtil;import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;/*** @Author admin* @Date 2023/4/4 11:20*/
public class SqlLiteHelper {private Connection connection;private Statement statement;private ResultSet resultSet;private String dbFilePath;   // db文件的位置Logger logger = LogUtil.installFormatter(Logger.getLogger(SqlLiteHelper.class.getSimpleName()));/*** 每次创建都将建立一个连接* @param dbFilePath* @throws ClassNotFoundException* @throws SQLException*/public SqlLiteHelper(String dbFilePath) throws ClassNotFoundException, SQLException{this.dbFilePath = dbFilePath;connection = getConnection(dbFilePath);}public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {Connection conn = null;Class.forName("org.sqlite.JDBC");conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);return conn;}private Connection getConnection() throws ClassNotFoundException, SQLException {if (null == connection) connection = getConnection(dbFilePath);return connection;}private Statement getStatement() throws SQLException, ClassNotFoundException {if (null == statement) statement = getConnection().createStatement();return statement;}/**返回对象**/public <T> T executeQuery(String sql, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {try {resultSet = getStatement().executeQuery(sql);T rs = rse.extractData(resultSet);return rs;} finally {destroyed();}}/**返回对象集合**/public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {List<T> rsList = new ArrayList<T>();try {resultSet = getStatement().executeQuery(sql);while (resultSet.next()) {rsList.add(rm.mapRow(resultSet, resultSet.getRow()));}} finally {destroyed();}return rsList;}public <T> List<T> executeQueryList(String sql, Class<T> clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {List<T> rsList = new ArrayList<T>();try {resultSet = getStatement().executeQuery(sql);while (resultSet.next()) {T t = clazz.newInstance();for (Field field : t.getClass().getDeclaredFields()) {field.setAccessible(true);field.set(t,resultSet.getObject(field.getName()));}rsList.add(t);}} finally {destroyed();}return rsList;}public <T> T executeQuery(String sql, Class<T> clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {try {resultSet = getStatement().executeQuery(sql);T t = clazz.newInstance();for (Field field : t.getClass().getDeclaredFields()) {field.setAccessible(true);//  ---> 连接断开了field.set(t,resultSet.getObject(field.getName()));}return t;} finally {destroyed();}}public int count(String sql) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {try {resultSet = getStatement().executeQuery(sql);if(resultSet.next()){return resultSet.getInt(1);}} finally {destroyed();}return 0;}/**返回更新成功的条数**/public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {try {int c = getStatement().executeUpdate(sql);return c;} finally {destroyed();}}/**执行多个更新**/public void executeUpdate(String...sqls) throws SQLException, ClassNotFoundException {try {for (String sql : sqls) {getStatement().executeUpdate(sql);}} finally {destroyed();}}public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {try {for (String sql : sqls) {getStatement().executeUpdate(sql);}} finally {destroyed();}}/**数据插入更新**/public int executeInsert(String tableName, Map<String,Object> param) throws SQLException, ClassNotFoundException {try {StringBuffer sql = new StringBuffer();sql.append("INSERT INTO ");sql.append(tableName);sql.append(" ( ");for (String key : param.keySet()) {sql.append(key);sql.append(",");}sql.delete(sql.length()-1,sql.length());sql.append(")  VALUES ( ");for (String key : param.keySet()) {sql.append("'");sql.append(param.get(key));sql.append("',");}sql.delete(sql.length()-1,sql.length());sql.append(");");int c = getStatement().executeUpdate(sql.toString());return c;} finally {destroyed();}}/**数据库资源关闭和释放**/public void destroyed() {// 每一次crud都关闭了所有的资源try {if (null != statement) {statement.close();statement = null;}if (null != connection) {connection.close();connection = null;}if (null != resultSet) {resultSet.close();resultSet = null;}} catch (SQLException e) {logger.info("Sqlite数据库关闭时异常"+e.getMessage());}}}

• 结果集实现

package sample.common.sqlLite;import java.sql.ResultSet;
import java.sql.SQLException;/*** @Author admin* @Date 2023/4/4 11:25*/
public interface RowMapper<T> {public abstract T mapRow(ResultSet rs, int index) throws SQLException;
}

package sample.common.sqlLite;import java.sql.ResultSet;/*** @Author admin* @Date 2023/4/4 11:24*/
public interface ResultSetExtractor<T> {public abstract T extractData(ResultSet resulltSet);
}

（3）调用举例

1. 加载库和表

SqliteHelper sqlLiteHelper = new SqlLiteHelper(dbFilePath);  // 库
String createCard = "create table if not exists card(id integer primary key autoincrement,name text,lastReport text)";
sqlLiteHelper.executeUpdate(createCard); // 建表

2. sqlite-devel in linux

sqlite-devel

centos7.6
https://www.sqlite.org/download.html

yum install sqlite-devel

使用

sqlite 连接不需要用户名和密码

[root@localhost trdp]# sqlite3 trdp.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .database   
# 输出显示了当前连接的数据库文件路径。
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /usr/local/tynoo/trdp/trdp.dbsqlite> .tables
msgs  port
sqlite> .schema port
CREATE TABLE port
(port String
);
sqlite> select * from msgs;
sqlite> insert into port (port) values (null);
sqlite> .quit

三、更多应用

1. 数据类型

https://www.sqlite.net.cn/datatype3.html

2. 如何存储日期和时间

3. 备份

因为sqlite基于文件存储的特殊特性，
它的备份本质上是这个sqlite_database.db这个文件的备份，尤其是在单用户或低并发场景下，SQLite 3 的数据备份可以通过简单的文件复制cp来实现

但是这里就存在几个问题：
（1）文件 被锁定，比如说正在写入数据
（2）事务不一致，你复制的时候，某个事务正在进行中