JDBC自定义连接池

开发中，"获得连接"和"释放资源"是非常消耗系统资源的，为了解决此类性能问题可以采用连接池技术来共享连接connection。

1、概述

用池来管理connection,这样可以重复使用connection.这样我们就不用创建connection,用池来管理connection对象，当使用完connection对象后，将connection对象归还给池，这样后续还可以从池中获取connection对象，可以重新再利用这个连接对象啦。
java为数据库连接池提供了公共接口：javax.sql.datasource,各个厂商需要让自己的连接池实现这个接口。
常见的连接池：dbcp,c3p0

2、自定义连接池

编写自定义连接池
1、创建连接池并实现接口javax.sql.datasource，并使用接口中的getconnection()方法
2、提供一个集合，用于存放连接，可以采用linkedlist
3、后面程序如果需要，可以调用实现类getconnection(),并从list中获取链接。为保证当前连接只能提供给一个线程使用，所以我们需要将连接先从连接池中移除
4、当用户用完连接后，将连接归还到连接池中

3、自定义连接池采用装饰者设计模式

public class connectionpool implements connection {

    private connection connection;
    private linkedlist<connection> pool;

    public connectionpool(connection connection, linkedlist<connection> pool){
        this.connection=connection;
        this.pool=pool;
    }

    @override
    public preparedstatement preparestatement(string sql) throws sqlexception {
        return connection.preparestatement(sql);
    }

    @override
    public void close() throws sqlexception {
        pool.add(connection);

    }
    @override
    public statement createstatement() throws sqlexception {
        return null;
    }



    @override
    public callablestatement preparecall(string sql) throws sqlexception {
        return null;
    }

    @override
    public string nativesql(string sql) throws sqlexception {
        return null;
    }

    @override
    public void setautocommit(boolean autocommit) throws sqlexception {

    }

    @override
    public boolean getautocommit() throws sqlexception {
        return false;
    }

    @override
    public void commit() throws sqlexception {

    }

    @override
    public void rollback() throws sqlexception {

    }



    @override
    public boolean isclosed() throws sqlexception {
        return false;
    }

    @override
    public databasemetadata getmetadata() throws sqlexception {
        return null;
    }

    @override
    public void setreadonly(boolean readonly) throws sqlexception {

    }

    @override
    public boolean isreadonly() throws sqlexception {
        return false;
    }

    @override
    public void setcatalog(string catalog) throws sqlexception {

    }

    @override
    public string getcatalog() throws sqlexception {
        return null;
    }

    @override
    public void settransactionisolation(int level) throws sqlexception {

    }

    @override
    public int gettransactionisolation() throws sqlexception {
        return 0;
    }

    @override
    public sqlwarning getwarnings() throws sqlexception {
        return null;
    }

    @override
    public void clearwarnings() throws sqlexception {

    }

    @override
    public statement createstatement(int resultsettype, int resultsetconcurrency) throws sqlexception {
        return null;
    }

    @override
    public preparedstatement preparestatement(string sql, int resultsettype, int resultsetconcurrency) throws sqlexception {
        return null;
    }

    @override
    public callablestatement preparecall(string sql, int resultsettype, int resultsetconcurrency) throws sqlexception {
        return null;
    }

    @override
    public map<string, class<?>> gettypemap() throws sqlexception {
        return null;
    }

    @override
    public void settypemap(map<string, class<?>> map) throws sqlexception {

    }

    @override
    public void setholdability(int holdability) throws sqlexception {

    }

    @override
    public int getholdability() throws sqlexception {
        return 0;
    }

    @override
    public savepoint setsavepoint() throws sqlexception {
        return null;
    }

    @override
    public savepoint setsavepoint(string name) throws sqlexception {
        return null;
    }

    @override
    public void rollback(savepoint savepoint) throws sqlexception {

    }

    @override
    public void releasesavepoint(savepoint savepoint) throws sqlexception {

    }

    @override
    public statement createstatement(int resultsettype, int resultsetconcurrency, int resultsetholdability) throws sqlexception {
        return null;
    }

    @override
    public preparedstatement preparestatement(string sql, int resultsettype, int resultsetconcurrency, int resultsetholdability) throws sqlexception {
        return null;
    }

    @override
    public callablestatement preparecall(string sql, int resultsettype, int resultsetconcurrency, int resultsetholdability) throws sqlexception {
        return null;
    }

