1 import java.sql.Connection; 2 import java.sql.PreparedStatement; 3 import java.sql.ResultSet; 4 import java.sql.ResultSetMetaData; 5 import java.sql.SQLException; 6 import java.util.ArrayList; 7 import java.util.HashMap; 8 import java.util.List; 9 import java.util.Map;10 11 import org.cc.foo_006.DbUtil;12 13 /**14  * SQL执行工具类15  * @author CC1100110016  *17  */18 public class SqlHelper{19     20     /**21      * 执行sql dql语句22      * @param clazz23      * @param sql24      * @param args25      * @return26      */27     public <T> List<T> dql(Class<T> clazz,String sql,Object ...args){28         29         List<T> list=new ArrayList<>();30         31         Connection conn=null;32         PreparedStatement pstmt=null;33         ResultSet rs=null;34         35         try {36             conn=DbUtil.getInstance().getConnection();37             pstmt=conn.prepareStatement(sql);38             for(int i=0;i<args.length;i++){39                 pstmt.setObject(i+1,args[i]);40             }41             rs=pstmt.executeQuery();42             while(rs.next()){43                 Map<String,Object> map=new HashMap<>();44                 ResultSetMetaData meta=rs.getMetaData();45                 for(int i=1;i<=meta.getColumnCount();i++){46                     map.put(meta.getColumnLabel(i),rs.getObject(i));47                 }48                 T o=clazz.newInstance();49                 for(String key:map.keySet()){50                     ReflectionUtil.setFieldValue(o,key,map.get(key));51                 }52                 list.add(o);53             }54         } catch (SQLException e) {55             e.printStackTrace();56         } catch (NoSuchFieldException e) {57             e.printStackTrace();58         } catch (InstantiationException e) {59             e.printStackTrace();60         } catch (IllegalAccessException e) {61             e.printStackTrace();62         }finally{63             DbUtil.getInstance().close(conn,pstmt,rs);64         }65         return list;66     }67     68     /**69      * 执行sql dml语句70      * @param conn71      * @param sql72      * @param args73      * @throws SQLException74      */75     public void dml(Connection conn,String sql,Object ...args) throws SQLException{76         PreparedStatement pstmt=null;77         78         try {79             pstmt=conn.prepareStatement(sql);80             for(int i=0;i<args.length;i++){81                 pstmt.setObject(i+1,args[i]);82             }83             pstmt.executeUpdate();84         } catch (SQLException e) {85             e.printStackTrace();86             throw e;87         }finally{88             DbUtil.getInstance().close(pstmt);89         }90     }91     92 }

  1 import java.lang.reflect.Field;  2 import java.lang.reflect.InvocationTargetException;  3 import java.lang.reflect.Method;  4 import java.lang.reflect.Modifier;  5 import java.util.Arrays;  6   7 /**  8  * 反射工具类  9  * @author CC11001100 10  * 11  */ 12 public class ReflectionUtil { 13      14     /** 15      * 设置属性可访问 16      * @param field 17      */ 18     public static void makeFieldAccessible(Field field) { 19         if (!Modifier.isPublic(field.getModifiers())) { 20             field.setAccessible(true); 21         } 22     } 23  24     /** 25      * 设置方法可访问 26      * @param method 27      */ 28     public static void makeMethodAccessible(Method method) { 29         if (!Modifier.isPublic(method.getModifiers())) { 30             method.setAccessible(true); 31         } 32     } 33  34     /** 35      * 获取本类显示声明的属性 36      * @param object 37      * @param fieldName 38      * @return 39      */ 40     public static Field getDeclaredField(Object object, String fieldName) { 41         try { 42             return object.getClass().getDeclaredField(fieldName); 43         } catch (NoSuchFieldException | SecurityException e) { 44             e.printStackTrace(); 45         } 46         return null; 47     } 48  49     /** 50      * 获取属性值（自己的或继承来的） 51      * @param object 52      * @param fieldName 53      * @return 54      */ 55     public static Object getFieldValue(Object object, String fieldName) { 56         try { 57             Field field=getField(object,fieldName); 58             if(field==null) return null; 59              60             Field.setAccessible(new Field[]{field},true); 61             return field.get(object); 62         } catch (SecurityException | IllegalArgumentException | IllegalAccessException e) { 63             e.printStackTrace(); 64         } 65         return null; 66     } 67  68     /** 69      * 设置属性的值（自己的或继承来的） 70      * @param object 71      * @param fieldName 72      * @param fieldValue 73      * @throws NoSuchFieldException  74      */ 75     public static void setFieldValue(Object object, String fieldName, Object fieldValue) throws NoSuchFieldException { 76         Field field=getField(object,fieldName); 77         if(field==null) throw new NoSuchFieldException() ; 78          79         try { 80             Field.setAccessible(new Field[]{field},true); 81             field.set(object,fieldValue); 82         } catch (SecurityException | IllegalArgumentException | IllegalAccessException e) { 83             e.printStackTrace(); 84         } 85     } 86      87     /** 88      * 提取冗余代码：根据名字获取属性，然后setField or getField  89      * @param object 90      * @param fieldName 91      * @return 92      */ 93     private static Field getField(Object object,String fieldName){ 94         try { 95             //从自己开始往上顺藤摸瓜 96             Class<?> clazz=object.getClass(); 97             while(clazz!=Object.class){ 98                 Field[] fields=clazz.getDeclaredFields(); 99                 for(int i=0;i<fields.length;i++){100                     //子类不应该继承父类的private，所以无视父类们的private field101                     if(Modifier.isPrivate(fields[i].getModifiers()) && clazz!=object.getClass()) continue;102                     if(fieldName.equals(fields[i].getName())){103                         return fields[i];104                     }105                 }106                 clazz=clazz.getSuperclass();107             }108         } catch (IllegalArgumentException | SecurityException e) {109             e.printStackTrace();110         }111         return null;112     }113 114     /**115      * 获取在本类显示声明的方法116      * @param object117      * @param methodName118      * @param parameterTypes119      * @return120      */121     public static Method getDeclaredMethod(Object object, String methodName, Class<?>[] parameterTypes) {122         try {123             return object.getClass().getDeclaredMethod(methodName,parameterTypes);124         } catch (NoSuchMethodException | SecurityException e) {125             e.printStackTrace();126         }127         return null;128     }129 130     /**131      * 获得方法，无论是本类声明还是继承来的132      * @param object133      * @param methodName134      * @param parameterTypes135      * @return136      */137     private static Method getMethod(Object object, String methodName, Class<?>[] parameterTypes) {138         Class clazz=object.getClass();139         while(clazz!=Object.class){140             Method methods[]=clazz.getDeclaredMethods();141             Method.setAccessible(methods,true); //太挫了..142             for(int i=0;i<methods.length;i++){143                 //子类不应该操作父类的private method144                 if(Modifier.isPrivate(methods[i].getModifiers()) && clazz!=object.getClass()) continue;145                 //因为Class只有一份，所以可以借助内置的进行比较，如果**名称**和**参数（数量、位置、类型）**都相同，则认定为同一个方法，返回值修饰符等不予考虑146                 //这么做不能区分包装类型和基本类型，难道它们底层实现不一致？妈的我怎么知道... int.class!=Integer.class147                 if(methodName.equals(methods[i].getName()) && Arrays.equals(methods[i].getParameterTypes(),parameterTypes)) return methods[i]; 148             }149             clazz=clazz.getSuperclass();150         }151         return null;152     }153     154     /**155      * 调用方法（本类声明或继承而来）156      * @param object157      * @param methodName158      * @param parameterTypes159      * @param parameters160      * @return161      * @throws NoSuchMethodException 162      */163     public static Object invokeMethod(Object object, String methodName, Class<?>[] parameterTypes,Object[] parameters) throws NoSuchMethodException {164         Method method=getMethod(object,methodName,parameterTypes);165         if(method==null) throw new NoSuchMethodException();166         167         try {168             return method.invoke(object,parameters);169         } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {170             e.printStackTrace();171         }172         return null;173     }174 175 }

 1 public class User { 2      3     private String id; 4     private String username; 5     private String passwd; 6     private int power; 7  8     @Override 9     public String toString() {10         return "User [id=" + id + ", username=" + username + ", passwd=" + passwd + ", power=" + power + "]";11     }12 13     public int getPower() {14         return power;15     }16 17     public void setPower(int power) {18         this.power = power;19     }20 21     public String getId() {22         return id;23     }24 25     public void setId(String id) {26         this.id = id;27     }28 29     public String getUsername() {30         return username;31     }32 33     public void setUsername(String username) {34         this.username = username;35     }36 37     public String getPasswd() {38         return passwd;39     }40 41     public void setPasswd(String passwd) {42         this.passwd = passwd;43     }44 }

 1 import java.sql.Connection; 2 import java.sql.SQLException; 3 import java.util.List; 4  5 import org.cc.foo_006.DbUtil; 6 import org.junit.Test; 7  8 public class Main_016 { 9 10     @Test11     public void test_001(){12         13         Connection conn=null;14         15         try {16             conn=DbUtil.getInstance().getConnection();17             conn.setAutoCommit(false);18             19             SqlHelper sqlHelper=new SqlHelper();20             21             sqlHelper.dml(conn,"INSERT INTO t_user (id,username,passwd) VALUES (?,?,?)","1001","root","toor");22             sqlHelper.dml(conn,"INSERT INTO t_power (u_id,power) VALUES (?,?)","1001",7);23             24             conn.commit();25         } catch (SQLException e) {26             e.printStackTrace();27             try {28                 conn.rollback();29             } catch (SQLException e1) {30                 e1.printStackTrace();31             }32         }finally{33             DbUtil.getInstance().close(conn);34         }35     }36     37     38     @Test39     public void test_002(){40         41         SqlHelper sqlHelper=new SqlHelper();42         List<User> list=sqlHelper.dql(User.class,"SELECT t1.id,username,passwd,power FROM t_user AS t1 JOIN t_power AS t2 ON t1.id=t2.u_id WHERE t1.id=?","1001");43         44         for(User u:list){45             System.out.println(u);46         }47         48     }49     50     51 }

 1 drop table if exists t_power; 2 drop table if exists t_user; 3  4 drop table if exists t_user; 5 create table t_user( 6     id varchar(20) primary key , 7     username varchar(20) not null, 8     passwd varchar(20) not null 9 )charset utf8;10 11 12 drop table if exists t_power;13 create table t_power (14     id int primary key auto_increment ,15     u_id varchar(20) ,16     power int ,17     constraint t_user_fk foreign key (u_id) references t_user(id)18 )charset utf8;