关于Java 项目封装sqlite连接池操作持久化数据的方法

关于Java 项目封装sqlite连接池操作持久化数据的方法

Sqlite

sqlite是C实现的一个开源SQL引擎，其api提供sql语法支持，通过sql解析后对存储层的磁盘文件进行操作，完整配置的sqlite库小于400kb，多用于移动端应用，小型项目中。

对Sqlite有兴趣的可以了解下其体系结构

之前自研SQL解析器的时候便是借鉴了SQLcompiler的源码，这里不展开介绍

封装java的Sqlite连接池

首先maven项目引入依赖sqlite-jdbc，其主要是java版的sqliteapi，关于Sqlite api的操作，大家可以看菜鸟教程

org.xerial

sqlite-jdbc

3.30.1

同时引入spring jdbc方便解析数据

org.springframework

spring-jdbc

5.3.12

先编写测试用例

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, " +

whpFO " 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 datas = new LinkedList<>();

datas = sqliteHelper.ExecuteQuery(sql, new RowMapper() {

@Override

http:// public String mapRow(ResultSet rs, int index)

throws SQLException {

return rs.getString("NAME");

}

}

);

sqliteHelper.PutSqliteHelper();

}

}

再根据TDD实现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 {

/**

* 磁盘文件名 即db

*/

private String path = null;

/**

* 操作链接

*/

private Connection connection = null;

/**

* 语法执行层

*/

private Statement statement = null;

/**

* 构建一次链接

*

* @param path

* @throws SQLException

* @throws ClassNotFoundException

*/

SqliteHelper(String path) throws SQLException, ClassNotFoundException {

this.path = path;

this.connection = this.getConnect(path);

}

/**

* 读写锁，也可以使用ConcurrentHashMap

*/

static ReentrantLock hashMapLock = new ReentrantLock();

/**

* Sqlite的连接池

*/

static HashMap> sqlitePool = new HashMap<>();

/**

* sqlite对磁盘文件的操作是在一次连接上执行

*

* @param path sqlite数据存储的磁盘文件

* @return

*/

public static SqliteHelper GetSqliteHelper(String path) throws SQLException, ClassNotFoundException {

hashMapLock.lock();

List 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 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数据表，为磁盘文件名

* @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 执行的sqlite 语句

* @param row

* @param 映射的模板

* @return

*/

public List ExecuteQuery(String sql, RowMapper row) throws SQLException {

try {

List 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;

}

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。