JDBC-java连接Mysql数据库，自定义连接池防止内存不足，提高效率。

小灰灰 2023-02-10 05:03 24阅读 0赞

思路：  
第一步：创建类A实现DataSource,重写getConnection方法。  
第二步：在A类中，构造函数初始化连接池，用list<Connection》集合存储  
第三步：在重写getConnection方法中，用list.remove(0)的方法返回一个Connection连接给调用者。  
第四步：由于remove方法是把连接从集合中移除，所以我们要想办法回收资源，接下来面向接口的方法，采用装饰者模式，重写connection接口的close方法，使close方法不是关闭资源而是回收资源进入list集合。  
第五步：重写close方法后，我们在A类中的getConnection方法中，返回调用者的connection附加此属性就可以了。  
上代码：  
自定义线程池类，实现DataSource类，重写getConnection方法

/*
    * 功能：
    * 自定义连接池，防止与数据库的大量连接引发内存资源不足。
    * 我们可以定义一定数量的连接池，当一个用户使用完成之后，连接对象又可以重复
    * 回到连接池中等待使用，而不需要重复创建。
    * */
    import javax.sql.DataSource;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.SQLFeatureNotSupportedException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.logging.Logger;
    
    public class connectionPools implements DataSource {
        List<Connection> list = new ArrayList<Connection>();
        JDBCUtils jdbcUtils = new JDBCUtils();
        public connectionPools(){
            for (int i=0;i<5;i++){
                //我们用ArrayList集合保存5个连接，作为连接池资源
                list.add(jdbcUtils.getConnection());
            }
        }
    
        //重写getConnection方法，此方法就是从连接池中取出一个连接
        @Override
        public Connection getConnection() throws SQLException {
            //从集合中取出第一个连接
            Connection connection = list.remove(0);
            //把此连接作为参数传递增强Connection类，返回close方法是回收资源的connection对象
            MyConnection myConnection = new MyConnection(connection,list);
            //返回此对象
            return myConnection;
        }
    /*
        public void addBack(Connection conn){
            list.add(conn);
        }
    */
    
        @Override
        public Connection getConnection(String s, String s1) throws SQLException {
            return null;
        }
    
        @Override
        public PrintWriter getLogWriter() throws SQLException {
            return null;
        }
    
        @Override
        public void setLogWriter(PrintWriter printWriter) throws SQLException {
    
        }
    
        @Override
        public void setLoginTimeout(int i) throws SQLException {
    
        }
    
        @Override
        public int getLoginTimeout() throws SQLException {
            return 0;
        }
    
        @Override
        public Logger getParentLogger() throws SQLFeatureNotSupportedException {
            return null;
        }
    
        @Override
        public <T> T unwrap(Class<T> aClass) throws SQLException {
            return null;
        }
    
        @Override
        public boolean isWrapperFor(Class<?> aClass) throws SQLException {
            return false;
        }
    }

装饰者模式，增强修改了close方法的Connection实现类，这里我们继承了我们的模板类，模板类的功能没有任何增强，只是方便书写而存在的。

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    public class MyConnection extends ConnectionModel {
        private Connection conn;
        private List<Connection> connList;
        public MyConnection(Connection conn,List<Connection> connList){
            //装饰者模式，初始化之前需要调用父类的初始化，以保持其他功能的完整性。
            super(conn);
            this.conn = conn;
            this.connList = connList;
    
        }
    
      //重写需要增强的方法
        @Override
        public void close() throws SQLException {
            //父类的close方法是关闭资源，现在我们需要该方法是回收资源
    //        super.close();
            //重现添加进list集合即可。
            connList.add(conn);
        }
    }

模板类

