数据库连接池

数据库的连接对象创建工作比较消耗性能，一开始先在内存中开辟一块空间（集合），先往池子里面放置多个连接对象。后面需要连接的话，直接从池子里取，不要自行创建连接。使用完毕后归还连接，确保连接对象能循环使用。

一、自己实现一个连接池

1、连接池的创建

 1 package com.util;
 2 
 3 import java.io.PrintWriter;
 4 import java.sql.Connection;
 5 import java.sql.SQLException;
 6 import java.sql.SQLFeatureNotSupportedException;
 7 import java.util.ArrayList;
 8 import java.util.List;
 9 import java.util.logging.Logger;
10 import javax.sql.DataSource;
11 
12 public class MyDataSource implements DataSource{
13     List<Connection> list=new ArrayList<Connection>();
14     //构造器
15     public MyDataSource() {
16         for(int i=0;i<10;i++) {
17             Connection conn = jdbcUtil.getMysqlConn();
18             list.add(conn);
19         }
20         
21     }    
22     //连接池对外公布获取连接的方法
23     @Override
24     public Connection getConnection() throws SQLException {
25         // TODO Auto-generated method stub
26         if(list.size()==0) {
27             for(int i=0;i<5;i++) {
28                 Connection conn = jdbcUtil.getMysqlConn();
29                 list.add(conn);
30             }
31         }
32         Connection conn = list.remove(0);
33         return conn;
34     }    
35     public void addBack(Connection conn) {
36         list.add(conn);
37     }    
38     @Override
39     public PrintWriter getLogWriter() throws SQLException {
40         // TODO Auto-generated method stub
41         return null;
42     }
43     @Override
44     public void setLogWriter(PrintWriter out) throws SQLException {
45         // TODO Auto-generated method stub
46         
47     }
48     @Override
49     public void setLoginTimeout(int seconds) throws SQLException {
50         // TODO Auto-generated method stub
51         
52     }
53     @Override
54     public int getLoginTimeout() throws SQLException {
55         // TODO Auto-generated method stub
56         return 0;
57     }
58     @Override
59     public Logger getParentLogger() throws SQLFeatureNotSupportedException {
60         // TODO Auto-generated method stub
61         return null;
62     }
63     @Override
64     public <T> T unwrap(Class<T> iface) throws SQLException {
65         // TODO Auto-generated method stub
66         return null;
67     }
68     @Override
69     public boolean isWrapperFor(Class<?> iface) throws SQLException {
70         // TODO Auto-generated method stub
71         return false;
72     }
73     @Override
74     public Connection getConnection(String username, String password) throws SQLException {
75         // TODO Auto-generated method stub
76         return null;
77     }
78 }

View Code

2、连接池的简单使用

 1 package com.util;
 2 import java.sql.Connection;
 3 import java.sql.PreparedStatement;
 4 import java.sql.SQLException;
 5 import org.junit.Test;
 6 public class TestPool {
 7     @Test
 8     public void testPool() {
 9         Connection conn=null;
10         PreparedStatement ps =null;
11         MyDataSource dataSource=null;
12         try {
13             dataSource=new MyDataSource();
14             conn=dataSource.getConnection();
15             System.out.println(conn.isClosed());
16             String sql="insert into acc values ('lisi',200)";
17             ps = conn.prepareStatement(sql);
18             ps.execute();
19         } catch (SQLException e) {
20             // TODO Auto-generated catch block
21             e.printStackTrace();
22         }finally{
23             try {
24                 ps.close();
25             } catch (SQLException e) {
26                 // TODO Auto-generated catch block
27                 e.printStackTrace();
28             }
29             //归还连接
30             dataSource.addBack(conn);
31         }
32     }
33 }

View Code

存在的问题：

（1）需要额外记住addaBack方法；

（2）单例；

（3）无法面向接口编程。

　　接口里面没有addBack方法。

解决办法：

