DBUtils使用详解

DBUtils连接数据库代码

import java.sql.Connection;

import javax.sql.*;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;

public class C3p0Util {
    //得到一个数据源
    private static DataSource dataSource=new ComboPooledDataSource();
    public static DataSource getDataSource() {
        return dataSource;
    }
    //从数据源中得到一个连接对象
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //关闭连接资源
    public static void closeConn(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;
        }
    }


}

c3p0配置

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://ip地址:3306/java_review?useUnicode=true&amp;characterEncoding=UTF8
    </property>//&的转义字符为&amp;
    <property name="user">用户名</property>
    <property name="password">密码</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>//最大等待时间
    <property name="maxPoolSize">100</property>//连接池最大连接数
    <property name="minPoolSize">10</property>//连接词最小连接数
  </default-config>
</c3p0-config>

DBUtlis的update(增删改)操作代码

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

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

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(?,?,?,?)", "菜10","123","c10@163.com",new Date());

    }

    @Test
    //修改数据
    public void testUpdate() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("update users set username=?,password=? where id=?", "周杰杰","333",15);
    }


    @Test//删除数据
    public void testDelete() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("delete from users where id=?", 15);
    }

    @Test//批处理，只能执行相同的sql语句
    public void testBatch() throws SQLException{
        //创建一个QueryRunner对象
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());

        Object[][] params = new Object[10][];//高维代表执行多少次sql语句
        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 );

    }
}

DBUtils的select(查询)操作代码

import java.sql.SQLException;
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.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
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 com.itheima.entity.User;

public class TestResultSetHandler {
    //@Test是测试的，不需要写main函数既可以执行方法
    @Test//ArrayHandler:适合取1条记录。把该条记录的每列值封装到一个数组中Object[]
    public void tese1() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[] arr  = qr.query("select * from users", new ArrayHandler());

        for (Object o : arr) {
            System.out.println(o);
        }
    }

    @Test//ArrayListHandler:适合取多条记录。把每条记录的每列值封装到一个数组中Object[]，把数组封装到一个List中
    public void tese2() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Object[]> query = qr.query("select * from users", new ArrayListHandler());

        for (Object[] os : query) {
            for (Object o : os) {
                System.out.println(o);
            }
            System.out.println("--------------");
        }
    }

    @Test //ColumnListHandler:取某一列的数据。封装到List中。
    public void tese3() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Object> list = qr.query("select username,password from users", new ColumnListHandler(1));

        for (Object o : list) {
            System.out.println(o);
        }
    }

    @Test //KeyedHandler:取多条记录，每一条记录封装到一个Map中，再把这个Map封装到另外一个Map中，key为指定的字段值。
    public void tese4() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        //大Map的key是表中的某列数据，小Map中的key是表的列名，所以大Map的key是Object类型，小Map的key是String类型
        Map<Object,Map<String,Object>> map = qr.query("select * from users", new KeyedHandler(1));

        for (Map.Entry<Object, Map<String,Object>> m : map.entrySet()) {
            System.out.println(m.getKey());//大Map中key值就是id列的值
            for (Map.Entry<String, Object> mm : m.getValue().entrySet()) {
                System.out.println(mm.getKey()+"\t"+mm.getValue());//取出小Map中的列名和列值
            }
            System.out.println("---------------------");
        }

    }


    @Test//MapHandler:适合取1条记录。把当前记录的列名和列值放到一个Map中
    public void tese5() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Map<String,Object> map = qr.query("select * from users where id=?", new MapHandler(),20);

        for (Map.Entry<String, Object> m : map.entrySet()) {
            System.out.println(m.getKey()+"\t"+m.getValue());
        }

    }


    @Test//MapListHandler:适合取多条记录。把每条记录封装到一个Map中，再把Map封装到List中
    public void tese6() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Map<String,Object>> list = qr.query("select * from users", new MapListHandler());

        for (Map<String, Object> map : list) {
            for (Map.Entry<String, Object> m : map.entrySet()) {
                System.out.println(m.getKey()+"\t"+m.getValue());
            }
            System.out.println("---------------");
        }
    }

    @Test //ScalarHandler:适合取单行单列数据
    public void tese7() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object o = qr.query("select count(*) from users", new ScalarHandler(1));
        System.out.println(o.getClass().getName());
    }

    @Test //BeanHandler:适合取单行单列数据
    public void tese8() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        User user = qr.query("select * from users where id=?", new BeanHandler<User>(User.class),1);
        System.out.println(user);
    }


    @Test //BeanListHandler 
    public void tese9() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<User> list = qr.query("select * from users where id=?", new BeanListHandler<User>(User.class),1);

        System.out.println(list.size());
    }

}