1 简介

SQLite 作为数据库特别小，Android系统中自带，Windows中使用也特别方便。 下面代码是在不使用任何框架的情况下，操作SQLite。

2 工具类

2.1 ResultSetMapper

package com.wu.port.utils.sqlite; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.persistence.Column; import javax.persistence.Entity; import org.apache.commons.beanutils.BeanUtils; /** * @Author ：吴用 * @Date ：2020/9/3 16:12 * @Version ：1.0 */ public class ResultSetMapper { /** * 用于将 ResultSet 对象通过javaBean转换成List 对象，极大简化了操作 * @param rs * @param outputClass * @param <T> * @return */ @SuppressWarnings("unchecked") static public<T> List<T> mapRersultSetToObject(ResultSet rs, Class outputClass) { List<T> outputList = null; try { // make sure resultset is not null if (rs != null) { // check if outputClass has 'Entity' annotation if (outputClass.isAnnotationPresent(Entity.class)) { // get the resultset metadata ResultSetMetaData rsmd = rs.getMetaData(); // get all the attributes of outputClass Field[] fields = outputClass.getDeclaredFields(); while (rs.next()) { T bean = (T) outputClass.newInstance(); for (int _iterator = 0; _iterator < rsmd.getColumnCount(); _iterator++) { // getting the SQL column name String columnName = rsmd.getColumnName(_iterator + 1); // reading the value of the SQL column Object columnValue = rs.getObject(_iterator + 1); // iterating over outputClass attributes to check if // any attribute has 'Column' annotation with // matching 'name' value for (Field field : fields) { if (field.isAnnotationPresent(Column.class)) { Column column = field.getAnnotation(Column.class); if (column.name().equalsIgnoreCase(columnName) && columnValue != null) { BeanUtils.setProperty(bean, field.getName(), columnValue); break; } } } } if (outputList == null) { outputList = new ArrayList<T>(); } outputList.add(bean); } } else { // throw some error } } else { return null; } } catch (IllegalAccessException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } return outputList; } }

2.2 SqlController

package com.wu.port.utils.sqlite; import lombok.Getter; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; /** * 该类不管 SQL 语句只对操作进行了简单封装 * @Author ：吴用 * @Date ：2020/9/3 14:58 * @Version ：1.0 */ @Getter public class SqlController { @Getter private static SqliteConfig sqliteConfig; @Getter private static SqlController sqlController = new SqlController(); /** * 单例设计模式 */ private SqlController(){ sqliteConfig = new SqliteConfig(); } /** * 返回 PreparedStatement 对象，用于放置参数,无返回值 * @param sql * @return */ public void excuteSql(String sql,SqlTemplate sqlTemplate){ Connection c = sqliteConfig.getConnection(); try { PreparedStatement stmt = c.prepareStatement(sql); c.setAutoCommit(false); sqlTemplate.setPreparedStatement(stmt); // 最主要是就是这2行 stmt.addBatch(); // 执行 stmt.executeBatch(); // 提交 c.commit(); stmt.close(); c.close(); } catch ( Exception e ) { e.printStackTrace(); System.exit(0); } } /** * 返回用于执行大量的sql语句 * @param sql 单个sql模板 * @param sqlTemplates 执行方式的集合 */ public void excuteSqlMany(String sql,List<SqlTemplate> sqlTemplates){ Connection c = sqliteConfig.getConnection(); try { c.setAutoCommit(false); PreparedStatement stmt = c.prepareStatement(sql); for (int i = 0; i < sqlTemplates.size(); i++) { SqlTemplate sqlTemplate = sqlTemplates.get(i); sqlTemplate.setPreparedStatement(stmt); // 最主要是就是这2行 stmt.addBatch(); // 执行 stmt.executeBatch(); } // 提交 c.commit(); stmt.close(); c.close(); } catch ( Exception e ) { e.printStackTrace(); System.exit(0); } } /** * 主要用于查询操作 * @param sql * @param sqlTemplate */ public<E> List<E> excuteSqlAndGetData(String sql, SqlTemplate sqlTemplate,Class<E> clazz){ Connection c = sqliteConfig.getConnection(); PreparedStatement stmt = null; try { stmt = c.prepareStatement(sql); sqlTemplate.setPreparedStatement(stmt); ResultSet resultSet = stmt.executeQuery(); List<E> objects = ResultSetMapper.mapRersultSetToObject(resultSet, clazz); return objects; } catch ( Exception e ) { e.printStackTrace(); System.exit(0); }finally{ try { stmt.close(); c.close(); } catch (SQLException e) { e.printStackTrace(); } } return null; } /** * 通过这个接口获取PreparedStatement实例，然后放置对应的参数 */ public interface SqlTemplate{ /** * 在刚方法内部需要通过 preparedStatement 放置参数 * @param preparedStatement */ void setPreparedStatement(PreparedStatement preparedStatement); } /** * 关闭连接 */ public void close(){ sqliteConfig.close(); } }

2.3 SqliteConfig

这里需要修改数据库名（修改 databaseName 变量的值）

package com.wu.port.utils.sqlite; import lombok.Getter; import lombok.extern.slf4j.Slf4j; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * 需要注入成单例，创建的时候需要给定数据库名称 * * @Author ：吴用 * @Date ：2020/9/3 13:32 * @Version ：1.0 */ @Slf4j public class SqliteConfig { private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>(); @Getter private final static String databaseName = "test.db"; @Getter private final static String url = "jdbc:sqlite:" + databaseName; static { try { Class.forName("org.sqlite.JDBC"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 连接数据库 */ public SqliteConfig() { try { Connection temp = DriverManager.getConnection(url); threadLocal.set(temp); } catch (Exception e) { System.err.println(e.getClass().getName() + ": " + e.getMessage()); System.exit(0); } } /** * 获取连接 * * @return */ public Connection getConnection() { Connection connection = threadLocal.get(); boolean isNeedCreate = false; if (connection == null) { isNeedCreate = true; }else { try { if (connection.isClosed()) { isNeedCreate = true; } } catch (SQLException e1) { isNeedCreate = true; } } if (isNeedCreate) { Connection temp = null; try { temp = DriverManager.getConnection(url); } catch (SQLException e) { e.printStackTrace(); } threadLocal.set(temp); return temp; } else { return connection; } } /** * 释放连接对应线程的连接 */ public void close() { Connection c = threadLocal.get(); threadLocal.remove(); try { c.close(); } catch (SQLException e) { e.printStackTrace(); } } }

3 工具类的使用

1）sqlController的excuteSql方法没有返回结果，第一个参数是 sql 语句，第二个参数可以填写一个匿名函数，该匿名函数的形参是PreparedStatement类对象，方法体内只放置对应位置的"?“的参数，不做其他操作。 2）sqlController的excuteSqlAndGetData有返回值，返回值是List数组，第一个参数是 sql 语句，第二个参数可以填写一个匿名函数，该匿名函数的形参是PreparedStatement类对象，方法体内只放置对应位置的”?"的参数，不做其他操作。第三个参数是实体类的字节码对象，用于内省操作。

3.1 添加表

private void createTable(){ SqlController sqlController = SqlController.getSqlController(); String sql = "CREATE TABLE IF NOT EXISTS receiveData" + "(id INT PRIMARY KEY NOT NULL," + " par_id INT NOT NULL, " + " par_var INT NOT NULL, " + " receive_time DATE NOT NULL) "; sqlController.excuteSql(sql, stmt -> { }); }

3.2 插入数据

private void insert(){ SqlController sqlController = SqlController.getSqlController(); sqlController.excuteSql("INSERT INTO receiveData(id,par_id,par_var,receive_time) VALUES(?,?,?,?);" , stmt -> { try { stmt.setInt(1,1 ); stmt.setInt(2,20 ); stmt.setInt(3,200 ); stmt.setDate(4,new Date(new java.util.Date().getTime())); } catch (SQLException e) { e.printStackTrace(); } }); }

3.3 查询

查询的实体类对象

package com.wu.port.utils.protocol.pojo; import com.wu.port.utils.protocol.ProtocolData; import com.wu.port.utils.protocol.utils.UnsignedCharUtils; import lombok.Data; import javax.persistence.Column; import javax.persistence.Entity; import java.util.Date; /** * @Author ：吴用 * @Date ：2020-08-22 17:08 * @Version ：1.0 */ @Data @Entity public class ProtocolDataPojo { @Column(name = "id") private int databaseId; /** * 目标地址 */ private int dAddr; /** * 功能码 */ private int id; /** * 数据内容 */ @Column(name = "par_id") private int parId; @Column(name = "par_var") private int parVar; /** * 时间信息 */ @Column(name = "receive_time") private Date receiveTime; public ProtocolDataPojo() { } }

查询

private void find(){ SqlController sqlController = SqlController.getSqlController(); List<ProtocolDataPojo> protocolDataPojos = sqlController.excuteSqlAndGetData( "SELECT id,par_id,par_var,receive_time " + "FROM receiveData order by receive_time DESC" , stmt -> { }, ProtocolDataPojo.class); System.out.println(protocolDataPojos); }