由于多了一个addBack()，使用这个连接池的地方，需要额外记住这个方法，并且还不能面向接口编程。我们打算修改接口中的close()方法，原来的Connection对象的close方法，是真的关闭连接。我们将其修改为归还对象。

如何扩展某一个方法？

1、直接改源码，无法实现；

2、继承，必须得知道这个接口的具体实现类。

3、使用装饰者设计模式。

4、动态代理

使用装饰者设计模式，代码如下：

 1 import java.io.PrintWriter;
 2 import java.sql.Connection;
 3 import java.sql.SQLException;
 4 import java.sql.SQLFeatureNotSupportedException;
 5 import java.util.ArrayList;
 6 import java.util.List;
 7 import java.util.logging.Logger;
 8 import javax.sql.DataSource;
 9 
10 public class MyDataSource implements DataSource{
11     List<Connection> list=new ArrayList<Connection>();
12     //构造器
13     public MyDataSource() {
14         for(int i=0;i<10;i++) {
15             Connection conn = jdbcUtil.getMysqlConn();
16             list.add(conn);
17         }
18         
19     }    
20     //连接池对外公布获取连接的方法
21     @Override
22     public Connection getConnection() throws SQLException {
23         // TODO Auto-generated method stub
24         if(list.size()==0) {
25             for(int i=0;i<5;i++) {
26                 Connection conn = jdbcUtil.getMysqlConn();
27                 list.add(conn);
28             }
29         }
30         Connection conn = list.remove(0);
31         //把对象抛出时，先对对象进行包装
32         Connection con=new ConnectionWrap(conn,list);
33         return con;
34     }    
35     public void addBack(Connection conn) {
36     
37         list.add(conn);
38     }    
39     @Override
40     public PrintWriter getLogWriter() throws SQLException {
41         // TODO Auto-generated method stub
42         return null;
43     }
44     @Override
45     public void setLogWriter(PrintWriter out) throws SQLException {
46         // TODO Auto-generated method stub
47         
48     }
49     @Override
50     public void setLoginTimeout(int seconds) throws SQLException {
51         // TODO Auto-generated method stub
52         
53     }
54     @Override
55     public int getLoginTimeout() throws SQLException {
56         // TODO Auto-generated method stub
57         return 0;
58     }
59     @Override
60     public Logger getParentLogger() throws SQLFeatureNotSupportedException {
61         // TODO Auto-generated method stub
62         return null;
63     }
64     @Override
65     public <T> T unwrap(Class<T> iface) throws SQLException {
66         // TODO Auto-generated method stub
67         return null;
68     }
69     @Override
70     public boolean isWrapperFor(Class<?> iface) throws SQLException {
71         // TODO Auto-generated method stub
72         return false;
73     }
74     @Override
75     public Connection getConnection(String username, String password) throws SQLException {
76         // TODO Auto-generated method stub
77         return null;
78     }
79 }

