JDBC连接池-自定义连接池

时间：2023-11-23 19:43:08

JDBC连接池

java JDBC连接中用到Connection   在每次对数据进行增删查改 都要 开启  、关闭  ，在实例开发项目中 ，浪费了很大的资源 ，以下是之前连接JDBC的案例

JDBC连接池-自定义连接池

package com.jdbc.connection;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Properties;

public class jdbcConnection {

    private static String driver;

    private static String url;

    private static String username;

    private static String password;

    /**

     * 静态代码块加载配置文件信息

     */

    static {

        try {

            // 1.通过当前类获取类加载器

            ClassLoader classLoader = jdbcConnection.class.getClassLoader();

            // 2.通过类加载器的方法获得一个输入流

            InputStream is = classLoader.getResourceAsStream("db.properties");

            // 3.创建一个properties对象

            Properties props = new Properties();

            // 4.加载输入流

            props.load(is);

            // 5.获取相关参数的值

            driver = props.getProperty("driver");

            url = props.getProperty("url");

            username = props.getProperty("username");

            password = props.getProperty("password");

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    /**

     * 获取连接方法

     *

     */

    public static Connection getConnection() {

        Connection conn = null;

        try {

            Class.forName(driver);

            conn = DriverManager.getConnection(url, username, password);

        } catch (Exception e) {

            e.printStackTrace();

        }

        return conn;

    }

    /**

     * 释放资源方法

     *

     * @param conn

     * @param pstmt

     * @param rs

     */

    public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {

        if (rs != null) {

            try {

                rs.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        if (pstmt != null) {

            try {

                pstmt.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

        if (conn != null) {

            try {

                conn.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

考虑节省资源  ，可以创建一个connection连接池 ，每次使用connection连接时 ，直接从连接池中取出一个连接，不用时再放回连接池 ，代替之前的关闭连接  。  java提供了javax.sql.DataSource 接口 ，各大连接池厂商直接实现这个接口

常见的连接池有C3P0  DBCP连接池

自定义连接池

在上面代码的基础上添加一个自定义连接池，减少对资源的浪费

1.创建一个类实现javax.jdbc.dataSource 类

JDBC连接池-自定义连接池

2.创建一个 存放多个连接的容器 ，因为需要经常删除 ，查找  。 这里用LinkedList   容器

JDBC连接池-自定义连接池

3.在MyDataSource类中静态代码块中创建多个连接，添加到连接池容器中  (这里面创建连接可以直接使用 com.jdbc.connection包中的jdbcConnection 中的静态方法getConnection)

     static{

         for (int i = 0; i < 5; i++) {

             Connection conn=jdbcConnection.getConnection();

         }

     }

4.重写Datasource 方法中的 getConnection()  方法    ，

JDBC连接池-自定义连接池

注：在使用pool这个变量之前需要判断pool是否为空 ，若为空需要重新创建

4.在MyDataSource方法中添加  一个 把当前连接归还给连接池的方法

public void addBack(Connection conn){

        pool.add(conn);

    }

具体操作代码如下

package com.jdbc.dataSource;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.SQLFeatureNotSupportedException;

import java.util.LinkedList;

import java.util.logging.Logger;

import javax.sql.DataSource;

import com.jdbc.connection.jdbcConnection;

public class MyDataSource implements DataSource {

    private static LinkedList<Connection> pool=new LinkedList<Connection>();

    static{

        for (int i = 0; i < 5; i++) {

            Connection conn=jdbcConnection.getConnection();

            pool.add(conn);

        }

    }

    @Override

    public Connection getConnection() throws SQLException {

        Connection conn=null;

        if (pool.size()==0) {

            for (int i = 0; i < 5; i++) {

                conn=jdbcConnection.getConnection();

                pool.add(conn);

            }

        }

        conn=pool.remove(0);

        return conn;

    }

    public void addBack(Connection conn){

        pool.add(conn);

    }

    @Override

    public PrintWriter getLogWriter() throws SQLException {

        return null;

    }

    @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 void setLogWriter(PrintWriter arg0) throws SQLException {

        // TODO Auto-generated method stub

    }

    @Override

    public void setLoginTimeout(int arg0) throws SQLException {

        // TODO Auto-generated method stub

    }

    @Override

    public boolean isWrapperFor(Class<?> arg0) throws SQLException {

        // TODO Auto-generated method stub

        return false;

    }

    @Override

    public <T> T unwrap(Class<T> arg0) throws SQLException {

        // TODO Auto-generated method stub

        return null;

    }

    @Override

    public Connection getConnection(String arg0, String arg1)

            throws SQLException {

        return null;

    }

}

连接池测试类

 package com.jdbc.Util;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.SQLException;

 import org.junit.Test;

 import com.jdbc.dataSource.MyDataSource;

 public class JdbcTest {

     @Test

     public void test1(){

         Connection conn=null;

         PreparedStatement pstm=null;

         MyDataSource  myDataSource=new MyDataSource();

         try {

             conn=myDataSource.getConnection();

         } catch (SQLException e) {

             e.printStackTrace();

         }

     }

 }

自定义连接池：方法增强

装饰者模式

  1. 创建类B，并实现接口A
  2. 提供类B的构造方法，参数类型为A，用于接收A接口的其他实现类（c）
  3. 给类B添加类型为A的成员变量，用于存放A接口的其他实现类
  4. 增加需要的方法
  5. 实现不需要增强的方法，方法体  重点调用成员变量存放的其他实现类对应的方法

翻译过来就是创建类 MyConnection 实现Connection接口

提供MyConnection的构造方法，参数类型为Connection的、  MyDataSource 类中的连接池对象list

。。。等

 package com.jdbc.dataSource;

 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.NClob;

 import java.sql.PreparedStatement;

 import java.sql.SQLClientInfoException;

 import java.sql.SQLException;

 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.Map;

 import java.util.Properties;

 import java.util.concurrent.Executor;

 public class MyConnection implements Connection {

     private  Connection conn;

     private  LinkedList<Connection> pool;

     public MyConnection(Connection conn ,LinkedList<Connection> pool) {

         this.pool=pool;

         this.conn = conn;

     }

     @Override

     public void close() throws SQLException {

         pool.add(conn);

     }

     @Override

     public boolean isWrapperFor(Class<?> arg0) throws SQLException {

         // TODO Auto-generated method stub

         return false;

     }

     @Override

     public <T> T unwrap(Class<T> arg0) 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 clearWarnings() throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void commit() throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public Array createArrayOf(String typeName, Object[] elements)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Blob createBlob() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Clob createClob() 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 Statement createStatement() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Statement createStatement(int resultSetType, int resultSetConcurrency)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Statement createStatement(int resultSetType,

             int resultSetConcurrency, int resultSetHoldability)

             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 boolean getAutoCommit() throws SQLException {

         // TODO Auto-generated method stub

         return false;

     }

     @Override

     public String getCatalog() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Properties getClientInfo() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public String getClientInfo(String name) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public int getHoldability() throws SQLException {

         // TODO Auto-generated method stub

         return 0;

     }

     @Override

     public DatabaseMetaData getMetaData() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public int getNetworkTimeout() throws SQLException {

         // TODO Auto-generated method stub

         return 0;

     }

     @Override

     public String getSchema() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public int getTransactionIsolation() throws SQLException {

         // TODO Auto-generated method stub

         return 0;

     }

     @Override

     public Map<String, Class<?>> getTypeMap() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public SQLWarning getWarnings() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public boolean isClosed() throws SQLException {

         // TODO Auto-generated method stub

         return false;

     }

     @Override

     public boolean isReadOnly() throws SQLException {

         // TODO Auto-generated method stub

         return false;

     }

     @Override

     public boolean isValid(int timeout) throws SQLException {

         // TODO Auto-generated method stub

         return false;

     }

     @Override

     public String nativeSQL(String sql) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public CallableStatement prepareCall(String sql) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public CallableStatement prepareCall(String sql, int resultSetType,

             int resultSetConcurrency) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public CallableStatement prepareCall(String sql, int resultSetType,

             int resultSetConcurrency, int resultSetHoldability)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public PreparedStatement prepareStatement(String sql) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public PreparedStatement prepareStatement(String sql, int[] columnIndexes)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public PreparedStatement prepareStatement(String sql, String[] columnNames)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public PreparedStatement prepareStatement(String sql, int resultSetType,

             int resultSetConcurrency) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public PreparedStatement prepareStatement(String sql, int resultSetType,

             int resultSetConcurrency, int resultSetHoldability)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public void releaseSavepoint(Savepoint savepoint) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void rollback() throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void rollback(Savepoint savepoint) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setAutoCommit(boolean autoCommit) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setCatalog(String catalog) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setClientInfo(Properties properties)

             throws SQLClientInfoException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setClientInfo(String name, String value)

             throws SQLClientInfoException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setHoldability(int holdability) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setNetworkTimeout(Executor executor, int milliseconds)

             throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setReadOnly(boolean readOnly) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public Savepoint setSavepoint() throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Savepoint setSavepoint(String name) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public void setSchema(String schema) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setTransactionIsolation(int level) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setTypeMap(Map<String, Class<?>> map) throws SQLException {

         // TODO Auto-generated method stub

     }

 }

更改后的myDataSource类为

 package com.jdbc.dataSource;

 import java.io.PrintWriter;

 import java.sql.Connection;

 import java.sql.SQLException;

 import java.sql.SQLFeatureNotSupportedException;

 import java.util.LinkedList;

 import java.util.logging.Logger;

 import javax.sql.DataSource;

 import com.jdbc.connection.jdbcConnection;

 public class MyDataSource1  implements DataSource{

     private static LinkedList<Connection> pool=new LinkedList<Connection>();

     static{

         for (int i = 0; i < 5; i++) {

             Connection conn=jdbcConnection.getConnection();

             pool.add(conn);

         }

     }

     @Override

     public Connection getConnection() throws SQLException {

         if (pool.size()==0) {

             for (int i = 0; i < 5; i++) {

                 Connection conn=jdbcConnection.getConnection();

                 //MyConnection 类实现了Connection接口

                 //将创建的连接用装饰者类包装

                 MyConnection myConnection=new MyConnection(conn, pool);

                 //pool.add(conn);

                 pool.add(myConnection);//添加被装饰后的connection对象

             }

         }

       Connection conn=pool.remove(0);

         return conn;

     }

     @Override

     public PrintWriter getLogWriter() throws SQLException {

         return null;

     }

     @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 void setLogWriter(PrintWriter arg0) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public void setLoginTimeout(int arg0) throws SQLException {

         // TODO Auto-generated method stub

     }

     @Override

     public boolean isWrapperFor(Class<?> iface) throws SQLException {

         // TODO Auto-generated method stub

         return false;

     }

     @Override

     public <T> T unwrap(Class<T> iface) throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

     @Override

     public Connection getConnection(String username, String password)

             throws SQLException {

         // TODO Auto-generated method stub

         return null;

     }

 }

测试

package com.study.jdbc.test;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import org.junit.Test;

import com.study.jdbc.Utils.JDBCUtils_V3;

import com.study.jdbc.dataSource.MyDataSource;

import com.study.jdbc.dataSource.MyDataSource1;

public class TestMyDataSource {

    /*

     * 添加用户

     * 使用改造过的connection

     *

     */

    @Test

    public void dataTestAddUser1(){

        Connection conn=null;

        PreparedStatement pstm=null;

        MyDataSource1 dataSource=new MyDataSource1();

            try {

                conn=dataSource.getConnection();

                System.out.println(conn);

                String sql="insert into user values(10,?,?)";

                pstm=conn.prepareStatement(sql);

                pstm.setString(1,"吕布1");

                pstm.setString(2,"点错1");

                int rows =pstm.executeUpdate();

                if (rows>0) {

                     System.out.println("ok");

                }else {

                    System.out.println("no");

                }

            } catch (SQLException e) {

                e.printStackTrace();

            }finally{

                //release中conn.close()方法已经被装饰

                JDBCUtils_V3.release(conn, pstm, null);

            }

    }

}

需要注意的是：MyConection 实现Connection接口时重新了Connection所有的方法，在使用 被MyConnection类修饰的Connecting连接的某些方法要注意需要重写MyConnection类中的这些方法

比如：当使用prepareStatement(sql); 时  要注意MyConnection中的prepareStatement(String sql)方法

MyConnection 在未处理之前的prepareStatement(String sql)方法如下图所示

JDBC连接池-自定义连接池

不重写直接使用 返回的结果永远是null  ，重写后代码：

     @Override

     public PreparedStatement prepareStatement(String sql) throws SQLException {

         // TODO Auto-generated method stub

         return conn.prepareStatement(sql);

     }

相关文章