JDBC之SqlHelper

SqlHelper工具类如下：

 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 CC11001100
16  *
17  */
18 public class SqlHelper{
19     
20     /**
21      * 执行sql dql语句
22      * @param clazz
23      * @param sql
24      * @param args
25      * @return
26      */
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 conn
71      * @param sql
72      * @param args
73      * @throws SQLException
74      */
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 field
101                     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 object
117      * @param methodName
118      * @param parameterTypes
119      * @return
120      */
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 object
133      * @param methodName
134      * @param parameterTypes
135      * @return
136      */
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 method
144                 if(Modifier.isPrivate(methods[i].getModifiers()) && clazz!=object.getClass()) continue;
145                 //因为Class只有一份，所以可以借助内置的进行比较，如果**名称**和**参数（数量、位置、类型）**都相同，则认定为同一个方法，返回值修饰符等不予考虑
146                 //这么做不能区分包装类型和基本类型，难道它们底层实现不一致？妈的我怎么知道... int.class!=Integer.class
147                 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 object
157      * @param methodName
158      * @param parameterTypes
159      * @param parameters
160      * @return
161      * @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     @Test
11     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     @Test
39     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;