MyDataSource

  1 import java.sql.Array;
  2 import java.sql.Blob;
  3 import java.sql.CallableStatement;
  4 import java.sql.Clob;
  5 import java.sql.Connection;
  6 import java.sql.DatabaseMetaData;
  7 import java.sql.NClob;
  8 import java.sql.PreparedStatement;
  9 import java.sql.SQLClientInfoException;
 10 import java.sql.SQLException;
 11 import java.sql.SQLWarning;
 12 import java.sql.SQLXML;
 13 import java.sql.Savepoint;
 14 import java.sql.Statement;
 15 import java.sql.Struct;
 16 import java.util.List;
 17 import java.util.Map;
 18 import java.util.Properties;
 19 import java.util.concurrent.Executor;
 20 
 21 public class ConnectionWrap implements Connection{
 22      
 23     Connection conn=null;
 24     List<Connection> list;
 25     
 26     public ConnectionWrap(Connection conn,List<Connection> list) {
 27         super();
 28         this.conn = conn;
 29         this.list=list;
 30     }
 31 
 32     @Override
 33     public void close() throws SQLException {
 34         // TODO Auto-generated method stub
 35          //conn.close();
 36         list.add(conn);
 37         System.out.println("有人归还连接对象");
 38     }
 39     
 40     @Override
 41     public PreparedStatement prepareStatement(String sql) throws SQLException {
 42         // TODO Auto-generated method stub
 43         return conn.prepareStatement(sql);
 44     }
 45 
 46     
 47     @Override
 48     public <T> T unwrap(Class<T> iface) throws SQLException {
 49         // TODO Auto-generated method stub
 50         return null;
 51     }
 52 
 53     @Override
 54     public boolean isWrapperFor(Class<?> iface) throws SQLException {
 55         // TODO Auto-generated method stub
 56         return false;
 57     }
 58 
 59     @Override
 60     public Statement createStatement() throws SQLException {
 61         // TODO Auto-generated method stub
 62         return null;
 63     }
 64 
 65 
 66     @Override
 67     public CallableStatement prepareCall(String sql) throws SQLException {
 68         // TODO Auto-generated method stub
 69         return null;
 70     }
 71 
 72     @Override
 73     public String nativeSQL(String sql) throws SQLException {
 74         // TODO Auto-generated method stub
 75         return null;
 76     }
 77 
 78     @Override
 79     public void setAutoCommit(boolean autoCommit) throws SQLException {
 80         // TODO Auto-generated method stub
 81         
 82     }
 83 
 84     @Override
 85     public boolean getAutoCommit() throws SQLException {
 86         // TODO Auto-generated method stub
 87         return false;
 88     }
 89 
 90     @Override
 91     public void commit() throws SQLException {
 92         // TODO Auto-generated method stub
 93         
 94     }
 95 
 96     @Override
 97     public void rollback() throws SQLException {
 98         // TODO Auto-generated method stub
 99         
100     }
101 
102     
103 
104     @Override
105     public boolean isClosed() throws SQLException {
106         // TODO Auto-generated method stub
107         return false;
108     }
109 
110     @Override
111     public DatabaseMetaData getMetaData() throws SQLException {
112         // TODO Auto-generated method stub
113         return null;
114     }
115 
116     @Override
117     public void setReadOnly(boolean readOnly) throws SQLException {
118         // TODO Auto-generated method stub
119         
120     }
121 
122     @Override
123     public boolean isReadOnly() throws SQLException {
124         // TODO Auto-generated method stub
125         return false;
126     }
127 
128     @Override
129     public void setCatalog(String catalog) throws SQLException {
130         // TODO Auto-generated method stub
131         
132     }
133 
134     @Override
135     public String getCatalog() throws SQLException {
136         // TODO Auto-generated method stub
137         return null;
138     }
139 
140     @Override
141     public void setTransactionIsolation(int level) throws SQLException {
142         // TODO Auto-generated method stub
143         
144     }
145 
146     @Override
147     public int getTransactionIsolation() throws SQLException {
148         // TODO Auto-generated method stub
149         return 0;
150     }
151 
152     @Override
153     public SQLWarning getWarnings() throws SQLException {
154         // TODO Auto-generated method stub
155         return null;
156     }
157 
158     @Override
159     public void clearWarnings() throws SQLException {
160         // TODO Auto-generated method stub
161         
162     }
163 
164     @Override
165     public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
166         // TODO Auto-generated method stub
167         return null;
168     }
169 
170     @Override
171     public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
172             throws SQLException {
173         // TODO Auto-generated method stub
174         return null;
175     }
176 
177     @Override
178     public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
179         // TODO Auto-generated method stub
180         return null;
181     }
182 
183     @Override
184     public Map<String, Class<?>> getTypeMap() throws SQLException {
185         // TODO Auto-generated method stub
186         return null;
187     }
188 
189     @Override
190     public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
191         // TODO Auto-generated method stub
192         
193     }
194 
195     @Override
196     public void setHoldability(int holdability) throws SQLException {
197         // TODO Auto-generated method stub
198         
199     }
200 
201     @Override
202     public int getHoldability() throws SQLException {
203         // TODO Auto-generated method stub
204         return 0;
205     }
206 
207     @Override
208     public Savepoint setSavepoint() throws SQLException {
209         // TODO Auto-generated method stub
210         return null;
211     }
212 
213     @Override
214     public Savepoint setSavepoint(String name) throws SQLException {
215         // TODO Auto-generated method stub
216         return null;
217     }
218 
219     @Override
220     public void rollback(Savepoint savepoint) throws SQLException {
221         // TODO Auto-generated method stub
222         
223     }
224 
225     @Override
226     public void releaseSavepoint(Savepoint savepoint) throws SQLException {
227         // TODO Auto-generated method stub
228         
229     }
230 
231     @Override
232     public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
233             throws SQLException {
234         // TODO Auto-generated method stub
235         return null;
236     }
237 
238     @Override
239     public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
240             int resultSetHoldability) throws SQLException {
241         // TODO Auto-generated method stub
242         return null;
243     }
244 
245     @Override
246     public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
247             int resultSetHoldability) throws SQLException {
248         // TODO Auto-generated method stub
249         return null;
250     }
251 
252     @Override
253     public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
254         // TODO Auto-generated method stub
255         return null;
256     }
257 
258     @Override
259     public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
260         // TODO Auto-generated method stub
261         return null;
262     }
263 
264     @Override
265     public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
266         // TODO Auto-generated method stub
267         return null;
268     }
269 
270     @Override
271     public Clob createClob() throws SQLException {
272         // TODO Auto-generated method stub
273         return null;
274     }
275 
276     @Override
277     public Blob createBlob() throws SQLException {
278         // TODO Auto-generated method stub
279         return null;
280     }
281 
282     @Override
283     public NClob createNClob() throws SQLException {
284         // TODO Auto-generated method stub
285         return null;
286     }
287 
288     @Override
289     public SQLXML createSQLXML() throws SQLException {
290         // TODO Auto-generated method stub
291         return null;
292     }
293 
294     @Override
295     public boolean isValid(int timeout) throws SQLException {
296         // TODO Auto-generated method stub
297         return false;
298     }
299 
300     @Override
301     public void setClientInfo(String name, String value) throws SQLClientInfoException {
302         // TODO Auto-generated method stub
303         
304     }
305 
306     @Override
307     public void setClientInfo(Properties properties) throws SQLClientInfoException {
308         // TODO Auto-generated method stub
309         
310     }
311 
312     @Override
313     public String getClientInfo(String name) throws SQLException {
314         // TODO Auto-generated method stub
315         return null;
316     }
317 
318     @Override
319     public Properties getClientInfo() throws SQLException {
320         // TODO Auto-generated method stub
321         return null;
322     }
323 
324     @Override
325     public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
326         // TODO Auto-generated method stub
327         return null;
328     }
329 
330     @Override
331     public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
332         // TODO Auto-generated method stub
333         return null;
334     }
335 
336     @Override
337     public void setSchema(String schema) throws SQLException {
338         // TODO Auto-generated method stub
339         
340     }
341 
342     @Override
343     public String getSchema() throws SQLException {
344         // TODO Auto-generated method stub
345         return null;
346     }
347 
348     @Override
349     public void abort(Executor executor) throws SQLException {
350         // TODO Auto-generated method stub
351         
352     }
353 
354     @Override
355     public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
356         // TODO Auto-generated method stub
357         
358     }
359 
360     @Override
361     public int getNetworkTimeout() throws SQLException {
362         // TODO Auto-generated method stub
363         return 0;
364     }
365     
366 }

