Java 数据连接池的实现

缺乏、安全感 2022-04-17 03:16 194阅读 0赞

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;
    	}
    	
    }