DBCP

使用dbcp第三方包要导入

这里写图片描述

三个jar包

使用很简单：

1、纯java方式拿dbcp连接池

@Test
    public void testDbcpDemo() throws SQLException{
        BasicDataSource pool = new BasicDataSource();
        pool.setUsername("root");
        pool.setPassword("");
        pool.setUrl("jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf-8");
        pool.setDriverClassName("com.mysql.jdbc.Driver");

        System.out.println(pool.getMaxActive());//最大多少个连接对象
        System.out.println(pool.getMaxIdle());//最大空闲时间，一个用户多久不用，就收回
        System.out.println(pool.getMaxWait());//最多等待多久，超过这个时间就跑出异常
        System.out.println(pool.getInitialSize());//初始化有几个连接
        System.out.println("---------------我是传说的分隔符-------------------------");
        for(int i=0;i<20;i++){
            Connection con = pool.getConnection();
            System.out.println((i+1)+":   "+con.hashCode());
            if(i%2==0){
                con.close();
            }
        }
    }

2、使用配置文件的方式拿connection

@Test
    public void testPropertyDpcp() throws Exception {
        /*
         * 注意：在使用配置文件拿dbcp池的时候，我们要将配置文件中的key值与pool池的私有变量名一致才能拿到
         */

        Properties p = new Properties();
        p.load(DbcpPoolDemo.class.getResourceAsStream("dbcp.properties")); //这种方式是将配置文件和当前class放在一起
        //p.load(DbcpPoolDemo.class.getClassLoader().getResourceAsStream("dpcp.properties")); //这种方式是将配置文件放在与src和bin同级
        DataSource pool = BasicDataSourceFactory.createDataSource(p);
        //System.out.println(pool);
        for(int i=0;i<10;i++){
            Connection con = pool.getConnection();
            System.out.println((i+1)+":   "+con.hashCode());
            if(i%2==0){
                con.close();
                System.out.println(con.hashCode());
                System.out.println(con.toString());
            }
        }
    }
}

3、做一个由dbcp数据连接池的工具类

package cn.hncu.dbcp;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DbcpUtil {
    private static DataSource pool ;
    private static ThreadLocal<Connection> threads = new ThreadLocal<Connection>();
    static{
        //采用配置文件的方式连接数据库
        try {
            Properties p = new Properties();
            p.load(DbcpUtil.class.getResourceAsStream("jdbc.properties"));
            pool = BasicDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //返回pool池
    public static  DataSource getDataSource(){
        return pool ;
    }

    public static synchronized Connection  getCon() throws InterruptedException, SQLException{
        Connection con = threads.get();
        if(con==null){
            con = pool.getConnection();
            threads.set(con);
        }
        return con;
    }

//  public static void main(String[] args) throws InterruptedException {
//      System.out.println(getCon());
//  }
}

4、配置文件信息

driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://127.0.0.1\:3306/aa?useUnicode\=true&characterEncoding\=UTF-8
username=root
password=

C3P0

c3p0这个比较稳定，速度也比较快，hibernate框架使用的就是这个数据库连接池

需要导入一个jar包，我们要注意的是使用配置文件使用c3p0的时候，它的配置文件我们要放在classpath目录下，空参new是使用默认的配置文件，而带参数名new是使用名为
<named-config name="hncu">
这个name指定的配置。

C3P0配置文件

<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">
            <![CDATA[jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=UTF-8]]>
        </property>
        <property name="user">root</property>
        <property name="password"></property>
        <!-- 初始化池大小 -->
        <property name="initialPoolSize">2</property>
        <!-- 最大空闲时间 -->
        <property name="maxIdleTime">30</property>
        <!-- 最多有多少个连接 -->
        <property name="maxPoolSize">10</property>
        <!-- 最少几个连接 -->
        <property name="minPoolSize">2</property>
        <!-- 每次最多可以执行多少个批处理语句 -->
        <property name="maxStatements">50</property>
    </default-config> 
    <!-- 命名的配置 -->
    <named-config name="hncu">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">
            <![CDATA[jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=UTF-8]]>
        </property>
        <property name="user">root</property>
        <property name="password"></property>
        <property name="acquireIncrement">5</property><!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
    </named-config>
</c3p0-config>

1、纯java方式拿C3P0连接池

//纯java的方式使用c3p0数据库连接池
    @Test
    public void testC3p0() throws PropertyVetoException, SQLException{
        ComboPooledDataSource pool = new ComboPooledDataSource();
        pool.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf-8");
        pool.setUser("root");
        pool.setPassword("");
        pool.setDriverClass("com.mysql.jdbc.Driver");
        for(int i=0;i<20;i++){
            Connection con = pool.getConnection();
            System.out.println((i+1)+":  "+con.hashCode());
            if(i%2==0){
                con.close();
            }
        }
        //注意 c3p0和dbcp的close方法不同的是，c3p0如果关了就释放内存，下次取时重新开内存，内存地址不共用（不会重复）
    }

2、使用配置文件的方式使用c3p0

@Test
    public void testPropertiesDemo() throws Exception{
        //空参-去classpath目录下加载配置文件"c3p0-config.xml"，且使用配置文件当中的默认配置
//      ComboPooledDataSource pool = new ComboPooledDataSource();
        //指定配置文件中的<named-config name="hncu"> 加载这个指定的配置
        ComboPooledDataSource pool = new ComboPooledDataSource("hncu");
        for(int i=0;i<20;i++){
            Connection con = pool.getConnection();
            System.out.println((i+1)+":  "+con.hashCode());
//          if(i%2==0){
//              con.close();
//          }
        }

    }

3、使用c3p0做一个拿数据库连接对象工具类

package cn.hncu.c3p0;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Pool {
    private static ComboPooledDataSource pool ;
    private static ThreadLocal<Connection> threads = new ThreadLocal<Connection>();
    static{
        pool = new ComboPooledDataSource("hcun");
    }
    public static DataSource getDataSource(){
        return pool;
    }
    public static Connection getConnection() throws Exception{
        Connection con = threads.get();
        if(con==null){
            con = pool.getConnection();
            threads.set(con);
        }
        return con;
    }
}

c3p0和dbcp的close方法的区别：

c3p0和dbcp的close方法不同的是，c3p0如果关了就释放内存，下次取时重新开内存，内存地址不共用（不会重复）

DBUTILS

使用dbutlis我们也要导入相关的jar包，这点一定要注意

1、使用dbutls做查询–使用BeanListHandler

我们要注意的是，只要值对象的setter-getter的函数名和数据库的段名一致即可，博主测试可行。

@Test
    public void dbUtilsQuery() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql ="select * from test1";
        List<Stud> studs = run.query(sql, new BeanListHandler<Stud>(Stud.class));
        System.out.println(studs);
    }

2、使用dbutls做查询–使用MapListHandler

@Test
    public void dbUtilsQuery2() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql ="select * from test1";
        List<Map<String, Object>> studs = run.query(sql, new MapListHandler());
        System.out.println(studs);
    }

