50,528
社区成员
发帖
与我相关
我的任务
分享
package com.lv9.jdbc;
import java.io.FileInputStream;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
public class ConnectionPool {
private volatile LinkedList<Connection> connectionPool = new LinkedList<Connection>();
private final String userName;
private final String password;
private final String driverName;
private final String connectionURL;
private final String databaseName;
private final int maxFreeConnectionsSize;
private int poolSize;
private static ConnectionPool pool;
private ConnectionPool() throws Exception {
Properties properties = new Properties();
properties.load(new FileInputStream(getClass().getResource("")
.getPath()
+ "database.properties"));
this.userName = properties.getProperty("userName");
this.password = properties.getProperty("password");
this.driverName = properties.getProperty("driverName");
this.connectionURL = properties.getProperty("connectionURL");
this.databaseName = properties.getProperty("databaseName");
this.maxFreeConnectionsSize = Integer.parseInt(properties
.getProperty("maxFreeConnectionsSize"));
this.poolSize = Integer.parseInt(properties.getProperty("poolSize"));
init();
}
public synchronized static ConnectionPool getConnectionPool()
throws Exception {
if (pool == null) {
pool = new ConnectionPool();
}
return pool;
}
public int executeUpdate(String sql) throws Exception {
Connection connection = null;
try {
connection = getConnection();
Statement statement = connection.createStatement();
return statement.executeUpdate(sql);
} finally {
if (connection != null) {
connection.close();
}
}
}
public List<?> executeQuery(String sql) throws Exception {
List<?> result = null;
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
connection = getConnection();
statement = connection.createStatement();
rs = statement.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount;
if ((columnCount = metaData.getColumnCount()) > 1) {
result = getSingleList(rs);
} else {
result = getList(rs, columnCount);
}
} finally {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
return result;
}
public synchronized Connection getConnection() throws Exception {
Connection connection;
if (connectionPool.isEmpty()) {
System.out.println("创建连接");
poolSize++;
connection = getProxyConnection();
} else {
connection = connectionPool.removeFirst();
}
return connection;
}
public String getDataBaseName() {
return databaseName;
}
protected int getMaxFreeConnectionSize() {
return maxFreeConnectionsSize;
}
protected int getFreeConnectionCount() {
return connectionPool.size();
}
protected synchronized void removeAConnection() {
poolSize--;
}
protected int getPoolSize() {
return poolSize;
}
protected synchronized void resetConnection(Connection connection) {
connectionPool.addLast(connection);
}
private void init() throws Exception {
Class.forName(driverName);
for (int i = 0; i < poolSize; i++) {
connectionPool.addFirst(getProxyConnection());
}
}
private Connection getProxyConnection() throws Exception {
PoolConnection invocationHandlerObject = new PoolConnection(
getDataBaseConnection(), this);
return (Connection) Proxy.newProxyInstance(this.getClass()
.getClassLoader(), new Class<?>[] { Connection.class },
invocationHandlerObject);
}
private Connection getDataBaseConnection() throws Exception {
return DriverManager.getConnection(connectionURL, userName, password);
}
private List<Object[]> getList(ResultSet rs, int columnCount)
throws Exception {
List<Object[]> list = new ArrayList<Object[]>();
while (rs.next()) {
Object[] objs = new Object[columnCount];
for (int i = 1; i <= columnCount; i++) {
objs[i - 1] = rs.getObject(i);
}
list.add(objs);
}
return list;
}
private List<Object> getSingleList(ResultSet rs) throws Exception {
List<Object> list = new ArrayList<Object>();
while (rs.next()) {
list.add(rs.getObject(1));
}
return list;
}
}
class PoolConnection implements InvocationHandler {
private Connection connection;
private ConnectionPool pool;
public PoolConnection(Connection connection, ConnectionPool pool) {
this.connection = connection;
this.pool = pool;
}
@Override
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
if (method.getName().trim().equals("close")) {
System.out.println(this + " : 已经重置.当前连接池内连接数为:"
+ pool.getPoolSize() + ". 当前空闲连接有:"
+ pool.getFreeConnectionCount());
if (pool.getFreeConnectionCount() < pool.getMaxFreeConnectionSize()) {
pool.resetConnection((Connection) proxy);
} else {
method.invoke(connection, args);
pool.removeAConnection();
}
return null;
} else {
return method.invoke(connection, args);
}
}
}
package com.java.lv9.jdbc;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;
public class ConnectionPoolProperties {
private String userName; // 用户名
private String password;// 密码
private String driverName;// 驱动
private String connectionURL;// 连接地址
private String databaseName;// 驱动
private int maxConnectionsSize;// 最大连接
private int initPoolSize;// 初始化连接
private long connectionUpdateInterval;// 连接更新间隔时间
private boolean isWaitingFreeConnection;// 是否等待空闲连接 默认为false 如果是的话
// 当连接池没有连接的时候 等待回收的连接
public ConnectionPoolProperties(FileInputStream in) {
Properties properties = new Properties();
try {
properties.load(in);
this.userName = properties.getProperty("userName");
this.password = properties.getProperty("password");
this.driverName = properties.getProperty("driverName");
this.connectionURL = properties.getProperty("connectionURL");
this.databaseName = properties.getProperty("databaseName");
this.maxConnectionsSize = Integer.parseInt(properties.getProperty("maxConnectionsSize"));
this.isWaitingFreeConnection = Boolean.parseBoolean(properties.getProperty("isWaitingFreeConnection"));
this.initPoolSize = Integer.parseInt(properties.getProperty("initPoolSize"));
this.connectionUpdateInterval = Long.parseLong(properties.getProperty("connectionUpdateInterval")) * 1000 * 60;
} catch (IOException e) {
throw new RuntimeException(e);
}
}
public String getUserName() {
return userName;
}
public String getPassword() {
return password;
}
public String getDriverName() {
return driverName;
}
public String getConnectionURL() {
return connectionURL;
}
public String getDatabaseName() {
return databaseName;
}
public int getMaxConnectionsSize() {
return maxConnectionsSize;
}
public int getInitPoolSize() {
return initPoolSize;
}
public long getConnectionUpdateInterval() {
return connectionUpdateInterval;
}
public boolean isWaitingFreeConnection() {
return isWaitingFreeConnection;
}
}
package com.lv9.jdbc;
import java.util.Random;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
public class TestConnectionPool {
public static void main(String[] args) throws Exception {
ExecutorService service = Executors.newCachedThreadPool();
for (int i = 0; i < 100; i++) {
service.submit(new TestConnectPoolThread());
}
}
}
class TestConnectPoolThread extends Thread {
public Random random = new Random();
public void run() {
while (true) {
try {
Thread.sleep(Math.abs(random.nextInt() % 200));
} catch (InterruptedException e1) {
e1.printStackTrace();
}
ConnectionPool connectionPool;
try {
connectionPool = ConnectionPool.getConnectionPool();
connectionPool
.executeQuery("SELECT * FROM mytable m LIMIT 0,1000");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
driverName=com.mysql.jdbc.Driver
userName=root
password=1234
databaseName=mysql
connectionURL=jdbc\:mysql\://127.0.0.1\:3306/mydatabase?useUnicode\=true&characterEncoding\=gbk
poolSize=10
maxFreeConnectionsSize=10