QueryRunner使用案例

QueryRunner使用   http://blog.sina.com.cn/s/blog_64e467d60100u1uu.html
在相继学习了JDBC和数据库操作之后，我们明显感到编写JDBC代码并非一件轻松的事儿。为了帮助我们更高效的学习工作，从JDBC的繁重代码中解脱出来，老佟给我们详尽介绍了一个简化JDBC操作的组件——DBUtils。我们今天主要学习了它所提供的两个类和一个接口。
组件下载地址：http://commons.apache.org/dbutils/
DbUtils类(org.apache.commons.dbutils.DbUtils)主要负责装载驱动、关闭连接的常规工作。
1、close: 检查所提供的参数是不是NULL，如果不是的话，它们就关闭连接、声明和结果集。
2、CloseQuietly:避免连接、声明或结果集为NULL的情况被关闭。
3、CommitAndCloseQuietly(Connection conn)：用来提交连接，然后关闭连接，并且在关闭连接时不向上抛出在关闭时发生的一些SQL异常。
4、LoadDriver(String driveClassName): 装载并注册JDBC驱动程序，如果成功就返回TRUE。
QreryRunner类(org.apache.commons.dbutils.QueryRunner) 显著的简化了SQL查询，并与ResultSetHandler协同工作将使编码量大为减少。
1、query(Connection conn, String sql, Object[] params, ResultSetHandler rsh)：执行选择查询，在查询中，对象阵列的值被用来作为查询的置换参数。
2、query(String sql, Object[] params, ResultSetHandler rsh)：方法本身不提供数据库连接，执行选择查询，在查询中，对象阵列的值被用来作为查询的置换参数。
3、query(Connection conn, String sql, ResultSetHandler rsh)：执行无需参数的选择查询。
4、update(Connection conn, String sql, Object[] params)：被用来执行插入、更新或删除（DML）操作。
ResultSetHandler接口(org.apache.commons.dbutils.ResultSethandler)执行处理一个结果集对象，将数据转变并处理为任何一种形式，供其他应用使用。
1、Object handle (java.sql.ResultSet .rs) ：结果集（ResultSet）作为参数传入方法内，处理这个结果集，返回一个对象。
ArrayHandler
ArrayListHandler
BeanHandler
BeanListHandler
MapHandler
MapListHandler
ScalarHandler
我们学习了此组件的两个类和一个接口以后，写了下列代码供参考。
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class TestQueryRunner {
       public static void main(String[] args) throws SQLException {
               //queryOracle();
               update();
      }
      private static void update() throws SQLException{
               QueryRunner runner = new QueryRunner();
               Connection conn = DBManager.getConnection();
               String sql = "UPDATE examstudent SET student_name = ? WHERE flow_id = ?"; //删除非 manager 中工资 低于 5000 的员工
               Object [] params = new Object[]{"Jerry", 5000};
               runner.update(conn, sql, params);
       }
       private static void insert() throws SQLException{
                   QueryRunner runner = new QueryRunner();
                   Connection conn = DBManager.getConnection();
                 String sql = "INSERT INTO examstudent(flow_id, type, id_card, exam_card, student_name, location, grade) VALUES(?, ?, ?, ?, ?, ?, ?)"; //删除非 manager 中工资 低于 5000 的员工
                   Object [] params = new Object[]{5000, 6, "身份证", "准考证", "Tom", "北京", 99};
                   runner.update(conn, sql, params);
         }
         private static void delete() throws SQLException{
                   QueryRunner runner = new QueryRunner();
                   Connection conn = DBManager.getConnection();
                   //删除非 manager 中工资 低于 5000 的员工
                   String sql = "delete from employees " + "where employee_id not in " +
                                                " (select distinct d.manager_id from departments d where d.manager_id is not null) " +
                                                "and salary < ?";
                   System.out.println(sql);
                   Object [] params = new Object[]{5000};
                   runner.update(conn, sql, params);
         }
         private static void queryOracle() throws SQLException{
                   QueryRunner runner = new QueryRunner();
                   Connection conn = DBManager.getConnection();
                   //oracle 中的别名可以别解析
                   String sql = "SELECT flow_id flowid, type, id_card idcard, exam_card examcard, student_name studentname, location, grade FROM examstudent";
                   Object obj = runner.query(conn, sql, new BeanListHandler(ExamStudent.class));
                   System.out.println(obj);
         }
         private static void query() throws SQLException {
                   //1. 创建一个 QueryRunner 的实例
                   QueryRunner runner = new QueryRunner();
                   Connection conn = DBManager.getConnection();
                   String sql = "SELECT id, name, address, phone FROM customers WHERE name LIKE ?";
                   Class type = Customer.class;
                   Object [] params = new Object[]{"%%"};
                   //2. 查询操作
                   //conn: 查询需要的数据库连接, sql: 查询使用的 sql 语句, rsh: 如何转换查询得到的结果集, params: 填补 sql 语句参数的数组
                   Object obj = runner.query(conn, sql, new BeanListHandler(type), params);
                   //System.out.println("^^" + obj);
                   sql = "SELECT flow_id flowid, type, id_card idcard, exam_card examcard, student_name studentname, location, grade FROM examstudent";
                   type = ExamStudent.class;
                   obj = runner.query(conn, sql, new BeanListHandler(ExamStudent.class));
                   System.out.println(obj);
                   }
}

