package com.qa.xxx;

 import org.springframework.stereotype.Component;

 import java.lang.reflect.Method;

 import java.sql.*;

 import java.util.ArrayList;

 import java.util.List;

 @Component

 public class MySQLUtil {

     private static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";

     private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

     public static Connection getMysqlConnection(String url, String userName, String userPassword){

         Connection connection = threadLocal.get();

         if(null == connection){

             try {

                 Class.forName(MYSQL_DRIVER);

                 connection = DriverManager.getConnection(url, userName, userPassword);

                 return connection;

             } catch (ClassNotFoundException e) {

                 e.printStackTrace();

             } catch (SQLException e) {

                 e.printStackTrace();

             }

         }

         return connection;

     }

     /**

      * 查询定义的相应的数据库对象bean值

      * @param url

      * @param userName

      * @param userPassword

      * @param sql

      * @param t

      * @param objs

      * @param <T>

      * @return

      */

     public static <T> List<T> excuteQuery(String url, String userName, String userPassword, String sql, T t, Object...objs){

         List<T> list = new ArrayList<>();

         Connection connection = null;

         PreparedStatement ps = null;

         ResultSet rs = null;

         try{

             connection = MySQLUtil.getMysqlConnection(url,userName,userPassword);

             ps = connection.prepareStatement(sql);

             //占位符赋值

             if(null != objs){

                 for(int i=0; i<objs.length; i++){

                     ps.setObject((i+1), objs[i]);

                 }

             }

             rs = ps.executeQuery();

             ResultSetMetaData rm = rs.getMetaData();

             int columnCount = rm.getColumnCount();

             while (rs.next()){

                 Class<? extends Object> clzss = t.getClass();

                 T newInstance = (T)clzss.newInstance();

                 for(int i=1; i<=columnCount; i++){

                     String columnName = rm.getColumnName(i);

                     String methodName = "set" + columnName.substring(0,1).toUpperCase() + columnName.substring(1);

                     String columnClassName = rm.getColumnClassName(i);

                     Method method = clzss.getDeclaredMethod(methodName, Class.forName(columnClassName));

                     method.invoke(newInstance, rs.getObject(columnName));

                 }

                 list.add(newInstance);

             }

         }catch (Exception e){

             e.printStackTrace();

         }finally {

             MySQLUtil.close(ps);

         }

         return list;

     }

     /**

      * 查询单个字段值

      * @param url

      * @param userName

      * @param userPassword

      * @param sql

      * @param objs

      * @return

      */

     public static List<String> excuteOneFieldQuery(String url, String userName, String userPassword, String sql, Object...objs){

         List<String> list = new ArrayList<>();

         Connection connection = null;

         PreparedStatement ps = null;

         ResultSet rs = null;

         try{

             connection = MySQLUtil.getMysqlConnection(url,userName,userPassword);

             ps = connection.prepareStatement(sql);

             //占位符赋值

             if(null != objs){

                 for(int i=0; i<objs.length; i++){

                     ps.setObject((i+1), objs[i]);

                 }

             }

             rs = ps.executeQuery();

             ResultSetMetaData rm = rs.getMetaData();

             int columnCount = rm.getColumnCount();

             while (rs.next()){

                 list.add(rs.getString(1));

             }

         }catch (Exception e){

             e.printStackTrace();

         }finally {

             MySQLUtil.close(ps);

         }

         return list;

     }

     /**

      * 增删改

      * @param url

      * @param userName

      * @param userPassword

      * @param sql

      * @param objs

      * @return

      */

     public static Integer executeDML(String url, String userName, String userPassword, String sql, Object...objs){

         Connection connection = null;

         PreparedStatement ps = null;

         Integer integer = 0;

         try{

             connection = MySQLUtil.getMysqlConnection(url,userName,userPassword);

             ps = connection.prepareStatement(sql);

             if(null != objs){

                 for(int i=0; i<objs.length; i++){

                     ps.setObject((i+1), objs[i]);

                 }

             }

             integer = ps.executeUpdate();

         }catch (SQLException e){

             e.printStackTrace();

         }finally {

             MySQLUtil.close(ps);

         }

         return integer;

     }

     /**

      * 关闭操作

      * @param t

      * @param <T>

      */

     private static <T>void close(T...t){

         //循环关流

         for(T tmp:t) {

             //关闭流对象

             if(tmp instanceof AutoCloseable) {

                 try {

                     ((AutoCloseable)tmp).close();

                 } catch (Exception e) {

                     e.printStackTrace();

                 }

             }

         }

     }

 }

 package com.qa.xxx;

 import org.slf4j.Logger;

 import org.slf4j.LoggerFactory;

 import org.springframework.stereotype.Component;

 import java.lang.reflect.Field;

 import java.lang.reflect.Method;

 import java.util.HashMap;

 import java.util.List;

 import java.util.Map;

 @Component

 public class DataCompareUtil {

     private final static Logger logger = LoggerFactory.getLogger(DataCompareUtil.class);

     /**

      * 数据库表查询字段比对

      * @param obj1 老查询获取的数据

      * @param obj2 新查询获取的数据

      * @param list 要对比的字段

      * @return 返回<字段名称，原值x 新值x>

      */

     public static Map<String, String> compareObject(Object obj1, Object obj2, List<String> list){

         Map<String, String> map = new HashMap<>();

         if(null != list && !list.isEmpty()){

             for(String field : list){

                 String firstLetter = field.substring(0,1).toUpperCase();

                 String getter = "get" + firstLetter + field.substring(1);

                 try {

                     Method method1 = obj1.getClass().getMethod(getter, new Class[]{});

                     Method method2 = obj2.getClass().getMethod(getter, new Class[]{});

                     Object oldValue = method1.invoke(obj1, new Object[] {});

                     Object newValue = method2.invoke(obj2, new Object[] {});

                     map.put(field, "原值：" + oldValue.toString() + " 新值：" + newValue.toString());

                 } catch (Exception e) {

                     e.printStackTrace();

                 }

             }

             return map;

         }else {

             Class clazz = obj2.getClass();

             Field[] fields = clazz.getDeclaredFields();

             for(Field field : fields){

                 String fieldName = field.getName();

                 String firstLetter = fieldName.substring(0,1).toUpperCase();

                 String getter = "get" + firstLetter + fieldName.substring(1);

                 try {

                     Method method1 = obj1.getClass().getMethod(getter, new Class[]{});

                     Method method2 = obj2.getClass().getMethod(getter, new Class[]{});

                     Object oldValue = method1.invoke(obj1, new Object[] {});

                     Object newValue = method2.invoke(obj2, new Object[] {});

                     map.put(fieldName, "原值：" + oldValue.toString() + " 新值：" + newValue.toString());

                 } catch (Exception e) {

                     e.printStackTrace();

                 }

             }

             return map;

         }

     }

 }