1 packagecom.qa.xxx;2
3
4 importorg.springframework.stereotype.Component;5 importjava.lang.reflect.Method;6 import java.sql.*;7 importjava.util.ArrayList;8 importjava.util.List;9
10 @Component11 public classMySQLUtil {12
13 private static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";14
15 private static ThreadLocal threadLocal = new ThreadLocal<>();16
17
18 public staticConnection getMysqlConnection(String url, String userName, String userPassword){19 Connection connection =threadLocal.get();20 if(null ==connection){21 try{22 Class.forName(MYSQL_DRIVER);23 connection =DriverManager.getConnection(url, userName, userPassword);24 returnconnection;25 } catch(ClassNotFoundException e) {26 e.printStackTrace();27 } catch(SQLException e) {28 e.printStackTrace();29 }30 }31 returnconnection;32 }33
34 /**
35 * 查询定义的相应的数据库对象bean值36 *@paramurl37 *@paramuserName38 *@paramuserPassword39 *@paramsql40 *@paramt41 *@paramobjs42 *@param43 *@return
44 */
45 public static ListexcuteQuery(String url, String userName, String userPassword, String sql, T t, Object...objs){46 List list = new ArrayList<>();47 Connection connection = null;48 PreparedStatement ps = null;49 ResultSet rs = null;50 try{51 connection =MySQLUtil.getMysqlConnection(url,userName,userPassword);52 ps =connection.prepareStatement(sql);53 //占位符赋值
54 if(null !=objs){55 for(int i=0; i clzss =t.getClass();64 T newInstance =(T)clzss.newInstance();65 for(int i=1; i<=columnCount; i++){66 String columnName =rm.getColumnName(i);67 String methodName = "set" + columnName.substring(0,1).toUpperCase() + columnName.substring(1);68 String columnClassName =rm.getColumnClassName(i);69 Method method =clzss.getDeclaredMethod(methodName, Class.forName(columnClassName));70 method.invoke(newInstance, rs.getObject(columnName));71 }72 list.add(newInstance);73 }74
75 }catch(Exception e){76 e.printStackTrace();77 }finally{78 MySQLUtil.close(ps);79 }80 returnlist;81 }82
83 /**
84 * 查询单个字段值85 *@paramurl86 *@paramuserName87 *@paramuserPassword88 *@paramsql89 *@paramobjs90 *@return
91 */
92 public static ListexcuteOneFieldQuery(String url, String userName, String userPassword, String sql, Object...objs){93 List list = new ArrayList<>();94 Connection connection = null;95 PreparedStatement ps = null;96 ResultSet rs = null;97 try{98 connection =MySQLUtil.getMysqlConnection(url,userName,userPassword);99 ps =connection.prepareStatement(sql);100 //占位符赋值
101 if(null !=objs){102 for(int i=0; i
120 /**
121 * 增删改122 *@paramurl123 *@paramuserName124 *@paramuserPassword125 *@paramsql126 *@paramobjs127 *@return
128 */
129 public staticInteger executeDML(String url, String userName, String userPassword, String sql, Object...objs){130 Connection connection = null;131 PreparedStatement ps = null;132 Integer integer = 0;133 try{134 connection =MySQLUtil.getMysqlConnection(url,userName,userPassword);135 ps =connection.prepareStatement(sql);136 if(null !=objs){137 for(int i=0; i
150 /**
151 * 关闭操作152 *@paramt153 *@param154 */
155 private static voidclose(T...t){156 //循环关流
157 for(T tmp:t) {158 //关闭流对象
159 if(tmp instanceofAutoCloseable) {160 try{161 ((AutoCloseable)tmp).close();162 } catch(Exception e) {163 e.printStackTrace();164 }165 }166 }167 }168
169 }