[原创] Java JDBC连接数据库，反射创建实体类对象并赋值数据库行记录（支持存储过程）

1、SqlHelper.java

  1 import java.lang.reflect.*;
  2 import java.sql.*;
  3 import java.util.*;
  4 
  5 public class SqlHelper {
  6     // SQL Server
  7     /**
  8      * JDBC驱动名称
  9      */
 10     public static final String CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
 11     /**
 12      * 数据库连库字符串
 13      */
 14     public static final String URL = "jdbc:sqlserver://192.168.1.254:1433;databaseName=BBSDB";
 15     /**
 16      * 用户名
 17      */
 18     public static final String UID = "sa";
 19     /**
 20      * 密码
 21      */
 22     public static final String PWD = "";
 23     /**
 24      * JDBC驱动类型
 25      */
 26     public static Class CLS = null;
 27 
 28     // Oracle
 29     // public static final String CLASS_NAME =
 30     // "oracle.jdbc.driver.OracleDriver";
 31     // public static final String URL =
 32     // "jdbc:oracle:thin:@localhost:1522:accp11g";
 33     // public static final String UID = "system";
 34     // public static final String PWD = "manager";
 35     /**
 36      * 获取数据库连接对象
 37      * 
 38      * @return
 39      * @throws ClassNotFoundException
 40      * @throws SQLException
 41      */
 42     public static Connection getConnection() throws ClassNotFoundException,
 43             SQLException {
 44         if (CLS == null) {
 45             CLS = Class.forName(CLASS_NAME);
 46         }
 47         return DriverManager.getConnection(URL, UID, PWD);
 48     }
 49 
 50     /**
 51      * 执行SQL语句不返回查询的操作，返回受影响的行数
 52      * 
 53      * @param sql
 54      *            SQL语句
 55      * @return 受影响的行数
 56      * @throws ClassNotFoundException
 57      * @throws SQLException
 58      */
 59     public static int executeNonQuery(String sql) {
 60         int result = -1;
 61         Connection con = null;
 62         PreparedStatement ps = null;
 63         try {
 64             con = getConnection();
 65             ps = con.prepareStatement(sql);
 66             result = ps.executeUpdate();
 67         } catch (Exception e) {
 68             e.printStackTrace();
 69         } finally {
 70             close(con, ps, null);
 71         }
 72         return result;
 73     }
 74 
 75     /**
 76      * 执行Insert语句，返回Insert成功之后标识列的值
 77      * 
 78      * @param sql
 79      * @return
 80      * @throws ClassNotFoundException
 81      * @throws SQLException
 82      */
 83     public static int executeIdentity(String sql) {
 84         int identity = -1;
 85         Connection con = null;
 86         Statement ps = null;
 87         ResultSet rs = null;
 88         try {
 89             con = getConnection();
 90             ps = con.createStatement();
 91             ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
 92             rs = ps.getGeneratedKeys();
 93             if (rs.next()) {
 94                 // identity = rs.getInt("GENERATED_KEYS");
 95                 identity = rs.getInt(1);
 96             }
 97         } catch (Exception e) {
 98             e.printStackTrace();
 99         } finally {
100             close(con, ps, null);
101         }
102         return identity;
103     }
104 
105     /**
106      * 执行不返回结果集的存储过程
107      * 
108      * @param sql
109      *            存储过程名称
110      * @param params
111      *            存储过程参数
112      * @throws ClassNotFoundException
113      * @throws SQLException
114      */
115     public static void executeNonQuery(String sql, SqlParameter... params) {
116         Connection con = null;
117         CallableStatement cs = null;
118         try {
119             con = getConnection();
120             cs = con.prepareCall(sql);
121             setSqlParameter(cs, params);
122             cs.executeUpdate();
123             getSqlParameter(cs, params);
124         } catch (Exception e) {
125             e.printStackTrace();
126         } finally {
127             close(con, cs, null);
128         }
129     }
130 
131     /**
132      * 执行返回聚合函数的操作
133      * 
134      * @param sql
135      *            含有聚合函数的SQL语句
136      * @return 聚合函数的执行结果
137      * @throws SQLException
138      * @throws ClassNotFoundException
139      */
140     public static int executeScalar(String sql) {
141         int result = -1;
142         Connection con = null;
143         PreparedStatement ps = null;
144         ResultSet rs = null;
145         try {
146             con = getConnection();
147             ps = con.prepareStatement(sql);
148             rs = ps.executeQuery();
149             if (rs.next()) {
150                 result = rs.getInt(1);
151             }
152         } catch (Exception e) {
153             e.printStackTrace();
154         } finally {
155             close(con, ps, rs);
156         }
157         return result;
158     }
159 
160     /**
161      * 执行返回泛型集合的SQL语句
162      * 
163      * @param cls
164      *            泛型类型
165      * @param sql
166      *            查询SQL语句
167      * @return 泛型集合
168      * @throws ClassNotFoundException
169      * @throws SQLException
170      * @throws InstantiationException
171      * @throws IllegalAccessException
172      */
173     public static <T> List<T> executeList(Class<T> cls, String sql) {
174         List<T> list = new ArrayList<T>();
175         Connection con = null;
176         PreparedStatement ps = null;
177         ResultSet rs = null;
178         try {
179             con = getConnection();
180             ps = con.prepareStatement(sql);
181             rs = ps.executeQuery();
182             while (rs.next()) {
183                 T obj = executeResultSet(cls, rs);
184                 list.add(obj);
185             }
186         } catch (Exception e) {
187             e.printStackTrace();
188         } finally {
189             close(con, ps, rs);
190         }
191         return list;
192     }
193 
194     /**
195      * 执行返回泛型集合的存储过程
196      * 
197      * @param cls
198      *            泛型类型
199      * @param sql
200      *            存储过程名称
201      * @param params
202      *            存储过程参数
203      * @return 泛型集合
204      * @throws ClassNotFoundException
205      * @throws SQLException
206      * @throws InstantiationException
207      * @throws IllegalAccessException
208      */
209     public static <T> List<T> executeList(Class<T> cls, String sql,
210             SqlParameter... params) {
211         List<T> list = new ArrayList<T>();
212         Connection con = null;
213         CallableStatement cs = null;
214         ResultSet rs = null;
215         try {
216             con = getConnection();
217             cs = con.prepareCall(sql);
218             setSqlParameter(cs, params);
219             rs = cs.executeQuery();
220             while (rs.next()) {
221                 T obj = executeResultSet(cls, rs);
222                 list.add(obj);
223             }
224         } catch (Exception e) {
225             e.printStackTrace();
226         } finally {
227             close(con, cs, rs);
228         }
229         return list;
230     }
231 
232     /**
233      * 执行返回泛型类型对象的SQL语句
234      * 
235      * @param cls
236      *            泛型类型
237      * @param sql
238      *            SQL语句
239      * @return 泛型类型对象
240      * @throws SQLException
241      * @throws ClassNotFoundException
242      * @throws InstantiationException
243      * @throws IllegalAccessException
244      */
245     public static <T> T executeEntity(Class<T> cls, String sql) {
246         T obj = null;
247         Connection con = null;
248         PreparedStatement ps = null;
249         ResultSet rs = null;
250         try {
251             con = getConnection();
252             ps = con.prepareStatement(sql);
253             rs = ps.executeQuery();
254             while (rs.next()) {
255                 obj = executeResultSet(cls, rs);
256                 break;
257             }
258         } catch (Exception e) {
259             e.printStackTrace();
260         } finally {
261             close(con, ps, rs);
262         }
263         return obj;
264     }
265 
266     /**
267      * 执行返回泛型类型对象的存储过程
268      * 
269      * @param cls
270      *            泛型类型
271      * @param sql
272      *            SQL语句
273      * @param params
274      *            存储过程参数
275      * @return 泛型类型对象
276      * @throws SQLException
277      * @throws ClassNotFoundException
278      * @throws InstantiationException
279      * @throws IllegalAccessException
280      */
281     public static <T> T executeEntity(Class<T> cls, String sql,
282             SqlParameter... params) {
283         T obj = null;
284         Connection con = null;
285         CallableStatement cs = null;
286         ResultSet rs = null;
287         try {
288             con = getConnection();
289             cs = con.prepareCall(sql);
290             setSqlParameter(cs, params);
291             rs = cs.executeQuery();
292             while (rs.next()) {
293                 obj = executeResultSet(cls, rs);
294                 break;
295             }
296         } catch (Exception e) {
297             e.printStackTrace();
298         } finally {
299             close(con, cs, rs);
300         }
301         return obj;
302     }
303 
304     /**
305      * 将一条记录转成一个对象
306      * 
307      * @param cls
308      *            泛型类型
309      * @param rs
310      *            ResultSet对象
311      * @return 泛型类型对象
312      * @throws InstantiationException
313      * @throws IllegalAccessException
314      * @throws SQLException
315      */
316     private static <T> T executeResultSet(Class<T> cls, ResultSet rs)
317             throws InstantiationException, IllegalAccessException, SQLException {
318         T obj = cls.newInstance();
319         ResultSetMetaData rsm = rs.getMetaData();
320         int columnCount = rsm.getColumnCount();
321         // Field[] fields = cls.getFields();
322         Field[] fields = cls.getDeclaredFields();
323         for (int i = 0; i < fields.length; i++) {
324             Field field = fields[i];
325             String fieldName = field.getName();
326             for (int j = 1; j <= columnCount; j++) {
327                 String columnName = rsm.getColumnName(j);
328                 if (fieldName.equalsIgnoreCase(columnName)) {
329                     Object value = rs.getObject(j);
330                     field.setAccessible(true);
331                     field.set(obj, value);
332                     break;
333                 }
334             }
335         }
336         return obj;
337     }
338 
339     /**
340      * 设置存储过程参数名称，参数值，参数方向
341      * 
342      * @param cs
343      * @param params
344      * @throws SQLException
345      */
346     private static void setSqlParameter(CallableStatement cs,
347             SqlParameter... params) throws SQLException {
348         if (params != null) {
349             for (SqlParameter param : params) {
350                 if (param.OutPut) {
351                     String paramName = param.Name;
352                     if (paramName == null || paramName.equals("")) {
353                         cs.registerOutParameter(1, param.Type);// 设置返回类型参数
354                     } else {
355                         cs.registerOutParameter(paramName, param.Type);// 设置输出类型参数
356                     }
357                 } else {
358                     cs.setObject(param.Name, param.Value);// 设置输入类型参数
359                 }
360             }
361         }
362     }
363 
364     /**
365      * 得到存储过程参数执行结果
366      * 
367      * @param cs
368      * @param params
369      * @throws SQLException
370      */
371     private static void getSqlParameter(CallableStatement cs,
372             SqlParameter... params) throws SQLException {
373         for (SqlParameter param : params) {
374             if (param.OutPut) {
375                 String paramName = param.Name;
376                 if (paramName == null || paramName.equals("")) {
377                     param.Value = cs.getObject(1);// 返回类型参数值
378                 } else {
379                     param.Value = cs.getObject(paramName);// 输出类型参数值
380                 }
381             }
382         }
383     }
384 
385     /**
386      * 关闭JDBC对象，释放资源。
387      * 
388      * @param con
389      *            连接对象
390      * @param ps
391      *            命令对象
392      * @param rs
393      *            结果集对象
394      * @throws SQLException
395      */
396     private static void close(Connection con, Statement ps, ResultSet rs) {
397         try {
398             rs.close();
399             if (rs != null) {
400 
401                 rs = null;
402             }
403             if (ps != null) {
404                 ps.close();
405                 ps = null;
406             }
407             if (con != null) {
408                 con.close();
409                 con = null;
410             }
411         } catch (SQLException e) {
412             // TODO Auto-generated catch block
413             e.printStackTrace();
414         }
415     }
416 }

