Java Mysql--链接数据库，数据库字段比较
连接库操作：
1 package com.qa.xxx; 2 3 4 import org.springframework.stereotype.Component; 5 import java.lang.reflect.Method; 6 import java.sql.*; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 @Component 11 public class MySQLUtil { 12 13 private static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver"; 14 15 private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>(); 16 17 18 public static Connection 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 return connection; 25 } catch (ClassNotFoundException e) { 26 e.printStackTrace(); 27 } catch (SQLException e) { 28 e.printStackTrace(); 29 } 30 } 31 return connection; 32 } 33 34 /** 35 * 查询定义的相应的数据库对象bean值 36 * @param url 37 * @param userName 38 * @param userPassword 39 * @param sql 40 * @param t 41 * @param objs 42 * @param <T> 43 * @return 44 */ 45 public static <T> List<T> excuteQuery(String url, String userName, String userPassword, String sql, T t, Object...objs){ 46 List<T> 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<objs.length; i++){ 56 ps.setObject((i+1), objs[i]); 57 } 58 } 59 rs = ps.executeQuery(); 60 ResultSetMetaData rm = rs.getMetaData(); 61 int columnCount = rm.getColumnCount(); 62 while (rs.next()){ 63 Class<? extends Object> 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 return list; 81 } 82 83 /** 84 * 查询单个字段值 85 * @param url 86 * @param userName 87 * @param userPassword 88 * @param sql 89 * @param objs 90 * @return 91 */ 92 public static List<String> excuteOneFieldQuery(String url, String userName, String userPassword, String sql, Object...objs){ 93 List<String> 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<objs.length; i++){ 103 ps.setObject((i+1), objs[i]); 104 } 105 } 106 rs = ps.executeQuery(); 107 ResultSetMetaData rm = rs.getMetaData(); 108 int columnCount = rm.getColumnCount(); 109 while (rs.next()){ 110 list.add(rs.getString(1)); 111 } 112 }catch (Exception e){ 113 e.printStackTrace(); 114 }finally { 115 MySQLUtil.close(ps); 116 } 117 return list; 118 } 119 120 /** 121 * 增删改 122 * @param url 123 * @param userName 124 * @param userPassword 125 * @param sql 126 * @param objs 127 * @return 128 */ 129 public static Integer 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<objs.length; i++){ 138 ps.setObject((i+1), objs[i]); 139 } 140 } 141 integer = ps.executeUpdate(); 142 }catch (SQLException e){ 143 e.printStackTrace(); 144 }finally { 145 MySQLUtil.close(ps); 146 } 147 return integer; 148 } 149 150 /** 151 * 关闭操作 152 * @param t 153 * @param <T> 154 */ 155 private static <T>void close(T...t){ 156 //循环关流 157 for(T tmp:t) { 158 //关闭流对象 159 if(tmp instanceof AutoCloseable) { 160 try { 161 ((AutoCloseable)tmp).close(); 162 } catch (Exception e) { 163 e.printStackTrace(); 164 } 165 } 166 } 167 } 168 169 }
数据库字段比对：
1 package com.qa.xxx; 2 3 4 import org.slf4j.Logger; 5 import org.slf4j.LoggerFactory; 6 import org.springframework.stereotype.Component; 7 import java.lang.reflect.Field; 8 import java.lang.reflect.Method; 9 import java.util.HashMap; 10 import java.util.List; 11 import java.util.Map; 12 13 @Component 14 public class DataCompareUtil { 15 16 private final static Logger logger = LoggerFactory.getLogger(DataCompareUtil.class); 17 18 /** 19 * 数据库表查询字段比对 20 * @param obj1 老查询获取的数据 21 * @param obj2 新查询获取的数据 22 * @param list 要对比的字段 23 * @return 返回<字段名称，原值x 新值x> 24 */ 25 public static Map<String, String> compareObject(Object obj1, Object obj2, List<String> list){ 26 Map<String, String> map = new HashMap<>(); 27 if(null != list && !list.isEmpty()){ 28 for(String field : list){ 29 String firstLetter = field.substring(0,1).toUpperCase(); 30 String getter = "get" + firstLetter + field.substring(1); 31 try { 32 Method method1 = obj1.getClass().getMethod(getter, new Class[]{}); 33 Method method2 = obj2.getClass().getMethod(getter, new Class[]{}); 34 Object oldValue = method1.invoke(obj1, new Object[] {}); 35 Object newValue = method2.invoke(obj2, new Object[] {}); 36 map.put(field, "原值：" + oldValue.toString() + " 新值：" + newValue.toString()); 37 } catch (Exception e) { 38 e.printStackTrace(); 39 } 40 } 41 return map; 42 }else { 43 Class clazz = obj2.getClass(); 44 Field[] fields = clazz.getDeclaredFields(); 45 for(Field field : fields){ 46 String fieldName = field.getName(); 47 String firstLetter = fieldName.substring(0,1).toUpperCase(); 48 String getter = "get" + firstLetter + fieldName.substring(1); 49 try { 50 Method method1 = obj1.getClass().getMethod(getter, new Class[]{}); 51 Method method2 = obj2.getClass().getMethod(getter, new Class[]{}); 52 Object oldValue = method1.invoke(obj1, new Object[] {}); 53 Object newValue = method2.invoke(obj2, new Object[] {}); 54 map.put(fieldName, "原值：" + oldValue.toString() + " 新值：" + newValue.toString()); 55 } catch (Exception e) { 56 e.printStackTrace(); 57 } 58 } 59 return map; 60 } 61 } 62 63 }
