import javax.persistence.Column;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class DBMysqlUtil {
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private String dbDriver = "com.mysql.cj.jdbc.Driver";
private String dbConnectionURL = "jdbc:mysql://host:3306/test?serverTimezone=Hongkong&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false ";
private String dbUsername = "test";
private String dbPassword = "123";
public DBMysqlUtil() {
}
public DBMysqlUtil(String dbDriver, String dbConnectionURL, String dbUsername, String dbPassword){
this.dbDriver = dbDriver;
this.dbConnectionURL = dbConnectionURL;
this.dbUsername = dbUsername;
this.dbPassword = dbPassword;
}
/**
* 功能:获取数据库连接
*/
private Connection getConnection() {
System.out.println("连接地址:"+dbConnectionURL);
System.out.println("用户名:"+dbUsername);
System.out.println("密码:"+dbPassword);
try {
Class.forName(dbDriver);
conn = DriverManager.getConnection(dbConnectionURL, dbUsername,
dbPassword);
System.out.println("数据库连接成功");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 功能:执行查询语句
*/
public <T> List<T> select(Class<T> t,String sql) {
System.out.println("Exec select sql:" + sql);
List<T> list = new ArrayList<T>();
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
while (rs.next()) {
list.add(instance(t, rs, sql));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 将RusultSet对象实例化T对象
*
* @param <T>
* @param t
* @param rs
* @param sql
* @return t
* @throws Exception
*/
private static <T> T instance(Class<T> t, ResultSet rs, String sql) throws Exception{
List<String> columns = getColumnsFromSelect(sql);
T obj = t.newInstance();
for (String col : columns) {
try{
Field f = t.getDeclaredField(col);
f.setAccessible(true);
Object v = getValue(col, f.getType().getName(), rs);
f.set(obj, v);
}catch(NoSuchFieldException e){
Field[] fields = t.getDeclaredFields();
for (Field f : fields) {
Column column = f.getAnnotation(Column.class);
if(column != null && column.name().equals(col)){
f.setAccessible(true);
Object v = getValue(col, f.getType().getName(), rs);
f.set(obj, v);
}
}
}
}
return obj;
}
private static Object getValue(String columnName, String type, ResultSet rs) throws SQLException{
Object obj = null;
// System.out.println("name="+f.getName()+", type="+f.getType().getName() );
if("java.lang.Integer".equals(type) || "int".equals(type)) {
obj = rs.getInt(columnName);
}else if("java.lang.Long".equals(type) || "long".equals(type)) {
obj = rs.getLong(columnName);
}else if("java.lang.Short".equals(type)||"short".equals(type)) {
obj = rs.getShort(columnName);
}else if("java.lang.Float".equals(type)||"float".equals(type)) {
obj = rs.getFloat(columnName);
}else if("java.lang.Double".equals(type)||"double".equals(type)) {
obj = rs.getDouble(columnName);
}else if("java.lang.Byte".equals(type)||"byte".equals(type)) {
obj = rs.getByte(columnName);
}else if("java.lang.Boolean".equals(type)||"boolean".equals(type)) {
obj = rs.getBoolean(columnName);
}else if("java.lang.String".equals(type)) {
obj = rs.getString(columnName);
}else {
obj = rs.getObject(columnName);
}
// System.out.println("name="+f.getName() +", type="+f.getType().getName()+", value="+(obj == null ? "NULL" : obj.getClass())+",{"+columnName+":"+obj+"}");
return obj;
}
/**
* 利用正则表达式,获得SELECT SQL中的列名
*
* @param sql
* @return
*/
private static List<String> getColumnsFromSelect(String sql) {
List<String> colNames = new ArrayList<String>();
// 取出sql中列名部分
Pattern p = Pattern.compile("(?i)select\\s(.*?)\\sfrom.*");
Matcher m = p.matcher(sql.trim());
String[] tempA = null;
if (m.matches()) {
tempA = m.group(1).split(",");
}
if (tempA == null) {
return null;
}
String p1 = "(\\w+)";
String p2 = "(?:\\w+\\s(\\w+))";
String p3 = "(?:\\w+\\sas\\s(\\w+))";
String p4 = "(?:\\w+\\.(\\w+))";
String p5 = "(?:\\w+\\.\\w+\\s(\\w+))";
String p6 = "(?:\\w+\\.\\w+\\sas\\s(\\w+))";
String p7 = "(?:.+\\s(\\w+))";
String p8 = "(?:.+\\sas\\s(\\w+))";
p = Pattern.compile("(?:" + p1 + "||" + p2 + "||" + p3 + "||" + p4
+ "||" + p5 + "||" + p6 + "||" + p7 + "||" + p8 + ")");
for (String temp : tempA) {
m = p.matcher(temp.trim());
if (!m.matches()) {
continue;
}
for (int i = 1; i <= m.groupCount(); i++) {
if (m.group(i) == null || "".equals(m.group(i))) {
continue;
}
colNames.add(m.group(i));
}
}
return colNames;
}
/**
* 功能:执行查询语句,获取记录数
*/
public int getRecordCount(String sql) {
System.out.println("Exec getRecordCount sql:" + sql);
int counter = 0;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
while (rs.next()) {
counter++;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
System.out.println("counter总数:"+counter);
return counter;
}
/**
* 功能:针对单条记录执行更新操作(新增、修改、删除)
*/
public int executeupdate(String sql) throws Exception {
System.out.println("Exec update sql:" + sql);
int num = 0;
try {
conn = getConnection();
ps = conn.prepareStatement(sql);
num = ps.executeUpdate();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close();
}
System.out.println("影响条数:"+num);
return num;
}
/**
* 功能:针对单条记录执行更新操作(新增、修改、删除)
*/
public int batchupdate(List<String> sqls) throws Exception {
System.out.println("Exec update sql:" + sqls.size());
int num = 0;
try {
conn = getConnection();
conn.setAutoCommit(false);
Statement statement = conn.createStatement();
for (String sql : sqls) {
statement.addBatch(sql);
}
int result[] = statement.executeBatch();
conn.commit();
System.out.println("影响条数:"+result.length);
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close();
}
return num;
}
/**
*
* 功能:批量执行SQL(update或delete)
*
* @param sqlList
* sql语句集合
*/
public int executeBatch(List<String> sqlList) {
int result = 0;
for (String sql : sqlList) {
try {
result += executeupdate(sql);
} catch (Exception e) {
System.out.println("查询异常:"+e.getMessage());
}
}
System.out.println("executeBatch Result:"+result);
return result;
}
/**
* 功能:关闭数据库的连接
*/
public void close() {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}