mysql之数据连接池

数据库连接池

C3P0：

• 配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <property name="user">root</property>
        <property name="password">admin#1234</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">3</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">2</property>
        <property name="maxStatements">200</property>
    </default-config>
    <!-- 命名的配置,可以通过方法调用实现 -->
    <named-config name="mysql">
        <property name="user">root</property>
        <property name="password">admin#1234</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">20</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">25</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>

• 工具类

package com.yeyue.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils_C3P0 {

    private static DataSource dataSource = null;

    static {
        try{
            //配置文件写法
            dataSource = new ComboPooledDataSource("mysql");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }

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

• 测试类

package com.yeyue.lesson05;

import com.yeyue.lesson05.utils.JdbcUtils_C3P0;
import com.yeyue.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils_C3P0.getConnection();
            //INSERT INTO `users` (id,NAME,PASSWORD,email) VALUES (6,'wewwqw','2323','323wwqd')
            String sql = "INSERT INTO `users` (id,NAME,PASSWORD,email,birthday) VALUES (?,?,?,?,?)";

            st = conn.prepareStatement(sql);  //预编译sql 不执行

            st.setInt(1,5);
            st.setString(2,"wewwqw");
            st.setString(3,"2323");
            st.setString(4,"323wwqd");
            //java.sql.Date 数据库  java.util.Date java  new Date().getTime() 获取时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(conn,st,rs);
        }
    }
}

DBCP

• 配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=admin#1234

• 工具类

package com.yeyue.lesson05.utils;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;

    static {
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            System.out.println(properties.getProperty("driverClassName"));
            System.out.println(properties.getProperty("url"));
            System.out.println(properties.getProperty("username"));
            System.out.println(properties.getProperty("password"));

            //创建数据源 工厂模式 -->创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
            System.out.println("1");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }

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

• 测试类

package com.yeyue.lesson05;

import com.yeyue.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils_DBCP.getConnection();
            //INSERT INTO `users` (id,NAME,PASSWORD,email) VALUES (6,'wewwqw','2323','323wwqd')
            String sql = "INSERT INTO `users` (id,NAME,PASSWORD,email,birthday) VALUES (?,?,?,?,?)";

            st = conn.prepareStatement(sql);  //预编译sql 不执行

            st.setInt(1,6);
            st.setString(2,"wewwqw");
            st.setString(3,"2323");
            st.setString(4,"323wwqd");
            //java.sql.Date 数据库  java.util.Date java  new Date().getTime() 获取时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,rs);
        }
    }
}