    @override
    public preparedstatement preparestatement(string sql, int autogeneratedkeys) throws sqlexception {
        return null;
    }

    @override
    public preparedstatement preparestatement(string sql, int[] columnindexes) throws sqlexception {
        return null;
    }

    @override
    public preparedstatement preparestatement(string sql, string[] columnnames) throws sqlexception {
        return null;
    }

    @override
    public clob createclob() throws sqlexception {
        return null;
    }

    @override
    public blob createblob() throws sqlexception {
        return null;
    }

    @override
    public nclob createnclob() throws sqlexception {
        return null;
    }

    @override
    public sqlxml createsqlxml() throws sqlexception {
        return null;
    }

    @override
    public boolean isvalid(int timeout) throws sqlexception {
        return false;
    }

    @override
    public void setclientinfo(string name, string value) throws sqlclientinfoexception {

    }

    @override
    public void setclientinfo(properties properties) throws sqlclientinfoexception {

    }

    @override
    public string getclientinfo(string name) throws sqlexception {
        return null;
    }

    @override
    public properties getclientinfo() throws sqlexception {
        return null;
    }

    @override
    public array createarrayof(string typename, object[] elements) throws sqlexception {
        return null;
    }

    @override
    public struct createstruct(string typename, object[] attributes) throws sqlexception {
        return null;
    }

    @override
    public void setschema(string schema) throws sqlexception {

    }

    @override
    public string getschema() throws sqlexception {
        return null;
    }

    @override
    public void abort(executor executor) throws sqlexception {

    }

    @override
    public void setnetworktimeout(executor executor, int milliseconds) throws sqlexception {

    }

    @override
    public int getnetworktimeout() throws sqlexception {
        return 0;
    }

    @override
    public <t> t unwrap(class<t> iface) throws sqlexception {
        return null;
    }

    @override
    public boolean iswrapperfor(class<?> iface) throws sqlexception {
        return false;
    }
}

datasourcepool

public class datasourcepool implements datasource {
    //1.创建1个容器用于存储connection对象
    private static linkedlist<connection> pool = new linkedlist<connection>();

    //2.创建5个连接放到容器中去
    static{
        for (int i = 0; i < 5; i++) {
            connection conn = jdbcutils.getconnection();
            //放入池子中connection对象已经经过改造了
            connectionpool connectionpool = new connectionpool(conn, pool);
            pool.add(connectionpool);
        }
    }

    /**
     * 重写获取连接的方法
     */
    @override
    public connection getconnection() throws sqlexception {
        connection conn = null;
        //3.使用前先判断
        if(pool.size()==0){
            //4.池子里面没有，我们再创建一些
            for (int i = 0; i < 5; i++) {
                conn = jdbcutils.getconnection();
                //放入池子中connection对象已经经过改造了
                connectionpool connectionpool = new connectionpool(conn, pool);
                pool.add(connectionpool);
            }
        }
        //5.从池子里面获取一个连接对象connection
        conn = pool.remove(0);
        return conn;
    }


    @override
    public connection getconnection(string username, string password) throws sqlexception {
        return null;
    }

    @override
    public <t> t unwrap(class<t> iface) throws sqlexception {
        return null;
    }

    @override
    public boolean iswrapperfor(class<?> iface) throws sqlexception {
        return false;
    }

    @override
    public printwriter getlogwriter() throws sqlexception {
        return null;
    }

    @override
    public void setlogwriter(printwriter out) throws sqlexception {

    }

    @override
    public void setlogintimeout(int seconds) throws sqlexception {

    }

    @override
    public int getlogintimeout() throws sqlexception {
        return 0;
    }

    @override
    public logger getparentlogger() throws sqlfeaturenotsupportedexception {
        return null;
    }
}

测试代码如下

 @test
    public void test1(){
        connection conn = null;
        preparedstatement pstmt = null;
        // 1.创建自定义连接池对象
        datasource datasource = new datasourcepool();
        try {
            // 2.从池子中获取连接
            conn = datasource.getconnection();
            string sql = "insert into user values(?,?)";
            //3.必须在自定义的connection类中重写preparestatement(sql)方法
            pstmt = conn.preparestatement(sql);
            pstmt.setstring(1, "李四");
            pstmt.setstring(2, "1234");
            int rows = pstmt.executeupdate();
            system.out.println("rows:"+rows);
        } catch (exception e) {
            throw new runtimeexception(e);
        } finally {
            jdbcutils.relase(conn, pstmt, null);
        }
    }