第十七天dbutils的使用------Commons DbUtils(Apache)第三方的:只是对JDBC编码进行了简单的封装



这篇文章和前一篇的文章--------->第十七天3月12日之一句代码实现crud操作（手动写的，没使用其他jar包和框架）

所实现的功能差不多。这是第三方开发的，要导入相应的jar包(就一个jar包)，比自己写的强大。强大之处在于结果集的处理。主要涉及的类是

org.apache.commons.dbutils
Class QueryRunner

API如下：

Constructor SummaryQueryRunner()
          Constructor for QueryRunner.QueryRunner(boolean pmdKnownBroken)
          Constructor for QueryRunner, allows workaround for Oracle driversQueryRunner(DataSource ds)
          Constructor for QueryRunner which takes a DataSource.QueryRunner(DataSource ds, boolean pmdKnownBroken)
          Constructor for QueryRunner, allows workaround for Oracle drivers.

 

Method Summary int[]batch(Connection conn,String sql, Object[][] params)
          Execute a batch of SQL INSERT, UPDATE, or DELETE queries. int[]batch(String sql,Object[][] params)
          Execute a batch of SQL INSERT, UPDATE, or DELETE queries.<T> Tquery(Connection conn,String sql, Object[] params, ResultSetHandler<T> rsh)
          Deprecated. Use query(Connection,String,ResultSetHandler,Object...) instead<T> Tquery(Connection conn,String sql, Object param, ResultSetHandler<T> rsh)
          Deprecated. Use query(Connection, String, ResultSetHandler, Object...)<T> Tquery(Connection conn,String sql, ResultSetHandler<T> rsh)
          Execute an SQL SELECT query without any replacement parameters.<T> Tquery(Connection conn,String sql, ResultSetHandler<T> rsh, Object... params)
          Execute an SQL SELECT query with replacement parameters.<T> Tquery(String sql,Object[] params, ResultSetHandler<T> rsh)
          Deprecated. Use query(String, ResultSetHandler, Object...)<T> Tquery(String sql,Object param, ResultSetHandler<T> rsh)
          Deprecated. Use query(String, ResultSetHandler, Object...)<T> Tquery(String sql,ResultSetHandler<T> rsh)
          Executes the given SELECT SQL without any replacement parameters.<T> Tquery(String sql,ResultSetHandler<T> rsh, Object... params)
          Executes the given SELECT SQL query and returns a result object. intupdate(Connection conn,String sql)
          Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters. intupdate(Connection conn,String sql, Object... params)
          Execute an SQL INSERT, UPDATE, or DELETE query. intupdate(Connection conn,String sql, Object param)
          Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter. intupdate(String sql)
          Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters. intupdate(String sql,Object... params)
          Executes the given INSERT, UPDATE, or DELETE SQL statement. intupdate(String sql,Object param)
          Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter.

一、框架编写准备：数据库元数据的获取
1、元数据：数据库、表、列的定义信息
二、编写自己的框架简化JDBC开发

CUD：语句不同和参数不同。

三、ORM：
O：Object
R：Relation
M：Mapping
对象关系映射

JavaBean   关系数据库表结构   ：对应的映射关系

Hibernate：ORM映射框架   -----》规范化：JPA（Java Persistent API)
IBatis(Apache):ORM映射框架----------->2010(google) MyBatis

Commons DbUtils(Apache):只是对JDBC编码进行了简单的封装。
Spring JDBC Template:只是对JDBC编码进行了简单的封装。

例子：不用自己关闭资源，底层源代码已经关了，而且是结合dbcp使用的，关闭不是真的关闭，而是加入到连接池中，以后就放心使用，一句话解决crud操作。

