Java(68):Java对于jdbc对数据库的封装(list＜map＞)

针对《Java(31):Java对于jdbc对数据库的封装[2]》进行优化，连接信息直接放到调用中。

原有的封装Java(31):Java对于jdbc对数据库的封装[2]可参考：

https://blog.csdn.net/fen_fen/article/details/120469074

固定数据可参考：

Java(33):Java对于jdbc对数据库的封装(Bean)-优化_fen_fen的专栏-CSDN博客

如果固定数据，可直接返回到bean中，如果不固定的字段名，需要返回到list<map>中

Java(67):Java对于jdbc对数据库的封装(list<map>)

package com.ciphergateway.utils;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.*;


/**
 *数据库工具类
 *Author:HMF
 *@create 2021-11-17
 **/

public class DbUtils {
    private static final Logger log= LogManager.getLogger(LogManager.ROOT_LOGGER_NAME);

    //SQL Statement Object
    //private static Statement stmt = null;
    private static PreparedStatement psTmt = null;

    //SQL ResultSet Object
    private static ResultSet rs = null;

    public DbUtils(){}

    /**
     * 获得连接
     * @param driver 数据库驱动
     * @param dbUrl 数据库url
     * @param dbUser 用户名
     * @param dbSecret 用户密码
     * @return conn
     */
    public Connection getConnection(String driver,String dbUrl,String dbUser,String dbSecret){
        Connection conn=null;
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(dbUrl, dbUser, dbSecret);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("fail to connect database");
            log.error(e+" fail to connect database");
        }
        return conn;
    }


    /**
     * 增加、删除、改
     * @param conn,sql  @description sql语句
     * @param params @description 参数
     * @return flag
     * @throws SQLException @description 异常
     */
    public boolean updateByPreparedStatement(Connection conn,String sql, List<Object> params) throws SQLException {
        boolean flag;
        int result;
        psTmt = conn.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (Object param : params) {
                psTmt.setObject(index++, param);
            }
        }
        result = psTmt.executeUpdate();
        flag = result > 0 ? true : false;
        //closeAll();
        return flag;
    }

    public List<Map<String, Object>> executeQuery(Connection conn,String sql) throws SQLException {
        return executeQuery(conn,sql,null);
    }

    /**
     * 查询多条记录
     * @param conn 连接信息
     * @param sql sql语句
     * @param params @description 参数
     * @throws SQLException @description 异常
     */
    public List<Map<String, Object>> executeQuery(Connection conn,String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        int index = 1;
        psTmt = conn.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                psTmt.setObject(index++, params.get(i));
            }
        }
        rs = psTmt.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int cols_len = metaData.getColumnCount();
        while (rs.next()) {
            Map<String, Object> map = new LinkedHashMap();
            for (int i = 0; i < cols_len; i++) {
                String cols_name = metaData.getColumnName(i + 1);
                Object cols_value = rs.getObject(cols_name);
                if (cols_value == null) {
                    cols_value = "";
                }
                map.put(cols_name, cols_value);
            }
            list.add(map);
        }
        //关闭连接
        closeAll(conn);

        return list;
    }

    /*
     * 获得表中最大ID
     * @param tableName
     * @return
     * @throws SQLException
     */
    public int getMaxId(Connection conn,String sql) {
        int maxId = 0;
        try {
            psTmt = conn.prepareStatement(sql);
            rs = psTmt.executeQuery();
            // 从resultSet对象中将数据取出
            if (rs.next()) {
                maxId = rs.getInt("maxId");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return maxId;
    }

    /**
     * 释放数据库连接
     * 不关闭的话会影响性能、并且占用资源。注意关闭的顺序，最后使用的最先关闭 ！
     */
    public static void closeAll(Connection conn) throws SQLException {
        if(rs!=null)rs.close();
        if(psTmt!=null)psTmt.close();
        if(conn!=null)conn.close();
    }



}

调用：

    public static void main(String[] args) throws SQLException {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://10.1.1.143:3306/demo_cg?useSSL=false&useUnicode=yes&characterEncoding=UTF-8";
        String user = "root";
        String password = "123456";

        DbUtils database = new DbUtils();
        Connection conn = database.getConnection(driver, url, user, password);

        //字段名和字段值
        String column = "aoeId,aoeAes,aoeSm4,aoeSm4_a,aoeEmail,aoePhone,aoeIdCard,aoeOfficerCard,aoePassport,aoeGeneralIdCard,aoeCreditCard";
        String value = "1,吴秀梅,Beijing Refining Network Technology Co.Ltd.,北京市海淀区,qianxiulan@yahoo.com,15652996964,210302199608124861,武水电字第3632734号,BWP018930705,0299233902,6212262502009182455";
        //解析字段名
        String[] column_aa = column.split(",");
        List<String> columnList=Arrays.asList(column_aa);
        //解析值
        String[] value_aa = value.split(",");
        ArrayList<Object> valueList = new ArrayList<>();
        valueList.addAll(Arrays.asList(value_aa));

/*       //插入数据
        String sql = "INSERT INTO aoedata (aoeId,aoeAes, aoeSm4, aoeSm4_a, aoeEmail, aoePhone, aoeIdCard, aoeOfficerCard, aoePassport, aoeGeneralIdCard, aoeCreditCard) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        boolean flag = database.updateByPreparedStatement(conn,sql, valueList);
        System.out.println(flag);*/
        //查询数据
        String select_sql = "select * from aoedata";
        List<Map<String, Object>> list = new ArrayList<>();
        list = database.executeQuery(conn, select_sql);
        //System.out.println(list);
        for (Map<String, Object> map:list){
            for(Map.Entry<String,Object> entry :map.entrySet()){
                System.out.println(entry.getKey()+":"+entry.getValue());
            }

        }
    }

断言类：DatabaseAssert.java中的方法

  /**
     * 断言(入参Map_input)
     * @param listMaps_out，Map_input
     * @param Map_input
     */
    public static int assertData(List<Map<String, Object>> listMaps_out,Map<String, String> Map_input){
        int num=0;
        for (Map<String, Object> map : listMaps_out) {
            for (String key : map.keySet()) {
                boolean flag=false;
                for (String key_input : Map_input.keySet()) {
                    //key值相同再对比value值
                    if(key.equals(key_input)){
                        //System.out.println(Map_input.get(key_input));
                        flag =StringUtils.endsWith(String.valueOf(map.get(key)), String.valueOf(Map_input.get(key_input)));
                        if (flag){
                            num += 1;
                        } else {
                            System.out.println("未找到匹配" + key);

                        }
                    }
                }
            }
        }
        return num;
    }

    /**
     * 断言(入参,标识符)
     * @param listMaps_out，cipher
     * @param cipher
     */
    public static int assertData(List<Map<String, Object>> listMaps_out,String cipher){
        int num=0;
        for (Map<String, Object> map : listMaps_out) {
            for (String key : map.keySet()) {
            boolean flag = false;
                if (!key.equals("aoe_id")) {
                    //System.out.println(String.valueOf(map.get(key)));
                    //System.out.println(cipher);
                    flag = StringUtils.endsWith(String.valueOf(map.get(key)), cipher);
                    if (flag) {
                        num += 1;
                    } else {
                        System.out.println("未找到匹配" + key);
                    }
                }
            }

        }
        return num;
    }