Java 数据连接池的实现

Mysql版本5.6

java 在进行数据库操作时每次需要进行物理连接一次数据库,物理连接一次平均是150mm(数值只针对我自己电脑)。

 缺点：开销太大

在写连接池的时候来了解一下mysql配置 ,WIN找 my.ini (默认位置:C:\ProgramData\MySQL\版本)   

Linux找 my.cnf  执行 sudo find / -name my.cnf 进行查找

mysql默认并发数是151 我们可以设置为  max_connections=1000

max_connections 必须设置否在下面代码连接过151时会出错

innodb处理io读写的后台并发线程数量，根据cpu核来确认
默认值：4，建议值：与逻辑cpu数量的一半保持一致。
innodb_read_io_threads = 4
innodb_write_io_threads = 4
MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
MySQL连接闲置超过一定时间后(单位：秒，此处为1800秒)将会被强行关闭

为了更好的测试我设置为 120秒
interactive_timeout = 120 
wait_timeout = 120

下面代码只是用队列简单实现了连接池

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.LinkedBlockingQueue;

/**
 * @author XiaoTian
 */
public class BlockingQueueConnection implements Runnable {
	
	//单例对象
	private static BlockingQueueConnection blocking = new BlockingQueueConnection();

	private BlockingQueue<Connection> blockingQueue;
	
	private DataSource dataSource;
	
	//获取对象
	public static BlockingQueueConnection getBlock() {
		return blocking;
	}
	
	public BlockingQueueConnection setBlockQueue(DataSource dataSource) {
		if(null == blockingQueue || blockingQueue.size() <= 0) {
			blockingQueue = new LinkedBlockingQueue<Connection>();
			this.dataSource = dataSource;
		}
		return blocking;
	}

	public void run() {
		// TODO Auto-generated method stub
		try {
			//1.加载驱动
	    	Class.forName(dataSource.getJdbcDriverName());
			while(blocking.blockingQueue.size() < dataSource.getInitialSize()) {
		    	//获取连接
		    	Connection connection =  DriverManager.getConnection(dataSource.getUrl(), dataSource.getUsername(), dataSource.getPassword());
		    	blockingQueue.put(connection);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() {
		return blocking.blockingQueue.poll();
	}
	
	public static void setConnection(Connection connection) throws InterruptedException {
		blocking.blockingQueue.put(connection);
	}
	
	public static BlockingQueue<Connection> get(){
		return blocking.blockingQueue;
	}

}

使用 LinkedBlockingQueue put方法和take方法都进行加锁操作。获取take的时候，检索并移除此队列的头，在必要时等待，直到一个元素可用。put 在这个队列的尾部插入指定的元素，在必要时等待空间可用。在使用take和put方法的时候没有空间使用就和进行堵塞直到可用的空间

从数据结构放面看 LinkedBlockingQueue （先进先出）put从尾部插入是O1操作，take获取头部 O1操作。效率当然快

启动线程进行测试

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

	private final static String username = "root";
	
	private final static String password = "111111";
	
	private final static String url = "jdbc:mysql:///test?useSSL=true&useUnicode=true&characterEncoding=utf8";
	
	private final static String jdbcDriverName = "com.mysql.jdbc.Driver";
	
	public static void main(String[] args) throws InterruptedException, SQLException {
		DataSource dataSource = new DataSource(url, username, password, jdbcDriverName);
		BlockingQueueConnection blockingQueue = BlockingQueueConnection.getBlock();
		long time = System.currentTimeMillis();
		blockingQueue.setBlockQueue(dataSource);
		blockingQueue.run();
		System.out.println((System.currentTimeMillis() - time)/1000d);
		System.out.println(BlockingQueueConnection.get().size());
		
		Connection connection1 = BlockingQueueConnection.getConnection();
		Connection connection2 = BlockingQueueConnection.getConnection();
		System.out.println(connection1 == connection2);
		new Thread(new Runnable() {	
			@Override
			public void run() {
				// TODO Auto-generated method stub
				int i = 0;
				while(i++ < 1000) {
					ResultSet rs = null;
					Statement st = null;
					try {
						Connection connection =  BlockingQueueConnection.getConnection();
						st = connection.createStatement();
						String sql = "SELECT id FROM `user` where id =4";	
					       rs = st.executeQuery(sql);
					        //5.取出结果集的数据
				        	while(rs.next()){ 
				        		System.out.println(rs.getObject("id")+ " "+Thread.currentThread().getName()+" :" +  " S1>>"+i);
				        	}
				        BlockingQueueConnection.setConnection(connection);
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (InterruptedException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally {
						//6.关闭链接，释放资源
						try {
							rs.close();
							st.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} 
					}
			   }
			   
			}
		}).start();
		new Thread(new Runnable() {	
			@Override
			public void run() {
				// TODO Auto-generated method stub
				int i = 0;
					while(i++ < 5000) {
						ResultSet rs = null;
						Statement st = null;
						try {
							Connection connection =  BlockingQueueConnection.getConnection();
							st = connection.createStatement();
							String sql = "SELECT id FROM `user` where id =4";	
						       rs = st.executeQuery(sql);
						        //5.取出结果集的数据
					        	while(rs.next()){ 
					        		System.out.println(rs.getObject("id")+ " "+Thread.currentThread().getName()+" :" +  " S2>>"+i);
					        	}
					        BlockingQueueConnection.setConnection(connection);
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (InterruptedException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}finally {
							//6.关闭链接，释放资源
							try {
								rs.close();
								st.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} 
						}
				}
			}
		}).start();
	}
}

这样一个简单连接池就实现了,每次从加载里获取 Connection 连接 用完再放回去

ResultSet和Statement 用完之后 再finally 里进行关闭，如果不关闭内存内增加并且导致内存溢出。

public class DataSource {
	  
	private volatile String url;
	
	private volatile String username;
	
	private volatile String password;
	
	private volatile String jdbcDriverName;
	
	/**
	 * 初始化大小
	 */
	private volatile Integer initialSize= 500; 
	
	public DataSource() {
		this(null,null,null,null);
	}
	
	/**
	 * 
	 * @param url	
	 * @param username	用户名
	 * @param password  密码
	 * @param jdbcDriverName  启动全类名
	 */
	public DataSource(String url, String username, String password, String jdbcDriverName) {
		super();
		this.url = url;
		this.username = username;
		this.password = password;
		this.jdbcDriverName = jdbcDriverName;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getJdbcDriverName() {
		return jdbcDriverName;
	}

	public void setJdbcDriverName(String jdbcDriverName) {
		this.jdbcDriverName = jdbcDriverName;
	}

	public Integer getInitialSize() {
		return initialSize;
	}

	public void setInitialSize(Integer initialSize) {
		this.initialSize = initialSize;
	}
	
}