JDBC连接池-自定义连接池

JDBC连接池

java JDBC连接中用到Connection 在每次对数据进行增删查改都要开启、关闭，在实例开发项目中，浪费了很大的资源，以下是之前连接JDBC的案例

package com.jdbc.connection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class jdbcConnection {
    
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    /**
     * 静态代码块加载配置文件信息
     */
    static {
        try {
            // 1.通过当前类获取类加载器
            ClassLoader classLoader = jdbcConnection.class.getClassLoader();
            // 2.通过类加载器的方法获得一个输入流
            InputStream is = classLoader.getResourceAsStream("db.properties");
            // 3.创建一个properties对象
            Properties props = new Properties();
            // 4.加载输入流
            props.load(is);
            // 5.获取相关参数的值
            driver = props.getProperty("driver");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 获取连接方法
     * 
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 释放资源方法
     * 
     * @param conn
     * @param pstmt
     * @param rs
     */
    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

考虑节省资源，可以创建一个connection连接池，每次使用connection连接时，直接从连接池中取出一个连接，不用时再放回连接池，代替之前的关闭连接。 java提供了javax.sql.DataSource 接口，各大连接池厂商直接实现这个接口

常见的连接池有C3P0 DBCP连接池

自定义连接池

在上面代码的基础上添加一个自定义连接池，减少对资源的浪费

1.创建一个类实现javax.jdbc.dataSource 类

2.创建一个存放多个连接的容器，因为需要经常删除，查找。这里用LinkedList 容器

3.在MyDataSource类中静态代码块中创建多个连接，添加到连接池容器中 (这里面创建连接可以直接使用 com.jdbc.connection包中的jdbcConnection 中的静态方法getConnection)

1     static{
2         for (int i = 0; i < 5; i++) {
3             Connection conn=jdbcConnection.getConnection();
4             
5         }
6     }

4.重写Datasource 方法中的 getConnection() 方法，

注：在使用pool这个变量之前需要判断pool是否为空，若为空需要重新创建

4.在MyDataSource方法中添加一个把当前连接归还给连接池的方法

public void addBack(Connection conn){
        pool.add(conn);
    }

具体操作代码如下

package com.jdbc.dataSource;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import com.jdbc.connection.jdbcConnection;

public class MyDataSource implements DataSource {
    private static LinkedList<Connection> pool=new LinkedList<Connection>();
    static{
        for (int i = 0; i < 5; i++) {
            Connection conn=jdbcConnection.getConnection();
            pool.add(conn);
        }
    }
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn=null;
        if (pool.size()==0) {
            for (int i = 0; i < 5; i++) {
                conn=jdbcConnection.getConnection();
                pool.add(conn);
            }
        }
        conn=pool.remove(0);
        return conn;
    }
    
    public void addBack(Connection conn){
        pool.add(conn);
    }
    @Override
    public PrintWriter getLogWriter() throws SQLException {
        
        
        return null;
    }

    @Override
    public int getLoginTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter arg0) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public void setLoginTimeout(int arg0) throws SQLException {
        // TODO Auto-generated method stub

    }

    @Override
    public boolean isWrapperFor(Class<?> arg0) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public <T> T unwrap(Class<T> arg0) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }



    @Override
    public Connection getConnection(String arg0, String arg1)
            throws SQLException {
        
        return null;
    }

}

连接池测试类

 1 package com.jdbc.Util;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.SQLException;
 6 
 7 import org.junit.Test;
 8 
 9 import com.jdbc.dataSource.MyDataSource;
10 
11 public class JdbcTest {
12 
13     @Test
14     public void test1(){
15         Connection conn=null;
16         PreparedStatement pstm=null;
17         MyDataSource  myDataSource=new MyDataSource();
18         try {
19             conn=myDataSource.getConnection();
20             
21         } catch (SQLException e) {
22             e.printStackTrace();
23         }
24         
25     }
26 }

自定义连接池：方法增强

装饰者模式

创建类B，并实现接口A
提供类B的构造方法，参数类型为A，用于接收A接口的其他实现类（c）
给类B添加类型为A的成员变量，用于存放A接口的其他实现类
增加需要的方法
实现不需要增强的方法，方法体重点调用成员变量存放的其他实现类对应的方法

翻译过来就是创建类 MyConnection 实现Connection接口

