sqlite是C實(shí)現(xiàn)的一個(gè)開(kāi)源SQL引擎，其api提供sql語(yǔ)法支持，通過(guò)sql解析后對(duì)存儲(chǔ)層的磁盤(pán)文件進(jìn)行操作，完整配置的sqlite庫(kù)小于400kb，多用于移動(dòng)端應(yīng)用，小型項(xiàng)目中。

對(duì)Sqlite有興趣的可以了解下其體系結(jié)構(gòu)

在這里插入圖片描述

之前自研SQL解析器的時(shí)候便是借鑒了SQLcompiler的源碼，這里不展開(kāi)介紹

封裝Java的Sqlite連接池

首先maven項(xiàng)目引入依賴sqlite-jdbc，其主要是java版的sqliteapi，關(guān)于Sqlite api的操作，大家可以看菜鳥(niǎo)教程

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

同時(shí)引入spring jdbc方便解析數(shù)據(jù)

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.12</version>
        </dependency>

先編寫(xiě)測(cè)試用例

import org.junit.Test;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class TestSqliteHelper {

    @Test
    public void test() throws SQLException, ClassNotFoundException {
        SqliteHelper sqliteHelper = SqliteHelper.GetSqliteHelper("test.db");

        String sql = "CREATE TABLE COMPANY " +
                "(ID INT PRIMARY KEY     NOT NULL," +
                " NAME           TEXT    NOT NULL, " +
                " AGE            INT     NOT NULL, " +
                " ADDRESS        CHAR(50), " +
                " SALARY         REAL)";
        
        sqliteHelper.ExecuteUpdate(sql);

        sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                "VALUES (1, 'Paul', 32, 'California', 20000.00 );";

        sqliteHelper.ExecuteUpdate(sql);

        sql = "SELECT * FROM COMPANY;";

        List<String> datas = new LinkedList<>();
        datas = sqliteHelper.ExecuteQuery(sql, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int index)
                            throws SQLException {
                        return rs.getString("NAME");

                    }
                }
        );

        sqliteHelper.PutSqliteHelper();
    }
}

再根據(jù)TDD實(shí)現(xiàn)ExecuteUpdate\GetSqliteHelper、ExecuteQuery、PutSqliteHelper等方法

import org.springframework.jdbc.core.RowMapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;

public class SqliteHelper {

    /**
     * 磁盤(pán)文件名 即db
     */
    private String path = null;

    /**
     * 操作鏈接
     */
    private Connection connection = null;

    /**
     * 語(yǔ)法執(zhí)行層
     */
    private Statement statement = null;

    /**
     * 構(gòu)建一次鏈接
     *
     * @param path
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    SqliteHelper(String path) throws SQLException, ClassNotFoundException {
        this.path = path;
        this.connection = this.getConnect(path);
    }

    /**
     * 讀寫(xiě)鎖，也可以使用ConcurrentHashMap
     */
    static ReentrantLock hashMapLock = new ReentrantLock();

    /**
     * Sqlite的連接池
     */
    static HashMap<String, List<SqliteHelper>> sqlitePool = new HashMap<>();

    /**
     * sqlite對(duì)磁盤(pán)文件的操作是在一次連接上執(zhí)行
     *
     * @param path sqlite數(shù)據(jù)存儲(chǔ)的磁盤(pán)文件
     * @return
     */
    public static SqliteHelper GetSqliteHelper(String path) throws SQLException, ClassNotFoundException {
        hashMapLock.lock();
        List<SqliteHelper> sqliteHelpers = sqlitePool.get(path);
        if (sqliteHelpers == null) {
            sqliteHelpers = new LinkedList<>();
            sqlitePool.put(path, sqliteHelpers);
        }
        SqliteHelper sqliteHelper = new SqliteHelper(path);
        sqliteHelpers.add(sqliteHelper);
        return sqliteHelper;
    }

    public void PutSqliteHelper() throws SQLException {
        hashMapLock.lock();
        List<SqliteHelper> sqliteHelpers = sqlitePool.get(this.path);
        if (sqliteHelpers == null) {
            sqliteHelpers = new LinkedList<>();
            sqlitePool.put(path, sqliteHelpers);
        }
        if(sqliteHelpers.size() > 2){
            releaseConn();
        }else{
            sqliteHelpers.add(this);
        }
    }
    /**
     * 獲取Sqlite操作鏈接
     *
     * @param path sqlite數(shù)據(jù)表，為磁盤(pán)文件名
     * @return
     */
    private Connection getConnect(String path) throws ClassNotFoundException, SQLException {
        Connection c = null;
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:" + path);
        return c;
    }

    /**
     * @param sql 執(zhí)行的sqlite 語(yǔ)句
     * @param row
     * @param <T> 映射的模板
     * @return
     */
    public <T> List<T> ExecuteQuery(String sql, RowMapper<T> row) throws SQLException {
        try {
            List<T> datas = new ArrayList<>();
            ResultSet resultSet = getStmt().executeQuery(sql);
            while (resultSet.next()) {
                datas.add(row.mapRow(resultSet, resultSet.getRow()));
            }
            resultSet.close();
            return datas;
        } finally {
            releaseConn();
        }
    }

    public void ExecuteUpdate(String sql) throws SQLException {
        getStmt().executeUpdate(sql);
    }

    private Statement getStmt() throws SQLException {
        if (this.statement == null) {
            this.statement = this.connection.createStatement();
        }
        return this.statement;
    }


    private void releaseConn() throws SQLException {
        if (this.connection != null) {
            this.connection.close();
            this.connection = null;
        }

        if (this.statement != null) {
            this.statement.close();
            this.statement = null;
        }
    }

}

到此這篇關(guān)于Java 項(xiàng)目封裝sqlite連接池操作持久化數(shù)據(jù)的文章就介紹到這了,更多相關(guān)java sqlite連接池操作內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: