在Java中使用SQLite数据库
一、安装
下载最新的 Sqlite Jdbc 驱动程序jar文件，并添加到Java工程的class路径下；
二、使用
以 sqlite Jdbc 驱动版本为 sqlitejdbc-v56.jar 为例
SqliteHelper.java 类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
*
* 本类基于 sqlite jdbc v56
*
* @author haoqipeng
*/
public class SqliteHelper {
final static Logger logger = LoggerFactory.getLogger(SqliteHelper.class);
private Connection connection;
private Statement statement;
private ResultSet resultSet;
private String dbFilePath;
/**
* 构造函数
* @param dbFilePath sqlite db 文件路径
* @throws ClassNotFoundException
* @throws SQLException
*/
public SqliteHelper(String dbFilePath) throws ClassNotFoundException, SQLException {
this.dbFilePath = dbFilePath;
connection = getConnection(dbFilePath);
}
/**
* 获取数据库连接
* @param dbFilePath db文件路径
* @return 数据库连接
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
Connection conn = null;
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
return conn;
}
/**
* 执行sql查询
* @param sql sql select 语句
* @param rse 结果集处理类对象
* @return 查询结果
* @throws SQLException
* @throws ClassNotFoundException
*/
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();
}
}
/**
* 执行select查询，返回结果列表
*
* @param sql sql select 语句
* @param rm 结果集的行数据处理类对象
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
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;
}
/**
* 执行数据库更新sql语句
* @param sql
* @return 更新行数
* @throws SQLException
* @throws ClassNotFoundException
*/
public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
try {
int c = getStatement().executeUpdate(sql);
return c;
} finally {
destroyed();
}
}
/**
* 执行多个sql更新语句
* @param sqls
* @throws SQLException
* @throws ClassNotFoundException
*/
public void executeUpdate(String...sqls) throws SQLException, ClassNotFoundException {
try {
for (String sql : sqls) {
getStatement().executeUpdate(sql);
}
} finally {
destroyed();
}
}
/**
* 执行数据库更新 sql List
* @param sqls sql列表
* @throws SQLException
* @throws ClassNotFoundException
*/
public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
try {
for (String sql : sqls) {
getStatement().executeUpdate(sql);
}
} finally {
destroyed();
}
}
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 void destroyed() {
try {
if (null != connection) {
connection.close();
connection = null;
}
if (null != statement) {
statement.close();
statement = null;
}
if (null != resultSet) {
resultSet.close();
resultSet = null;
}
} catch (SQLException e) {
logger.error("Sqlite数据库关闭时异常", e);
}
}
}
ResltSetExtractor.java 结果集处理类
import java.sql.ResultSet;
public interface ResultSetExtractor<T> {
public abstract T extractData(ResultSet rs);
}
RowMapper.java 结果集行数据处理类
import java.sql.ResultSet;
import java.sql.SQLException;
public interface RowMapper<T> {
public abstract T mapRow(ResultSet rs, int index) throws SQLException;
}
SqliteTest.java 测试类
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.junit.Test;
public class SqliteTest {
@Test
public void testHelper() {
try {
SqliteHelper h = new SqliteHelper("testHelper.db");
h.executeUpdate("drop table if exists test;");
h.executeUpdate("create table test(name varchar(20));");
h.executeUpdate("insert into test values('sqliteHelper test');");
List<String> sList = h.executeQuery("select name from test", new RowMapper<String>() {
@Override
public String mapRow(ResultSet rs, int index)
throws SQLException {
return rs.getString("name");
}
});
System.out.println(sList.get(0));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
测试输出结果
sqliteHelper test
下面关于SQLite相关的内容你可能也喜欢：
SQLite 的详细介绍：请点这里
SQLite 的下载地址：请点这里