c3p0和dbcp的简单配置

c3p0的配置与使用案例

测试类

package com.serendipity.c3p0;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.serendipity.dbutils.User;

/**
 * @author 谢英亮
 * @date 2018年1月6日  上午9:17:30
 * @Description: c3p0测试类
 */
public class Demo {
    /**
     * 手动配置
     * @throws Exception
     */
    @Test
    public void testC3p0() throws Exception{
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql:///test");
        dataSource.setUser("root");
        dataSource.setPassword("admin");
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
    }
    /**
     * 测试自动配置 以及和dbutils组件结合使用
     * @throws Exception
     */
    @Test
    public void testConfig () throws Exception{
        //底层会自动在src目录下找一个 名为  c3p0-config.xml的配置文件
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        QueryRunner runner=new QueryRunner(dataSource);
        List<User> users = runner.query("select id ,name,age,gendar from t_user where id < ?", new BeanListHandler<>(User.class),7);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

c3p0-config.xml

<c3p0-config>
  <default-config>
     <!--驱动-->
     <property name="driverClass">com.mysql.jdbc.Driver</property>
     <!--访问的url-->
     <property name="jdbcUrl">jdbc:mysql:///test</property>
     <!--用户-->
     <property name="user">root</property>
     <!--密码-->
     <property name="password">admin</property>
     <!--池子里面的最小数-->
     <property name="minPoolSize">5</property>
     <!--池子里面的最大数-->
     <property name="maxPoolSize">100</property>
     <!--默认不够的时候每一次的增量-->
     <property name="acquireIncrement">5</property>
     <!--最大的空闲时间-->
     <property name="maxIdleTime">3000</property>
     <!--最大的并发数-->
     <property name="maxStatements">100</property>  
  </default-config>
</c3p0-config>

dbcp的配置与使用

配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=admin
initialSize=2
maxActive=100
maxIdle=3000
minIdle=1000

测试类

package com.serendipity.dbcp;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import com.serendipity.dbutils.User;

/**
 * @author 谢英亮
 * @date 2018年1月6日  上午9:51:10
 * @Description: 测试dbcp连接池
 */
public class Demo {
    /**
     * 手动配置dbcp
     * @throws SQLException 
     */
    @Test
    public void dbcp() throws SQLException{
        BasicDataSource dataSource = new BasicDataSource();

        //配置四大金刚
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql:///test");
        dataSource.setUsername("root");
        dataSource.setPassword("admin");
        //获取连接
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
    }
    /**
     * 通过配置文件配置dbcp  
     * @throws Exception 
     */
    @Test
    public void testConfig() throws Exception{
        Properties properties=new Properties();
        //加载配置文件
        InputStream in = Demo.class.getResourceAsStream("db.properties");
        //properties.load(new FileInputStream("src/com/serendipity/dbcp/db.properties"));
        properties.load(in);
        DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
        QueryRunner runner=new QueryRunner(dataSource);
        String sql = "select id,name,age,gendar from t_user where id < ?";
        List<User> users = runner.query(sql, new BeanListHandler<>(User.class),10);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

实体类

package com.serendipity.dbutils;

/**
 * @author 谢英亮
 * @date 2018年1月4日  下午8:58:30
 * @Description: 用户实体类
 */
public class User {
    private int id;
    private String name;
    private int age;
    private String gendar;
    public User() {

    }
    public User(String name, int age, String gendar, int id) {
        super();
        this.name = name;
        this.age = age;
        this.gendar = gendar;
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getGendar() {
        return gendar;
    }
    public void setGendar(String gendar) {
        this.gendar = gendar;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + ", gendar=" + gendar + "]";
    }


}

注：本文只是简单的对c3p0和dbcp进行了配置和测试，适合初学者使用。