//账户维护public class DBUtilDemo {private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());@Testpublic void testAdd() throws Exception{qr.update("insert into account(name,money) values(?,?)", "fff",1000);}@Testpublic void testFindOne() throws Exception{Account a = qr.query("select * from account where id=?", new BeanHandler<Account>(Account.class),1);System.out.println(a);}@Testpublic void testFindAll() throws Exception{List<Account> list = qr.query("select * from account", new BeanListHandler<Account>(Account.class));for(Account a:list)System.out.println(a);}//插入大文本行不行：知道clob对应的类型是什么/* * create table t1(id int,content text); */@Testpublic void testText() throws Exception{File file = new File("src/jpm.txt");Reader reader = new FileReader(file);//流并没有对应的数据库类型char ch[] = new char[(int)file.length()];reader.read(ch);//不好，开发中不用reader.close();Clob clob = new SerialClob(ch);qr.update("insert into t1(id,content) values(?,?)", 1,clob);}//插入大二进制行不行：知道blob对应的类型是什么/* * create table t2(id int,content longblob); */@Testpublic void testBlob() throws Exception{InputStream in = new FileInputStream("src/1.jpg");byte b[] = new byte[in.available()];in.read(b);in.close();Blob blob = new SerialBlob(b);qr.update("insert into t2(id,content) values(?,?)", 1,blob);}//批处理/* * create table t3(id int,name varchar(200)); */@Testpublic void testBatch()throws Exception{Object params[][] = new Object[10][];//第1维，插入的条数。第2维，每条需要的参数for(int i=0;i<params.length;i++){params[i] = new Object[]{i+1,"aaa"+(i+1)};}qr.batch("insert into t3 values(?,?)", params);}}

部分源代码如下：说明已经关闭资源了。

  public int update(String sql, Object... params) throws SQLException {        Connection conn = this.prepareConnection();        return this.update(conn, true, sql, params);    }    /**     * Calls update after checking the parameters to ensure nothing is null.     * @param conn The connection to use for the update call.     * @param closeConn True if the connection should be closed, false otherwise.     * @param sql The SQL statement to execute.     * @param params An array of update replacement parameters.  Each row in     * this array is one set of update replacement values.     * @return The number of rows updated.     * @throws SQLException If there are database or parameter errors.     */    private int update(Connection conn, boolean closeConn, String sql, Object... params) throws SQLException {        if (conn == null) {            throw new SQLException("Null connection");        }        if (sql == null) {            if (closeConn) {                close(conn);            }            throw new SQLException("Null SQL statement");        }        PreparedStatement stmt = null;        int rows = 0;        try {            stmt = this.prepareStatement(conn, sql);            this.fillStatement(stmt, params);            rows = stmt.executeUpdate();        } catch (SQLException e) {            this.rethrow(e, sql, params);        } finally {            close(stmt);            if (closeConn) {                close(conn);            }        }        return rows;    }}

DBUtils框架提供的结果处理器例子:

public class ResultSetHandlerDemo {private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());//ArrayHandler：把结果集中的第一行数据转成对象数组。@Testpublic void test1() throws SQLException{//数组中的元素就是记录的每列的值Object[] objs = qr.query("select * from account where id=?", new ArrayHandler(), 1);for(Object obj:objs)System.out.println(obj);}//ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。@Testpublic void test2() throws SQLException{//数组中的元素就是记录的每列的值List<Object[]> list = qr.query("select * from account", new ArrayListHandler());for(Object[] objs:list){System.out.println("------------------");for(Object obj:objs){System.out.println(obj);}}}//ColumnListHandler：将结果集中某一列的数据存放到List中。 投影@Testpublic void test3() throws SQLException{//数组中的元素就是记录的每列的值List<Object> list = qr.query("select * from account", new ColumnListHandler("name"));for(Object objs:list){System.out.println(objs);}}//KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map<列名,列值>里，再把这些map再存到一个map里，其key为指定的key。@Testpublic void test4() throws SQLException{Map<Object,Map<String,Object>> bmap= qr.query("select * from account", new KeyedHandler("id"));for(Map.Entry<Object,Map<String,Object>> bme:bmap.entrySet()){System.out.println("-----------------");for(Map.Entry<String,Object> lme:bme.getValue().entrySet()){System.out.println(lme.getKey()+"="+lme.getValue());}}}//MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值@Testpublic void test5() throws SQLException{Map<String,Object> map = qr.query("select * from account where id=?", new MapHandler(),1);for(Map.Entry<String,Object> lme:map.entrySet()){System.out.println(lme.getKey()+"="+lme.getValue());}}//MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List@Testpublic void test6() throws SQLException{List<Map<String,Object>> list= qr.query("select * from account", new MapListHandler());for(Map<String,Object> map:list){System.out.println("-----------------");for(Map.Entry<String,Object> lme:map.entrySet()){System.out.println(lme.getKey()+"="+lme.getValue());}}}//ScalarHandler :只有一条记录的投影查询 select count(*) from account;@Testpublic void test7() throws SQLException{//Object obj = qr.query("select * from account where id=?", new ScalarHandler("name"),1);//System.out.println(obj);Object obj = qr.query("select count(*) from account", new ScalarHandler(1));int num = ((Long)obj).intValue();System.out.println(num);}}