数据库连接池

浏览数：29 / 时间：2015年06月12日

编写连接池需实现java.sql.DataSource接口。DataSource接口中定义了两个重载的getConnection方法：

　　•Connection getConnection()

　　•Connection getConnection(String username, String password)

实现DataSource接口，并实现连接池功能的步骤：

　　•在DataSource构造函数中批量创建与数据库的连接，并把创建的连接加入LinkedList对象中。

　　•实现getConnection方法，让getConnection方法每次调用时，从LinkedList中取一个Connection返回给用户。

当用户使用完Connection，调用Connection.close()方法时，Collection对象应保证将自己返回到LinkedList中,而不要把conn还给数据库。

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day16
username=root
password=lcp8090

JdbcPool.java

package cn.lcp.demo;

import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
import java.util.logging.Logger;

import javax.sql.DataSource;

public class JdbcPool implements DataSource {

    // 用集合保存一批连接。
    private static LinkedList<Connection> list = new LinkedList<Connection>();

    // 静态代码块，向数据库要连接
    static {
        try {
            InputStream in = JdbcPool.class.getClassLoader()
                    .getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(in);

            // 获取驱动等信息
            String driver = prop.getProperty("driver");
            String url = prop.getProperty("url");
            String username = prop.getProperty("username");
            String password = prop.getProperty("password");

            // 装载驱动
            Class.forName(driver);

            // 向数据库要多少个连接
            for (int i = 0; i < 10; i++) {
                // 要到一个连接
                Connection conn = DriverManager.getConnection(url, username,
                        password);
                System.out.println("获取到了链接" + conn);
                // 把链接到集合里面去了
                list.add(conn);
            }
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    // 从池里面获取连接


/*
 * 1.写一个子类，覆盖close方法--若里面封装有信息，就不要使用此方法 
 * 2、写一个connection的包装类，增强close方法
 * 3、用动态代理，返回一个代理对象出去，拦截close方法的调用，对close进行增强
 */
    @Override
    public Connection getConnection() throws SQLException {
        // proxyConnection.commit() proxyConnection.rollback
        if (list.size() > 0) {
            //动态设计模式
            final Connection conn = list.removeFirst(); // myconnection.commit
            System.out.println("池大小是" + list.size());
            //Proxy创建一个动态代理对象
            return (Connection) Proxy.newProxyInstance(JdbcPool.class
                    .getClassLoader(), conn.getClass().getInterfaces(),
                    new InvocationHandler() {

                        public Object invoke(Object proxy, Method method,
                                Object[] args) throws Throwable {
                            if (!method.getName().equals("close")) {
                                return method.invoke(conn, args);
                            } else {
                                list.add(conn);
                                System.out.println(conn + "被还给池了！！");
                                System.out.println("池大小为" + list.size());
                                return null;
                            }

                        }

                    });

        } else {
            throw new RuntimeException("对不起，数据库忙");
        }

    }

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

    public PrintWriter getLogWriter() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

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

    public void setLogWriter(PrintWriter arg0) throws SQLException {
        // TODO Auto-generated method stub

    }

    public void setLoginTimeout(int arg0) throws SQLException {
        // TODO Auto-generated method stub

    }

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




/*
用包装设计模式对某个对象进行增强
1.写一个类，实现与被增强对象(mysql的connection)相同的接口
2、定义一个变量，指向被增强对象
3、定义一个构造方法，接收被增强对象
4、覆盖想增强的方法
5、对于不想增强的方法，直接调用被增强对象的方法
Connection的包装类
 */

//包装设计模式
class MyConnection implements Connection{
    
    
    private Connection conn; //记住被增强的对象
    private List pool;//记住数据库连接池
    //构造函数接受Connection
    public MyConnection(Connection conn,List pool){
        this.conn = conn;
        this.pool = pool;   //得到那个数据库连接池
    }
    //调用自己写的close
    public void close() throws SQLException {
        pool.add(conn);   //把增强的数据还到连接池里面去
    }
    
    public void clearWarnings() throws SQLException {
        this.conn.clearWarnings();   //对于不想增强的方法，直接调用被增强对象的方法   
        
    }
    
    public void commit() throws SQLException {
        this.conn.commit();
        
    }
    public Statement createStatement() throws SQLException {
        return this.conn.createStatement();
    }
    public Statement createStatement(int resultSetType, int resultSetConcurrency)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public Statement createStatement(int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public boolean getAutoCommit() throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }
    public String getCatalog() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public int getHoldability() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }
    public DatabaseMetaData getMetaData() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public int getTransactionIsolation() throws SQLException {
        // TODO Auto-generated method stub
        return 0;
    }
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public SQLWarning getWarnings() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public boolean isClosed() throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }
    public boolean isReadOnly() throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }
    public String nativeSQL(String sql) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public CallableStatement prepareCall(String sql) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public CallableStatement prepareCall(String sql, int resultSetType,
            int resultSetConcurrency) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public CallableStatement prepareCall(String sql, int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public PreparedStatement prepareStatement(String sql, String[] columnNames)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public PreparedStatement prepareStatement(String sql, int resultSetType,
            int resultSetConcurrency) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public PreparedStatement prepareStatement(String sql, int resultSetType,
            int resultSetConcurrency, int resultSetHoldability)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void rollback() throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void rollback(Savepoint savepoint) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void setCatalog(String catalog) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void setHoldability(int holdability) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void setReadOnly(boolean readOnly) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public Savepoint setSavepoint() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public Savepoint setSavepoint(String name) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
    public void setTransactionIsolation(int level) throws SQLException {
        // TODO Auto-generated method stub
        
    }
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @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 Clob createClob() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Blob createBlob() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public NClob createNClob() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        // TODO Auto-generated method stub
        return false;
    }

