使用装饰着设计模式开发自定义jdbc连接池

1.数据库配置文件db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web08?useUnicode=true&characterEncoding=utf8
username=root
password=root

2:获取连接和释放资源的方法

package cn.itheima.jdbc.utils;

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;
import java.util.ResourceBundle;

/**
 * 获取连接和释放资源的方法
 * @author Administrator
 *
 */
public class JDBCUtils_V3 {

    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static{
        try {
            //通过类加载器的方法获得文件输入流
            InputStream in = JDBCUtils_V3.class.getClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(in);
            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /**
     * 获取连接方法
     * @return
     */
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(driver);
            String url = "jdbc:mysql://localhost:3306/web08";
            conn = DriverManager.getConnection(url,username , password);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }


    public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
        if(rs!=null)
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        if(pstmt!=null)
            try {
                pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        if(conn!=null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }

}

3：使用装饰者模式重写获取连接的方法和关闭连接的close()方法

package cn.itheima.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.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

//1.实现同一个接口Connection
public class MyConnection implements Connection{
    //3.定义一个变量
    private Connection conn;
    private LinkedList<Connection> pool;
    //2.编写一个构造方法(参数使用了面向对象的多态)
    public MyConnection(Connection conn,LinkedList<Connection> pool) {
        this.conn = conn;
        this.pool = pool;
    }
    //4.书写需要增强的方法
    @Override
    public void close() throws SQLException {
        pool.add(conn);
    }

    //5.！！！此方法必须覆盖，否则会出现空指针异常
    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    ...其他实现类
}

4：创建连接池并获取连接

package cn.itheima.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 cn.itheima.jdbc.utils.JDBCUtils_V3;

public class MyDataSource2 implements DataSource {

    //1.创建一个容器用于存储Connection对象
    private static LinkedList<Connection> pool = new LinkedList<Connection>();
    //2.创建5个连接放到容器中去
    static{
        for (int i = 0; i < 5; i++) {
            Connection conn = JDBCUtils_V3.getConnection();
            //放入池子中的Connection对象已经经过改造
            **MyConnection myconn = new MyConnection(conn, pool);**
            pool.add(myconn);
        }
    }

    /**
     * 重写获取连接的方法
     */
    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        //3.使用前先判断
        if(pool.size()==0){
            //4.再创建5个连接
            for (int i = 0; i < 5; i++) {
                conn = JDBCUtils_V3.getConnection();
                **MyConnection myconn = new MyConnection(conn, pool);**
                pool.add(myconn);
            }
        }
        //5.从池子里获取连接对象Connection
        conn = pool.remove(0);

        return conn;
    }

    /**
     * 归还连接对象到连接池
     */
    public void backConnection(Connection conn){
        pool.add(conn);
    }

}





}

5：编写测试类

package cn.itheima.jdbc.test;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

import cn.itheima.jdbc.dataSource.MyDataSource;
import cn.itheima.jdbc.dataSource.MyDataSource2;
import cn.itheima.jdbc.utils.JDBCUtils_V3;

public class TestMyDataSource2 {

    /**
     * 添加用户，使用改造的Connection方法
     */
    @Test
    public void addUser(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        //1.创建自定义连接池
        MyDataSource2 dataSource = new MyDataSource2();
        try {
            //2.从池子中获取连接
            conn = dataSource.getConnection();
            String sql = "insert into tbl_user values(null,?,?)";
            //必须在自定义的Connection类中重写prepareStatement(sql)方法
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "吕布1");
            pstmt.setString(2, "貂蝉1");
            int rows = pstmt.executeUpdate();
            if(rows>0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils_V3.release(conn, pstmt, null);
        }
    }

}