SqlHelper

2、SqlParameter.java

 1 /**
 2  * 存储过程参数类型
 3  * @author Administrator
 4  *
 5  */
 6 public class SqlParameter {
 7     /**
 8      * 参数名称
 9      */
10     public String Name;
11     /**
12      * 参数值
13      */
14     public Object Value;
15     /**
16      * true表示参数为输出类型
17      */
18     public boolean OutPut;
19     /**
20      * 参数类型
21      */
22     public int Type;
23     /**
24      * 输入类型参数的构造函数
25      * @param name 存储过程 输入类型 参数名称
26      * @param value 存储过程 输入类型 参数值
27      */
28     public SqlParameter(String name,Object value){
29         this.Name = name;
30         this.Value= value;
31     }
32     /**
33      * 输出类型参数的构造函数
34      * @param type 存储过程 输出类型 参数类型
35      * @param name 存储过程 输出类型 参数名称
36      */
37     public SqlParameter(int type,String name){
38         this.Name = name;
39         this.OutPut = true;
40         this.Type = type;
41     }
42     /**
43      * 返回类型参数的构造函数
44      * @param type 存储过程 返回类型
45      */
46     public SqlParameter(int type){
47         this.Name = "";
48         this.OutPut = true;
49         this.Type = type;
50     }
51 }

