没有spring boot框架集成sqlite 数据库（新手研究）

前言

sqlite 数据库是一款轻量级数据库，不需要像 MySQL 那样需要在机器上安装，占用端口等，它可以通过一个 .db 文件 就能存库，存数据。
官网 https://www.sqlite.org/index.html
网上对于 sqlite 数据库 的使用场景，大多在 Android 系统中，我使用的场景是 开发一个 引擎工具包，供 desktop app 或者命令行直接使用，这个工具包所需要写库操作，所以选用此 数据库。
废话不多说……

一、sqlite 基本使用

1. 下载操作

官网直接下载：https://www.sqlite.org/download.html
根据所需版操作系统版本下载对应的 zip 包即可，我在 Windows 机器上使用的，下载 zip 包解压后，它有三个文件

足见其轻量型，解压后配置环境变量即可， sqlite3.exe 在命令行执行可以进入到库中，后根数据库文件 .db，就可以打开数据文件进行操作了。

2. 基本操作使用

参考：https://www.cnblogs.com/aeolian/p/16179638.html

创建数据库

-- 直接创建数据库
sqlite3 DatabaseName.db
-- 进入sqlite环境创建数据库
.open test.db
-- 查看当前数据库
.databases
-- 查看所有表
SELECT tbl_name FROM sqlite_master WHERE type = 'table';
-- 退出
.quit

导入导出数据库

-- 导出
sqlite3 testDB.db .dump > testDB.sql
-- 导入
sqlite3 testDB.db < testDB.sql

建表

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   WEBSITE        CHAR(200),
   RANK         INT
);

--查看表结构
.schema

数据增删改查

新增insert
-- insert
INSERT INTO COMPANY (ID,NAME,WEBSITE,RANK)
VALUES (1, 'Walmart Inc. ', 'https://www.stock.walmart.com', 1);
INSERT INTO COMPANY (ID,NAME,WEBSITE,RANK)
VALUES (2, 'Amazon.com, Inc.', 'https://www.amazon.com', 2);
INSERT INTO COMPANY (ID,NAME,WEBSITE,RANK)
VALUES (3, 'Apple Inc. ', 'https://www.apple.com', 3);
删除delete
DELETE FROM COMPANY WHERE ID = 3;
修改update
UPDATE COMPANY SET RANK = 10 where id = 2;

查询select

-- 显示表头
.header on
-- 表头按列对齐
.mode column
-- 查询
SELECT * FROM COMPANY;

文件数据导入sqlite

方式一：Excel导入sqlite
excel文件一般是以xslx格式存储，导入sqlite3数据库前需要另存为csv格式。
当csv格式文件中存在逗号(’,’) 和换行符需要替换掉。因为逗号是csv中column的分割符，而换行会被认为是excel中的下一个row,而且这两种字符会影响sqlite对导入数据的解析。这两个字符可以用一些特殊的字符（！@＃￥＃的组合）替换掉。简而言之，csv格式文件中不要出现逗号和换行符。
待导入的csv文件中只保留纯数据文本，不需要表头。

#1 打开或创建数据库test.db
./sqlite3 test.db

#2 创建tb
create table tb(method_id interger primary key,ic TEXT,oi TEXT, oi_name TEXT);

#3 更改显示信息的分隔符（默认分隔符 '|'）
.separator ','

#4 导入数据到已创建的列表中
.import data.csv tb

方式二：txt文本导入sqlite3
excel拷贝到txt本文中的数据会包含tab键，我们只需要将tab键替换为非（逗号和换行符）之外的且不影响sqlite3数据解析的其他符号即可，如‘*’.（因为是分隔符，一定不能使用txt中已经存在的符号，否则会出现“数据类型不符”的错误）
tab键替换：在txt文本中复制一个tab键到文本快捷替换中就可以了。

#3 更改显示信息的分隔符，设置为自己更换的符号
.separator '*'

#4 导入数据到已创建的列表中
.import data.txt tablename

sqlite3数据导出

.output file.txt tablename

三、Java中使用 sqlite数据库

