Common Dbutils 详解

jopen 8年前

Common Dbutils是操作数据库的组件,对传统操作数据库的类进行二次封装,可以把结果集转化成List。
DBUtils包括3个包:
org.apache.commons.dbutils 
org.apache.commons.dbutils.handlers 
org.apache.commons.dbutils.wrappers
DBUtils封装了对JDBC的操作,简化了JDBC操作,可以少写代码。
 
org.apache.commons.dbutils
DbUtils 关闭链接等操作
QueryRunner 进行查询的操作
 
org.apache.commons.dbutils.handlers
ArrayHandler :将ResultSet中第一行的数据转化成对象数组
ArrayListHandler将ResultSet中所有的数据转化成List,List中存放的是 Object[]
BeanHandler :将ResultSet中第一行的数据转化成类对象
BeanListHandler :将ResultSet中所有的数据转化成List,List中存放的是类对象
ColumnListHandler :将ResultSet中某一列的数据存成List,List中存放的是 Object对象
KeyedHandler :将ResultSet中存成映射,key为某一列对应为Map。Map中存放的是数据
MapHandler :将ResultSet中第一行的数据存成Map映射
MapListHandler :将ResultSet中所有的数据存成List。List中存放的是Map
ScalarHandler :将ResultSet中一条记录的其中某一列的数据存成Object
 
org.apache.commons.dbutils.wrappers
SqlNullCheckedResultSet :对ResultSet进行操作,改版里面的值
StringTrimmedResultSet :去除ResultSet中中字段的左右空格。Trim()
 

主要方法:
DbUtils类:启动类

ResultSetHandler接口:转换类型接口

MapListHandler类:实现类,把记录转化成List

BeanListHandler类:实现类,把记录转化成List,使记录为JavaBean类型的对象

Qrery Runner类:执行SQL语句的类

 

建立三个Java文件

命名为BeanListExample.java

Guestbook.java

MapListExample.java
 

源码:
BeanListExample.java

package  com.sy;    import  org.apache.commons.dbutils.DbUtils;  import  org.apache.commons.dbutils.QueryRunner;  import  org.apache.commons.dbutils.handlers.BeanListHandler;  import  java.sql.Connection;  import  java.sql.DriverManager;  import  java.sql.SQLException;  import  java.util.List;    public   class  BeanListExample {      public   static   void  main(String[] args) {          Connection conn =   null ;          String url =   " jdbc:mysql://localhost:3306/people" ;          String jdbcDriver =   " com.mysql.jdbc.Driver" ;          String user =   " root " ;          String password =   " hicc " ;            DbUtils.loadDriver(jdbcDriver);          try   {              conn =  DriverManager.getConnection(url, user, password);              QueryRunner qr =   new  QueryRunner();              List results =  (List) qr.query(conn, " select id,name from guestbook" , new  BeanListHandler(Guestbook.class ));              for  ( int  i  =   0 ; i  <  results.size(); i++ )  {                  Guestbook gb =  (Guestbook) results.get(i);                  System.out.println(" id: "   +  gb.getId() +   " ,name: "   +  gb.getName());              }          }  catch  (SQLException e)  {              e.printStackTrace();          }  finally  {              DbUtils.closeQuietly(conn);          }      }  } 


 

Guestbook.java
package  com.sy;    public   class  Guestbook {       private  Integer id;       private  String name;        public  Integer getId() {          return  id;      }        public   void  setId(Integer id) {          this .id =  id;      }        public  String getName() {          return  name;      }        public   void  setName(String name) {          this .name =  name;      }  }   


MapListExample.java
 
package  com.sy;    import  org.apache.commons.dbutils.DbUtils;  import  org.apache.commons.dbutils.QueryRunner;  import  org.apache.commons.dbutils.handlers.MapListHandler;    import  java.sql.Connection;  import  java.sql.DriverManager;  import  java.sql.SQLException;    import  java.util.List;  import  java.util.Map;    public   class  MapListExample {      public   static   void  main(String[] args) {          Connection conn =   null ;          String url =   " jdbc:mysql://localhost:3306/people" ;          String jdbcDriver =   " com.mysql.jdbc.Driver" ;          String user =   " root " ;          String password =   " hicc " ;            DbUtils.loadDriver(jdbcDriver);          try   {              conn =  DriverManager.getConnection(url, user, password);              QueryRunner qr =   new  QueryRunner();              List results =  (List) qr.query(conn, " select id,name from guestmessage" , new  MapListHandler());              for  ( int  i  =   0 ; i  <  results.size(); i++ )  {                  Map map =  (Map) results.get(i);                  System.out.println(" id: "   +  map.get( " id " )  +   " ,name: "   +  map.get( " name " ));              }          }  catch  (SQLException e)  {              e.printStackTrace();          }  finally  {              DbUtils.closeQuietly(conn);          }      }  }   



使用组建好需要添加commons - dbutils - 1.1 .jar和mysql - connector - java - 5.1 . 6 - bin.jar 两个jar包。

 

配置完毕!!!

  // 另一种方法  // 使用dbutils1.0版本    import  java.util. * ;  import  java.util.logging.* ;  import  java.sql.* ;  import  org.apache.commons.dbutils.* ;  import  org.apache.commons.dbutils.handlers.* ;    public   class  TestDBUnits {      public   static   void  main(String[]args) throws  Exception {    TestDBUnits test =   new  TestDBUnits();        for ( int  i  =   0  ; i <   1  ; i ++ )  {        test.testQuery1();     test.testQuery2();     test.testUpdate();    }   }      public   void  testQuery1(){    try   {     QueryRunner qr =   new  QueryRunner() ;     ResultSetHandler rsh =   new  ArrayListHandler();        String strsql =   " select * from test1" ;        ArrayList result =  (ArrayList)qr.query(getConnection() ,strsql ,rsh);      // System.out.print("");    }  catch (Exception ex) {     ex.printStackTrace(System.out);    }   }      public   void  testQuery2(){    try   {     QueryRunner qr =   new  QueryRunner() ;     ResultSetHandler rsh =   new  MapListHandler();        String strsql =   " select * from test1" ;        ArrayList result =  (ArrayList)qr.query(getConnection() ,strsql ,rsh);     for ( int  i  =   0  ; i <  result.size() ; i++ )  {      Map map =  (Map)result.get(i);       // System.out.println(map);         }      // System.out.print("");    }  catch (Exception ex) {     ex.printStackTrace(System.out);    }   }      public   void  testUpdate(){    try   {     QueryRunner qr =   new  QueryRunner() ;     ResultSetHandler rsh =   new  ArrayListHandler();     String strsql =   " insert test1(page ,writable ,content)values('ttt','ttt','faskldfjklasdjklfjasdklj')" ;     qr.update(getConnection() ,strsql);      // System.out.print("");    }  catch (Exception ex) {     ex.printStackTrace(System.out);    }   }       private  Connection getConnection() throws  InstantiationException,     IllegalAccessException, ClassNotFoundException, SQLException {        String strDriver =   " org.gjt.mm.mysql.Driver" ;    String strUrl =   " jdbc:mysql://localhost:3306/test" ;    String strUser =   " root " ;    String strPass =   "" ;         Class.forName(strDriver).newInstance();       return  DriverManager.getConnection(strUrl, strUser, strPass);   }  }