mysql:day7--开源数据库连接池（DBCP/C3P0）、DBUTILS及ext-dbutils扩展包）

素颜马尾好姑娘i 2022-09-25 08:24 130阅读 0赞

# DBCP #

使用dbcp第三方包要导入  
![这里写图片描述][20160817153634441]  
三个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> 
        </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;
        }
    
    }

[20160817153634441]: /images/20220718/6d15539fc52b485b81351d89b13b85a5.png