/*
    * 作用：
    * 实现了Connection接口的实现类，里面原封不动的重写了该接口的所有
    * 方法，该类是为了方便其他类为增强connection接口中的某个方法而不需要重写所有方法
    * 而存在的
    * 原理：
    * 采用装饰者模式，这个功能相同的增强类中初始化时传入被增强类的引用，也就是connection对象
    *
    * */
    
    import java.sql.*;
    import java.util.Map;
    import java.util.Properties;
    import java.util.concurrent.Executor;
    
    public class ConnectionModel implements Connection {
        //装饰者模式，获得被增强类的引用，然后用该引用调用其他所有不需要增强的方法
        //这样就达到了复制的作用，其他增强类如果要增强某个方法，只要继承该类，重写需要
        //修改的方法即可。
        private Connection conn;
        public ConnectionModel(Connection conn){
            super();
            this.conn = conn;
        }
        @Override
        public void close() throws SQLException {
            conn.close();
        }
        @Override
        public Statement createStatement() throws SQLException {
            return conn.createStatement();
        }
    
        @Override
        public PreparedStatement prepareStatement(String s) throws SQLException {
            return conn.prepareStatement(s);
        }
    
        @Override
        public CallableStatement prepareCall(String s) throws SQLException {
            return conn.prepareCall(s);
        }
    
        @Override
        public String nativeSQL(String s) throws SQLException {
            return conn.nativeSQL(s);
        }
    
        @Override
        public void setAutoCommit(boolean b) throws SQLException {
            conn.setAutoCommit(b);
        }
    
        @Override
        public boolean getAutoCommit() throws SQLException {
            return conn.getAutoCommit();
        }
    
        @Override
        public void commit() throws SQLException {
            conn.commit();
        }
    
        @Override
        public void rollback() throws SQLException {
            conn.rollback();
        }
    
    
    
        @Override
        public boolean isClosed() throws SQLException {
            return conn.isClosed();
        }
    
        @Override
        public DatabaseMetaData getMetaData() throws SQLException {
            return conn.getMetaData();
        }
    
        @Override
        public void setReadOnly(boolean b) throws SQLException {
            conn.setReadOnly(b);
        }
    
        @Override
        public boolean isReadOnly() throws SQLException {
            return conn.isReadOnly();
        }
    
        @Override
        public void setCatalog(String s) throws SQLException {
            conn.setCatalog(s);
        }
    
        @Override
        public String getCatalog() throws SQLException {
            return conn.getCatalog();
        }
    
        @Override
        public void setTransactionIsolation(int i) throws SQLException {
            conn.setTransactionIsolation(i);
        }
    
        @Override
        public int getTransactionIsolation() throws SQLException {
            return conn.getTransactionIsolation();
        }
    
        @Override
        public SQLWarning getWarnings() throws SQLException {
            return conn.getWarnings();
        }
    
        @Override
        public void clearWarnings() throws SQLException {
            conn.clearWarnings();
        }
    
        @Override
        public Statement createStatement(int i, int i1) throws SQLException {
            return conn.createStatement(i,i1);
        }
    
        @Override
        public PreparedStatement prepareStatement(String s, int i, int i1) throws SQLException {
            return conn.prepareStatement(s,i,i1);
        }
    
        @Override
        public CallableStatement prepareCall(String s, int i, int i1) throws SQLException {
            return conn.prepareCall(s,i,i1);
        }
    
        @Override
        public Map<String, Class<?>> getTypeMap() throws SQLException {
            return conn.getTypeMap();
        }
    
        @Override
        public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
            conn.setTypeMap(map);
        }
    
        @Override
        public void setHoldability(int i) throws SQLException {
            conn.setHoldability(i);
        }
    
        @Override
        public int getHoldability() throws SQLException {
            return conn.getHoldability();
        }
    
        @Override
        public Savepoint setSavepoint() throws SQLException {
            return conn.setSavepoint();
        }
    
        @Override
        public Savepoint setSavepoint(String s) throws SQLException {
            return conn.setSavepoint(s);
        }
    
        @Override
        public void rollback(Savepoint savepoint) throws SQLException {
            conn.rollback(savepoint);
        }
    
        @Override
        public void releaseSavepoint(Savepoint savepoint) throws SQLException {
            conn.releaseSavepoint(savepoint);
        }
    
        @Override
        public Statement createStatement(int i, int i1, int i2) throws SQLException {
            return conn.createStatement(i,i1,i2);
        }
    
        @Override
        public PreparedStatement prepareStatement(String s, int i, int i1, int i2) throws SQLException {
            return conn.prepareStatement(s,i,i1,i2);
        }
    
        @Override
        public CallableStatement prepareCall(String s, int i, int i1, int i2) throws SQLException {
            return conn.prepareCall(s,i,i1,i2);
        }
    
        @Override
        public PreparedStatement prepareStatement(String s, int i) throws SQLException {
            return conn.prepareStatement(s,i);
        }
    
        @Override
        public PreparedStatement prepareStatement(String s, int[] ints) throws SQLException {
            return conn.prepareStatement(s,ints);
        }
    
        @Override
        public PreparedStatement prepareStatement(String s, String[] strings) throws SQLException {
            return conn.prepareStatement(s,strings);
        }
    
        @Override
        public Clob createClob() throws SQLException {
            return conn.createClob();
        }
    
        @Override
        public Blob createBlob() throws SQLException {
            return conn.createBlob();
        }
    
        @Override
        public NClob createNClob() throws SQLException {
            return conn.createNClob();
        }
    
        @Override
        public SQLXML createSQLXML() throws SQLException {
            return conn.createSQLXML();
        }
    
        @Override
        public boolean isValid(int i) throws SQLException {
            return conn.isValid(i);
        }
    
        @Override
        public void setClientInfo(String s, String s1) throws SQLClientInfoException {
            conn.setClientInfo(s,s1);
        }
    
        @Override
        public void setClientInfo(Properties properties) throws SQLClientInfoException {
            conn.setClientInfo(properties);
        }
    
        @Override
        public String getClientInfo(String s) throws SQLException {
            return conn.getClientInfo(s);
        }
    
        @Override
        public Properties getClientInfo() throws SQLException {
            return conn.getClientInfo();
        }
    
        @Override
        public Array createArrayOf(String s, Object[] objects) throws SQLException {
            return conn.createArrayOf(s,objects);
        }
    
        @Override
        public Struct createStruct(String s, Object[] objects) throws SQLException {
            return conn.createStruct(s,objects);
        }
    
        @Override
        public void setSchema(String s) throws SQLException {
            conn.setSchema(s);
        }
    
        @Override
        public String getSchema() throws SQLException {
            return conn.getSchema();
        }
    
        @Override
        public void abort(Executor executor) throws SQLException {
            conn.abort(executor);
        }
    
        @Override
        public void setNetworkTimeout(Executor executor, int i) throws SQLException {
            conn.setNetworkTimeout(executor,i);
        }
    
        @Override
        public int getNetworkTimeout() throws SQLException {
            return conn.getNetworkTimeout();
        }
    
        @Override
        public <T> T unwrap(Class<T> aClass) throws SQLException {
            return conn.unwrap(aClass);
        }
    
        @Override
        public boolean isWrapperFor(Class<?> aClass) throws SQLException {
            return conn.isWrapperFor(aClass);
        }
    }

最后我们测试类

import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    public class connectionPoolsDemo {
        public static void main(String[] args) {
            Connection conn = null;
            PreparedStatement ppstatement = null;
            ResultSet resultSet = null;
            //自定义连接池对象定义
            connectionPools connPools = null;
            //工具类，资源释放用
            JDBCUtils jdbcUtils = new JDBCUtils();
            try{
                //创建连接池
                connPools = new connectionPools();
                //获取具有close为回收资源的一个connection对象
                conn = connPools.getConnection();
                String sql = "select * from student";
                ppstatement = conn.prepareStatement(sql);
                //执行sql语句
                resultSet = ppstatement.executeQuery();
                while(resultSet.next()){
                    String sname = resultSet.getString("sname");
                    String spassword = resultSet.getString("spassword");
                    System.out.println(sname+" "+spassword);
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                //conn的close方法是回收资源，而不是关闭资源。
                jdbcUtils.relResource(conn,ppstatement,resultSet);
            }
        }
    }

结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200520101137232.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpZXJlbmJpamkyMQ==,size_16,color_FFFFFF,t_70