自定义一个数据库连接池

①准备工作

package com.yxfyg.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {
    
    static String url = null;
    static String driverClass = null;
    
    static {
        try {
            Properties properties = new Properties();
            InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(is);
            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public static void release(ResultSet rs,Statement st,Connection conn) {
        closeRs(rs);
        closeSt(st);
        closeConn(conn);
    }
    
    public static void release(Statement st,Connection conn) {
        closeSt(st);
        closeConn(conn);
    }
    
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    private static void closeRs(ResultSet rs) {
        try {
            if(rs != null) {
                rs.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            rs = null;
        }
    }
    
    private static void closeSt(Statement st) {
        try {
            if(st != null) {
                st.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            st = null;
        }
    }
    
    private static void closeConn(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            conn = null;
        }
    }
}

url=jdbc:mysql://localhost/bank?user=root&password=root
driverClass=com.mysql.jdbc.Driver

②使用装饰者模式对Connection对象进行包装，改写close方法的作用为归还连接对象到连接池中，其他方法作用不便

public class ConnectionWrap implements Connection{
    
    private Connection conn;
    private List<Connection> list;
    
    public ConnectionWrap(Connection conn,List<Connection> list) {
        this.conn = conn;
        this.list = list;
    }

    @Override
    public void close() throws SQLException {
        list.add(this);
    }
    
    ...
}

③自定义连接池（实现规范DataSource接口）

package com.yxfyg.ds;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

import javax.sql.DataSource;

import com.yxfyg.util.ConnectionWrap;
import com.yxfyg.util.JDBCUtil;

public class MyDataSource implements DataSource{
    
    private List<Connection> list = new LinkedList<Connection>();
    
    private static MyDataSource instance = new MyDataSource();
    
    private MyDataSource(){
        for(int i=0;i<20;i++) {
            Connection conn = JDBCUtil.getConn();
            list.add(conn);
        }
    }
    
    public static MyDataSource getInstance() {
        return instance;
    }
    
    @Override
    public Connection getConnection() throws SQLException {
        if(list.size() == 0) {
            for(int i=0;i<3;i++) {
                Connection conn = JDBCUtil.getConn();
                conn = new ConnectionWrap(conn, list);
                list.add(conn);
            }
        }
        
        return list.remove(0);
    }
    
    @Override
    public PrintWriter getLogWriter() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public int getLoginTimeout() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

}

④测试使用

package com.yxfyg.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.junit.Test;

import com.yxfyg.ds.MyDataSource;
import com.yxfyg.util.JDBCUtil;

public class MainTest {
    
    @Test
    public void TestMyDataSource() {
        
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        
        try {
            conn = MyDataSource.getInstance().getConnection();
            String sql = "select * from account where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            rs = ps.executeQuery();
            while(rs.next()) {
                String name = rs.getString("name");
                int money = rs.getInt("money");
                System.out.println("name:" + name + " money:" + money);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.release(rs, ps, conn);
        }
        
    }
}