优化原因：
发现之前写的代码，有很多数据(例如sql里的字段等)在方法里
优化：本次把方法里的数据都提取出来，参数从前面开始设置并传参(方法里不在出现具体的数据)
具体实现：
Java从数据库中读取数据，封装到 bean 对象
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、实体类 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;
}
}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=aoeData4、工具类:Common.java
package com.ciphergateway.utils;
import java.io.*;
import java.util.Properties;
public class Common {
/**
* @return Windows系统返回 true
* @function 判断当前系统是否为Windows系统
*/
public static boolean isWindows() {
String OS = System.getProperty("os.name").toLowerCase();
return (OS.contains("windows"));
}
/**
* @return pro
* @function InputStream获取配置文件内容
*/
public static Properties getProperty(String filename) throws Exception {
String enconding = "UTF-8";
if (isWindows())
enconding = "GBK";
Properties pro = new Properties();
try{
//从配置文件中获取
InputStream in = Common.class.getClassLoader().getResourceAsStream(filename);
pro.load(new InputStreamReader(in,enconding));
//pro.load(in);
} catch (Exception e) {
e.printStackTrace();
}
return pro;
}
public static Properties getProperty_2() throws Exception{
//从配置文件中获取
Properties properties = new Properties();
try {
//当前工作目录
String path = System.getProperty("user.dir");
String filePath = path + "/src/main/resources/db.properties";
String enconding = "UTF-8";
if (isWindows())
enconding = "GBK";
BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filePath), enconding));
properties.load(br);
}
catch (Exception e){
e.printStackTrace();
}
return properties;
}
}5、针对数据库的封装DataBaseUtil.java
package com.ciphergateway.utils;
import com.ciphergateway.bean.AoeData;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
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) {
e.printStackTrace();
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,List<String> columnList) throws SQLException {
return executeQuery(conn,sql,null,columnList);
}
/**
* 查询多条记录
* @param conn,sql
* @param params
* @throws SQLException
*/
public List<AoeData> executeQuery(Connection conn,String sql, List<Object> params,List<String> columnList) 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(columnList.get(0)));
data.setAoeAes(rs.getString(columnList.get(1)));
data.setAoeSm4(rs.getString(columnList.get(2)));
data.setAoeSm4_a(rs.getString(columnList.get(3)));
data.setAoeEmail(rs.getString(columnList.get(4)));
data.setAoePhone(rs.getString(columnList.get(5)));
data.setAoeIdCard(rs.getString(columnList.get(6)));
data.setAoeOfficerCard(rs.getString(columnList.get(7)));
data.setAoePassport(rs.getString(columnList.get(8)));
data.setAoeGeneralIdCard(rs.getString(columnList.get(9)));
data.setAoeCreditCard(rs.getString(columnList.get(10)));
}
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();
}
}6、数据检查(断言)
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 mapInput
*/
public static int assertData(List<AoeData> datalist,Map<String, Object> mapInput) throws IllegalAccessException{
//List<AoeData> datalist=new ArrayList<>();
int num=0;
for (AoeData data : datalist) {
boolean flag=false;
for (Field field:data.getClass().getDeclaredFields()) {
// 一定要设置setAccessible为ture
field.setAccessible(true);
String filedName=field.getName();
for (String key : mapInput.keySet()) {
//key值相同再对比value值
if (filedName.equals("aoeId") == false) {
if (filedName.equals(key)) {
//System.out.println(field.get(data).toString());
//System.out.println(String.valueOf(mapInput.get(key)));
try {
flag = StringUtils.endsWith(field.get(data).toString(), String.valueOf(mapInput.get(key)));
} catch (IllegalAccessException e) {
e.printStackTrace();
}
if (flag) {
num += 1;
} else {
System.out.println("未找到匹配" + key);
}
}
}
}
}
}
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;
}
}7、主方法调用
import com.ciphergateway.asserts.DatabaseAsserts;
import com.ciphergateway.bean.AoeData;
import com.ciphergateway.utils.Common;
import com.ciphergateway.utils.DataBaseUtil;
import org.apache.commons.lang3.StringUtils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;
public class DabaBaseTest {
private static Map<String, Object> mapInput = new LinkedHashMap<String,Object>();
private static Connection conn=null;
private static Connection conn2=null;
//获取配置文件数据
private static String filename = "db.properties";
private static Properties pro;
static {
try {
pro = Common.getProperty(filename);
} catch (Exception e) {
e.printStackTrace();
}
}
private static String tableName = pro.getProperty("tableName"); //单个表时用这个
private static String column = "aoeId,aoeAes,aoeSm4,aoeSm4_a,aoeEmail,aoePhone,aoeIdCard,aoeOfficerCard,aoePassport,aoeGeneralIdCard,aoeCreditCard";
private static String value = pro.getProperty("value");
public static void main(String[] args) throws Exception {
if (column.equals(null) == false && value.equals(null) == false) {
//解析字段名
String[] column_aa = column.split(",");
List<String> columnList = Arrays.asList(column_aa);
//System.out.println(columnList);
//解析值
String[] value_aa = value.split(",");
List<Object> valueList = new ArrayList<>();
valueList.addAll(Arrays.asList(value_aa));
valueList.set(0,Integer.valueOf(valueList.get(0).toString()));
System.out.println(valueList);
mapInput = columnList.stream().collect(Collectors.toMap(key->key, key->valueList.get(columnList.indexOf(key))));
//开始执行AOE测试
System.out.println("=====================================AOEAutoTest Start======================================");
DataBaseUtil database = new DataBaseUtil();
String select_sql="select * from "+ tableName;
//操作数据库
conn = database.getConnection("aoe");
if (conn !=null) {
//删除已有数据
String sql_delete = "delete from " + tableName;
database.updateByPreparedStatement(conn, sql_delete, null);
//插入数据
String sql="INSERT INTO "+ tableName +" (aoeId,aoeAes, aoeSm4, aoeSm4_a, aoeEmail, aoePhone, aoeIdCard, aoeOfficerCard, aoePassport, aoeGeneralIdCard, aoeCreditCard) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
System.out.println(sql);
insertTest(conn, database, sql,valueList);
//连数据库查询数据
System.out.println("=======================================查询========================================");
List<AoeData> dataList1 = selectTest(conn, database, select_sql,columnList);
int num = DatabaseAsserts.assertData(dataList1, mapInput);
if (num == 10) {
System.out.println("表名:"+tableName+",插入数据后，查询，都展示明文。<--PASS-->");
System.out.println("=======================================查询========================================");
} else {
System.out.println("表名:"+tableName+",插入数据后，查询，有错误，错误数："+(10-num)+"<--FAIL-->");
System.out.println("=======================================查询========================================");
}
}
}
}
/**
* 插入数据
* @param database
*/
public static void insertTest(Connection conn,DataBaseUtil database,String sql,List<Object> params){
try {
boolean flag = database.updateByPreparedStatement(conn,sql, params);
System.out.println(flag);
}
catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 通过主键查询
* @param database
* @param select_sql
* @throws SQLException
*/
public static List<AoeData> selectTest(Connection conn, DataBaseUtil database, String select_sql,List<String> columnList) throws SQLException {
List<AoeData> datalist = new ArrayList<>();
datalist=database.executeQuery(conn,select_sql,columnList);
for(AoeData data:datalist){
for (Field field:data.getClass().getDeclaredFields()) {
// 一定要设置setAccessible为ture
field.setAccessible(true);
String filedName = field.getName();
String value = null;
try {
value = field.get(data).toString();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
System.out.println(filedName+": "+value);
}
}
return datalist;
}
}
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
