自定义DbUtils通用类

本实例使用C3P0连接池做连接，详见https://www.cnblogs.com/qf123/p/10097662.html开源连接池C3P0的使用

DBUtils.java

 1 package com.qf.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.ResultSet;
 5 import java.sql.SQLException;
 6 
 7 import com.mchange.v2.c3p0.ComboPooledDataSource;
 8 
 9 import java.sql.Statement;
10 
11 public class DBUtils {
12 　　 
13     static ComboPooledDataSource ds ;
14     static {
15         ds = new ComboPooledDataSource();//创建c3p0连接池数据源
16     }
17     
18     public static Connection getConn() {
19         Connection conn = null;
20         try {
21             conn = ds.getConnection();//从连接池获取数据库连接
22         } catch (SQLException e) {
23             e.printStackTrace();
24         }
25         return conn;
26     }
27 
28     /*
29      * 释放资源
30      */
31     public static void releaseResource(ResultSet rs,Statement statement,Connection conn) {
32         closeConnect(conn);
33         closeResultSet(rs);
34         closeStatement(statement);
35     }
36     public static void releaseResource(Statement statement,Connection conn) {
37         closeConnect(conn);
38         closeStatement(statement);
39     }
40     private static void closeResultSet(ResultSet rs) {
41         try {
42             if(rs != null) {
43                 rs.close();
44             }
45         } catch (SQLException e) {
46             e.printStackTrace();
47         }
48     }
49     private static void closeStatement(Statement statement) {
50         try {
51             if(statement != null) {
52                 statement.close();
53             }
54         } catch (SQLException e) {
55             e.printStackTrace();
56         }
57     }
58     private static void closeConnect(Connection conn) {
59         try {
60             if(conn != null) {
61                 conn.close();
62             }
63         } catch (SQLException e) {
64             e.printStackTrace();
65         }
66     }
67 }

ResultSetHandler接口，用于定义处理结果集的方法

package com.qf.util.dao;

import java.sql.ResultSet;

public interface ResultSetHandler<T> {

	T handle(ResultSet rs);
}

自定义通用DbUtils类MyDbUtils.java

query方法参数ResultSetHandler就是为了让用户根据实际情况自己定义结果集处理的方法
使用T泛型，灵活返回具体对象

  1 package com.qf.util;
  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 import java.util.ArrayList;
  9 import java.util.Date;
 10 import java.util.List;
 11 
 12 import org.junit.Test;
 13 
 14 import com.qf.pojo.Person;
 15 import com.qf.util.dao.ResultSetHandler;
 16 
 17 public class MyDbUtils {
 18     @Test
 19     public void test1() {
 20         Person person = query("select * from person where id=?", new ResultSetHandler<Person>() {
 21 
 22             @Override
 23             public Person handle(ResultSet rs) {
 24                 try {
 25                     if(rs.next()) {
 26                         String address = rs.getString("address");
 27                         Date time = rs.getDate("time");
 28                         int age = rs.getInt("age");
 29                         String name = rs.getString("name");
 30                         Person person = new Person(name , age, time, address);
 31                         return person;
 32                     }
 33                 } catch (SQLException e) {
 34                     e.printStackTrace();
 35                 }
 36                 return null;
 37             }
 38         }, 1);
 39         System.out.println(person);
 40         /**控制台输出结果：
 41          * Person [name=smile, age=12, time=2018-03-06, address=null]
 42          */
 43     }
 44     
 45     @Test
 46     public void test2() {
 47         List<Person> list = query("select * from person", new ResultSetHandler<List<Person>>() {
 48             @Override
 49             public List<Person> handle(ResultSet rs) {
 50                 try {
 51                     List<Person> list = new ArrayList<Person>();
 52                     while(rs.next()) {
 53                         String address = rs.getString("address");
 54                         Date time = rs.getDate("time");
 55                         int age = rs.getInt("age");
 56                         String name = rs.getString("name");
 57                         Person person = new Person(name , age, time, address);
 58                         list.add(person);
 59                     }
 60                     return list;
 61                 } catch (SQLException e) {
 62                     e.printStackTrace();
 63                 }
 64                 return null;
 65             }
 66         });
 67         for (Person person : list) {
 68             System.out.println(person);
 69         }
 70         
 71         /**控制台输出结果：
 72          * Person [name=smile, age=12, time=2018-03-06, address=null]
 73          * Person [name=wxf, age=13, time=2018-03-07, address=null]
 74          * Person [name=smile, age=24, time=1970-01-01, address=null]
 75          */
 76     }
 77     
 78     /**
 79      * 查询
 80      * @param sql
 81      * @param handler    用于处理结果集rs
 82      * @param args    sql中？对应的参数值
 83      * @return
 84      */
 85     public <T> T query(String sql,ResultSetHandler<T> handler, Object ...args){
 86         ResultSet rs = null;
 87         PreparedStatement ps = null;
 88         Connection conn = null;
 89         try {
 90             conn = DBUtils.getConn();
 91             
 92             ps = conn.prepareStatement(sql);
 93             ParameterMetaData metaData = ps.getParameterMetaData();
 94             int count = metaData.getParameterCount();
 95             for (int i = 0; i < count; i++) {
 96                 ps.setObject(i+1, args[i]);
 97             }
 98             rs = ps.executeQuery();
 99             T t = handler.handle(rs);
100             return t;
101         } catch (SQLException e) {
102             e.printStackTrace();
103         } finally {
104             DBUtils.releaseResource(ps, conn);
105         }
106         return null;
107     }
108     
109     /**
110      * 增删改
111      * @param sql
112      * @param args    sql中？对应的参数值
113      */
114     public void update(String sql,Object ...args) {
115         PreparedStatement ps = null;
116         Connection conn = null;
117         try {
118             conn = DBUtils.getConn();
119             ps = conn.prepareStatement(sql);
120             ParameterMetaData metaData = ps.getParameterMetaData();
121             int count = metaData.getParameterCount();
122             for (int i = 0; i < count; i++) {
123                 ps.setObject(i+1, args[i]);
124             }
125             ps.executeUpdate();
126         } catch (SQLException e) {
127             e.printStackTrace();
128         } finally {
129             DBUtils.releaseResource(ps, conn);
130         }
131     }
132     
133 }