QueryRunner使用案例：

package cn.imau.utils;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.persistence.Column;
import javax.persistence.Table;
import javax.sql.DataSource;

import org.apache.commons.dbutils.ResultSetHandler;
public class QueryRunner extends org.apache.commons.dbutils.QueryRunner{
    //添加save方法
    /** * 对传递的bean进行分析 * 将t对象转成insert into users */
    public <T> T save(T t) throws Exception{
        //获取类 
        Class<?> cls = t.getClass();
        //从这个字节码上获取name值这个就是表名
        Table table = cls.getAnnotation(Table.class);
        //获取表名 
        String tableName = table.name();
        //组成insert into users(id,name,pwd) values('id',"name','');
        String sql = "insert into "+tableName;
        String cols="(";
        String values="values(";

        //获取所有声明的字段
        Field[] fs = cls.getDeclaredFields();
        //遍历所有字段
        for(Field f:fs){
            if(f.isAnnotationPresent(Column.class)){
                //获取列名
                String colName = f.getName();
                //获取column的对象
                Column col = f.getAnnotation(Column.class);
                if(col.name()!=null && !col.name().trim().equals("")){
                    colName=col.name();
                } 
                //获取列值
                f.setAccessible(true);
                //获取列值
                Object value = f.get(t);
                if(cols.equals("(")){ 
                    cols+=colName;
                    if(value instanceof String){
                        values+="'"+value+"'";
                    }else{
                        values+=value;
                    }

                }else{
                    cols+=","+colName;
                    if(value instanceof String){
                        values+=",'"+value+"'";
                    }else{
                        values+=","+value;
                    }
                }
            }
        }
        cols+=")";
        values+=")";
        sql = sql+cols+" "+values;
        System.err.println(sql);
        update(sql);
        return t;
    }




    public QueryRunner() {
    }
    public QueryRunner(DataSource ds){
        super(ds);
    }
    @Override
    public int[] batch(Connection arg0, String arg1, Object[][] arg2){
        try {
            return super.batch(arg0, arg1, arg2);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }
    }
    @Override
    public int[] batch(String sql, Object[][] params){
        try {
            return super.batch(sql, params);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    protected void close(Connection conn) {
        try {
            super.close(conn);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override  
    protected void close(ResultSet rs){
        try {
            super.close(rs);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }
    }

    @Override
    protected void close(Statement stmt){
        try {
            super.close(stmt);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }
    }

    @Override
    public void fillStatement(PreparedStatement arg0, Object... arg1)
            {
        try {
            super.fillStatement(arg0, arg1);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public void fillStatementWithBean(PreparedStatement arg0, Object arg1,
            PropertyDescriptor[] arg2){
        try {
            super.fillStatementWithBean(arg0, arg1, arg2);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public void fillStatementWithBean(PreparedStatement arg0, Object arg1,
            String... arg2) {
        try {
            super.fillStatementWithBean(arg0, arg1, arg2);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public DataSource getDataSource() {
        return super.getDataSource();
    }

    @Override
    protected Connection prepareConnection() {
        try {
            return super.prepareConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    protected PreparedStatement prepareStatement(Connection conn, String sql)
            {
        try {
            return super.prepareStatement(conn, sql);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public <T> T query(Connection conn, String sql, Object param,
            ResultSetHandler<T> rsh) {
        try {
            return super.query(conn, sql, param, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public <T> T query(Connection conn, String sql, Object[] params,
            ResultSetHandler<T> rsh) {
        try {
            return super.query(conn, sql, params, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    public <T> T query(Connection arg0, String arg1, ResultSetHandler<T> arg2,
            Object... arg3){
        try {
            return super.query(arg0, arg1, arg2, arg3);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    } 

    @Override
    public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh)
             {
        try {
            return super.query(conn, sql, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public <T> T query(String sql, Object param, ResultSetHandler<T> rsh)
         {
        try {
            return super.query(sql, param, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh)
             {
        try {
            return super.query(sql, params, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
             {
        try {
            return super.query(sql, rsh, params);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public <T> T query(String sql, ResultSetHandler<T> rsh){
        try {
            return super.query(sql, rsh);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    protected void rethrow(SQLException cause, String sql, Object... params)
             {
        try {
            super.rethrow(cause, sql, params);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public int update(Connection arg0, String arg1, Object... arg2)
             {
        try {
            return super.update(arg0, arg1, arg2);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public int update(Connection conn, String sql, Object param)
             {
        try {
            return super.update(conn, sql, param);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public int update(Connection conn, String sql) {
        try {
            return super.update(conn, sql);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public int update(String sql, Object... params){
        try {
            return super.update(sql, params);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public int update(String sql, Object param){
        try {
            return super.update(sql, param);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    public int update(String sql) {
        try {
            return super.update(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage(),e);
        }

    }

    @Override
    protected ResultSet wrap(ResultSet rs) {
        return super.wrap(rs);
    }
}