ConnectionWrap

 1 package com.util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 
 9 public class jdbcUtil {
10     /**获得连接
11      * @return
12      */
13     public static Connection getMysqlConn() {    
14         try {
15             Class.forName("com.mysql.jdbc.Driver");
16             return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test","root","112209");
17         } catch (Exception e) {
18             // TODO Auto-generated catch block
19             e.printStackTrace();
20             return null;
21         }
22     }
23     
24     public static void close(PreparedStatement ps,Connection conn) {
25         if(ps!=null) {
26             try {
27                 ps.close();
28             } catch (SQLException e) {
29                 // TODO Auto-generated catch block
30                 e.printStackTrace();
31             }
32         }
33         if(conn!=null) {
34             try {
35                 conn.close();
36             } catch (SQLException e) {
37                 // TODO Auto-generated catch block
38                 e.printStackTrace();
39             }
40         }
41     }
42     
43     public static boolean close(ResultSet rs,PreparedStatement ps,Connection conn) {
44         if(rs!=null) {
45             try {
46                 rs.close();
47                 return true;
48             } catch (SQLException e) {
49                 // TODO Auto-generated catch block
50                 e.printStackTrace();
51             }
52         }
53         if(ps!=null) {
54             try {
55                 ps.close();
56                 return true;
57             } catch (SQLException e) {
58                 // TODO Auto-generated catch block
59                 e.printStackTrace();
60             }
61         }
62         if(conn!=null) {
63             try {
64                 conn.close();
65                 return true;
66             } catch (SQLException e) {
67                 // TODO Auto-generated catch block
68                 e.printStackTrace();
69             }
70         }
71         return false;
72     }
73     
74     public static void close(Connection conn) {
75         if(conn!=null) {
76             try {
77                 conn.close();
78             } catch (SQLException e) {
79                 // TODO Auto-generated catch block
80                 e.printStackTrace();
81             }
82         }
83     }
84     
85     
86     
87     
88 }

