C3P0Util 的制作 及DButils 中的 QueryRunner 类的使用

package cn.javabs.util;

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

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
 * C3p0的工具类
 * @author Mryang
 * 2018.08.05
 */
public class C3p0Util {
  private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
  
  public static DataSource getDataSource(){
    return dataSource;
  }
  
  public static Connection getConnection(){
    try {
      return dataSource.getConnection();
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
  <!--以下属性name的属性值都不可以更改-->
    <!--连接数据库驱动-->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <!--数据库连接，如本地主机，可直接更改数据库名称:user-->
    <property name="jdbcUrl">jdbc:mysql:///user</property>
    <!--数据库用户名-->
    <property name="user">root</property>
    <!--数据库密码-->
    <property name="password">sorry</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>
  </default-config> 
</c3p0-config>

package cn.javabs.entity;

public class User {

  private Integer id;
  
  private String username;
  
  private String password;

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getUsername() {
    return username;
  }

  public void setUsername(String username) {
    this.username = username;
  }

  public String getPassword() {
    return password;
  }
  public void setPassword(String password) {
    this.password = password;
  }

  @Override
  public String toString() {
    return "User [id=" + id + ", username=" + username + ", password="
        + password + "]";
  }
  
}

package cn.javabs.test;

import java.sql.SQLException;
import java.util.List;

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

import cn.javabs.entity.User;
import cn.javabs.util.C3p0Util;
/**
 * 数据源连接池的测试类
 * @author Mryang
 * 本类中引入了一个新的工具类是 QueryRunner
 */
public class DataTest {

  QueryRunner qr = new QueryRunner(C3p0Util.getDataSource());
  
  @Test
  public void addDataTest(){
    
    User user  = new User();
    user.setId(9);
    user.setUsername("aaa");
    user.setPassword("123");
    try {
      qr.update("insert into user(id, username,password) values (?,?,?)", //
          user.getId(),user.getUsername(),user.getPassword());
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  @Test
  public void delDataTest(){
    
    User user  = new User();
    user.setId(9);
    try {
      qr.update("delete from user where id = ?", //
          user.getId());
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  @Test
  public void updateDataTest(){
    
    User user  = new User();
    user.setPassword("222");
    user.setId(9);
    try {
      qr.update("update user set  password = ? where id = ? ", //
        user.getPassword(),user.getId());
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  @Test
  public void queryAllDataTest(){
    
    try {
      List<User> result = qr.query("select * from user", //
          new BeanListHandler<User>(User.class));
      System.out.println(result);
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  
}

create database user;
use user;
create table user(
 id int primary key auto_increment,
 username varchar(50),
 password varchar(50)
);