java sqlite连接池 关于Java 项目封装sqlite连接池操作持久化数据的方法
BBinChina 人气:0想了解关于Java 项目封装sqlite连接池操作持久化数据的方法的相关内容吗，BBinChina在本文为您仔细讲解java sqlite连接池的相关知识和一些Code实例，欢迎阅读和指正，我们先划重点：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的操作，大家可以看菜鸟教程
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.30.1</version>
</dependency>
同时引入spring jdbc方便解析数据
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.12</version>
</dependency>
先编写测试用例
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();
}
}
再根据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<String, List<SqliteHelper>> sqlitePool = new HashMap<>();
/**
* sqlite对磁盘文件的操作是在一次连接上执行
*
* @param path sqlite数据存储的磁盘文件
* @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数据表，为磁盘文件名
* @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 <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;
}
}
}
加载全部内容