jdbcUtil

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
public class TestPool {
    @Test
    public void testPool() {
        Connection conn=null;
        PreparedStatement ps =null;
        MyDataSource dataSource=null;
        try {
            dataSource=new MyDataSource();
            conn=dataSource.getConnection();
            String sql="insert into acc values ('liuping',-200)";
            ps = conn.prepareStatement(sql);
            ps.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            //归还连接
            //dataSource.addBack(conn);
            jdbcUtil.close(ps, conn);
        }
    }
}

TestPool

二、开源的连接池

DBCP(DataBase Connection Pool) 数据库连接池，是java数据库连接池的一种，由Apache开发，通过数据库连接池，可以让程序自动管理数据库连接的释放和断开。

DBCP的使用：

1、导入jar包

commons-dbcp-1.4.jar

commons-pool-1.5.6.jar

2、代码连接

 1 import java.sql.Connection;
 2 import java.sql.PreparedStatement;
 3 import java.sql.SQLException;
 4 
 5 import org.apache.commons.dbcp.BasicDataSource;
 6 import org.junit.Test;
 7 
 8 import com.util.jdbcUtil;
 9 
10 public class DBCPdemo {
11     @Test
12     public void testDPCB01() {
13         Connection conn=null;
14         PreparedStatement ps=null;
15         try {
16             //构建数据源对象
17             BasicDataSource dataSource=new BasicDataSource();
18             //指定访问的数据库
19             dataSource.setDriverClassName("com.mysql.jdbc.Driver");
20             dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
21             dataSource.setUsername("root");
22             dataSource.setPassword("112209");
23             //得到连接对象
24             conn = dataSource.getConnection();
25         
26             String sql="insert into acc values(?,?)";
27             ps = conn.prepareStatement(sql);
28             ps.setString(1,"lpsb");
29             ps.setInt(2, -10000);
30             ps.executeUpdate();
31             
32         } catch (SQLException e) {
33             // TODO Auto-generated catch block
34             e.printStackTrace();
35         }finally {
36             jdbcUtil.close(ps, conn);
37         }
38     }
39 }

DBCPdemo

3、使用属性配置文件

 1 import java.sql.Connection;
 2 import java.sql.PreparedStatement;
 3 import java.sql.SQLException;
 4 
 5 import org.apache.commons.dbcp.BasicDataSource;
 6 import org.junit.Test;
 7 
 8 import com.util.jdbcUtil;
 9 
