嵌入式数据库 sqllite & h2 utils

使用场景：

　　　简单脚本，但是有需要数据记录． （使用前升级下版本）

          我的使用：　老机器，老项目，jkd6,  需要记录

 

SqlLiteUtils

package com.sea.edi.listener;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/***************************
 *<pre>
 * @Project Name : edi-kafka-service
 * @Package      : com.icil.edi.listener
 * @File Name    : SqlLiteUtils
 * @Author       :  Sea
 * @Date         : 12/14/22 3:53 PM
 * @Purpose      :
 * @History      :
 *</pre>
 * <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
 *         <dependency>
 *             <groupId>org.xerial</groupId>
 *             <artifactId>sqlite-jdbc</artifactId>
 *             <version>3.21.0</version>
 *         </dependency>
 ***************************/
public class SqlLiteUtils {

    static Connection connection = null;

    public static Connection getConnection() throws Exception {
        if(connection==null||connection.isValid(15)){
            synchronized (SqlLiteUtils.class){
                if(connection==null||connection.isValid(15)){
                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection("jdbc:sqlite:FileCopy.db");
//                connection = DriverManager.getConnection("jdbc:sqlite:/home/sealiu/FileCopy.db");
                connection.setAutoCommit(true);}
            }
        }
        return connection;
    }


    /**
     * insert update delete
     * table  tb_file_name :  id  fileName   createTs
     * @param sql
     * @throws Exception
     */
    public static  void doUpdate(String sql){
        try
        {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
//            statement.executeUpdate("CREATE TABLE IF NOT EXISTS tb_file_name (createTs integer, name string)");
//            statement.executeUpdate("insert into tb_file_name values(System.currentTimeMillis(), 'Sea')");
            statement.executeUpdate(sql);
        }catch (Exception e)
        {
            e.printStackTrace();
            System.err.println("exec sql "+ sql + "exp and exception: " + e);
        }

    }

    public static List<Map> doQuery(String sql){
        try
        {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
//          ResultSet rs = statement.executeQuery("select * from person");
            ResultSet rs = statement.executeQuery(sql);
            List<Map> maps = convertList(rs);
            return maps;
        }catch (Exception e)
        {
            e.printStackTrace();
            System.err.println("exec sql "+ sql + "exp and exception: " + e);
        }
        return null;
    }

    // 将查询结果resultset转换为List<Map>
    private static List<Map> convertList(ResultSet rs) throws SQLException{
        List<Map> list = new ArrayList<Map>();
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取列的数量
        while (rs.next()) {
            Map<String,Object> rowData = new HashMap<String,Object>();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));//获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }

//    @Test
    public static void main(String[] args) throws Exception
    {

//      String schema= "CREATE TABLE IF NOT EXISTS tb_file_name (createTs Long, name string ,primary key(name))";
      String schema= "CREATE TABLE IF NOT EXISTS tb_file_name (createTs Long, name string )";
      String insertSql= "insert into tb_file_name values(3, 'Sea')";
      String query= "select * from tb_file_name where createTs>2";
      String delete= "DELETE FROM tb_file_name WHERE createTs=2 ";

      doUpdate(schema);
      doUpdate(insertSql);
//      doUpdate(delete);
      List<Map> maps = doQuery(query);
      System.err.println(maps);
    }

}

 

H2DbUtils:

package com.sea.edi.listener;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/***************************
 *<pre>
 * @Project Name : edi-kafka-service
 * @Package      : com.sea.edi.listener
 * @File Name    : H2BDUtils
 * @Author       :  Sea
 * @Date         : 12/15/22 3:31 PM
 * @Purpose      :
 * @History      :
 *         <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
 *         <dependency>
 *             <groupId>com.h2database</groupId>
 *             <artifactId>h2</artifactId>
 *             <version>1.4.187</version>
 *         </dependency>
 *</pre>
 ***************************/
public class H2DBUtil {

    public  static String DB_DIR = null;
    static Connection connection = null;
    public static Connection getConnection() throws Exception {
        if(connection==null||connection.isValid(15)){
            synchronized (H2DBUtil.class){
                if(connection==null||connection.isValid(15)){
                    // 加载H2数据库驱动
                    Class.forName("org.h2.Driver");
                    //jdbc:h2:/tmp/tmp/h2DB
                    String JDBC_URL = DB_DIR!=null?("jdbc:h2:"+DB_DIR):"jdbc:h2:/tmp/h2DB";
                    // 根据连接URL，用户名，密码获取数据库连接
                    connection = DriverManager.getConnection(JDBC_URL, "root", "root");
                    connection.setAutoCommit(true);}
            }
        }
        return connection;
    }

    /**
     * insert update delete
     * @param sql
     * @throws Exception
     */
    public static  void doUpdate(String sql){
        try
        {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            statement.executeUpdate(sql);
        }catch (Exception e)
        {
            e.printStackTrace();
            System.err.println("exec sql "+ sql + "exp and exception: " + e);
        }

    }


    public static List<Map> doQuery(String sql){
        try
        {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
//          ResultSet rs = statement.executeQuery("select * from person");
            ResultSet rs = statement.executeQuery(sql);
            List<Map> maps = convertList(rs);
            return maps;
        }catch (Exception e)
        {
            e.printStackTrace();
            System.err.println("exec sql "+ sql + "exp and exception: " + e);
        }
        return null;
    }

    // 将查询结果resultset转换为List<Map>
    private static List<Map> convertList(ResultSet rs) throws SQLException{
        List<Map> list = new ArrayList<Map>();
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取列的数量
        while (rs.next()) {
            Map<String,Object> rowData = new HashMap<String,Object>();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put((""+md.getColumnName(i)).toLowerCase(), rs.getObject(i));//获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }



    //    @Test
    public static void main(String[] args) throws Exception
    {
//      String schema="CREATE TABLE  IF NOT EXISTS tb_file_name(name VARCHAR(70) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4))";
        String schema="CREATE TABLE  IF NOT EXISTS tb_file_name(name VARCHAR(70),cts BIGINT,times INT)";
        String insertSql= "insert into tb_file_name values('Sea',312421412,3)";
        String query= "select * from tb_file_name";
        String delete= "DELETE FROM tb_file_name WHERE times=2 ";

        doUpdate(schema);
        doUpdate(insertSql);
       doUpdate(delete);
        List<Map> maps = doQuery(query);
        System.err.println(maps);
    }


}