QueryRunner实战(query_update)、BeanList\BeanHandler、MapList\MapHandler、ScalarHandler

1.c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <named-config name="helloc3p0">

        <!-- 指定连接数据源的基本属性 -->
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///filter</property>

        <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">10</property>

        <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
        <property name="maxStatements">20</property>
        <!-- 每个连接同时可以使用的 Statement 对象的个数 -->
        <property name="maxStatementsPerConnection">5</property>

    </named-config>

</c3p0-config>

2.test

package com.pers.test;/**
 * Created by hoobey on 2017/11/18.
 */

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

import java.io.IOException;
import java.util.Map;

/**
 * All rights Reserved, Designed By www.hoobey.cn
 *
 * @Description: 测试用例(用一句话描述该文件做什么)
 * Create by hoobey
 * User:user
 * Date:2017/11/18
 * Time:下午 04:51
 * 注意：本内容仅限于本公司内部传阅，禁止外泄以及用于其他的商业目的
 */
public class test {
    /**
     * QueryLoader: 可以用来加载存放着 SQL 语句的资源文件.
     * 使用该类可以把 SQL 语句外置化到一个资源文件中. 以提供更好的解耦
     *
     * @throws IOException
     */
    @Test
    public void testQueryLoader() throws IOException {
        // / 代表类路径的根目录.
        Map<String, String> sqls = QueryLoader.instance().load("/sql.properties");

        String updateSql = sqls.get("UPDATE_CUSTOMER");
        //UPDATE_CUSTOMER=UPDATE customer SET name = ? WHERE id = ?
        System.out.println(updateSql);
    }
}

3.Del.java

package com.pers.test;/**
 * Created by hoobey on 2017/11/18.
 */

import com.pers.util.JdbcTools;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import java.sql.Connection;

/**
 * All rights Reserved, Designed By www.hoobey.cn
 *
 * @Description: (用一句话描述该文件做什么)
 * Create by hoobey
 * User:user
 * Date:2017/11/18
 * Time:下午 05:04
 * 注意：本内容仅限于本公司内部传阅，禁止外泄以及用于其他的商业目的
 */
public class Del {
    /**
     * 测试 QueryRunner 类的 update 方法
     * 该方法可用于 INSERT, UPDATE 和 DELETE
     */
    @Test
    public void testQueryRunnerUpdate() {
        //1. 创建 QueryRunner 的实现类
        QueryRunner queryRunner = new QueryRunner();

        String sql = "DELETE FROM customer " +
                "WHERE id IN (?,?)";

        Connection connection = null;

        try {
            connection = JdbcTools.getConnection();
            //2. 使用其 update 方法
            int update = queryRunner.update(connection,
                    sql, 1, 2);
            System.out.println("update:" + update);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null, null, connection);
        }

    }
}

4.query.java

package com.pers.test;/**
 * Created by hoobey on 2017/11/18.
 */

import com.pers.domain.Customer;
import com.pers.util.JdbcTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * All rights Reserved, Designed By www.hoobey.cn
 *
 * @Description: 通过QueryRunner实现增删改查(用一句话描述该文件做什么)
 * Create by hoobey
 * User:user
 * Date:2017/11/18
 * Time:下午 04:59
 * 注意：本内容仅限于本公司内部传阅，禁止外泄以及用于其他的商业目的
 */
