JDBC数据库连接池 - (Driver:Mysql, 可替换为其他Driver)

逃离我推掉我的手 2022-07-16 04:26 208阅读 0赞

不通过连接池连接数据库时，主要有两大劣势：

1.每次操作数据库均需连接数据库，耗时；

2.每次连接数据库均需要单独的线程，消耗内存严重，容易导致服务器宕机。

直接上代码：

连接池接口，提供连接和创建连接：

/** 
     * @projectName:JavaProxy 
     * @fileName:IPool.java 
     * @packageName:club.younge.jdbc 
     * @date:2016年10月5日下午1:21:38 
     * @copyright (c) 2016, heqy@finansir.nt All Rights Reserved. 
     * 
     */  
      
    package club.younge.jdbc;
    
    /** 
     * @className:IPool 
     * @function: TODO ADD FUNCTION.  
     * @reason:   TODO ADD REASON. 
     * @date:     2016年10月5日 下午1:21:38
     * @author   Younge 
     * @version   
     * @since    JDK 1.8 
     * @see       
     */
    public interface IPool {
    	public PooledConnection getConnection();
    	public void createConnections(int count);
    }

连接池实现类。

/** 
     * @projectName:JavaProxy 
     * @fileName:JdbcPool.java 
     * @packageName:club.younge.jdbc 
     * @date:2016年10月5日下午1:23:58 
     * @copyright (c) 2016, heqy@finansir.nt All Rights Reserved. 
     * 
     */  
      
    package club.younge.jdbc;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;
    import java.util.Vector;
    
    import com.mysql.jdbc.Driver;
    
    /** 
     * @className:JdbcPool 
     * @function: TODO ADD FUNCTION.  
     * @reason:   TODO ADD REASON. 
     * @date:     2016年10月5日 下午1:23:58
     * @author   Younge 
     * @version   
     * @since    JDK 1.8 
     * @see       
     */
    public class JdbcPool implements IPool {
    	
    	private String jdbcDriver;
    	private String jdbcUser;
    	private String jdbcPass;
    	private String jdbcUrl;
    	private int initConnCount;
    	private int maxConnCount;
    	private int incrementCount;
    	
    	private Vector<PooledConnection> vector = new Vector<>();
    	
    	public void init(){
    		InputStream in = JdbcPool.class.getClassLoader().getResourceAsStream("club/younge/jdbc/jdbc.properties");
    		Properties properties = new Properties();
    		try {
    			properties.load(in);
    			this.jdbcDriver = properties.getProperty("jdbcDriver");
    			this.jdbcUrl = properties.getProperty("jdbcUrl");
    			this.jdbcPass = properties.getProperty("jdbcPass");
    			this.jdbcUser = properties.getProperty("jdbcUser");
    			this.initConnCount = Integer.parseInt(properties.getProperty("initalConnCount"));
    			this.maxConnCount = Integer.parseInt(properties.getProperty("maxConnCount"));
    			this.incrementCount = Integer.parseInt(properties.getProperty("incrementCount"));
    			Driver driver = (Driver) Class.forName(jdbcDriver).newInstance();
    			DriverManager.registerDriver(driver);  //注册之后，每次创建连接时不用再加载驱动
    			createConnections(initConnCount); //创建初始连接
    		} catch (IOException e) {
    			e.printStackTrace();
    		} catch (InstantiationException e) {
    			e.printStackTrace();
    		} catch (IllegalAccessException e) {
    			e.printStackTrace();
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    	}
    	@Override
    	public  synchronized PooledConnection getConnection() {
    		if (vector.size() <= 0) {
    			System.out.println("The pool has no connection to provided!");
    			throw new RuntimeException("The pool has no connection to provided!");
    		}
    		PooledConnection conn = getActiveConnection();
    		if (conn == null) {
    			createConnections(incrementCount);
    			conn = getActiveConnection();
    			while(conn == null){
    				try {
    					Thread.sleep(300);
    				} catch (InterruptedException e) {
    					e.printStackTrace();
    				}
    				conn = getActiveConnection();
    			}
    		}
    		
    		return conn;
    	}
    	
    	private  PooledConnection getActiveConnection(){
    		for (PooledConnection connection : vector) {
    			if (!connection.isBusy()) {
    				Connection conn = connection.getConn();
    				try {
    					if (!conn.isValid(0)) {
    						conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPass);
    						connection.setConn(conn);
    					}
    				} catch (SQLException e) {
    					e.printStackTrace();
    				}
    				connection.setBusy(true);
    				return connection;
    			}
    		}
    		return null;
    	}
    
    	@Override
    	public void createConnections(int count) {
    		for (int i = 0; i < count; i++) {
    			if (maxConnCount > 0 && vector.size() >= maxConnCount) {
    				System.out.println("The pool connection number has reached max count!");
    				throw new RuntimeException("The pool connection number has reached max count!");
    			}
    			try {
    				Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPass);
    				vector.add(new PooledConnection(false, conn));
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    
    	}
    
    }

自定义连接类，封装数据库的连接类。

/** 
     * @projectName:JavaProxy 
     * @fileName:PooledConnection.java 
     * @packageName:club.younge.jdbc 
     * @date:2016年10月5日下午1:24:23 
     * @copyright (c) 2016, heqy@finansir.nt All Rights Reserved. 
     * 
     */  
      
    package club.younge.jdbc;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import com.mysql.jdbc.Statement;
    
    /** 
     * @className:PooledConnection 
     * @function: TODO ADD FUNCTION.  
     * @reason:   TODO ADD REASON. 
     * @date:     2016年10月5日 下午1:24:23
     * @author   Younge 
     * @version   
     * @since    JDK 1.8 
     * @see       
     */
    public class PooledConnection {
    	private boolean isBusy;
    	private Connection conn;
    	
    	public PooledConnection() {
    	}
    	
    	public PooledConnection(boolean isBusy, Connection conn){
    		this.isBusy = isBusy;
    		this.conn = conn;
    	}
    	
    	public ResultSet querySql(String sql){
    		ResultSet rs = null;
    		try {
    			Statement statement = (Statement) this.conn.createStatement();
    			rs = statement.executeQuery(sql);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return rs;
    	}
    	
    	public boolean isBusy() {
    		return isBusy;
    	}
    
    	public void setBusy(boolean isBusy) {
    		this.isBusy = isBusy;
    	}
    
    	public Connection getConn() {
    		return conn;
    	}
    
    	public void setConn(Connection conn) {
    		this.conn = conn;
    	}
    	public void close(){
    		this.isBusy = false;
    	}
    	
    }

连接池单例模式实现，采用内部类懒加载方式，类加载时线程互斥。

/** 
     * @projectName:JavaProxy 
     * @fileName:DBManager.java 
     * @packageName:club.younge.jdbc 
     * @date:2016年10月5日下午2:27:10 
     * @copyright (c) 2016, heqy@finansir.nt All Rights Reserved. 
     * 
     */  
      
    package club.younge.jdbc;  
    /** 
     * @className:DBManager 
     * @function: TODO ADD FUNCTION.  
     * @reason:   TODO ADD REASON. 
     * @date:     2016年10月5日 下午2:27:10
     * @author   Younge 
     * @version   
     * @since    JDK 1.8 
     * @see       
     */
    public class DBManager {
    	private static final class CreatePool{//类的加载过程是线程互斥的，同时实现懒加载，双检查锁存在问题（创建对象与赋值分开进行）
    		//具体参看博客http://blog.csdn.net/yongaini10/article/details/52738866
    		public static JdbcPool pool = new JdbcPool();
    	}
    	
    	public static JdbcPool getJdbcPool(){
    		return CreatePool.pool;
    	}
    }

测试类：

/** 
     * @projectName:JavaProxy 
     * @fileName:PoolTest.java 
     * @packageName:club.younge.test 
     * @date:2016年10月5日下午3:22:41 
     * @copyright (c) 2016, heqy@finansir.nt All Rights Reserved. 
     * 
     */  
      
    package club.younge.test;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import club.younge.jdbc.DBManager;
    import club.younge.jdbc.JdbcPool;
    import club.younge.jdbc.PooledConnection;
    
    /** 
     * @className:PoolTest 
     * @function: TODO ADD FUNCTION.  
     * @reason:   TODO ADD REASON. 
     * @date:     2016年10月5日 下午3:22:41
     * @author   Younge 
     * @version   
     * @since    JDK 1.8 
     * @see       
     */
    public class PoolTest {
    	private static JdbcPool pool;
    	
    	public static void main(String[] args) throws SQLException {
    		pool = DBManager.getJdbcPool();
    		pool.init();
    		for (int i = 0; i < 100; i++) {
    			new Thread(new Runnable() {
    				@Override
    				public void run() {
    					query();
    				}
    			}).start();
    		}
    		
    		
    		
    	}
    	
    	public synchronized static void query(){
    		System.err.println("\nCurrent thread is " + Thread.currentThread().getName());
    		String sql = "select * from student;";
    		PooledConnection connection = pool.getConnection();
    		ResultSet rs = connection.querySql(sql);
    		try {
    			while(rs.next()){
    				System.out.print(rs.getString("SID") + " ");
    				System.out.print(rs.getString("SNAME") + " ");
    				System.out.print(rs.getString("SAGE") + " ");
    				System.out.print(rs.getString("SSEX") + " \n");
    			}
    			rs.close();
    			connection.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    	}
    }

属性文件，jdbc.properties：

jdbcDriver=com.mysql.jdbc.Driver
    #请自行更改数据库连接信息
    jdbcUrl=jdbc:mysql://127.0.0.1/test
    jdbcUser=root
    jdbcPass=xxxxx 
    
    #initial connection count of jdbc pool
    initalConnCount=10
    
    #maximum connection count of jdbc pool
    maxConnCount=100
    
    #increment count of jdbc pool
    incrementCount=2