在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 {@Testpublic 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>() {@Overridepublic 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