importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importorg.slf4j.Logger;importorg.slf4j.LoggerFactory;/*** sqlite帮助类,直接创建该类示例,并调用相应的借口即可对sqlite数据库进行操作
*
* 本类基于 sqlite jdbc v56
*
*@authorhaoqipeng*/
public classSqliteHelper {final static Logger logger = LoggerFactory.getLogger(SqliteHelper.class);privateConnection connection;privateStatement statement;privateResultSet resultSet;privateString dbFilePath;/*** 构造函数
*@paramdbFilePath sqlite db 文件路径
*@throwsClassNotFoundException
*@throwsSQLException*/
public SqliteHelper(String dbFilePath) throwsClassNotFoundException, SQLException {this.dbFilePath =dbFilePath;
connection=getConnection(dbFilePath);
}/*** 获取数据库连接
*@paramdbFilePath db文件路径
*@return数据库连接
*@throwsClassNotFoundException
*@throwsSQLException*/
public Connection getConnection(String dbFilePath) throwsClassNotFoundException, SQLException {
Connection conn= null;
Class.forName("org.sqlite.JDBC");
conn= DriverManager.getConnection("jdbc:sqlite:" +dbFilePath);returnconn;
}/*** 执行sql查询
*@paramsql sql select 语句
*@paramrse 结果集处理类对象
*@return查询结果
*@throwsSQLException
*@throwsClassNotFoundException*/
public T executeQuery(String sql, ResultSetExtractor rse) throwsSQLException, ClassNotFoundException {try{
resultSet=getStatement().executeQuery(sql);
T rs=rse.extractData(resultSet);returnrs;
}finally{
destroyed();
}
}/*** 执行select查询,返回结果列表
*
*@paramsql sql select 语句
*@paramrm 结果集的行数据处理类对象
*@return*@throwsSQLException
*@throwsClassNotFoundException*/
public List executeQuery(String sql, RowMapper rm) throwsSQLException, ClassNotFoundException {
List rsList = new ArrayList();try{
resultSet=getStatement().executeQuery(sql);while(resultSet.next()) {
rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
}
}finally{
destroyed();
}returnrsList;
}/*** 执行数据库更新sql语句
*@paramsql
*@return更新行数
*@throwsSQLException
*@throwsClassNotFoundException*/
public int executeUpdate(String sql) throwsSQLException, ClassNotFoundException {try{int c =getStatement().executeUpdate(sql);returnc;
}finally{
destroyed();
}
}/*** 执行多个sql更新语句
*@paramsqls
*@throwsSQLException
*@throwsClassNotFoundException*/
public void executeUpdate(String...sqls) throwsSQLException, ClassNotFoundException {try{for(String sql : sqls) {
getStatement().executeUpdate(sql);
}
}finally{
destroyed();
}
}/*** 执行数据库更新 sql List
*@paramsqls sql列表
*@throwsSQLException
*@throwsClassNotFoundException*/
public void executeUpdate(List sqls) throwsSQLException, ClassNotFoundException {try{for(String sql : sqls) {
getStatement().executeUpdate(sql);
}
}finally{
destroyed();
}
}private Connection getConnection() throwsClassNotFoundException, SQLException {if (null == connection) connection =getConnection(dbFilePath);returnconnection;
}private Statement getStatement() throwsSQLException, ClassNotFoundException {if (null == statement) statement =getConnection().createStatement();returnstatement;
}/*** 数据库资源关闭和释放*/
public voiddestroyed() {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);
}
}
}