数据库连接池

数据库的连接对象创建工作，比较消耗性能
在开始的时候在内存中开辟一块空间(集合、池子)，一开始先往池子里放置多个连接对象
以后需要连接对象时，直接从池子中取，不需要再自己去创建对象了
使用完毕，将连接归还给池子，保证连接对象可以循环利用

自定义数据库连接池

代码实现

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

 1 public class TestDemo {
 2     @Test
 3     public void test() {
 4         Connection conn = null;
 5         PreparedStatement ps = null;
 6         MyDataSource dataSource = new MyDataSource();
 7         try {
 8             conn = dataSource.getConnection();
 9             String sql = "select * from person";
10             ps = conn.prepareStatement(sql);
11             ResultSet resultSet = ps.executeQuery();
12             while (resultSet.next()) {
13                 String name = resultSet.getString(2);
14                 System.out.println(name);
15             }
16         } catch (SQLException e) {
17             e.printStackTrace();
18         } finally {
19             try {
20                 if(ps!=null) {
21                     ps.close();
22                 }
23             } catch (SQLException e) {
24                 e.printStackTrace();
25             }
26             dataSource.addBack(conn);
27         }
28     }
29 }

出现的问题

需要额外记住 addBack方法
单例
无法面向接口编程

使用DataSource dataSource = new MyDataSource();代替代码中的MyDataSource dataSource = new MyDataSource();finall块中的dataSource.addback(conn);会抛出异常

因为接口里面没有定义addBack方法(编译看左边，运行看右边)

怎么解决? 以addBack 为切入点

由于多了一个addBack 方法，所以使用这个连接池的地方，需要额外记住这个方法，并且还不能面向接口编程

　　我们打算修改接口中的那个close方法。原来的Connection对象的close方法，是真的关闭连接。打算修改这个close方法，以后在调用close，并不是真的关闭，而是归还连接对象

如何扩展Connection中的close方法

直接改源码，无法实现
继承，必须知道这个接口的具体实现，但是Connection接口的具体实现类找不到
使用装饰者模式，可以

使用装饰者模式自定义数据库连接池

包装类

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

View Code

自定义数据库连接池

public class MyDataSource implements DataSource {
    
    private List<Connection> list = new ArrayList<Connection>();

    public MyDataSource() {
        for (int i = 0; i < 10; i++) {
            Connection conn = DBUtils.getConn();
            list.add(conn);
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if (list.size() == 0) {
            for (int i = 0; i < 5; i++) {
                Connection conn = DBUtils.getConn();
                list.add(conn);
            }
        }
        Connection conn = list.remove(0);
        //使用Connection的包装类
        ConnectionWrap connWrap = new ConnectionWrap(conn, list);
        return connWrap;
    }
    
    @Override
    public PrintWriter getLogWriter() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

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

    }

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

    }

    @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 <T> T unwrap(Class<T> iface) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

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

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
}

测试类

public class TestDemo {
    @Test
    public void test() {
        Connection conn = null;
        PreparedStatement ps = null;
        MyDataSource dataSource = new MyDataSource();
        ResultSet resultSet = null;
        try {
            conn = dataSource.getConnection();
            String sql = "select * from person";
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString(2);
                System.out.println(name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.releaseResource(resultSet, ps, conn);
        }
    }
}

控制台输出

smile
wxf
admin
qf
wxf_1
连接归还前，连接池中连接数：9
连接归还后，连接池中连接数：10

开源连接池

DBCP
C3P0（常用）

DBCP

导入相关jar（commons-dbcp-*.jar、commons-pool-*.jar、mysql-connector-java-*-bin.jar）

dbcpconfig.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

TestDBCP.java　

 1 public class TestDBCP {
 2 
 3     @Test
 4     public void test() {
 5         /*BasicDataSource dataSource = new BasicDataSource();
 6         dataSource.setUrl("jdbc:mysql://localhost:3306/test");
 7         dataSource.setDriverClassName("com.mysql.jdbc.Driver");
 8         dataSource.setUsername("root");
 9         dataSource.setPassword("root");*/
10         BasicDataSourceFactory factory = new BasicDataSourceFactory();
11         DataSource dataSource = null;
12         
13         Connection conn = null;
14         PreparedStatement ps = null;
15         try {
16             Properties prop = new Properties();
17             InputStream inStream = new FileInputStream("src/dbcpconfig.properties");
18             prop.load(inStream);
19             dataSource = factory.createDataSource(prop);
20             
21             
22             conn = dataSource.getConnection();
23             String sql = "insert into person values(null,?,?,?,null)";
24             ps = conn.prepareStatement(sql);
25             ps.setString(1, "admin");
26             ps.setInt(2, 24);
27             ps.setDate(3, new Date(0));
28             int result = ps.executeUpdate();
29             if(result>0) {
30                 System.out.println("操作成功！！！");
31             }else {
32                 System.out.println("操作失败！！！");
33             }
34         } catch (SQLException e) {
35             e.printStackTrace();
36         } catch (Exception e) {
37             e.printStackTrace();
38         } finally {
39             try {
40                 if (conn != null) {
41                     conn.close();
42                 }
43                 if(ps != null) {
44                     ps.close();
45                 }
46             } catch (SQLException e) {
47                 e.printStackTrace();
48             }
49         }
50     }
51 }

控制台输出

操作成功！！！

C3P0

使用代码方式

导入jar（c3p0-0.9.1.2-jdk1.3.jar、c3p0-0.9.1.2.jar、c3p0-oracle-thin-extras-0.9.1.2.jar）

TestC3P0.java