3、使用dbutlis做保存

@Test
    public void save() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        String sql = "insert into dbutil(id,name,address,age) values(?,?,?,?) ";
        run.update(sql,"A001","Aike","中国上海",25);
    }

4、使用dbutlis做保存，同时开启事务

注意

：要想将dbutils也用事务绑定，那么必须在执行的时候是

同一个con连接。

@Test
    public void save2() throws Exception{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        Connection con = C3p0Pool.getConnection();
        try {
            con.setAutoCommit(false);
            //要想将dbutils也用事务绑定，那么必须在执行的时候是同一个con连接。
            run.update(con,"insert into dbutil(id,name,address,age) values(?,?,?,?)", "A006","Tom","中国杭州",27);
            run.update(con,"insert into dbutil(id,name,address,age) values(?,?,?,?)", "A004","Mike","中国武汉",29);
            con.commit();
        } catch (Exception e) {
            con.rollback();
            System.out.println("事务回滚了");
        }finally{
            con.setAutoCommit(true);
            con.close();
        }

    }

5、dbutil的批处理

这里要注意的是参数是一个二维参数数组。

@Test
    public void  batch() throws SQLException{
        QueryRunner run = new QueryRunner(C3p0Pool.getDataSource());
        for(int i=0;i<100;i++){
            String sql = "insert into person3(id,name) values(?,?) ";
            String str = "000"+i;
            str = str.substring(str.length()-3,str.length());
            String id1 ="A"+str;
            String id2="B"+str;
            String params[][]= {{id1,"Alice"+i},{id2,"Bob"+i}};
            run.batch(sql, params);
        }
    }

扩展包commons-dbutilss-ext.jar的功能

注意的是，扩展包的功能要实现，我们必须在值对象那边加注解,并且保证字段名和setter-getter的函数名一致

不执行sql语句，直接查询

@Test
    public void query4(){
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
        List<DbUtil> dbutils = run.query(DbUtil.class);
        System.out.println(dbutils);
    }

不执行sql语句，直接做保存数据

@Test
    public void save3(){
        ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource());
        DbUtil db = new DbUtil();
        db.setId("A007");
        db.setName("森");
        db.setAddress("衡山店门");
        db.setAge(22);
        run.save(db);
    }

值对象- – – Stud

package cn.hncu.dbutils;

public class Stud {
    private String id;
    private String name;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getNm() {
        return name;
    }
    public void setNm(String nm) {
        this.name = nm;
    }
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Stud other = (Stud) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }
    @Override
    public String toString() {
        return id + ", " + name ;
    }

}

值对象- – – -DbUtil – – – – – – – -加注解

package cn.hncu.dbutils;

import java.beans.ConstructorProperties;

import org.apache.commons.dbutils.ext.Column;
import org.apache.commons.dbutils.ext.Table;

@Table(value="dbutil")
public class DbUtil {
    @Column
    private String id;
    @Column
    private String name;
    @Column
    private String address;
    @Column
    private Integer age;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        DbUtil other = (DbUtil) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }
    @Override
    public String toString() {
        return id + ", " + name + "," + address
                + "," + age;
    }

}

原文链接：https://blog.csdn.net/new___Smile/article/details/52231801