Commons DbUtils入门教程（CRUD）

概述

Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，不是ORM框架，只提供数据库操作的简单实现，包含增、删、改、查、批量以及事务等操作。

两个核心对象

1. QueryRunner类

核心方法	说明
<T> T query(String sql, ResultSetHandler rsh, Object… params)	执行select命令
<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object… params)	执行select命令
int update(String sql, Object… params)	执行insert、delete、update命令
int update(Connection conn, String sql, Object… params)	执行insert、delete、update命令

2. ResultSetHandler接口

12个实现类	说明

jar包

• commons-dbutils-1.6.jar
  DbUtils官网下载地址。下载Binaries版本，例如：commons-dbutils-1.6-bin.zip。

• c3p0-0.9.5.2.jar && mchange-commons-java-0.2.11.jar
  C3P0数据源SourceForge下载地址。

• mysql-connector-java-5.1.40-bin.jar
  MySQL Connectors官网下载地址。

入门使用

• IDE ：MyEclipse 2015

• Navicat fo MySQL创建users表
  设计
  
  数据
  

• 新建Web Project(普通Java Project亦可)，并将四个jar包拷贝到lib目录下

• 在src根目录新建c2p0数据源配置文件c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <!-- mysql database settings -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/dbutils_test
        </property>
        <property name="user">root</property>
        <property name="password">13061079</property>
        <!-- c3p0 settings -->
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </default-config>
</c3p0-config>

• DbUtils应用代码
  工程结构
  
  User.java

package com.buaa.entity;

import java.util.Date;

public class User {
    private int id;
    private String username;
    private String password;
    private String email;
    private Date birthday;

    public int getId() {
        return id;
    }

    public void setId(int 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;
    }

    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;
    }

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

}

C3P0Util.java

package com.buaa.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Util {
    // 得到一个数据源
    private static DataSource dataSource = new ComboPooledDataSource();

    // 返回数据源
    public static DataSource getDataSource() {
        return dataSource;
    }

    // 从数据源中得到一个连接对象
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException("服务器错误");
        }
    }

    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        // 关闭资源
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();// 关闭
            } catch (Exception e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }

}

TestCRUD.java

package com.buaa.dao;

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

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

import com.buaa.entity.User;
import com.buaa.util.C3P0Util;

public class TestCRUD {
    @Test
    public void testInsert() throws SQLException {
        // 创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        // 执行sql语句
        qr.update("insert into users(username,password,email,birthday) values(?,?,?,?)",
                "Insert", "123", "insert@163.com", new Date());
    }

    @Test
    public void testDelete() throws SQLException {
        // 创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        // 执行sql语句
        qr.update("delete from users where id=?", 5);
    }

    @Test
    public void testUpdate() throws SQLException {
        // 创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        // 执行sql语句
        qr.update("update users set username=?,password=? where id=?", "周杰杰",
                "333", 8);
    }

    @Test
    // 批处理，只能执行相同的sql语句
    public void testBatch() throws SQLException {
        // 创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[][] params = new Object[10][];
        for (int i = 0; i < params.length; i++) {
            params[i] = new Object[] { "菜10" + i, "123", "c10@163.com",
                    new Date() };
        }
        // 执行sql语句
        qr.batch("insert into users(username,password,email,birthday) values(?,?,?,?)",
                params);
    }

    @Test
    public void testSelect() throws SQLException {
        // 创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        // 执行查询语句，并返回结果
        List<User> list = qr.query(
                "select * from users where id=? and username=?",
                new BeanListHandler<User>(User.class), 2, "swh");
        for (User user : list) {
            System.out.println(user);
        }
    }
}