java資料庫連接池

標籤：java   資料庫   資料庫連接池   

根據自己的理解寫的，資料庫連接池。

實現了動態增長，串連重用等。

串連池初始時，會有一定數量的串連，隨著串連請求的增多，動態增長串連。

存在的問題：當可用串連佔總串連數的百分比達到一個數值時，會有很多串連不會被使用，將來可能也不會使用，造成資源的浪費，我想的是產生一個線程，每隔一段時間動態掃描，計算這個百分比，然後關閉一定數量的串連。更好的是根據串連請求的統計，動態增長和關閉串連，比如使用者對網站的請求，淩晨的訪問肯定會比白天要少，淩晨就可以關閉一些串連，白天就適當的增加串連數。

主要代碼：

import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;public class ConnectionPool {private String driverName;private String dbURL;private String userName;private String userPwd;private String testTableName;private int minConnectionSize = 10;// 串連池最小個數private int maxConnectionSize = 100;private int addConnectionSize = 5;// 每次遞增的數量private List<MyConnection> connections;// 盛裝連結的容器private static int count = 0;// 可用串連數public ConnectionPool(String driverName, String dbURL, String userName,String userPwd, String testTableName) {this.driverName = driverName;this.dbURL = dbURL;this.userName = userName;this.userPwd = userPwd;this.testTableName = testTableName;}/** * 初始化,載入驅動等 *  * @throws ClassNotFoundException * @throws SQLException */public void createConnectionPool() throws ClassNotFoundException,SQLException {Class.forName(driverName);connections = new ArrayList<MyConnection>();addConnection(minConnectionSize);}/** * 從串連池中取出一個可用串連 *  * @return * @throws SQLException */public Connection getConnection() throws SQLException {for (MyConnection conn : connections) {if (!conn.isBusy()) {conn.setBusy(true);if (!testConnection(conn.getConnection())) {// 串連不可用時產生新的串連conn.setConnection(newConnection());}this.count--;return conn.getConnection();}}if (connections.size() < maxConnectionSize && maxConnectionSize > 0) {// 沒有可用連結時候，產生一些串連addConnection(addConnectionSize);} else {// 當串連數達到最大數量時，負荷狀態return null;}return getConnection();}/** * 測試某個串連是否可用 *  * @param connection * @return */private boolean testConnection(Connection connection) {try {Statement s = connection.createStatement();ResultSet rs = s.executeQuery("select count(*) from "+ testTableName);} catch (SQLException e) {e.printStackTrace();return false;}return true;}/** * 增加一定數量的串連 *  * @param num * @throws SQLException */private void addConnection(int num) throws SQLException {if (num + connections.size() > maxConnectionSize&& maxConnectionSize > 0) {num = maxConnectionSize - connections.size();}for (int i = 0; i < num; i++) {connections.add(new MyConnection(newConnection()));}}/** * 產生新的串連 *  * @return * @throws SQLException */private Connection newConnection() throws SQLException {Connection connection = DriverManager.getConnection(dbURL, userName,userPwd);this.count++;return connection;}/** * 釋放串連 *  * @param connection */public void freeConnection(Connection connection) {for (MyConnection mc : connections) {if (mc.getConnection() == connection) {mc.setBusy(false);this.count++;break;}}}/** * 關閉串連 *  * @param connection * @throws SQLException */public void closeConnection(Connection connection) throws SQLException {for (MyConnection mc : connections) {if (mc.getConnection() == connection) {connection.close();this.count--;connections.remove(mc);break;}}}/** * 清空串連池 *  * @throws SQLException */public void clearConnections() throws SQLException {for (MyConnection mc : connections) {mc.getConnection().close();}connections.clear();this.count = 0;}public String toString() {return "最大串連數" + maxConnectionSize + "，最小串連數:" + minConnectionSize+ "，當前串連數" + (connections.size() - count) + ",可用串連數:" + count+ "，總串連數:" + connections.size();}class MyConnection {private Connection connection;// 連結private boolean busy = false;// 是否在使用public MyConnection(Connection connection) {this.connection = connection;}public Connection getConnection() {return connection;}public boolean isBusy() {return busy;}public void setBusy(boolean busy) {this.busy = busy;}public void setConnection(Connection connection) {this.connection = connection;}}public String getTestTableName() {return testTableName;}public void setTestTableName(String testTableName) {this.testTableName = testTableName;}public int getMinConnectionSize() {return minConnectionSize;}public void setMinConnectionSize(int minConnectionSize) {this.minConnectionSize = minConnectionSize;}public int getMaxConnectionSize() {return maxConnectionSize;}public void setMaxConnectionSize(int maxConnectionSize) {this.maxConnectionSize = maxConnectionSize;}public int getAddConnectionSize() {return addConnectionSize;}public void setAddConnectionSize(int addConnectionSize) {this.addConnectionSize = addConnectionSize;}}

測試代碼：

import java.sql.Connection;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Random;public class Test {private static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";private static String dbURL = "jdbc:sqlserver://192.168.92.3:1433; DatabaseName=message_list_system";private static String userName = "sa";private static String userPwd = "123";private static String tableName = "user_info";public static void main(String[] args) throws ClassNotFoundException,SQLException, InterruptedException {ConnectionPool connectionPool = new ConnectionPool(driverName, dbURL,userName, userPwd, tableName);connectionPool.createConnectionPool();List<Connection> lists = new ArrayList<Connection>();for (int i = 0; i < 120; i++) {// 類比多次串連請求System.out.println(connectionPool);Connection connection = connectionPool.getConnection();lists.add(connection);Random random = new Random();int r = random.nextInt(2);// 產生0-2的隨機數類比釋放串連if (r == 0) {// 類比釋放串連 ,三分之一的機率釋放串連for (int j = 0; (j < random.nextInt(2) + 1) && lists.size() > 0; j++) {// 每次釋放1-2個串連int temp = random.nextInt(lists.size());connectionPool.freeConnection(lists.get(temp));// 在獲得的所有連結中隨機播放一個進行釋放串連lists.remove(temp);System.out.println("釋放了第" + temp + "個串連");}}Thread.sleep(200);}}}

：

java資料庫連接池