模仿Common DBUtils的功能实现自定义的DBUtils

package com.yxfyg.util; 2 3 import java.sql.Connection; 4 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 import com.mchange.v2.c3p0.ComboPooledDataSource; 9 10 public class JDBCUtil { 11 12 private static ComboPooledDataSource dataSource = null; 13 14 static { 15 dataSource = new ComboPooledDataSource(); 16 } 17 18 public static Connection getConnection() { 19 Connection conn = null; 20 try { 21 conn = dataSource.getConnection(); 22 } catch (SQLException e) { 23 e.printStackTrace(); 24 } 25 return conn; 26 } 27 28 public static ComboPooledDataSource getDataSource() { 29 return dataSource; 30 } 31 32 public static void release(ResultSet rs,Statement st,Connection conn) { 33 closeRs(rs); 34 closeSt(st); 35 closeConn(conn); 36 } 37 38 public static void release(Statement st,Connection conn) { 39 closeSt(st); 40 closeConn(conn); 41 } 42 43 private static void closeRs(ResultSet rs) { 44 try { 45 if(rs != null) { 46 rs.close(); 47 } 48 }catch(SQLException e) { 49 e.printStackTrace(); 50 }finally { 51 rs = null; 52 } 53 } 54 55 private static void closeSt(Statement st) { 56 try { 57 if(st != null) { 58 st.close(); 59 } 60 }catch(SQLException e) { 61 e.printStackTrace(); 62 }finally { 63 st = null; 64 } 65 } 66 67 private static void closeConn(Connection conn) { 68 try { 69 if(conn != null) { 70 conn.close(); 71 } 72 }catch(SQLException e) { 73 e.printStackTrace(); 74 }finally { 75 conn = null; 76 } 77 } 78 }

 1 package com.yxfyg.bean;
 2 
 3 public class Account {
 4     
 5     private int id;
 6     
 7     private String name;
 8     
 9     private int money;
10 
11     public int getId() {
12         return id;
13     }
14 
15     public void setId(int id) {
16         this.id = id;
17     }
18 
19     public String getName() {
20         return name;
21     }
22 
23     public void setName(String name) {
24         this.name = name;
25     }
26 
27     public int getMoney() {
28         return money;
29     }
30 
31     public void setMoney(int money) {
32         this.money = money;
33     }
34 
35     @Override
36     public String toString() {
37         return "Account [id=" + id + ", name=" + name + ", money=" + money + "]";
38     }
39         
40 }

③自定义的DBUtils和需要使用者进行实现的接口

 1 package com.yxfyg.dbutils;
 2 
 3 import java.sql.Connection;
 4 import java.sql.ParameterMetaData;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 
 9 import com.yxfyg.util.JDBCUtil;
10 
11 public class MyDBUtils {
12 
13     // 增、删、改
14     public int update(String sql, Object... args) {
15         // 获取连接
16         Connection conn = JDBCUtil.getConnection();
17         PreparedStatement ps = null;
18         int update = 0;
19         try {
20             ps = conn.prepareStatement(sql);
21             // 确定sql语句中问号的个数
22             ParameterMetaData data = ps.getParameterMetaData();
23             int count = data.getParameterCount();
24             for (int i = 0; i < count; i++) {
25                 ps.setObject(i + 1, args[i]);
26             }
27             update = ps.executeUpdate();
28         } catch (SQLException e) {
29             e.printStackTrace();
30         } finally {
31             JDBCUtil.release(ps, conn);
32         }
33         return update;
34     }
35 
36     public <T> T query(String sql, ResultSetHandler<T> handler, Object... args) {
37 
38         // 获取连接
39         Connection conn = JDBCUtil.getConnection();
40         PreparedStatement ps = null;
41         ResultSet rs = null;
42         T t = null;
43         try {
44             ps = conn.prepareStatement(sql);
45             // 确定sql语句中问号的个数
46             ParameterMetaData data = ps.getParameterMetaData();
47             int count = data.getParameterCount();
48             for (int i = 0; i < count; i++) {
49                 ps.setObject(i + 1, args[i]);
50             }
51             rs = ps.executeQuery();
52             
53             //将结果集交给由使用者实现的类ResultSetHandler的对象处理
54             t = handler.handle(rs);
55             
56         } catch (SQLException e) {
57             e.printStackTrace();
58         } finally {
59             JDBCUtil.release(rs , ps, conn);
60         }
61         return t;
62     }
63 
64 }

1 package com.yxfyg.dbutils;
2 
3 import java.sql.ResultSet;
4 
5 public interface ResultSetHandler<T> {
6     
7     T handle(ResultSet rs);
8     
9 }

④使用

 1 package com.yxfyg.test;
 2 
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.util.List;
 6 
 7 import org.junit.Test;
 8 
 9 import com.yxfyg.bean.Account;
10 import com.yxfyg.dbutils.MyDBUtils;
11 import com.yxfyg.dbutils.ResultSetHandler;
12 
13 public class MainTest {
14 
15     @Test
16     public void testUpdate() {
17 
18         MyDBUtils myUtils = new MyDBUtils();
19 
20         // 增、删、改都使用update方法，查使用query方法
21         // 插入一条数据
22         myUtils.update("insert into account values(null,?,?)","Lucy",2000);
23 
24         // 修改一条数据
25         myUtils.update("update account set money = money - ? where name = ?", 300, "Lucy");
26 
27         // 删除一条数据
28         myUtils.update("delete from account where name = ?","Lucy");
29 
30     }
31 
32     @Test
33     public void testQuery() {
34         
35         MyDBUtils myUtils = new MyDBUtils();
36         
37         // 查询一条数据(使用匿名内部类，自己实现方法)
38         Account account = myUtils.query("select * from account where name = ?", new ResultSetHandler<Account>() {
39 
40             @Override
41             public Account handle(ResultSet rs) {
42                 Account account = new Account();
43                 try {
44                     while (rs.next()) {
45                         account.setId(rs.getInt("id"));
46                         account.setName(rs.getString("name"));
47                         account.setMoney(rs.getInt("money"));
48                     }
49                 } catch (SQLException e) {
50                     e.printStackTrace();
51                 }
52                 return account;
53             }
54         }, "Jack");
55 
56         System.out.println(account.toString());
57     }
58 
59 }

 

模仿Common DBUtils的功能实现自定义的DBUtils

标签：set   color   count   个数   date   rac   one   final   common