    @Override
    public void setClientInfo(String name, String value)
            throws SQLClientInfoException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void setClientInfo(Properties properties)
            throws SQLClientInfoException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public String getSchema() throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds)
            throws SQLException {
        // TODO Auto-generated method stub
        
    }

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

}

JdbcUtils.java

package cn.lcp.utils;

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

import cn.lcp.demo.JdbcPool;

public class JdbcUtils {
    
    private static JdbcPool  pool = new JdbcPool();

    public static Connection getConnection() throws SQLException{
        return pool.getConnection();
    }
public static void release(Connection conn,Statement st,ResultSet rs){
        
        
        if(rs!=null){
            try{
                rs.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;

        }
        if(st!=null){
            try{
                st.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            
        }
        
        if(conn!=null){
            try{
                conn.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
            
        }
    }
    
}

demo.java

package cn.lcp.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;

import cn.lcp.utils.JdbcUtils;

//模拟转账
public class demo {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        Savepoint sp = null;
        try {
            conn = JdbcUtils.getConnection();
            // 执行多条sql语句，开启事务
            conn.setAutoCommit(false); // start transaction

            String sql1 = "update account set money=money-100 where name=‘aaa‘";
            // 预编译sql语句
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            
            //设置事务回滚点
            sp = conn.setSavepoint();
            
            String sql2 = "update account set money=money+100 where name=‘bbb‘";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();
            
            int i = 10 / 0;  //这里出现异常，到catch里面执行回滚
            
            String sql3 = "update account set money=money+100 where name=‘ccc‘";
            st = conn.prepareStatement(sql3);
            st.executeUpdate();

            //提交事务
            conn.commit();

            System.out.println("成功！！！"); // log4j

        } catch (Exception e) {
            
            try {
                conn.rollback();  // 出现异常自动回滚到事务回滚点
                conn.commit();    //提交事务---
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

l使用动态代理技术构建连接池中的connection

proxyConn = (Connection) Proxy.newProxyInstance(this.getClass()
            .getClassLoader(), conn.getClass().getInterfaces(),
            new InvocationHandler() {
        //此处为内部类，当close方法被调用时将conn还回池中,其它方法直接执行
            public Object invoke(Object proxy, Method method,
                      Object[] args) throws Throwable {
                if (method.getName().equals("close")) {
                    pool.addLast(conn);
                    return null;
            }
            return method.invoke(conn, args);
        }
    });