针对《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;
}