package com.sqlite_DaoHelper;
|
import java.io.File;
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
|
*
|
* 本类基于 sqlite jdbc v56
|
*
|
* @author haoqipeng
|
*/
|
public class SqliteHelper {
|
private static Connection connection;
|
private static Statement statement;
|
private static ResultSet resultSet;
|
private static String dbFilePath;
|
|
|
// 构造函数
|
//* dbFilePath sqlite db 文件路径
|
// ClassNotFoundException
|
//@throws SQLException
|
|
/* public SqliteHelper(String dbFilePath) throws ClassNotFoundException, SQLException {
|
this.dbFilePath = dbFilePath;
|
connection = getConnection(dbFilePath);
|
}*/
|
public static void getSqliteHelper() throws ClassNotFoundException, SQLException{
|
|
String path=System.getProperty("user.dir");
|
// System.out.println(path);
|
dbFilePath = path+"/app/src/main/java/com/sqlite_DaoHelper/fbs9600.db";
|
//System.out.println(dbFilePath);
|
connection = SqliteHelper.getConnection(dbFilePath);
|
// System.out.println(connection);
|
}
|
/**
|
* 获取数据库连接
|
* @param dbFilePath db文件路径
|
* @return 数据库连接
|
* @throws ClassNotFoundException
|
* @throws SQLException
|
*/
|
public static Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
|
Connection conn = null;
|
Class.forName("org.sqlite.JDBC");
|
conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
|
return conn;
|
}
|
|
/**
|
* 执行sql查询
|
*/
|
public static List executeQuery(String sql,Connection conn,Object[] values,CallBack call) throws SQLException, ClassNotFoundException {
|
//DBUtil.getConnections();
|
PreparedStatement ps = null;
|
ResultSet rs = null;
|
try {
|
ps = conn.prepareStatement(sql);
|
ps.setQueryTimeout(600);
|
//当查询条件不为空时,设置查询条件对应的值
|
if(values != null){
|
for (int i = 0; i < values.length; i++) {
|
ps.setObject(i+1, values[i]);
|
}
|
}
|
rs=ps.executeQuery();
|
return call.getResults(rs);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}finally{
|
destroyed();
|
//DBUtil.close(rs, ps, conn);
|
//System.out.println("释放之后++++++++++++");
|
//DBUtil.getConnections();
|
}
|
return null;
|
}
|
|
/**
|
* 执行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 static 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) {
|
|
}
|
}
|
|
public static void main(String[] args) throws ClassNotFoundException, SQLException{
|
//SqliteHelper.getSqliteHelper();
|
String sql="SELECT * from tb_battinf ";
|
getSqliteHelper();
|
List list=SqliteHelper.executeQuery(sql, connection, null, new CallBack() {
|
@Override
|
public List getResults(ResultSet rs) {
|
List list=new ArrayList();
|
try {
|
while(rs.next()){
|
list.add(rs.getString("StationId"));
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
return list;
|
}
|
});
|
for (int i = 0; i < list.size(); i++) {
|
System.out.println(list.get(i));
|
}
|
}
|
}
|