public class Query {
    /**
     * 测试 QueryRunner 的 query 方法
     */
    @SuppressWarnings({"unchecked", "rawtypes"})
    @Test
    public void testResultSetHandler() {
        String sql = "SELECT id, name, email, birth from customer";

        //1. 创建 QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();

        Connection conn = null;

        try {
            conn = JdbcTools.getConnection();
            /**
             * 2. 调用 query 方法:
             * ResultSetHandler 参数的作用: query 方法的返回值直接取决于
             * ResultSetHandler 的 hanlde(ResultSet rs) 是如何实现的. 实际上, 在
             * QueryRunner 类的 query 方法中也是调用了 ResultSetHandler 的 handle()
             * 方法作为返回值的。
             */
            Object object = queryRunner.query(conn, sql,
                    new ResultSetHandler() {
                        @Override
                        public Object handle(ResultSet rs) throws SQLException {
                            List<Customer> customers = new ArrayList<>();

                            while (rs.next()) {
                                int id = rs.getInt(1);
                                String name = rs.getString(2);
                                String email = rs.getString(3);
                                Date birth = rs.getDate(4);

                                System.out.println(id + "," + name + "," + email + "," + birth);
                                Customer customer =
                                        new Customer(id, name, email, birth);
                                customers.add(customer);
                            }

                            return customers;
                        }
                    }

            );
/*
0,hello,hu@qq.com,2017-11-16
1,hubin,122@qq.com,2017-10-18
2,xiaomi,77@163.com,2017-08-15
[Customer{id=0, name='hello', email='hu@qq.com', birth=2017-11-16}, Customer{id=1, name='hubin', email='122@qq.com', birth=2017-10-18},
Customer{id=2, name='xiaomi', email='77@163.com', birth=2017-08-15}]
 */
            System.out.println(object);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null, null, conn);
        }
    }


    /**
     * 测试 ResultSetHandler 的 BeanListHandler 实现类
     * BeanListHandler: 把结果集转为一个 Bean 的 List. 该 Bean
     * 的类型在创建 BeanListHandler 对象时传入:
     * <p>
     * new BeanListHandler<>(Customer.class)
     */
    @Test
    public void testBeanListHandler() {
        String sql = "SELECT id, name, email, birth FROM customer";

        //1. 创建 QueryRunner 对象
        QueryRunner queryRunner = new QueryRunner();

        Connection conn = null;

        try {
            conn = JdbcTools.getConnection();

            Object object = queryRunner.query(conn, sql, new BeanListHandler<>(Customer.class));
            //[Customer{id=0, name='hello', email='hu@qq.com', birth=2017-11-16}, Customer{id=1, name='hubin', email='122@qq.com',
            // birth=2017-10-18}, Customer{id=2, name='xiaomi', email='77@163.com', birth=2017-08-15}]
            System.out.println(object);

            ArrayList<Customer> customers = (ArrayList<Customer>) object;
            for (int i = 0; i < customers.size(); i++) {
                Customer customer = customers.get(i);
                System.out.println(customer.getName());//hello   hubin   xiaomi
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null, null, conn);
        }
    }


    /**
     * BeanHandler: 把结果集的第一条记录转为创建 BeanHandler 对象时传入的 Class 参数对应的对象.
     */

    @Test
    public void testBeanHanlder() {
        QueryRunner queryRunner = new QueryRunner();
        Connection connection = null;

        try {
            connection = JdbcTools.getConnection();
            String sql = "SELECT id, name, email, birth FROM customer WHERE name = ?";


            Customer customer = (Customer)queryRunner.query(connection, sql, new BeanHandler(Customer.class),"hello");

            System.out.println(customer.toString());//Customer{id=0, name='hello', email='hu@qq.com', birth=2017-11-16}
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null,null,connection);
        }
    }

/*
4、通过MapHandler、MapListHandler实现查询操作
 */
    @Test
    public void testMapHandler() {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();

        String sql = "SELECT id, name, email, birth FROM customer WHERE id = ?";

        try {
            connection = JdbcTools.getConnection();
            Map<String, Object> map = (Map<String, Object>)queryRunner.query(connection, sql, new MapHandler(), 2);

            System.out.println(map);//{name=xiaomi, birth=2017-08-15, id=2, email=77@163.com}
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null, null, connection);
        }
    }

    @Test
    public void testMapListHandler() {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();

        String sql = "SELECT id, name, email, birth " +
                "FROM customer";

        try {
            connection = JdbcTools.getConnection();
            List<Map<String, Object>> mapList = queryRunner.query(connection, sql, new MapListHandler());
            System.out.println(mapList);
            /*
            [{name=hello, birth=2017-11-16, id=0, email=hu@qq.com}, {name=hubin, birth=2017-10-18, id=1, email=122@qq.com}, {name=xiaomi,birth=2017-08-15, id=2, email=77@163.com}]

             */
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null, null, connection);
        }
    }

/*
5、通过ScalarHandler实现查询操作
 */
    @Test
    public void testScalarHandler() {
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT name FROM customer WHERE id = ?";
        try {
            connection = JdbcTools.getConnection();
            Object count = queryRunner.query(connection, sql,new ScalarHandler(), 2);
            System.out.println(count);//xiao   查看的是id=2的顾客姓名
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcTools.releaseDB(null, null, connection);
        }
    }
 //6、通过ArrayHandler\ArrayListHandler实现查询操作
    /*
     *  结果集第二种处理方法,ArrayListHandler
     *  将结果集的每一行,封装到对象数组中, 出现很多对象数组
     *  对象数组存储到List集合
     */
    public static void arrayListHandler() throws Exception {
        Connection con = JdbcTools.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM customer";
        //调用query方法,结果集处理的参数上,传递实现类ArrayListHandler
        //方法返回值 每行是一个对象数组,存储到List
        List<Object[]> result = qr.query(con, sql, new ArrayListHandler());

        //集合的遍历
        for (Object[] objs : result) {
            //遍历对象数组
            for (Object obj : objs) {
                System.out.print(obj + "  ");
            }
            System.out.println();
            /*
0  hello  hu@qq.com  2017-11-16
1  hubin  122@qq.com  2017-10-18
2  xiaomi  77@163.com  2017-08-15
             */
        }
    }

    /*

     *  结果集第一种处理方法, ArrayHandler
     *  将结果集的第一行存储到对象数组中  Object[]
     */
    public static void arrayHandler() throws Exception {
        Connection con = JdbcTools.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * FROM customer";
        //调用方法query执行查询,传递连接对象,SQL语句,结果集处理方式的实现类
        //返回对象数组
        Object[] result = qr.query(con, sql, new ArrayHandler());
        for (Object obj : result) {
            System.out.print(obj+"\t");//0    hello    hu@qq.com    2017-11-16
        }
    }
    public static void main(String[] args) throws Exception {
       // new Query().arrayHandler();
        new Query().arrayListHandler();
    }

}