首页 > > 程序设计 > Java技术 >

20连接池

2018-08-13 07:38:52来源：博客园阅读 ()

一、c3p0连接池

1.导包(lib 下)

数据库连接池

c3p0-0.9.5.2.jar

machange-commons-java-0.2.11.jar

MySQL驱动

mysql-connector-java-8.0.11.jar

2.核心方法

// 核心连接池类

ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

// 设置四个JDBC基本连接属性

comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");

comboPooledDataSource.setJdbcUrl("jdbc:mysql:///day04");

comboPooledDataSource.setUser("root");

comboPooledDataSource.setPassword("123");

3.jdbc.properties配置文件

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&characterEncoding=utf-8
username=root
password=111

4.JDBCUtils工具类

import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {

    private static final String driverClass;
    private static final String url;
    private static final String username;
    private static final String password;

    static {
        Properties prop = new Properties();

        try {
            prop.load(new FileReader("jdbc.properties"));

            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            // 加载驱动
            loadDriver();

        } catch (IOException e) {
            // e.printStackTrace();
            throw new RuntimeException("配置文件加载失败!");
        }
    }

    // 加载驱动
    public static void loadDriver() {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            // e.printStackTrace();
            throw new RuntimeException("驱动加载失败!");
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放资源
    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }

        release(conn, stmt);
    }

    public static void release(Connection conn, Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }

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

// 测试类
public class JDBCTemplateTest1 {

    @Test
    public void test1() throws SQLException, PropertyVetoException {

        // 需求 : 查询 user 表中的所有数据

        // 核心连接池类
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 设置四个JDBC基本连接属性
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC&characterEncoding=utf-8");
        dataSource.setUser("root");
        dataSource.setPassword("111");

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 1. 建立连接
            conn = dataSource.getConnection();
            // 2. 操作数据
            String sql = "select * from user;";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String email = rs.getString("email");
                System.out.println(id + " : " + username + " : " + password + " : " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 3. 释放资源
            JDBCUtils.release(conn, stmt, rs);
        }
    }
}

5.通过xml 获取配置信息

ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(); 会自定加载配置文件

常用基本连接池属性

acquireIncrement 如果连接池中连接都被使用了，一次性增长3个新的连接

initialPoolSize 连接池中初始化连接数量默认:3

maxPoolSize 最大连接池中连接数量默认：15连接

maxIdleTime 如果连接长时间没有时间，将被回收默认：0 连接永不过期

minPoolSize 连接池中最小连接数量默认：3

c3p0-config.xml 数据库连接池配置文件 : 需要创建在 src 目录下.
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 默认配置，c3p0框架默认加载这段默认配置 -->
    <default-config>
        <!-- 配置JDBC 四个基本属性 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC&characterEncoding=utf-8</property>
        <property name="user">root</property>
        <property name="password">111</property>
    </default-config>
    <!-- 可以自定义配置，为这段配置起一个名字，c3p0指定名称加载配置 -->
    <named-config name="xxxxx">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC&characterEncoding=utf-8</property>
        <property name="user">root</property>
        <property name="password">111</property>
    </named-config>
</c3p0-config>c

　　@Test
    public void test_c3p0() throws PropertyVetoException {
        // 需求 : 查询 user 表中的所有数据
        
        // 核心连接池类
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            // 1. 建立连接
            conn = dataSource.getConnection();
            // 2. 操作数据
            String sql = "select * from user;";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String email = rs.getString("email");
                System.out.println(id + " : " + username + " : " + password + " : " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 3. 释放资源
            JDBCUtils.release(conn, stmt, rs);
        }
    }

优化版的JDBCUtils 中的getConnectio 使用数据库连接池对象方式实现

public class JDBCUtils {
    
    // c3p0 数据库连接池对象属性
    private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
    
    // 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    // 释放资源
    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }
        release(conn, stmt);
    }
    
    public static void release(Connection conn, Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

    @Test
    public void test_jdbcUtils() {
        
        // 需求 : 查询 user 表中的所有数据
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            // 1. 建立连接
            conn = JDBCUtils.getConnection();
            // 2. 操作数据
            String sql = "select * from user;";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String email = rs.getString("email");
                System.out.println(id + " : " + username + " : " + password + " : " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 3. 释放资源
            JDBCUtils.release(conn, stmt, rs);
        }
    }