JDBC操作SQLite的工具类（已经对操作进行了很大程度的简化）

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);
}