QueryRunner使用案例

来源：互联网发布：手机能退淘宝保证金吗编辑：程序博客网时间：2024/05/19 15:25

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）作为参数传入方法内，处理这个结果集，返回一个对象。ArrayHandlerArrayListHandlerBeanHandlerBeanListHandlerMapHandlerMapListHandlerScalarHandler我们学习了此组件的两个类和一个接口以后，写了下列代码供参考。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);    }}

阅读全文

0 0