 1 public class TestC3P0 {
 2 
 3     @Test
 4     public void test() {
 5         Connection conn = null;
 6         PreparedStatement ps = null;
 7         //1.创建dataSource
 8         ComboPooledDataSource dataSource = new ComboPooledDataSource();
 9         
10         try {
11             //2.设置连接数据库属性
12             dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
13             dataSource.setDriverClass("com.mysql.jdbc.Driver");
14             dataSource.setUser("root");
15             dataSource.setPassword("root");
16             //3.获取连接
17             conn = dataSource.getConnection();
18             
19             //4.操作
20             String sql = "insert into person values(null,?,?,?,null)";
21             ps = conn.prepareStatement(sql);
22             ps.setString(1, "admin");
23             ps.setInt(2, 24);
24             ps.setDate(3, new Date(0));
25             int result = ps.executeUpdate();
26             if(result>0) {
27                 System.out.println("操作成功！！！");
28             }else {
29                 System.out.println("操作失败！！！");
30             }
31         } catch (PropertyVetoException e) {
32             e.printStackTrace();
33         } catch (SQLException e) {
34             e.printStackTrace();
35         } finally {
36             try {
37                 if (conn != null) {
38                     conn.close();
39                 }
40                 if(ps != null) {
41                     ps.close();
42                 }
43             } catch (SQLException e) {
44                 e.printStackTrace();
45             }
46         }
47     }
48 }

控制台输出

操作成功！！！

使用配置文件方式

c3p0-config.xml（文件名必须是这个，不能写错）

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="automaticTestTable">con_test</property>
    <property name="checkoutTimeout">30000</property>
    <property name="idleConnectionTestPeriod">30</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>
    
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
    <property name="user">root</property>
    <property name="password">root</property>
  </default-config>

  <!-- This app is massive! -->
  <named-config name="oracle"> 
    <property name="acquireIncrement">50</property>
    <property name="initialPoolSize">100</property>
    <property name="minPoolSize">50</property>
    <property name="maxPoolSize">1000</property>
    <!-- intergalactoApp adopts a different approach to configuring statement caching -->
    <property name="maxStatements">0</property> 
    <property name="maxStatementsPerConnection">5</property>
  </named-config>
</c3p0-config>

name-config标签中可以配置连接其他的数据库，如Oracle，sqlserver等
new ComboPooledDataSource("oracle"),就会找name是oracle的name-config

TestC3P0.java

 1 public class TestC3P0 {
 2 
 3     @Test
 4     public void test() {
 5         Connection conn = null;
 6         PreparedStatement ps = null;
 7         //1.创建dataSource（类加载器自动加载c3p0-config.xml文件，默认找default-config标签下的配置），
 8         ComboPooledDataSource dataSource = new ComboPooledDataSource();
 9         
10         try {
11             //2.设置连接数据库属性
12             /*dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
13             dataSource.setDriverClass("com.mysql.jdbc.Driver");
14             dataSource.setUser("root");
15             dataSource.setPassword("root");*/
16             //3.获取连接
17             conn = dataSource.getConnection();
18             
19             //4.操作
20             String sql = "insert into person values(null,?,?,?,null)";
21             ps = conn.prepareStatement(sql);
22             ps.setString(1, "admin");
23             ps.setInt(2, 24);
24             ps.setDate(3, new Date(0));
25             int result = ps.executeUpdate();
26             if(result>0) {
27                 System.out.println("操作成功！！！");
28             }else {
29                 System.out.println("操作失败！！！");
30             }
31         } catch (SQLException e) {
32             e.printStackTrace();
33         } finally {
34             try {
35                 if (conn != null) {
36                     conn.close();
37                 }
38                 if(ps != null) {
39                     ps.close();
40                 }
41             } catch (SQLException e) {
42                 e.printStackTrace();
43             }
44         }
45     }
46 }

控制台输出

操作成功！！！

posted @ 2018-12-10 17:22 *青锋* 阅读(171) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

*青锋*

数据库连接池

数据库连接池

自定义数据库连接池

代码实现

出现的问题

使用装饰者模式自定义数据库连接池

开源连接池

DBCP

C3P0

公告

青锋