提供MyConnection的构造方法，参数类型为Connection的、 MyDataSource 类中的连接池对象list

。。。等

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

更改后的myDataSource类为

 1 package com.jdbc.dataSource;
 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.LinkedList;
 8 import java.util.logging.Logger;
 9 
10 import javax.sql.DataSource;
11 
12 import com.jdbc.connection.jdbcConnection;
13 
14 public class MyDataSource1  implements DataSource{
15     private static LinkedList<Connection> pool=new LinkedList<Connection>();
16     static{
17         for (int i = 0; i < 5; i++) {
18             Connection conn=jdbcConnection.getConnection();
19             pool.add(conn);
20         }
21     }
22     @Override
23     public Connection getConnection() throws SQLException {
24         if (pool.size()==0) {
25             for (int i = 0; i < 5; i++) {
26                 Connection conn=jdbcConnection.getConnection();
27                 //MyConnection 类实现了Connection接口  
28                 //将创建的连接用装饰者类包装
29                 MyConnection myConnection=new MyConnection(conn, pool);
30                 //pool.add(conn);
31                 pool.add(myConnection);//添加被装饰后的connection对象
32             }
33         }
34       Connection conn=pool.remove(0);
35         return conn;
36     }
37     
38     @Override
39     public PrintWriter getLogWriter() throws SQLException {
40         
41         return null;
42     }
43 
44     @Override
45     public int getLoginTimeout() throws SQLException {
46         // TODO Auto-generated method stub
47         return 0;
48     }
49 
50     @Override
51     public Logger getParentLogger() throws SQLFeatureNotSupportedException {
52         // TODO Auto-generated method stub
53         return null;
54     }
55 
56     @Override
57     public void setLogWriter(PrintWriter arg0) throws SQLException {
58         // TODO Auto-generated method stub
59         
60     }
61 
62     @Override
63     public void setLoginTimeout(int arg0) throws SQLException {
64         // TODO Auto-generated method stub
65         
66     }
67 
68     @Override
69     public boolean isWrapperFor(Class<?> iface) throws SQLException {
70         // TODO Auto-generated method stub
71         return false;
72     }
73 
74     @Override
75     public <T> T unwrap(Class<T> iface) throws SQLException {
76         // TODO Auto-generated method stub
77         return null;
78     }
79 
80     
81 
82     @Override
83     public Connection getConnection(String username, String password)
84             throws SQLException {
85         // TODO Auto-generated method stub
86         return null;
87     }
88 
89     
90 
91 }

测试

package com.study.jdbc.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.junit.Test;

import com.study.jdbc.Utils.JDBCUtils_V3;
import com.study.jdbc.dataSource.MyDataSource;
import com.study.jdbc.dataSource.MyDataSource1;

public class TestMyDataSource {
    
    
    /*
     * 添加用户
     * 使用改造过的connection 
     * 
     */
    @Test
    public void dataTestAddUser1(){
        Connection conn=null;
        PreparedStatement pstm=null;
        MyDataSource1 dataSource=new MyDataSource1();
            try {
                conn=dataSource.getConnection();
                System.out.println(conn);
                String sql="insert into user values(10,?,?)";
                pstm=conn.prepareStatement(sql);
                pstm.setString(1,"吕布1");
                pstm.setString(2,"点错1");
                int rows =pstm.executeUpdate();
                if (rows>0) {
                     System.out.println("ok");
                }else {
                    System.out.println("no");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                //release中conn.close()方法已经被装饰
                JDBCUtils_V3.release(conn, pstm, null);
            }
        
        
        
    }
}

需要注意的是：MyConection 实现Connection接口时重新了Connection所有的方法，在使用被MyConnection类修饰的Connecting连接的某些方法要注意需要重写MyConnection类中的这些方法

比如：当使用prepareStatement(sql); 时要注意MyConnection中的prepareStatement(String sql)方法

MyConnection 在未处理之前的prepareStatement(String sql)方法如下图所示

不重写直接使用返回的结果永远是null ，重写后代码：

1     @Override
2     public PreparedStatement prepareStatement(String sql) throws SQLException {
3         // TODO Auto-generated method stub
4         return conn.prepareStatement(sql);
5     }

posted @ 2017-10-31 09:08 逆水行舟！阅读(7731) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

变局

JDBC连接池-自定义连接池

公告