SqlParameter

3、Program.java

 1 import java.util.List;
 2 
 3 public class Program {
 4 
 5     public static void main(String[] args) {
 6         // TODO Auto-generated method stub
 7         
 8         String sql = "INSERT INTO [dbo].[UserInfo] ([UserName] ,[LoginName] ,[LoginPwd] ,[UserSex] ,[Birthday]) VALUES ('%s','%s','%s','%s','%s')";
 9         sql = String.format(sql, "wyp1","wyp1","wyp1","1","1981-04-21");
10         int identyValue = SqlHelper.executeIdentity(sql);
11         System.out.println(String.format("Identity Value:%d",identyValue));
12         
13 //        List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "select * from UserInfo");
14 //        for (UserInfo userInfo : list) {
15 //            System.out.println(String.format(
16 //                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",
17 //                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));
18 //        }
19         
20 //        SqlParameter param = new SqlParameter("sortField", "[UserInfoId] DESC");
21 //        List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectAll(?)}",param);
22 //        for (UserInfo userInfo : list) {
23 //            System.out.println(String.format(
24 //                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",
25 //                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));
26 //        }
27         
28 //        SqlParameter paramSortField = new SqlParameter("sortField", "[UserInfoId] DESC");
29 //        SqlParameter paramPageSize = new SqlParameter("pageSize", 10);
30 //        SqlParameter paramPageIndex = new SqlParameter("pageIndex", 1);
31 //        SqlParameter paramWhere = new SqlParameter("where", "1=1");
32 //        List<UserInfo> list = SqlHelper.executeList(UserInfo.class, "{call dbo.UserInfoSelectByPagerParams(?,?,?,?)}",paramSortField,paramPageSize,paramPageIndex,paramWhere);
33 //        for (UserInfo userInfo : list) {
34 //            System.out.println(String.format(
35 //                    "UserInfoId:%d,UserName:%s,LoginName:%s,LoginPwd:%s,UserSex:%s,Birthday:%s",
36 //                    userInfo.getUserInfoId(),userInfo.getUserName(),userInfo.getLoginName(),userInfo.getLoginPwd(),userInfo.getUserSex()?"男":"女",DateHelper.toString(userInfo.getBirthday())));
37 //        }
38         
39 //        SqlParameter paramWhere = new SqlParameter("where", "1=1");
40 //        SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount");
41 //        SqlHelper.executeNonQuery("{call dbo.UserInfoCountByWhere(?,?)}", paramWhere,paramRecordCount);
42 //        if(paramRecordCount.Value instanceof Integer){
43 //            Integer recordCount = (Integer)paramRecordCount.Value;
44 //            System.out.println(String.format("RecordCount:%d",recordCount));
45 //        }
46         
47 //        SqlParameter paramWhere = new SqlParameter("where", "1=1");
48 //        SqlParameter paramRecordCount = new SqlParameter(java.sql.Types.INTEGER, "recordCount");
49 //        SqlParameter paramReturnValue = new SqlParameter(java.sql.Types.INTEGER);
50 //        SqlHelper.executeNonQuery("{? = call dbo.UserInfoCountByWhere(?,?)}", paramReturnValue,paramWhere,paramRecordCount);
51 //        if(paramRecordCount.Value instanceof Integer){
52 //            Integer recordCount = (Integer)paramRecordCount.Value;
53 //            System.out.println(String.format("RecordCount:%d",recordCount));
54 //        }
55 //        if(paramReturnValue.Value instanceof Integer){
56 //            Integer returnValue = (Integer)paramReturnValue.Value;
57 //            System.out.println(String.format("ReturnValue:%d",returnValue));
58 //        }
59     }
60 
61 }

Program