1. maven项目中导入依赖

	<!-- sqlite-jdbc -->
	<dependency>
		<groupId>org.xerial</groupId>
		<artifactId>sqlite-jdbc</artifactId>
		<version>3.39.3.0</version>
	</dependency>
	<!-- mybatis -->
	<dependency>
	   <groupId>com.baomidou</groupId>
	    <artifactId>mybatis-plus</artifactId>
	    <version>3.5.2</version>
	</dependency>
	<dependency>
	    <groupId>org.mybatis</groupId>
	    <artifactId>mybatis</artifactId>
	    <version>3.5.10</version>
	</dependency>
<>

2. 创建 SqliteConfig 类配置 ，使用 mybatis 对 sqlite 数据库进行操作

package org.sqlite;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
@Slf4j
public class Sqlite3Config {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        // 指定 .db 文件的路径
        String fileParth = "";
        File dbFile = new File(fileParth);
        try {
            InputStream stream = Resources.getResourceAsStream("mybatis-config.xml");
            Properties properties = new Properties();
            // 配置 文件路径，传入 mybatis-config.xml 文件的 ${url} 中
            properties.put("url",JDBC.PREFIX + dbFile.getPath());
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            sqlSessionFactory = builder.build(stream,properties);
            Configuration configuration = sqlSessionFactory.getConfiguration();
            configuration.setMapUnderscoreToCamelCase(false);
        } catch (IOException e) {
            e.printStackTrace();
            log.error("database connection failed!");
        }
    }

	/**
	*   获取 sqlsession 用来和 mapper 层交流
	*/
    public static SqlSession getSqlSesstion(boolean autocommit){
        return sqlSessionFactory.openSession(autocommit);
    }

}

3. mybatis-config.xml 文件的配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 配置文件的根元素 -->
<configuration>
    <!-- 属性：定义配置外在化 -->
    <properties/>
    <!-- 设置：定义mybatis的一些全局性设置 -->
    <settings>
        <!-- STDOUT_LOGGING 控制台输出日志， NO_LOGGING 无多余日志输出 -->
        <setting name="logImpl" value="NO_LOGGING"/>
    </settings>
    <!-- 类型名称：为一些类定义别名 -->
    <typeAliases/>
    <!-- 类型处理器：定义Java类型与数据库中的数据类型之间的转换关系 -->
    <typeHandlers/>
    <!-- 对象工厂 -->
    <objectFactory type=""/>
    <!-- 插件：mybatis的插件,插件可以修改mybatis的内部运行规则 -->
    <plugins>
        <plugin interceptor=""/>
    </plugins>
    <!-- 环境：配置mybatis的环境 -->
    <environments default="sqlite">
        <!-- 环境变量：可以配置多个环境变量，比如使用多数据源时，就需要配置多个环境变量 -->
        <environment id="sqlite">
            <!-- 事务管理器 -->
            <transactionManager type="JDBC"/>
            <!-- 数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="org.sqlite.JDBC"/>
                <!--   此处的 ${url} 用于接收 sqliteConfig 文件中 properties 中的 url   -->
                <property name="url" value="${url}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 数据库厂商标识 -->
    <databaseIdProvider type=""/>
    <!-- 映射器：指定映射文件或者映射类 -->
    <mappers>
        <mapper class="org.example.TestMapper"/>
        <!--   ……     -->
    </mappers>
</configuration>

4. 下面是mybatis 对 sqlite3config 创建 sqlsession 的封装，使用 代理的方式，将创建 mapper 的过程封装起来，注意，这个封装设置了 sqlsession 为自动提交，所以可以用于查询操作，但是增、删、改操作不建议使用

package org.sqlite;

import org.apache.ibatis.session.SqlSession;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;

public class MapperInterceptor<T> implements InvocationHandler {
    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
    	// 创建的 sqlsession 为自动提交
        try (SqlSession sqlSession = Sqlite3Config.getSqlSesstion(true)){
            Class<?> aClass = method.getDeclaringClass();
            T mapper = (T)sqlSession.getMapper(aClass);
            return method.invoke(mapper,args);
        }
    }
}

package org.sqlite;

import java.lang.reflect.Proxy;

public class MapperProxy {

    public static <T> T getProxyInstance(Class<T> tClass){
        return (T) Proxy.newProxyInstance(tClass.getClassLoader(),
                new Class[]{tClass},
                new MapperInterceptor<T>());
    }

}

使用方式如下： 创建后的 mapper 可以用于查询操作

