MYSQL学习05 数据库连接池

最新推荐文章于 2024-10-09 10:32:38 发布

中行独复

最新推荐文章于 2024-10-09 10:32:38 发布

阅读量123

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/wuaopan/article/details/108236573

版权

数据库连接池

数据库连接 – 执行完毕 – 释放
连接 – 释放十分浪费资源
池化技术:准备一些预先的资源过来就连接预先准备好的
最小连接数: 10(常用连接数)
最大连接数 15
等待时间:100ms ( 超过15个连接则等待)
编写连接池, 实现只需要一个接口: DataSource

开源数据源

DBCP

C3P0

Druid: 阿里巴巴

使用这些数据库连接池后在项目开发中就不需要编写连接数据库代码

DBCP(DataBase Connection Pool)

// 用到的jar包 
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3307/jdbcstudy
username=root
password=123456

initialSize=10


maxActive=50


maxIdle=20


minIdle=5


maxWait=60000


connectionProperties=useUnicode=true;characterEncoding=utf8;useSSL =true


#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
 
// 封装工具类
package com.wu.lesson02.utils;



import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_DBCP {
    static BasicDataSource dataSource = null;
    static {
        try {
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源  工厂模式  返回一个数据源
            dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } 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.wu.lesson02.utils;

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

public class DbcpDemon01 {
    public static void main(String[] args) {
        Connection connection= null;
        PreparedStatement preparedStatement= null;
        ResultSet resultSet = null;
        try {
             connection = JdbcUtils_DBCP.getConnection();
             String sql = " select * from users";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println("id:" + resultSet.getInt("id"));
                System.out.println("name:" + resultSet.getString("name"));
                System.out.println("password:" + resultSet.getString("password"));
                System.out.println("email:" + resultSet.getString("email"));
                System.out.println("birthday:" + resultSet.getDate("birthday"));
                System.out.println("=========");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(connection,preparedStatement,resultSet);
        }
    }
}

C3P0

// 需要用到的jar包
//c3p0-0.9.5.5  mchange-commons-java-0.2.19
//c3p0 xml配置文件 c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
   <default-config>
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3307/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;&amp;useSSL=true</property>
      <property name="user">root</property>
      <property name="password">123456</property>

      <property name="initialPoolSize">10</property>
      <property name="maxIdleTime">30</property>
      <property name="maxPoolSize">100</property>
      <property name="minPoolSize">10</property>
      <property name="acquireIncrement">5</property>
   </default-config>

   <named-config name="MySQL">
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3307/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;&amp;useSSL=true</property>
      <property name="user">root</property>
      <property name="password">123456</property>

      <property name="initialPoolSize">10</property>
      <property name="maxIdleTime">30</property>
      <property name="maxPoolSize">100</property>
      <property name="minPoolSize">10</property>
      <property name="acquireIncrement">5</property>
   </named-config>


</c3p0-config>

// 封装c3p0工具类
package com.wu.lesson02.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcUtils_C3P0 {

    private  static ComboPooledDataSource dataSource = null;
    static {
        //不加参数 导入默认xml值
        try {
            dataSource = new ComboPooledDataSource();
        } 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.wu.lesson02.utils;

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

public class C3P0Demon01 {
    public static void main(String[] args) {
        Connection connection =null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
             connection = JdbcUtils_C3P0.getConnection();
             String sql = "select * from users";
             preparedStatement = connection.prepareStatement(sql);
             resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println("id:" + resultSet.getInt("id"));
                System.out.println("name:" + resultSet.getString("name"));
                System.out.println("password:" + resultSet.getString("password"));
                System.out.println("email:" + resultSet.getString("email"));
                System.out.println("birthday:" + resultSet.getDate("birthday"));
                System.out.println("===========");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(connection,preparedStatement,resultSet);
        }
    }
}