10 public class DBCPdemo2 {
11     @Test
12     public void testDPCB01() {
13         BasicDataSource dataSource=new BasicDataSource();
14         dataSource.setConnectionProperties("jdbc.properties");
15         Connection conn=null;
16         PreparedStatement ps=null;
17         try {
18             //构建数据源对象
19             
20             //指定访问的数据库
21             dataSource.setDriverClassName("com.mysql.jdbc.Driver");
22             dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
23             dataSource.setUsername("root");
24             dataSource.setPassword("112209");
25             //得到连接对象
26             conn = dataSource.getConnection();
27         
28             String sql="insert into acc values(?,?)";
29             ps = conn.prepareStatement(sql);
30             ps.setString(1,"lpsb");
31             ps.setInt(2, -200000);
32             ps.executeUpdate();
33             
34         } catch (SQLException e) {
35             // TODO Auto-generated catch block
36             e.printStackTrace();
37         }finally {
38             jdbcUtil.close(ps, conn);
39         }
40     }
41 }

View Code

C3P0的使用

1、导入jar包，c3p0-0.9.1.2.jar

2、使用代码连接

 1 package com.njust.connectionpool.cn;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 
 6 import org.junit.Test;
 7 
 8 import com.mchange.v2.c3p0.ComboPooledDataSource;
 9 import com.util.jdbcUtil;
10 
11 public class C3P0Demo {
12     @Test
13     public void testC3P0() {
14         Connection conn=null;
15         PreparedStatement ps=null;
16         try {
17             //构建数据源对象
18             ComboPooledDataSource dataSource=new ComboPooledDataSource();
19             //指定访问的数据库
20             dataSource.setDriverClass("com.mysql.jdbc.Driver");
21             dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test");
22             dataSource.setUser("root");
23             dataSource.setPassword("112209");
24             //得到连接对象
25             conn = dataSource.getConnection();
26         
27             String sql="insert into acc values(?,?)";
28             ps = conn.prepareStatement(sql);
29             ps.setString(1,"lpsb22");
30             ps.setInt(2, -900000);
31             ps.executeUpdate();
32             
33         } catch (Exception e) {
34             // TODO Auto-generated catch block
35             e.printStackTrace();
36         }finally {
37             jdbcUtil.close(ps, conn);
38         }
39 
40     }
41 }

View Code

3、使用配置文件（c3p0-config.xml）

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <c3p0-config>
 3 
 4     <!-- default-config 默认的配置，  -->
 5   <default-config>
 6     <property name="driverClass">com.mysql.jdbc.Driver</property>
 7     <property name="jdbcUrl">jdbc:mysql://localhost/acc</property>
 8     <property name="user">root</property>
 9     <property name="password">112209</property>
10     
11     
12     <property name="initialPoolSize">10</property>
13     <property name="maxIdleTime">30</property>
14     <property name="maxPoolSize">100</property>
15     <property name="minPoolSize">10</property>
16     <property name="maxStatements">200</property>
17   </default-config>
18   
19    <!-- This app is massive! -->
20   <named-config name="oracle"> 
21     <property name="acquireIncrement">50</property>
22     <property name="initialPoolSize">100</property>
23     <property name="minPoolSize">50</property>
24     <property name="maxPoolSize">1000</property>
25 
26     <!-- intergalactoApp adopts a different approach to configuring statement caching -->
27     <property name="maxStatements">0</property> 
28     <property name="maxStatementsPerConnection">5</property>
29 
30     <!-- he's important, but there's only one of him -->
31     <user-overrides user="master-of-the-universe"> 
32       <property name="acquireIncrement">1</property>
33       <property name="initialPoolSize">1</property>
34       <property name="minPoolSize">1</property>
35       <property name="maxPoolSize">5</property>
36       <property name="maxStatementsPerConnection">50</property>
37     </user-overrides>
38   </named-config>
39 
40  
41 </c3p0-config>
42

