采用AES加密,外置配置文件,快速批量的JDBC
先上文件
驱动文件/JDBC.java
package com.jdbcx.jdbc.Utils;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JDBC {
private static final org.slf4j.Logger LOG = org.slf4j.LoggerFactory.getLogger(JDBC.class);
private String pathname = "./config/config.txt";
private String jdbcDriver = "";
private String dbUrl = "";
private String dbUser = "";
private String dbPwd = "";
private static JDBC singleTon = null;
private static String getJdbcDriver() {
return JDBC.createInstance().jdbcDriver;
}
private static String getDbUrl() {
return JDBC.createInstance().dbUrl;
}
private static String getDbUser() {
return JDBC.createInstance().dbUser;
}
private static String getDbPwd() {
return JDBC.createInstance().dbPwd;
}
//单例
private static JDBC createInstance() {
if (singleTon == null) {
singleTon = new JDBC();
singleTon.initDb();
}
return singleTon;
}
/**
* @Description: 初始化数据库
* @Param: []
* @return: void
* @Author: Hisen
* @Date: 2019/12/31
*/
private void initDb() {
try {
FileReader reader = new FileReader(pathname);
BufferedReader br = new BufferedReader(reader);
String line = "";
while ((line = br.readLine()) != null) {
String[] strs = null;
strs = line.split(" ");
switch (strs[0]) {
case "JDBC_DRIVER":
jdbcDriver = strs[1];
break;
case "DB_URL":
dbUrl = strs[1];
break;
case "USER":
dbUser = AESUtil.aesDecrypt(strs[1], "isfromhanhuasoft");
break;
case "PASS":
//密码解密
dbPwd = AESUtil.aesDecrypt(strs[1], "isfromhanhuasoft");
break;
default:
}
}
} catch (Exception e) {
LOG.error(String.valueOf(e));
e.printStackTrace();
}
}
/**
* @Description: 数据库连接检查
* @Param: []
* @return: void
* @Author: Hisen
* @Date: 2019/12/31
*/
public static void dbConnCheck() {
Connection conn = null;
Statement stmt = null;
try {
// 注册 JDBC 驱动
String url = JDBC.getJdbcDriver();
Class.forName(url);
// 打开链接
conn = DriverManager.getConnection(JDBC.getDbUrl(), JDBC.getDbUser(), JDBC.getDbPwd());
if(conn != null)
{
LOG.info("数据库连接成功!"); //Log4j记录
}else{
//LOG.error("数据库连接失败!"); //Log4j记录
}
// 执行查询
stmt = conn.createStatement();
// 完成后关闭
stmt.close();
conn.close();
} catch (Exception se) {
// 处理 JDBC 错误
se.printStackTrace();
LOG.warn("数据库连接失败!"+se); //Log4j记录
}// 处理 Class.forName 错误
finally {
// 关闭资源
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException ignored) {
}// 什么都不做
try {
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
se.printStackTrace();
}
}
}
/**
* @Description: 查询
* @Param: [strSql]
* @return: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
* @Author: Hisen
* @Date: 2019/12/31
*/
public static List<Map<String, Object>> selTabWithSql(String strSql) {
Connection conn = null;
Statement stmt = null;
//JTable tableA = new JTable();
List<Map<String, Object>> list = new ArrayList<>();
try {
// 注册 JDBC 驱动
String url = JDBC.getJdbcDriver();
Class.forName(url);
// 打开链接
conn = DriverManager.getConnection(JDBC.getDbUrl(), JDBC.getDbUser(), JDBC.getDbPwd());
// 执行查询
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSql);
ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
int columnCount = md.getColumnCount(); //获得列数
// 展开结果集数据库
while (rs.next()) {
Map<String, Object> rowData = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnLabel(i), rs.getObject(i));
}
list.add(rowData); //之前rowData在while外面,list.add()重复引用,指向同一块内存,所以值被覆盖,重复出现后面的值
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
} catch (Exception se) {
LOG.error(String.valueOf(se));
se.printStackTrace();
}// 处理 Class.forName 错误
finally {
// 关闭资源
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException ignored) {
LOG.error(String.valueOf(ignored));
ignored.printStackTrace();
}// 什么都不做
try {
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
LOG.error(String.valueOf(se));
se.printStackTrace();
}
}
return list;
}
/**
* @Description: 插入
* @Param: [strSql]
* @return: int
* @Author: Hisen
* @Date: 2019/12/31
*/
public static int insOrUpWithSql(String strSql) {
Connection conn = null;
Statement stmt = null;
//JTable tableA = new JTable();
int iSum = 0;
try {
// 注册 JDBC 驱动
Class.forName(JDBC.getJdbcDriver());
// 打开链接
conn = DriverManager.getConnection(JDBC.getDbUrl(), JDBC.getDbUser(), JDBC.getDbPwd());
// 执行查询
stmt = conn.createStatement();
iSum = stmt.executeUpdate(strSql);
stmt.close();
conn.close();
} catch (Exception se) {
LOG.error(String.valueOf(se));
se.printStackTrace();
}// 处理 Class.forName 错误
finally {
// 关闭资源
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException ignored) {
LOG.error(String.valueOf(ignored));
ignored.printStackTrace();
}// 什么都不做
try {
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
LOG.error(String.valueOf(se));
se.printStackTrace();
}
}
return iSum;
}
/**
* @Description: 批量快速插入
* @Param: [sqlList]
* @return: void
* @Author: Hisen
* @Date: 2019/12/31
*/
public static void fastToIns(HashMap<Integer, String> sqlList){
Connection conn = null;
Statement pstm =null;
ResultSet rt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(JDBC.getDbUrl(), JDBC.getDbUser(), JDBC.getDbPwd());
pstm = conn.createStatement();
Long startTime = System.currentTimeMillis();
conn.setAutoCommit(false);
for (int q = 0; q < sqlList.size(); q++) {
try {
if(!sqlList.get(q).equals("")){
pstm.addBatch(sqlList.get(q));
}
} catch (Exception ignored) {
}
}
pstm.executeBatch();
conn.commit();
Long endTime = System.currentTimeMillis();
System.out.println("用时:" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally{
if(pstm!=null){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
}
配置文件/Config.txt
#数据库
JDBC_DRIVERHIS com.mysql.jdbc.Driver
DB_URLHIS jdbc:mysql://192.168.0.1:3306/jdbcx?useUnicode=true&characterEncoding=utf8
USERHIS 3TIutBa+W/F04KpEnGjUnA==
PASSHIS 12VTlGF2b970B+oxQrC1YQ==
加密工具/AESUtil.java
package com.jdbcx.jdbc.Utils;
/**
* @description: AES对称加密算法,采用加密模式为AES,填充模式为PKCS5Pading,数据块为128位,字符集位UTF-8
* @author: Hisen
* @create: 2019-09-25 13:08
**/
import org.apache.commons.codec.binary.Base64;
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import java.nio.charset.StandardCharsets;
public class AESUtil {
// 加密
public static String aesEncrypt(String sSrc, String sKey) throws Exception {
if (sKey == null) {
System.out.print("Key为空null");
return null;
}
// 判断Key是否为16位
if (sKey.length() != 16) {
System.out.print("Key长度不是16位");
return null;
}
byte[] raw = sKey.getBytes(StandardCharsets.UTF_8);
SecretKeySpec skeySpec = new SecretKeySpec(raw, "AES");
Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5Padding");//"算法/模式/补码方式"
cipher.init(Cipher.ENCRYPT_MODE, skeySpec);
byte[] encrypted = cipher.doFinal(sSrc.getBytes(StandardCharsets.UTF_8));
return new Base64().encodeToString(encrypted);//此处使用BASE64做转码功能,同时能起到2次加密的作用。
}
// 解密
public static String aesDecrypt(String sSrc, String sKey) {
try {
// 判断Key是否正确
if (sSrc.length() != 24) {
System.out.print("秘钥长度不是24位 ");
return null;
}
// 判断Key是否正确
if (sKey == null) {
System.out.print("Key为空null");
return null;
}
// 判断Key是否为16位
if (sKey.length() != 16) {
System.out.print("Key长度不是16位");
return null;
}
byte[] raw = sKey.getBytes(StandardCharsets.UTF_8);
SecretKeySpec skeySpec = new SecretKeySpec(raw, "AES");
Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5Padding");
cipher.init(Cipher.DECRYPT_MODE, skeySpec);
byte[] encrypted1 = new Base64().decode(sSrc);//先用base64解密
try {
byte[] original = cipher.doFinal(encrypted1);
String originalString = new String(original, StandardCharsets.UTF_8);
return originalString;
} catch (Exception e) {
System.out.println(e.toString());
return null;
}
} catch (Exception ex) {
System.out.println(ex.toString());
return null;
}
}
}
注意 配置项
1. jdbc配置文件路径 ./config/config.txt
2. 加密串 qwertyuiopasdfgh
3. 加密方式 AES Padding5 Utf-8