Java(32):Java对于jdbc对数据库的封装(Bean)

Java从数据库中读取数据，封装到 bean 对象

2.1表aoeData字段类型：

#进入docker

$docker exec -it af35448d73cd bash

#进入数据库mysql

root@af35448d73cd:/# mysql -u root -p

#使用数据库aoe_auto

mysql> use aoe_auto;

#查询表数据类型

mysql> DESC aoeData; +------------------+---------------+------+-----+---------+----------------+ | Field            | Type          | Null | Key | Default | Extra          | +------------------+---------------+------+-----+---------+----------------+ | aoeId            | int(19)       | NO   | PRI | NULL    | auto_increment | | aoeAes           | varchar(1000) | YES  |     | NULL    |                | | aoeSm4           | varchar(1000) | YES  |     | NULL    |                | | aoeSm4_a         | varchar(1000) | YES  |     | NULL    |                | | aoeEmail         | varchar(1000) | YES  |     | NULL    |                | | aoePhone         | varchar(1000) | YES  |     | NULL    |                | | aoeIdCard        | varchar(1000) | YES  |     | NULL    |                | | aoeOfficerCard   | varchar(1000) | YES  |     | NULL    |                | | aoePassport      | varchar(1000) | YES  |     | NULL    |                | | aoeGeneralIdCard | varchar(1000) | YES  |     | NULL    |                | | aoeCreditCard    | varchar(1000) | YES  |     | NULL    |                | +------------------+---------------+------+-----+---------+----------------+ 11 rows in set (0.00 sec)

2.2实体类 AoeData.java

2.2实体类 AoeData.java
package com.ciphergateway.bean;

/**
 *Author:HMF
 *@create 2021-09-16
 **/
public class AoeData {

    private int aoeId; //aoeId
    private String aoeAes; //aoeAes
    private String aoeSm4; //aoeSm4
    private String aoeSm4_a; //aoeSm4_a
    private String aoeEmail; //aoeEmail
    private String aoePhone; //aoePhone
    private String aoeIdCard; //aoeIdCard
    private String aoeOfficerCard; //aoeOfficerCard
    private String aoePassport; //aoePassport
    private String aoeGeneralIdCard; //aoeGeneralIdCard
    private String aoeCreditCard; //aoeCreditCard

    public int getAoeId() {
        return aoeId;
    }
    public void setAoeId(int aoeId) {
        this.aoeId = aoeId;
    }
    public String getAoeAes() {
        return aoeAes;
    }
    public void setAoeAes(String aoeAes) {
        this.aoeAes = aoeAes;
    }
    public String getAoeSm4() {
        return aoeSm4;
    }
    public void setAoeSm4(String aoeSm4) {
        this.aoeSm4 = aoeSm4;
    }
    public String getAoeSm4_a() {
        return aoeSm4_a;
    }
    public void setAoeSm4_a(String aoeSm4_a) {
        this.aoeSm4_a = aoeSm4_a;
    }
    public String getAoeEmail() {
        return aoeEmail;
    }
    public void setAoeEmail(String aoeEmail) {
        this.aoeEmail = aoeEmail;
    }
    public String getAoePhone() {
        return aoePhone;
    }
    public void setAoePhone(String aoePhone) {
        this.aoePhone = aoePhone;
    }
    public String getAoeIdCard() {
        return aoeIdCard;
    }
    public void setAoeIdCard(String aoeIdCard) {
        this.aoeIdCard = aoeIdCard;
    }
    public String getAoeOfficerCard() {
        return aoeOfficerCard;
    }
    public void setAoeOfficerCard(String aoeOfficerCard) {
        this.aoeOfficerCard = aoeOfficerCard;
    }
    public String getAoePassport() {
        return aoePassport;
    }
    public void setAoePassport(String aoePassport) {
        this.aoePassport = aoePassport;
    }
    public String getAoeGeneralIdCard() {
        return aoeGeneralIdCard;
    }
    public void setAoeGeneralIdCard(String aoeGeneralIdCard) {
        this.aoeGeneralIdCard = aoeGeneralIdCard;
    }
    public String getAoeCreditCard() {
        return aoeCreditCard;
    }
    public void setAoeCreditCard(String aoeCreditCard) {
        this.aoeCreditCard = aoeCreditCard;
    }
}

2.3.db.properties 文件中的内容：

#mysql原库 driverClass =com.mysql.jdbc.Driver url=jdbc:mysql://10.1.1.191:3306/aoe_auto?useSSL=false&useUnicode=yes&characterEncoding=UTF-8 userName=root passWord=123456 tableName=aoeData

2.4.针对数据库的封装DataBaseUtil.java

package com.ciphergateway.utils;

import com.ciphergateway.bean.AoeData;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
*Author:HMF
*@create 2021-09-16
**/

public class DataBaseUtil {