c3p0-config.xml

 1 package com.njust.connectionpool.cn;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 
 6 import org.junit.Test;
 7 
 8 import com.mchange.v2.c3p0.ComboPooledDataSource;
 9 import com.util.jdbcUtil;
10 
11 public class C3P0Demo2 {
12     @Test
13     public void testC3P0() {
14         Connection conn=null;
15         PreparedStatement ps=null;
16         try {
17             //构建数据源对象
18             ComboPooledDataSource dataSource=new ComboPooledDataSource();
19             //得到连接对象
20             conn = dataSource.getConnection();
21         
22             String sql="insert into acc values(?,?)";
23             ps = conn.prepareStatement(sql);
24             ps.setString(1,"lpsb22");
25             ps.setInt(2, 2333);
26             ps.executeUpdate();
27             
28         } catch (Exception e) {
29             // TODO Auto-generated catch block
30             e.printStackTrace();
31         }finally {
32             jdbcUtil.close(ps, conn);
33         }
34 
35     }
36 }

C3P0Demo2

注意点：配置文件必须命名为c3p0-config.xml。

三、DBUtils使用

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。

导入jar包 commons-dbutils-1.4.jar

 1 package com.util;
 2 
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.util.List;
 6 
 7 import org.apache.commons.dbutils.QueryRunner;
 8 import org.apache.commons.dbutils.ResultSetHandler;
 9 import org.apache.commons.dbutils.handlers.BeanHandler;
10 import org.apache.commons.dbutils.handlers.BeanListHandler;
11 import org.junit.Test;
12 import com.mchange.v2.c3p0.ComboPooledDataSource;
13 import com.njust.bean.Acc;
14 
15 public class TestDBUtils {
16     @Test
17     public void testInsert() throws SQLException {
18     
19         //DBUtils简化了CRUD的代码，但是连接的创建以及获取工作。不在考虑范围
20         QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
21         //增加
22         QueryRunner acc = queryRunner;
23         acc.update("insert into acc values(?,?)","lpsb3",-800);
24         //删除
25         acc.update("delete from acc where name=?","lpsb");
26         //修改
27         acc.update("update acc set money=? where name=?",-800000,"lpsb3");
28         
29         //查询
30         //1、单条记录,查询到的数据在result里面，然后调用handle方法，由用户手动封装
31         Acc account=queryRunner.query("select * from acc where name=?",new ResultSetHandler<Acc>() {
32             @Override
33             public Acc handle(ResultSet rs) throws SQLException {
34                 // TODO Auto-generated method stub
35                 Acc acc=new Acc();
36                 while(rs.next()) {
37                     String name=rs.getString("name");
38                     int money = rs.getInt("money");
39                     acc.setName(name);
40                     acc.setMoney(money);
41                 }
42                 return acc;
43             }    
44         },"erha");
45         System.out.println(account.toString());
46         //2、直接使用框架已写好的实现类
47         //查询单个对象,通过类的字节码得到该类的实例
48         Acc que=queryRunner.query("select * from acc where name=?",new BeanHandler<Acc>(Acc.class),"lisi");
49         System.out.println(que.toString());
50         //查询多个对象
51         List<Acc> list=queryRunner.query("select * from acc",new BeanListHandler<Acc>(Acc.class));
52         for (Acc acc2 : list) {
53             System.out.println(acc2.toString());
54         }
55     }
56 }

TestDBUtils

ResultSetHandler 常用的实现类

ArrayHandler：把结果集中的第一行数据转成对象数组。

ArrayListHandler：把结果集中的每一行数据都转成一个对象数组，再存放到List中。

BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。

BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。

MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。

MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

ColumnListHandler：将结果集中某一列的数据存放到List中。

KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里(List)，再把这些map再存到一个map里，其key为指定的列。

ScalarHandler:将结果集第一行的某一列放到某个对象中。