Java 操作 sqlite 工具类

目录

问题

代码

pom

sqlite工具类

测试类

---

问题

项目中通过 db 文件来上传数据，导出数据也需要导出成 db 文件。

需要通过 sqlite 来读写 db 文件，实现创建表，插入数据，查询数据方法。

代码

pom

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.40.0.0</version>
</dependency>

sqlite工具类

public class SqliteUtil {
    private static Connection connection = null;

    /**
     * 设置连接
     */
    public static void setConnection(String path) {
        try {
//            声明驱动类型
            Class.forName("org.sqlite.JDBC");
//            设置 sqlite文件路径，等同于mysql连接地址(jdbc:mysql://127.0.0.1:3306/test)
            String url = "jdbc:sqlite:" + path;
//            获取连接
            connection = DriverManager.getConnection(url);
            connection.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("建立Sqlite连接失败");
        }
    }

    /**
     * 创建表
     * @param tableName 要创建的表的名称
     * @param sourceList 表结构
     */
    public synchronized static void create(String tableName, List<Map<String, Object>> sourceList) {
        String column, type, targetString;
        long length;
        StringBuilder createBuilder = new StringBuilder();
        createBuilder.append(String.format("CREATE TABLE %s( ", tableName));
        try {
            Statement dropStatement = connection.createStatement();
            dropStatement.executeUpdate(String.format("DROP TABLE IF EXISTS %s", tableName));
            dropStatement.close();

            for (Map<String, Object> map : sourceList) {
                column = map.get("COLUMN_NAME").toString();
                type = map.get("DATA_TYPE").toString();
                length = Long.parseLong(map.get("DATA_LENGTH").toString());

                targetString = String.format("%s %s(%s), ", column, type, length);
                createBuilder.append(targetString);
            }
            createBuilder.deleteCharAt(createBuilder.lastIndexOf(",")).append(" );");

            Statement statement = connection.createStatement();
            statement.execute(createBuilder.toString());
            statement.close();

        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("建表失败，表名称：" + tableName);
        }
    }

    /**
     * 表中插入数据
     * @param tableName 表名
     * @param sourceList 待插入的对象集合
     */
    public synchronized static void insert(String tableName, List<Map<String, Object>> sourceList) {
        try {
            StringBuilder fieldBuilder, valueBuilder;
            String field, targetValue;
            Object value;
            for (Map<String, Object> map : sourceList) {
                fieldBuilder = new StringBuilder().append(String.format("INSERT INTO %s(", tableName));
                valueBuilder = new StringBuilder().append("VALUES(");
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    field = entry.getKey();
                    value = entry.getValue();

                    fieldBuilder.append(field).append(",");

                    targetValue = value instanceof Date
                            ? String.format("'%s', ", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value))
                            : value instanceof String ? String.format("'%s', ", value) : String.format("%s, ", value);
                    valueBuilder.append(targetValue);
                }
                fieldBuilder.deleteCharAt(fieldBuilder.lastIndexOf(",")).append(") ");
                valueBuilder.deleteCharAt(valueBuilder.lastIndexOf(",")).append("); ");
                fieldBuilder.append(valueBuilder);

                PreparedStatement prep = connection.prepareStatement(fieldBuilder.toString());
                prep.executeUpdate();
                prep.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String, Object>> select(String tableName) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        Map<String, Object> tempMap;
        try {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(String.format("SELECT * FROM %s", tableName));
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
            while ( rs.next() ) {
                tempMap = new HashMap<>();
                Object obj;
                for (int i = 1; i <= columnCount; i++) {
                    obj = rs.getObject(i);
                    if (md.getColumnTypeName(i).toUpperCase().contains("DATE")) {
                        LocalDateTime localDateTime = LocalDateTime.parse(obj.toString(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                        tempMap.put(md.getCatalogName(i), Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
                    } else {

                        tempMap.put(md.getColumnName(i), obj instanceof Number ? new BigDecimal(obj.toString()) : obj);
                    }
                }
                resultList.add(tempMap);
            }
            rs.close();
            stmt.close();
        } catch (Exception ex) {
            throw new RuntimeException(ex.getMessage());
        }

        return resultList;
    }

    /**
     * 关闭链接
     */
    public static void endConnection() {
        try {
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

测试类

class SqliteUtilTest {

    @Test
    public void test() {
        String tableName = "TEST";

        List<Map<String, Object>> createList = new ArrayList<>();
        Map<String, Object> map1 = new HashMap<>();
        map1.put("COLUMN_NAME", "F1");
        map1.put("DATA_TYPE", "Date");
        map1.put("DATA_LENGTH", 7);

        Map<String, Object> map2 = new HashMap<>();
        map2.put("COLUMN_NAME", "F2");
        map2.put("DATA_TYPE", "VARCHAR2");
        map2.put("DATA_LENGTH", 255);

        Map<String, Object> map0 = new HashMap<>();
        map0.put("COLUMN_NAME", "F3");
        map0.put("DATA_TYPE", "NUMBER");
        map0.put("DATA_LENGTH", 22);

        createList.add(map0);
        createList.add(map1);
        createList.add(map2);

        List<Map<String, Object>> insertList = new ArrayList<>();
        Map<String, Object> map3 = new HashMap<>();
        map3.put("F1", new Date());
        map3.put("F2", "dfssdg");
        map3.put("F3", 3);

        Map<String, Object> map4 = new HashMap<>();
        map4.put("F1", new Date());
        map4.put("F2", "gfds");
        map4.put("F3", 6);
        insertList.add(map3); insertList.add(map4);

        SqliteUtil.setConnection("C:\\Users\\18824\\Desktop\\test.db");
        SqliteUtil.create(tableName, createList);
        SqliteUtil.insert(tableName, insertList);
        List<Map<String, Object>> mapList = SqliteUtil.select(tableName);
        for (Map<String, Object> map : mapList) {
            System.out.println(map.toString());
        }
        SqliteUtil.endConnection();

    }

}