    // JDBC 驱动名及数据库 URL
    private static String driver;
    private static String url;
    // 过插件的JDBC 驱动名及数据库 URL
    private static String aoeDriver;
    private static String aoeUrl;

    // 数据库的用户名与密码，需要根据自己的设置
    private static String userName;
    private static String passWord;

    //SQL Connection Object
    private static Connection conn = null;

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

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


    public DataBaseUtil() throws Exception{
        InputStream in = DataBaseUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = pro.getProperty("driverClass");
        url = pro.getProperty("url");
        aoeDriver=pro.getProperty("aoeDriverClass");
        aoeUrl=pro.getProperty("aoeUrl");
        userName = pro.getProperty("userName");
        passWord = pro.getProperty("passWord");
        System.out.println(driver+url+userName+passWord);
    }

    /**
     * 直连数据库
     */
    public Connection getConnection(){
        return getConnection(null);
    }

    /**
     * 过AOE连接数据库
     * @return conn
     */
    public Connection getConnection(String aoe){
        try {
           // 加载 MySQL JDBC 驱动类
            Class.forName(driver);
            // 建立连接（连接对象内部其实包含了Socket对象，是一个远程的连接，比较耗时！这是Connection对象管理的一个要点！）
            conn = DriverManager.getConnection(url, userName, passWord);
            if (aoe !=null){
                Class.forName(aoeDriver);
                conn = DriverManager.getConnection(aoeUrl, userName, passWord);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            System.out.println("fail to connect database");
        }
        return conn;
    }

    /**
     * 增加、删除、改
     * @param conn,sql
     * @param params
     * @return flag
     * @throws SQLException
     */
    public boolean updateByPreparedStatement(Connection conn,String sql, List<Object> params) throws SQLException {
        boolean flag = false;
        int result = -1;
        pstmt = conn.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        flag = result > 0 ? true : false;
        //closeAll();
        return flag;
    }
    /**
     * 查询多条记录
     * @param conn,sql
     * @throws SQLException
     */

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

    /**
     * 查询多条记录
     * @param conn,sql
     * @param params
     * @throws SQLException
     */
    public List<AoeData> executeQuery(Connection conn,String sql, List<Object> params) throws SQLException {
        List<AoeData> datalist = new ArrayList<AoeData>();
        AoeData data=new AoeData();
        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<String, Object>();
            for (int i = 0; i < cols_len; i++) {
                data.setAoeId(rs.getInt("aoeId"));
                data.setAoeAes(rs.getString("aoeAes"));
                data.setAoeSm4(rs.getString("aoeSm4"));
                data.setAoeSm4_a(rs.getString("aoeSm4_a"));
                data.setAoeEmail(rs.getString("aoeEmail"));
                data.setAoePhone(rs.getString("aoePhone"));
                data.setAoeIdCard(rs.getString("aoeIdCard"));
                data.setAoeOfficerCard(rs.getString("aoeOfficerCard"));
                data.setAoePassport(rs.getString("aoePassport"));
                data.setAoeGeneralIdCard(rs.getString("aoeGeneralIdCard"));
                data.setAoeCreditCard(rs.getString("aoeCreditCard"));
            }
            datalist.add(data);
        }
        //关闭连接
        closeAll();

        return datalist;
    }

    /*
     * 获得表中最大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() throws SQLException {
        if(rs!=null)rs.close();
        if(pstmt!=null)pstmt.close();
        if(conn!=null)conn.close();
    }

}

2.5数据检查(断言)

package com.ciphergateway.asserts;

import com.ciphergateway.bean.AoeData;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 *Author:HMF
 *@create 2021-09-25
 **/
public class DatabaseAsserts {

    /**
     * 断言(入参Map_input)
     * @param datalist
     * @param Map_input
     */
    public static int assertData(List<AoeData> datalist,Map<String, Object> Map_input) throws IllegalAccessException{
        //List<AoeData> datalist=new ArrayList<>();
        int num=0;
        for (AoeData data : datalist) {
            boolean flag=false;
            for (String key_input : Map_input.keySet()) {
                //key值相同再对比value值
                for (Field field:data.getClass().getDeclaredFields()) {
                    // 一定要设置setAccessible为ture
                    field.setAccessible(true);
                    String filedName=field.getName();
                    if (filedName.equals("aoeId") == false) {
                        if(filedName.equals(key_input)) {
                            try {
                                flag = StringUtils.endsWith(field.get(data).toString(), String.valueOf(Map_input.get(key_input)));
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            }
                            if (flag) {
                                num += 1;
                            } else {
                                System.out.println("未找到匹配" + key_input);
                            }
                        }
                    }
                }
            }
        }
        return num;
    }

