jdbc操作工具类

 1     package com.iflytek.jdbcdemo;  
 2        
 3     import java.io.IOException;  
 4     import java.io.InputStream;  
 5     import java.util.Properties;  
 6        
 7     /** 
 8      * 数据库配置文件读取方法 
 9      * @author WANGYAN 
10      * 
11      */  
12     public class DbConfig {  
13            
14         private String driver;  
15         private String url;  
16         private String userName;  
17         private String password;  
18            
19         public DbConfig() {  
20             InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("com/iflytek/jdbcdemo/dbConfig.properties");  
21             Properties p=new Properties();  
22             try {  
23                 p.load(inputStream);  
24                 this.driver=p.getProperty("driver");  
25                 this.url=p.getProperty("url");  
26                 this.userName=p.getProperty("username");  
27                 this.password=p.getProperty("passwrod");  
28             } catch (IOException e) {  
29                 // TODO Auto-generated catch block  
30                 e.printStackTrace();  
31             }  
32                
33         }  
34            
35         public String getDriver() {  
36             return driver;  
37         }  
38         public String getUrl() {  
39             return url;  
40         }  
41         public String getUserName() {  
42             return userName;  
43         }  
44         public String getPassword() {  
45             return password;  
46         }  
47            
48            
49        
50     }

  1 package com.iflytek.jdbcdemo;  
  2    
  3 import java.sql.Connection;  
  4 import java.sql.DriverManager;  
  5 import java.sql.ResultSet;  
  6 import java.sql.SQLException;  
  7 import java.sql.Statement;  
  8    
  9 /** 
 10  * jdbc工具类 
 11  *  
 12  * @author WANGYAN 
 13  *  
 14  */  
 15 public final class JdbcUnits {  
 16    
 17     /** 
 18      * 数据库连接地址 
 19      */  
 20     private static String url ;  
 21     /** 
 22      * 用户名 
 23      */  
 24     private static String userName ;  
 25     /** 
 26      * 密码 
 27      */  
 28     private static String password;  
 29        
 30     private static String driver;  
 31    
 32     /** 
 33      * 装载驱动 
 34      */  
 35     static {  
 36            
 37         DbConfig config=new DbConfig();  
 38         url=config.getUrl();  
 39         userName=config.getUserName();  
 40         password=config.getPassword();  
 41         driver=config.getDriver();  
 42            
 43         try {  
 44             Class.forName(driver);  
 45         } catch (ClassNotFoundException e) {  
 46             throw new ExceptionInInitializerError(e);  
 47         }  
 48     }  
 49    
 50     /** 
 51      * 建立数据库连接 
 52      *  
 53      * @return 
 54      * @throws SQLException 
 55      */  
 56     public static Connection getConnection() throws SQLException {  
 57         Connection conn = null;  
 58         conn = DriverManager.getConnection(url, userName, password);  
 59         return conn;  
 60     }  
 61    
 62     /** 
 63      * 释放连接 
 64      * @param conn 
 65      */  
 66     private static void freeConnection(Connection conn) {  
 67         try {  
 68             conn.close();  
 69         } catch (SQLException e) {  
 70             // TODO Auto-generated catch block  
 71             e.printStackTrace();  
 72         }  
 73     }  
 74    
 75     /** 
 76      * 释放statement 
 77      * @param statement 
 78      */  
 79     private static void freeStatement(Statement statement) {  
 80         try {  
 81             statement.close();  
 82         } catch (SQLException e) {  
 83             // TODO Auto-generated catch block  
 84             e.printStackTrace();  
 85         }  
 86     }  
 87    
 88     /** 
 89      * 释放resultset 
 90      * @param rs 
 91      */  
 92     private static void freeResultSet(ResultSet rs) {  
 93         try {  
 94             rs.close();  
 95         } catch (SQLException e) {  
 96             // TODO Auto-generated catch block  
 97             e.printStackTrace();  
 98         }  
 99     }  
100    
101     /** 
102      * 释放资源 
103      *  
104      * @param conn 
105      * @param statement 
106      * @param rs 
107      */  
108     public static void free(Connection conn, Statement statement, ResultSet rs) {  
109         if (rs != null) {  
110             freeResultSet(rs);  
111         }  
112         if (statement != null) {  
113             freeStatement(statement);  
114         }  
115         if (conn != null) {  
116             freeConnection(conn);  
117         }  
118     }  
119    
120 }

  1     package com.iflytek.jdbcdemo;  
  2        
  3     import java.sql.CallableStatement;  
  4     import java.sql.Connection;  
  5     import java.sql.PreparedStatement;  
  6     import java.sql.ResultSet;  
  7     import java.sql.ResultSetMetaData;  
  8     import java.sql.SQLException;  
  9     import java.sql.Statement;  
 10     import java.sql.Types;  
 11     import java.util.ArrayList;  
 12     import java.util.HashMap;  
 13     import java.util.List;  
 14     import java.util.Map;  
 15        
 16     /** 
 17      * 数据库访问帮助类 
 18      *  
 19      * @author WANGYAN 
 20      *  
 21      */  
 22     public class JdbcHelper {  
 23        
 24         private static Connection conn = null;  
 25         private static PreparedStatement preparedStatement = null;  
 26         private static CallableStatement callableStatement = null;  
 27        
 28         /** 
 29          * 用于查询，返回结果集 
 30          *  
 31          * @param sql 
 32          *            sql语句 
 33          * @return 结果集 
 34          * @throws SQLException 
 35          */  
 36         @SuppressWarnings("rawtypes")  
 37         public static List query(String sql) throws SQLException {  
 38        
 39             ResultSet rs = null;  
 40             try {  
 41                 getPreparedStatement(sql);  
 42                 rs = preparedStatement.executeQuery();  
 43        
 44                 return ResultToListMap(rs);  
 45             } catch (SQLException e) {  
 46                 throw new SQLException(e);  
 47             } finally {  
 48                 free(rs);  
 49             }  
 50        
 51         }  
 52        
 53         /** 
 54          * 用于带参数的查询，返回结果集 
 55          *  
 56          * @param sql 
 57          *            sql语句 
 58          * @param paramters 
 59          *            参数集合 
 60          * @return 结果集 
 61          * @throws SQLException 
 62          */  
 63         @SuppressWarnings("rawtypes")  
 64         public static List query(String sql, Object... paramters)  
 65                 throws SQLException {  
 66        
 67             ResultSet rs = null;  
 68             try {  
 69                 getPreparedStatement(sql);  
 70        
 71                 for (int i = 0; i < paramters.length; i++) {  
 72                     preparedStatement.setObject(i + 1, paramters[i]);  
 73                 }  
 74                 rs = preparedStatement.executeQuery();  
 75                 return ResultToListMap(rs);  
 76             } catch (SQLException e) {  
 77                 throw new SQLException(e);  
 78             } finally {  
 79                 free(rs);  
 80             }  
 81         }  
 82        
 83         /** 
 84          * 返回单个结果的值，如countminmax等等 
 85          *  
 86          * @param sql 
 87          *            sql语句 
 88          * @return 结果集 
 89          * @throws SQLException 
 90          */  
 91         public static Object getSingle(String sql) throws SQLException {  
 92             Object result = null;  
 93             ResultSet rs = null;  
 94             try {  
 95                 getPreparedStatement(sql);  
 96                 rs = preparedStatement.executeQuery();  
 97                 if (rs.next()) {  
 98                     result = rs.getObject(1);  
 99                 }  
100                 return result;  
101             } catch (SQLException e) {  
102                 throw new SQLException(e);  
103             } finally {  
104                 free(rs);  
105             }  
106        
107         }  
108        
109         /** 
110          * 返回单个结果值，如countminmax等 
111          *  
112          * @param sql 
113          *            sql语句 
114          * @param paramters 
115          *            参数列表 
116          * @return 结果 
117          * @throws SQLException 
118          */  
119         public static Object getSingle(String sql, Object... paramters)  
120                 throws SQLException {  
121             Object result = null;  
122             ResultSet rs = null;  
123             try {  
124                 getPreparedStatement(sql);  
125        
126                 for (int i = 0; i < paramters.length; i++) {  
127                     preparedStatement.setObject(i + 1, paramters[i]);  
128                 }  
129                 rs = preparedStatement.executeQuery();  
130                 if (rs.next()) {  
131                     result = rs.getObject(1);  
132                 }  
133                 return result;  
134             } catch (SQLException e) {  
135                 throw new SQLException(e);  
136             } finally {  
137                 free(rs);  
138             }  
139         }  
140        
141         /** 
142          * 用于增删改 
143          *  
144          * @param sql 
145          *            sql语句 
146          * @return 影响行数 
147          * @throws SQLException 
148          */  
149         public static int update(String sql) throws SQLException {  
150        
151             try {  
152                 getPreparedStatement(sql);  
153        
154                 return preparedStatement.executeUpdate();  
155             } catch (SQLException e) {  
156                 throw new SQLException(e);  
157             } finally {  
158                 free();  
159             }  
160         }  
161        
162         /** 
163          * 用于增删改（带参数） 
164          *  
165          * @param sql 
166          *            sql语句 
167          * @param paramters 
168          *            sql语句 
169          * @return 影响行数 
170          * @throws SQLException 
171          */  
172         public static int update(String sql, Object... paramters)  
173                 throws SQLException {  
174             try {  
175                 getPreparedStatement(sql);  
176        
177                 for (int i = 0; i < paramters.length; i++) {  
178                     preparedStatement.setObject(i + 1, paramters[i]);  
179                 }  
180                 return preparedStatement.executeUpdate();  
181             } catch (SQLException e) {  
182                 throw new SQLException(e);  
183             } finally {  
184                 free();  
185             }  
186         }  
187        
188         /** 
189          * 插入值后返回主键值 
190          *  
191          * @param sql 
192          *            插入sql语句 
193          * @return 返回结果 
194          * @throws Exception 
195          */  
196         public static Object insertWithReturnPrimeKey(String sql)  
197                 throws SQLException {  
198             ResultSet rs = null;  
199             Object result = null;  
200             try {  
201                 conn = JdbcUnits.getConnection();  
202                 preparedStatement = conn.prepareStatement(sql,  
203                         PreparedStatement.RETURN_GENERATED_KEYS);  
204                 preparedStatement.execute();  
205                 rs = preparedStatement.getGeneratedKeys();  
206                 if (rs.next()) {  
207                     result = rs.getObject(1);  
208                 }  
209                 return result;  
210             } catch (SQLException e) {  
211                 throw new SQLException(e);  
212             }  
213         }  
214        
215         /** 
216          * 插入值后返回主键值 
217          *  
218          * @param sql 
219          *            插入sql语句 
220          * @param paramters 
221          *            参数列表 
222          * @return 返回结果 
223          * @throws SQLException 
224          */  
225         public static Object insertWithReturnPrimeKey(String sql,  
226                 Object... paramters) throws SQLException {  
227             ResultSet rs = null;  
228             Object result = null;  
229             try {  
230                 conn = JdbcUnits.getConnection();  
231                 preparedStatement = conn.prepareStatement(sql,  
232                         PreparedStatement.RETURN_GENERATED_KEYS);  
233                 for (int i = 0; i < paramters.length; i++) {  
234                     preparedStatement.setObject(i + 1, paramters[i]);  
235                 }  
236                 preparedStatement.execute();  
237                 rs = preparedStatement.getGeneratedKeys();  
238                 if (rs.next()) {  
239                     result = rs.getObject(1);  
240                 }  
241                 return result;  
242             } catch (SQLException e) {  
243                 throw new SQLException(e);  
244             }  
245        
246         }  
247        
248         /** 
249          * 调用存储过程执行查询 
250          *  
251          * @param procedureSql 
252          *            存储过程 
253          * @return 
254          * @throws SQLException 
255          */  
256         @SuppressWarnings("rawtypes")  
257         public static List callableQuery(String procedureSql) throws SQLException {  
258             ResultSet rs = null;  
259             try {  
260                 getCallableStatement(procedureSql);  
261                 rs = callableStatement.executeQuery();  
262                 return ResultToListMap(rs);  
263             } catch (SQLException e) {  
264                 throw new SQLException(e);  
265             } finally {  
266                 free(rs);  
267             }  
268         }  
269        
270         /** 
271          * 调用存储过程（带参数）,执行查询 
272          *  
273          * @param procedureSql 
274          *            存储过程 
275          * @param paramters 
276          *            参数表 
277          * @return 
278          * @throws SQLException 
279          */  
280         @SuppressWarnings("rawtypes")  
281         public static List callableQuery(String procedureSql, Object... paramters)  
282                 throws SQLException {  
283             ResultSet rs = null;  
284             try {  
285                 getCallableStatement(procedureSql);  
286        
287                 for (int i = 0; i < paramters.length; i++) {  
288                     callableStatement.setObject(i + 1, paramters[i]);  
289                 }  
290                 rs = callableStatement.executeQuery();  
291                 return ResultToListMap(rs);  
292             } catch (SQLException e) {  
293                 throw new SQLException(e);  
294             } finally {  
295                 free(rs);  
296             }  
297         }  
298        
299         /** 
300          * 调用存储过程，查询单个值 
301          *  
302          * @param procedureSql 
303          * @return 
304          * @throws SQLException 
305          */  
306         public static Object callableGetSingle(String procedureSql)  
307                 throws SQLException {  
308             Object result = null;  
309             ResultSet rs = null;  
310             try {  
311                 getCallableStatement(procedureSql);  
312                 rs = callableStatement.executeQuery();  
313                 while (rs.next()) {  
314                     result = rs.getObject(1);  
315                 }  
316                 return result;  
317             } catch (SQLException e) {  
318                 throw new SQLException(e);  
319             } finally {  
320                 free(rs);  
321             }  
322         }  
323        
324         /** 
325          * 调用存储过程(带参数)，查询单个值 
326          *  
327          * @param procedureSql 
328          * @param parameters 
329          * @return 
330          * @throws SQLException 
331          */  
332         public static Object callableGetSingle(String procedureSql,  
333                 Object... paramters) throws SQLException {  
334             Object result = null;  
335             ResultSet rs = null;  
336             try {  
337                 getCallableStatement(procedureSql);  
338        
339                 for (int i = 0; i < paramters.length; i++) {  
340                     callableStatement.setObject(i + 1, paramters[i]);  
341                 }  
342                 rs = callableStatement.executeQuery();  
343                 while (rs.next()) {  
344                     result = rs.getObject(1);  
345                 }  
346                 return result;  
347             } catch (SQLException e) {  
348                 throw new SQLException(e);  
349             } finally {  
350                 free(rs);  
351             }  
352         }  
353        
354         public static Object callableWithParamters(String procedureSql)  
355                 throws SQLException {  
356             try {  
357                 getCallableStatement(procedureSql);  
358                 callableStatement.registerOutParameter(0, Types.OTHER);  
359                 callableStatement.execute();  
360                 return callableStatement.getObject(0);  
361        
362             } catch (SQLException e) {  
363                 throw new SQLException(e);  
364             } finally {  
365                 free();  
366             }  
367        
368         }  
369        
370         /** 
371          * 调用存储过程，执行增删改 
372          *  
373          * @param procedureSql 
374          *            存储过程 
375          * @return 影响行数 
376          * @throws SQLException 
377          */  
378         public static int callableUpdate(String procedureSql) throws SQLException {  
379             try {  
380                 getCallableStatement(procedureSql);  
381                 return callableStatement.executeUpdate();  
382             } catch (SQLException e) {  
383                 throw new SQLException(e);  
384             } finally {  
385                 free();  
386             }  
387         }  
388        
389         /** 
390          * 调用存储过程（带参数），执行增删改 
391          *  
392          * @param procedureSql 
393          *            存储过程 
394          * @param parameters 
395          * @return 影响行数 
396          * @throws SQLException 
397          */  
398         public static int callableUpdate(String procedureSql, Object... parameters)  
399                 throws SQLException {  
400             try {  
401                 getCallableStatement(procedureSql);  
402                 for (int i = 0; i < parameters.length; i++) {  
403                     callableStatement.setObject(i + 1, parameters[i]);  
404                 }  
405                 return callableStatement.executeUpdate();  
406             } catch (SQLException e) {  
407                 throw new SQLException(e);  
408             } finally {  
409                 free();  
410             }  
411         }  
412        
413         /** 
414          * 批量更新数据 
415          *  
416          * @param sqlList 
417          *            一组sql 
418          * @return 
419          */  
420         public static int[] batchUpdate(List<String> sqlList) {  
421        
422             int[] result = new int[] {};  
423             Statement statenent = null;  
424             try {  
425                 conn = JdbcUnits.getConnection();  
426                 conn.setAutoCommit(false);  
427                 statenent = conn.createStatement();  
428                 for (String sql : sqlList) {  
429                     statenent.addBatch(sql);  
430                 }  
431                 result = statenent.executeBatch();  
432                 conn.commit();  
433             } catch (SQLException e) {  
434                 try {  
435                     conn.rollback();  
436                 } catch (SQLException e1) {  
437                     // TODO Auto-generated catch block  
438                     throw new ExceptionInInitializerError(e1);  
439                 }  
440                 throw new ExceptionInInitializerError(e);  
441             } finally {  
442                 free(statenent, null);  
443             }  
444             return result;  
445         }  
446        
447         @SuppressWarnings({ "unchecked", "rawtypes" })  
448         private static List ResultToListMap(ResultSet rs) throws SQLException {  
449             List list = new ArrayList();  
450             while (rs.next()) {  
451                 ResultSetMetaData md = rs.getMetaData();  
452                 Map map = new HashMap();  
453                 for (int i = 1; i < md.getColumnCount(); i++) {  
454                     map.put(md.getColumnLabel(i), rs.getObject(i));  
455                 }  
456                 list.add(map);  
457             }  
458             return list;  
459         }  
460        
461         /** 
462          * 获取PreparedStatement 
463          *  
464          * @param sql 
465          * @throws SQLException 
466          */  
467         private static void getPreparedStatement(String sql) throws SQLException {  
468             conn = JdbcUnits.getConnection();  
469             preparedStatement = conn.prepareStatement(sql);  
470         }  
471        
472         /** 
473          * 获取CallableStatement 
474          *  
475          * @param procedureSql 
476          * @throws SQLException 
477          */  
478         private static void getCallableStatement(String procedureSql)  
479                 throws SQLException {  
480             conn = JdbcUnits.getConnection();  
481             callableStatement = conn.prepareCall(procedureSql);  
482         }  
483        
484         /** 
485          * 释放资源 
486          *  
487          * @param rs 
488          *            结果集 
489          */  
490         public static void free(ResultSet rs) {  
491        
492             JdbcUnits.free(conn, preparedStatement, rs);  
493         }  
494        
495         /** 
496          * 释放资源 
497          *  
498          * @param statement 
499          * @param rs 
500          */  
501         public static void free(Statement statement, ResultSet rs) {  
502             JdbcUnits.free(conn, statement, rs);  
503         }  
504        
505         /** 
506          * 释放资源 
507          */  
508         public static void free() {  
509        
510             free(null);  
511         }  
512        
513     }

相关阅读:
Linux：打印（输出）所有的列（awk, $0）
Linux：批量修改分隔符（awk、BEGIN、FS、OFS、print、tr命令）
vcf格式文件转化为Excel（csv）格式文件（R语言的write.csv，write.table功能，Excel表的文件导入功能）
Linux追加文件内容并在内容前加上该文件名（awk, FILENAME功能妙用）
斯坦福大学公开课机器学习：监督学习在行人检测的应用(supervised learning for pedestrian detection)
斯坦福大学公开课机器学习：梯度下降运算的学习率a（gradient descent in practice 2：learning rate alpha）
斯坦福大学公开课机器学习：梯度下降运算的特征缩放（gradient descent in practice 1：feature scaling）
解决invalid record found in VCF4 file (at least 8 tab-delimited fields expected)问题，批量修改空格改为制表格格式
 vcf文件（call variants得来的）怎么看变异是纯合还是杂合的
 剑指offer四十八之不用加减乘除做加法
原文地址：https://www.cnblogs.com/buxingzhelyd/p/7375775.html