Java数据库连接池
写了个 Java数据库连接池,具备基本的功能点:
1、对池中活动连接的重用。
2、池满时的适时等待。
3、对空闲连接的适时关闭。
抛砖引玉,不吝赐教。
源码文件 DBConnection.java
1 package db; 2 3 import java.sql.Connection; 4 import java.util.concurrent.atomic.AtomicBoolean; 5 6 /** 7 * 封装的连接 8 * @author Linkwork, [email protected] 9 * @since 2014年11月01日 10 */ 11 public class DBConnection { 12 13 /** 14 * 原生的连接 15 */ 16 private Connection connection = null; 17 18 /** 19 * 是否空闲 20 */ 21 private AtomicBoolean idle = null; 22 23 /** 24 * 最近一次的空闲开始时间 25 */ 26 private volatile long idleStart = 0L; 27 28 /** 29 * 标识 30 */ 31 private int index = -1; 32 33 public DBConnection(int index, Connection connection, boolean idle) { 34 this.index = index; 35 this.connection = connection; 36 this.idle = new AtomicBoolean(idle); 37 } 38 39 public void release() { 40 if (this.idle.compareAndSet(false, true)) { 41 this.idleStart = System.currentTimeMillis(); 42 } 43 } 44 45 public Connection getConnection() { 46 return connection; 47 } 48 49 public AtomicBoolean getIdle() { 50 return idle; 51 } 52 53 public void setConnection(Connection connection) { 54 this.connection = connection; 55 } 56 57 public int getIndex() { 58 return index; 59 } 60 61 public long getIdleStart() { 62 return idleStart; 63 } 64 65 public void setIdleStart(long idleStart) { 66 this.idleStart = idleStart; 67 } 68 69 }
源码文件 DBConnectionPool.java
1 package db; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.util.Vector; 6 7 /** 8 * 数据库连接池 9 * @author Linkwork, [email protected] 10 * @since 2014年11月01日 11 */ 12 public class DBConnectionPool extends Thread { 13 14 /** 15 * 容量 16 */ 17 private volatile int capacity = 1; 18 19 /** 20 * 驱动 21 */ 22 private String driver = null; 23 24 /** 25 * 地址 26 */ 27 private String url = null; 28 29 /** 30 * 用户名 31 */ 32 private String user = null; 33 34 /** 35 * 密码 36 */ 37 private String password = null; 38 39 /** 40 * 等待的轮询间隔 41 */ 42 private volatile long waitInterval = 50L; 43 44 /** 45 * 等待的超时时间 46 */ 47 private volatile long waitTimeout = 300000L; 48 49 /** 50 * 空闲的超时时间 51 */ 52 private volatile long idleTimeout = 60L; 53 54 /** 55 * 连接集 56 */ 57 private Vector<DBConnection> dbconnectionLst = null; 58 59 /** 60 * 是否关闭 61 */ 62 private volatile boolean closing = false; 63 64 public DBConnectionPool( 65 int capacity, 66 String driver, 67 String url, 68 String user, 69 String password) { 70 this.capacity = capacity; 71 this.driver = driver; 72 this.url = url; 73 this.user = user; 74 this.password = password; 75 this.dbconnectionLst = new Vector<DBConnection>(); 76 } 77 78 public DBConnection getDBConnection() throws Exception { 79 long start = System.currentTimeMillis(); 80 while (! this.closing) { 81 // 遍历连接集,获取空闲、可用的连接 82 for (DBConnection dbconnection: this.dbconnectionLst) { 83 if (dbconnection.getIdle().compareAndSet(true, false)) { 84 // 若连接未关闭 85 if ((null != dbconnection.getConnection()) 86 && (! dbconnection.getConnection().isClosed())) { 87 return dbconnection; 88 } else { 89 dbconnection.getIdle().set(true); 90 } 91 } 92 } 93 // 若连接的总数未超出容量 94 if ((this.dbconnectionLst.size() < this.capacity) 95 && (! this.closing)) { 96 synchronized (this.dbconnectionLst) { 97 DBConnection dbconnection = this.createDBConnection(this.dbconnectionLst.size() + 1); 98 this.dbconnectionLst.add(dbconnection); 99 return dbconnection; 100 } 101 } 102 // 遍历连接集,重用空闲、不可用的连接 103 for (DBConnection dbconnection: this.dbconnectionLst) { 104 if (dbconnection.getIdle().compareAndSet(true, false)) { 105 // 若连接已关闭 106 if ((null == dbconnection.getConnection()) 107 || dbconnection.getConnection().isClosed()) { 108 Connection connection = this.createConnection(); 109 dbconnection.setConnection(connection); 110 return dbconnection; 111 } else { 112 dbconnection.getIdle().set(true); 113 } 114 } 115 } 116 // 延迟轮询 117 Thread.sleep(this.waitInterval); 118 long end = System.currentTimeMillis(); 119 if (end - start > this.waitTimeout) { 120 throw new Exception("ERROR_WAIT_TIMEOUT"); 121 } 122 start = end; 123 } // while (! this.closing) { 124 return null; 125 } 126 127 public void close() throws Exception { 128 this.closing = true; 129 boolean closed = false; 130 while (! closed) { 131 closed = true; 132 Thread.sleep(this.waitInterval); 133 // 遍历连接集,关闭所有连接 134 for (DBConnection dbconnection: this.dbconnectionLst) { 135 if (dbconnection.getIdle().compareAndSet(true, false)) { 136 closed = false; 137 if ((null != dbconnection.getConnection()) 138 && (! dbconnection.getConnection().isClosed())) { 139 dbconnection.getConnection().close(); 140 } 141 } 142 } 143 } // while (true) { 144 } 145 146 @Override 147 public void run() { 148 while (! this.closing) { 149 try { 150 Thread.sleep(this.waitInterval); 151 // 遍历连接集,适时关闭空闲连接 152 for (DBConnection dbconnection: this.dbconnectionLst) { 153 if (dbconnection.getIdle().get() 154 && (dbconnection.getIdleStart() > 0) 155 && (System.currentTimeMillis() - dbconnection.getIdleStart() > this.idleTimeout)) { 156 if (dbconnection.getIdle().compareAndSet(true, false)) { 157 if ((dbconnection.getIdleStart() > 0) 158 && (System.currentTimeMillis() - dbconnection.getIdleStart() > this.idleTimeout)) { 159 dbconnection.setIdleStart(0L); 160 dbconnection.getConnection().close(); 161 System.out.println("【dbconnection-" + dbconnection.getIndex() + "】idle timeout."); 162 } 163 dbconnection.getIdle().set(true); 164 } 165 } 166 } 167 } catch (Exception e) { 168 e.printStackTrace(); 169 } 170 } 171 } 172 173 public int getCapacity() { 174 return capacity; 175 } 176 177 public void setCapacity(int capacity) { 178 this.capacity = capacity; 179 } 180 181 public long getWaitTimeout() { 182 return waitTimeout; 183 } 184 185 public void setWaitTimeout(long waitTimeout) { 186 this.waitTimeout = waitTimeout; 187 } 188 189 public long getIdleTimeout() { 190 return idleTimeout; 191 } 192 193 public void setIdleTimeout(long idleTimeout) { 194 this.idleTimeout = idleTimeout; 195 } 196 197 private DBConnection createDBConnection(int index) throws Exception { 198 return new DBConnection(index, this.createConnection(), false); 199 } 200 201 private Connection createConnection() throws Exception { 202 Class.forName(this.driver); 203 return DriverManager.getConnection(this.url, this.user, this.password); 204 } 205 206 }
源码文件 DBConnectionPoolTest.java
1 package db; 2 3 import java.sql.PreparedStatement; 4 import java.sql.ResultSet; 5 6 /** 7 * 测试:数据库连接池 8 * @author Linkwork, [email protected] 9 * @since 2014年11月01日 10 */ 11 public class DBConnectionPoolTest { 12 13 public static void main(String[] args) throws Exception { 14 DBConnectionPool pool = new DBConnectionPool(3, "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/db", "Linkwork", "276247076"); 15 pool.start(); 16 DBConnection dbconnection = pool.getDBConnection(); 17 if (null != dbconnection) { 18 dbconnection.getConnection().prepareStatement("create table if not exists test_pool (tp_value int);").execute(); 19 dbconnection.release(); 20 } 21 mockConcurrent(pool); 22 Thread.sleep(2000); 23 mockConcurrent(pool); 24 Thread.sleep(50); 25 pool.close(); 26 } 27 28 public static void mockConcurrent(DBConnectionPool pool) { 29 for (int index = 0; index < 10; ++ index) { 30 final int value = index; 31 Thread thread = new Thread() { 32 public void run() { 33 DBConnection dbconnection = null; 34 try { 35 dbconnection = pool.getDBConnection(); 36 } catch (Exception e) { 37 e.printStackTrace(); 38 } 39 if (null != dbconnection) { 40 try { 41 if (Math.random() > 0.5) { 42 PreparedStatement statement = dbconnection.getConnection().prepareStatement("insert into test_pool(tp_value) values(?);"); 43 statement.setInt(1, value); 44 statement.execute(); 45 dbconnection.release(); 46 System.out.println("【dbconnection-" + dbconnection.getIndex() + "】insert: " + value); 47 } 48 else { 49 PreparedStatement statement = dbconnection.getConnection().prepareStatement("select * from test_pool where tp_value = ?;"); 50 statement.setInt(1, value); 51 ResultSet rs = statement.executeQuery(); 52 if ((null != rs) 53 && rs.next()) { 54 System.out.println("【dbconnection-" + dbconnection.getIndex() + "】query: " + rs.getInt(1)); 55 } else { 56 System.out.println("【dbconnection-" + dbconnection.getIndex() + "】query: "); 57 } 58 dbconnection.release(); 59 } 60 } catch (Exception e) { 61 e.printStackTrace(); 62 } 63 } 64 } 65 }; 66 thread.start(); 67 } 68 } 69 70 }
运行 DBConnectionPoolTest.java,控制台输出:
【dbconnection-1】query: 0 【dbconnection-1】query: 8 【dbconnection-1】query: 4 【dbconnection-2】insert: 2 【dbconnection-3】insert: 6 【dbconnection-2】insert: 3 【dbconnection-1】insert: 1 【dbconnection-1】insert: 9 【dbconnection-3】insert: 5 【dbconnection-2】insert: 7 【dbconnection-1】idle timeout. 【dbconnection-2】idle timeout. 【dbconnection-3】idle timeout. 【dbconnection-3】query: 3 【dbconnection-1】query: 1 【dbconnection-2】query: 0 【dbconnection-1】idle timeout. 【dbconnection-2】idle timeout. 【dbconnection-3】idle timeout.
说明:
这里模拟了多线程数据库请求的环境,且把数据库连接池的空闲的超时时间设置为 60毫秒(模拟适时关闭空闲连接)。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。