private TestMapper mapper = MapperProxy.getProxyInstance(TestMapper.class);

如果要进行批量增删改操作，用下面方式，手动提交

public int addBatch(List<String> stringList){
        try (SqlSession sqlSession = Sqlite3Config.getSqlSesstion(false)){
            TestMapper mapper = sqlSession.getMapper(TestMapper.class);
            stringList.forEach(s -> {
                mapper.insert(s);
            });
            sqlSession.commit();
            return stringList.size();
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

附录，最开始研究的时候，使用的最初是一套（已弃用）
参考：https://blog.csdn.net/qq_42413011/article/details/118640420

    public static SqlSessionFactory getSqlSessionFactory(){
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        MybatisConfiguration configuration = (MybatisConfiguration) new Configuration();
        configuration.setMapUnderscoreToCamelCase(false);
        configuration.setUseGeneratedKeys(true);
        configuration.addInterceptor(mybatisPlusInterceptor());

        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setSqlInjector(new DefaultSqlInjector());
        globalConfig.setIdentifierGenerator(new DefaultIdentifierGenerator());
//        globalConfig.setSuperMapperClass()
        globalConfig.setDbConfig(new GlobalConfig.DbConfig().setUpdateStrategy(FieldStrategy.IGNORED));
        GlobalConfigUtils.setGlobalConfig(configuration,globalConfig);

        try {
            registryMapperXml(configuration,"mapper");

        }catch (Exception e){
            e.printStackTrace();
        }
        Environment environment = new Environment("1", new JdbcTransactionFactory(), initDatasource());
        configuration.setEnvironment(environment);
        SqlSessionFactory sqlSessionFactory = builder.build(configuration);
        return sqlSessionFactory;

    }

    private static DataSource initDatasource() {
        SQLiteDataSource dataSource = new SQLiteDataSource();
        //数据库文件的路径
        String fileParth = "";
        File dbFile = new File(fileParth);
        dataSource.setUrl(JDBC.PREFIX+dbFile.getPath());
        return dataSource;
    }

    private static void registryMapperXml(MybatisConfiguration configuration, String classPath) throws IOException {
        ClassLoader contextClassLoader = Thread.currentThread().getContextClassLoader();
        Enumeration<URL> mapper = contextClassLoader.getResources(classPath);
        while (mapper.hasMoreElements()) {
            URL url = mapper.nextElement();
            if (url.getProtocol().equals("file")) {
                String path = url.getPath();
                File file = new File(path);
                File[] files = file.listFiles();
                for (File f : files) {
                    FileInputStream in = new FileInputStream(f);
                    XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(in, configuration, f.getPath(), configuration.getSqlFragments());
                    xmlMapperBuilder.parse();
                    in.close();
                }
            } else {
                JarURLConnection urlConnection = (JarURLConnection) url.openConnection();
                JarFile jarFile = urlConnection.getJarFile();
                Enumeration<JarEntry> entries = jarFile.entries();
                while (entries.hasMoreElements()) {
                    JarEntry jarEntry = entries.nextElement();
                    if (jarEntry.getName().endsWith(".xml")) {
                        InputStream in = jarFile.getInputStream(jarEntry);
                        XMLMapperBuilder xmlMapperBuilder = new XMLMapperBuilder(in, configuration, jarEntry.getName(), configuration.getSqlFragments());
                        xmlMapperBuilder.parse();
                        in.close();
                    }
                }
            }
        }
    }


    private static Interceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.SQLITE));
        return interceptor;
    }

这种方式没有使用 mybatis-config.xml 配置文件，将所有的配置数据通过代码的形式做了配置，

• registryMapperXml（）方法是将所有的 mapper.xml 操作数据库的文件 扫描出来，再注册到MybatisConfiguration 中
• getSqlSessionFactory（）方法是做 配置属性 properties 的操作
• initDatasource（）方法是初始化数据源
• mybatisPlusInterceptor（）常见的 mybatisplus 的拦截器设置

以此记录，其中也出现了很多坑，比如，写操作的时候，sqlite 会上锁，导致其他操作失败，这也是刚开始脱离熟悉的 springboot 框架后，单独使用mybatis 框架操作数据库，没有了依赖，使用原生的方式，还需要对底层进行更深入的理解。
未来道阻且长，继续努力。