    /**
     * 断言(入参,标识符)
     * @param datalist
     * @param cipher
     */
    public static int assertData(List<AoeData> datalist,String cipher){
        int num=0;
        for (AoeData data : datalist) {
            for (Field field:data.getClass().getDeclaredFields()) {
                // 一定要设置setAccessible为ture
                field.setAccessible(true);
                String filedName=field.getName();
                boolean flag = false;
                if (filedName.equals("aoeId") == false) {
                    //System.out.println(String.valueOf(map.get(key)));
                    //System.out.println(cipher);
                    try {
                        flag = StringUtils.endsWith(field.get(data).toString(), cipher);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    if (flag) {
                        num += 1;
                    } else {
                        System.out.println("未找到匹配" + filedName);
                    }
                }
            }
        }
        return num;
    }
}

2.6.主方法调用

import com.ciphergateway.asserts.DatabaseAssert;
import com.ciphergateway.asserts.DatabaseAsserts;
import com.ciphergateway.bean.AoeData;
import com.ciphergateway.utils.Common;
import com.ciphergateway.utils.DataBaseUtil;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class DabaBaseTest {
    private static Map<String, Object> Map_input = new LinkedHashMap<String,Object>();

    public static void main(String[] args) throws Exception {
        //获取配置文件数据
        String filename="db.properties";
        Properties pro= Common.getProperty(filename);
        String tableName=pro.getProperty("tableName");

        //定义插入数据
        Map_input.put("aoeId", 1);
        Map_input.put("aoeAes", "吴秀梅");
        Map_input.put("aoeSm4", "Beijing Refining Network Technology Co.Ltd.");
        Map_input.put("aoeSm4_a", "北京市海淀区北三环西路32号楼7层0710-1");
        Map_input.put("aoeEmail", "qianxiulan@yahoo.com");
        Map_input.put("aoePhone", "15652996964");
        Map_input.put("aoeIdCard", "210302199608124861");
        Map_input.put("aoeOfficerCard", "武水电字第3632734号");
        Map_input.put("aoePassport", "BWP018930705");
        Map_input.put("aoeGeneralIdCard", "0299233902");//智能识别证件号
        Map_input.put("aoeCreditCard", "6212262502009182455");//银行卡号

        DataBaseUtil database=new DataBaseUtil();
        //操作数据库
        Connection conn=database.getConnection();
        //删除已有数据
        String sql_delete="delete from "+tableName;
        database.updateByPreparedStatement(conn,sql_delete,null);

        //插入数据
        insertTest(conn,database,tableName);


        //直连数据库查询()
        Connection conn=database.getConnection();
        List<AoeData> dataList1=selectTest(conn,database,tableName);
        int num= DatabaseAsserts.assertData(dataList1,Map_input);
        if (num==10){
            System.out.println("插入数据后，直连数据库查询，10个字段对比正确");
            System.out.println("=====================================");
        }
        else {
            System.out.println("插入数据后，直连数据库查询，10个字段部分正确 "+num);
            System.out.println("=====================================");
        }
    }
    /**
     * 插入数据
     * @param database
     */
    public static void insertTest(Connection conn,DataBaseUtil database,String tableName){
        List<Object> params = new ArrayList<Object>();
        //params.add("1");
        params.add(Map_input.get("aoeId"));   //aoeId
        params.add(Map_input.get("aoeAes"));   //aoeAes
        params.add(Map_input.get("aoeSm4")); //aoeSm4
        params.add(Map_input.get("aoeSm4_a"));//aoeSm4_a
        params.add(Map_input.get("aoeEmail"));//aoeEmail
        params.add(Map_input.get("aoePhone"));//aoePhone
        params.add(Map_input.get("aoeIdCard"));//aoeIdCard
        params.add(Map_input.get("aoeOfficerCard"));//aoeOfficerCard
        params.add(Map_input.get("aoePassport"));//aoePassport
        params.add(Map_input.get("aoeGeneralIdCard"));//aoeGeneralIdCard,智能识别证件号
        params.add(Map_input.get("aoeCreditCard"));//aoeCreditCard
        System.out.println(params);
        //String sql ="insert into aoe_auto (aoe_aes, aoe_sm4) values (?,?)";   //'"+ tableName +"'
        String sql="INSERT INTO "+ tableName +" (aoeId,aoeAes, aoeSm4, aoeSm4_a, aoeEmail, aoePhone, aoeIdCard, aoeOfficerCard, aoePassport, aoeGeneralIdCard, aoeCreditCard) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        try {
            boolean flag = database.updateByPreparedStatement(conn,sql, params);
            System.out.println(flag);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 通过主键查询
     * @param database
     * @param tableName
     * @throws SQLException
     */
    public static List<AoeData> selectTest(Connection conn, DataBaseUtil database, String tableName) throws SQLException {
        String sql="select * from "+ tableName;
        List<AoeData> datalist = new ArrayList<>();
        datalist=database.executeQuery(conn,sql);
        //for(AoeData data:datalist){
            //System.out.println(data.getAoeAes());
        //}
        return datalist;
    }
}