DbUtils类
DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：
public static void close(…) throws java.sql.SQLException： DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。
public static void commitAndCloseQuietly(Connection conn)： 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。
QueryRunner类 ：
该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。
QueryRunner类提供了两个构造方法：
默认的构造方法
需要一个 javax.sql.DataSource 来作参数的构造方法。
QueryRunner类的主要方法：
public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException: 几乎与第一种方法一样；唯一的不同在于它不将数据库连接提供给方法，并且它是从提供给构造方法的数据源(DataSource) 或使用的setDataSource 方法中重新获得 Connection。
public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException : 执行一个不需要置换参数的查询操作。
public int update(Connection conn, String sql, Object[] params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
public int update(Connection conn, String sql) throws SQLException：用来执行一个不需要置换参数的更新操作。
c3p0config.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:3306/day16</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
-->
<named-config name="lcp">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day17</property>
<property name="user">root</property>
<property name="password">lcp8090</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
<!--
<named-config name="oracle">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
-->
</c3p0-config>
JdbcUtils.java
package cn.lcp.utils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JdbcUtils {
private static ComboPooledDataSource ds = null;
private static ThreadLocal<Connection> threadLocal = new ThreadLocal();
static{
ds = new ComboPooledDataSource("flx");
}
public static Connection getConnection() throws SQLException{
Connection conn = threadLocal.get();
if(conn==null){
conn = getDataSource().getConnection();
threadLocal.set(conn);
}
return conn;
}
public static DataSource getDataSource(){
return ds;
}
public static void startTransaction(){
try{
Connection conn = threadLocal.get();
if(conn==null){
conn = getConnection();
threadLocal.set(conn); //把 conn绑定到当前线程上
}
conn.setAutoCommit(false);
}catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void rollback(){
try{
Connection conn = threadLocal.get();
if(conn!=null){
conn.rollback();
}
}catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void commit(){
try{
Connection conn = threadLocal.get();
if(conn!=null){
conn.commit();
}
}catch (Exception e) {
throw new RuntimeException(e);
}
}
public static void release(){
try{
Connection conn = threadLocal.get();
if(conn!=null){
conn.close();
threadLocal.remove(); //解除当前线程上绑定conn
}
}catch (Exception e) {
throw new RuntimeException(e);
}
}
}
User.java
package cn.lcp.domain;
import java.util.Date;
public class User {
private Integer id;
private String name;
private String password;
private String email;
private Date birthday;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
Demo1.java
package cn.lcp.demo;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import javax.sql.rowset.serial.SerialClob;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import cn.lcp.domain.User;
import cn.lcp.utils.JdbcUtils;
public class Demo1 {
/*
*
* 使用dbutils框架完成curd,以及批处理
*
create database day17; use day17;
create table user( id int primary key auto_increment, name varchar(40),
password varchar(40), email varchar(60), birthday date );
insert into user(name,password,email,birthday)values('zs','123456','zs@sina.com','1980-09-09');
insert into user(name,password,email,birthday) values('lisi','123456','lisi@sina.com','1980-09-09');
insert into user(name,password,email,birthday) values('wangwu','123456','wangwu@sina.com','1980-09-09');
*
*/
@Test
public void add() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
Object params[] = { "ccc", "123", "cc@sina.com", new Date() };
qr.update(sql, params);
}
@Test
public void delete() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "delete from user where id=?";
qr.update(sql, 1);
}
@Test
public void update() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "update user set name=? where id=?";
Object params[] = { "ddd", 5 };
qr.update(sql, params);
}
@Test
public void find() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user where id=?";
Object params[] = { 2 };
User user = (User) qr.query(sql, params, new BeanHandler(User.class));
System.out.println(user.getBirthday());
}
@Test
public void getAll() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
List list = (List) qr.query(sql, new BeanListHandler(User.class));
System.out.println(list.size());
}
@Test
public void testBatch() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
Object params[][] = new Object[10][];
for (int i = 0; i < 10; i++) {
params[i] = new Object[] { "aa" + i, "123", "aa@sina.com",
new Date() };
}
qr.batch(sql, params);
}
}
用dbutils完成大数据（不建议用）。在src同目录下建一个1.txt文本
create table testclob
(
id int primary key auto_increment,
resume text
);
// 用dbutils完成大数
@Test
public void testclob() throws SQLException, IOException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into testclob(resume) values(?)"; //？需要的是clob字符大对象
String path = Demo1.class.getClassLoader().getResource("1.txt").getPath();
FileReader in = new FileReader(path);
char[] buffer = new char[(int) new File(path).length()];
in.read(buffer);
SerialClob clob = new SerialClob(buffer);
Object params[] = {clob};
runner.update(sql, params);
}
}
ResultSetHandler接口 ：
该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
ResultSetHandler 接口的实现类：
ArrayHandler：把结果集中的第一行数据转成对象数组。
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
ResultSetHandler 接口的实现类：
ColumnListHandler：将结果集中某一列的数据存放到List中。
KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List
package cn.lcp.demo;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import cn.lcp.utils.JdbcUtils;
public class Demo2 {
//测试dbutils各种类型的处理器
@Test
//ArrayHandler()
public void testArrayHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
Object result[] = (Object[]) qr.query(sql, new ArrayHandler());
System.out. println(Arrays.asList(result)); //list 重写toString()
}
@Test
//ArrayListHandler()
public void testArrayListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
List<Object[]> list = (List) qr.query(sql, new ArrayListHandler());
for(Object[] o : list){
System.out.println(Arrays.asList(o));
}
}
@Test
public void testColumnListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
List list = (List) qr.query(sql, new ColumnListHandler("id"));
System.out.println(list);
}
@Test
public void testKeyedHandler() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
Map<Integer,Map> map = (Map) qr.query(sql, new KeyedHandler("id"));
for(Map.Entry<Integer, Map> me : map.entrySet()){
int id = me.getKey();
Map<String,Object> innermap = me.getValue();
for(Map.Entry<String, Object> innerme : innermap.entrySet()){
String columnName = innerme.getKey();
Object value = innerme.getValue();
System.out.println(columnName + "=" + value);
}
System.out.println("----------------");
}
}
@Test
public void testMapHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
Map<String,Object> map = (Map) qr.query(sql, new MapHandler());
for(Map.Entry<String, Object> me : map.entrySet())
{
System.out.println(me.getKey() + "=" + me.getValue());
}
}
@Test
public void testMapListHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select * from user";
List<Map> list = (List) qr.query(sql, new MapListHandler());
for(Map<String,Object> map :list){
for(Map.Entry<String, Object> me : map.entrySet())
{
System.out.println(me.getKey() + "=" + me.getValue());
}
}
}
//java.lang.ClassCastException: java.lang.Long
@Test
public void testScalarHandler() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "select count(*) from user"; //[13] list[13]
/*long l = (Long) qr.query(sql, new ScalarHandler(1));
int count = (int) l;*/
//int count = Integer.parseInt(qr.query(sql, new ScalarHandler(1)).toString());
int count = ((Long)qr.query(sql, new ScalarHandler(1))).intValue();
System.out.println(count);
}
}
