传统JDBC的操作,对连接的对象销毁不是特别好。每次创建和销毁连接都是需要花费时间。可以使用连接池优化的程序。
在程序开始的时候,可以创建几个连接,将连接放入到连接池中,用户使用连接的时候,可以从连接池中进行获取。用完之后,可以将连接归还连接池。
Java为数据库连接池提供了公共的接口:javax.sql.DataSource。
根据我们对连接池简单的理解,如果我们要自定义连接池,需要完成以下步骤:
- 创建连接池实现(数据库),并实现接口javax.sql.DataSource.。因为我们只使用该接口中的getConnectoin()方法,简化本案例,我们将自己提供方法,而没有实现接口。
- 提供一个集合,用于存放连接,因为添加/移除频繁,所以选择LinkedList。
- 本案例在静态代码块中,为连接池初始化5个连接。
- 之后,如果程序需要连接,调用实现类的getConnection(),本方法将从连接池(容器List)获得连接。为了保证当前连接只能提供给一个线程使用,所以我们需要将连接先从连接池中移除。
- 当用户使用完连接,释放连接时,不执行close()方法,而是把连接归还到连接池中去。
实现示例代码 MyDataSource.java 如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
package cn.benz.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 cn.benz.test.JDBCUtils; public class MyDataSource implements DataSource{ //1. 创建1个容器,存储connection对象 private static LinkedList<Connection> pool = new LinkedList<Connection>(); //2. 创建5个链接到容器里去 static{ for (int i = 0; i < 5; i++) { //JDBCUtils.getConnection();是自己写的JDBC简单工具类,不是JDK提供 Connection conn = JDBCUtils.getConnection(); //把链接放到容器里 pool.add(conn); } } //继承DataSource接口,会有很多实现方法,这里我们只需要getConnection()这个方法 /** * 重写获取连接的方法 */ @Override public Connection getConnection() throws SQLException { Connection conn = null; //3. 使用前判断一下 if (pool.size() == 0) { //4. 池子里没有再创建一些 for (int i = 0; i < 5; i++) { conn = JDBCUtils.getConnection(); pool.add(conn); } } //5. 从池子里面获取一个连接对象Connection conn = pool.remove(0);//从池子里拿出一个Connection,并移除该Connection return conn; } /** * 归还Connection对象到连接池 * @param conn */ public void backConnection(Connection conn){ pool.add(conn); } @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; } } |
测试代码调用 TestMyDataSource.java:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
package cn.benz.test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.junit.Test; import cn.benz.datasource.MyDataSource; public class TestMyDataSource { @Test public void addUser(){ Connection conn = null; PreparedStatement pstmt = null; //1. 创建自定义连接池对象 MyDataSource dataSource = new MyDataSource(); try { //2. 从池子里取出连接 conn = dataSource.getConnection(); String sql = "insert into user values(null,?,?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "tester"); pstmt.setString(2, "00001"); int row = pstmt.executeUpdate(); if (row >0) { System.out.println("添加成功"); }else { System.out.println("添加失败"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //3. 归还连接 dataSource.backConnection(conn); } } } |
二、装饰者设计模式–修改close()方法
装饰者设计模式
点击这里查看装饰者设计模式
装饰者固定结构:接口A,已知实现类C,需要装饰者创建代理类B
- 创建类B,并实现接口A
- 提供类B的构造方法,参数类型为A,用于接收A接口的其他实现类(C)
- 给类B添加类型为A成员变量,用于存放A接口的其他实现类
- 增强需要的方法
- 实现不需要增强的方法,方法体重新调用成员变量存放的其他实现类对应的方法
在这里,我们的目标是修改JDBCUtils.java里的方法release()中的conn.close(),这里JDBCUtils是被修饰类(C)。
因为close()属于Connection接口(A),所以,我们创建一个装饰者类MyConnection(B),并实现同样的接口。
在装饰类 MyConnection中,需要提供类MyConnection的构造方法,用于接收需要修改方法的必要参数,然后再去修改需要增强的方法。
装饰类 MyConnection.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 |
package cn.benz.datasource; import java.sql.Array; import java.sql.Blob; import java.sql.CallableStatement; import java.sql.Clob; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.NClob; import java.sql.PreparedStatement; import java.sql.SQLClientInfoException; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.SQLXML; import java.sql.Savepoint; import java.sql.Statement; import java.sql.Struct; import java.util.LinkedList; import java.util.Map; import java.util.Properties; import java.util.concurrent.Executor; public class MyConnection implements Connection { private Connection conn = null; private LinkedList<Connection> pool = null; /** * 提供构造方法,用于接收参数 * @param conn * @param pool */ public MyConnection(Connection conn,LinkedList<Connection> pool) { this.conn =conn; this.pool = pool; } /** * 需要增强的方法 */ @Override public void close() throws SQLException { pool.add(conn); } /** * 此方法必须覆盖,否则会出现空指针错误 */ @Override public PreparedStatement prepareStatement(String sql) throws SQLException { // TODO Auto-generated method stub return conn.prepareStatement(sql); } @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 Statement createStatement() throws SQLException { // TODO Auto-generated method stub return null; } @Override public CallableStatement prepareCall(String sql) throws SQLException { // TODO Auto-generated method stub return null; } @Override public String nativeSQL(String sql) throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setAutoCommit(boolean autoCommit) throws SQLException { // TODO Auto-generated method stub } @Override public boolean getAutoCommit() throws SQLException { // TODO Auto-generated method stub return false; } @Override public void commit() throws SQLException { // TODO Auto-generated method stub } @Override public void rollback() throws SQLException { // TODO Auto-generated method stub } @Override public boolean isClosed() throws SQLException { // TODO Auto-generated method stub return false; } @Override public DatabaseMetaData getMetaData() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setReadOnly(boolean readOnly) throws SQLException { // TODO Auto-generated method stub } @Override public boolean isReadOnly() throws SQLException { // TODO Auto-generated method stub return false; } @Override public void setCatalog(String catalog) throws SQLException { // TODO Auto-generated method stub } @Override public String getCatalog() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setTransactionIsolation(int level) throws SQLException { // TODO Auto-generated method stub } @Override public int getTransactionIsolation() throws SQLException { // TODO Auto-generated method stub return 0; } @Override public SQLWarning getWarnings() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void clearWarnings() throws SQLException { // TODO Auto-generated method stub } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } @Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } @Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Map<String, Class<?>> getTypeMap() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setTypeMap(Map<String, Class<?>> map) throws SQLException { // TODO Auto-generated method stub } @Override public void setHoldability(int holdability) throws SQLException { // TODO Auto-generated method stub } @Override public int getHoldability() throws SQLException { // TODO Auto-generated method stub return 0; } @Override public Savepoint setSavepoint() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Savepoint setSavepoint(String name) throws SQLException { // TODO Auto-generated method stub return null; } @Override public void rollback(Savepoint savepoint) throws SQLException { // TODO Auto-generated method stub } @Override public void releaseSavepoint(Savepoint savepoint) throws SQLException { // TODO Auto-generated method stub } @Override public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { // TODO Auto-generated method stub return null; } @Override public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { // TODO Auto-generated method stub return null; } @Override public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { // TODO Auto-generated method stub return null; } @Override public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { // TODO Auto-generated method stub return null; } @Override public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { // TODO Auto-generated method stub return null; } @Override public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Clob createClob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Blob createBlob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public NClob createNClob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public SQLXML createSQLXML() throws SQLException { // TODO Auto-generated method stub return null; } @Override public boolean isValid(int timeout) throws SQLException { // TODO Auto-generated method stub return false; } @Override public void setClientInfo(String name, String value) throws SQLClientInfoException { // TODO Auto-generated method stub } @Override public void setClientInfo(Properties properties) throws SQLClientInfoException { // TODO Auto-generated method stub } @Override public String getClientInfo(String name) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Properties getClientInfo() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Array createArrayOf(String typeName, Object[] elements) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Struct createStruct(String typeName, Object[] attributes) throws SQLException { // TODO Auto-generated method stub return null; } @Override public void setSchema(String schema) throws SQLException { // TODO Auto-generated method stub } @Override public String getSchema() throws SQLException { // TODO Auto-generated method stub return null; } @Override public void abort(Executor executor) throws SQLException { // TODO Auto-generated method stub } @Override public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException { // TODO Auto-generated method stub } @Override public int getNetworkTimeout() throws SQLException { // TODO Auto-generated method stub return 0; } } |
使用装饰类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 |
package cn.benz.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 cn.benz.test.JDBCUtils; public class MyDataSource implements DataSource{ //1. 创建1个容器,存储connection对象 private static LinkedList<Connection> pool = new LinkedList<Connection>(); //2. 创建5个链接到容器里去 static{ for (int i = 0; i < 5; i++) { //JDBCUtils.getConnection();是自己写的JDBC简单工具类,不是JDK提供 Connection conn = JDBCUtils.getConnection(); MyConnection myConn = new MyConnection(conn, pool);//使用修饰类 //把链接放到容器里 pool.add(myConn); } } //继承DataSource接口,会有很多实现方法,这里我们只需要getConnection()这个方法 /** * 重写获取连接的方法 */ @Override public Connection getConnection() throws SQLException { Connection conn = null; //3. 使用前判断一下 if (pool.size() == 0) { //4. 池子里没有再创建一些 for (int i = 0; i < 5; i++) { conn = JDBCUtils.getConnection(); MyConnection myConn = new MyConnection(conn, pool);//使用修饰类 pool.add(myConn); } } //5. 从池子里面获取一个连接对象Connection conn = pool.remove(0);//从池子里拿出一个Connection,并移除该Connection return conn; } /** * 归还Connection对象到连接池 * @param conn */ public void backConnection(Connection conn){//此处不再需要 pool.add(conn); } @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; } } |
测试类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
package cn.benz.test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.junit.Test; import cn.benz.datasource.MyDataSource; public class TestMyDataSource { @Test public void addUser(){ Connection conn = null; PreparedStatement pstmt = null; //1. 创建自定义连接池对象 MyDataSource dataSource = new MyDataSource(); try { //2. 从池子里取出连接 conn = dataSource.getConnection(); String sql = "insert into user values(null,?,?)"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "tester02"); pstmt.setString(2, "00002"); int row = pstmt.executeUpdate(); if (row >0) { System.out.println("添加成功"); }else { System.out.println("添加失败"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ //3. 归还连接 //dataSource.backConnection(conn);//之前方法 JDBCUtils.release(conn, pstmt, null);//release()方法代码并没修改,但是调用后并不执行release()下的close(), //而是执行修饰类MyConnection里的增强后的close(),即归还连接。 //因为这里的参数conn是修饰类MyConnection处理过的,如果是原来的conn, //还是会调用close()关闭连接,而不是归还。 } } } |