DbUtils使用

jopen 12年前

前言

 本文介绍了如何使用apache dbutils。主要以测试代码介绍。本文的代码使用了dbcp,请参见文章:dbutils与dbcp整合
使用代码
1.更新操作(insert、update、delete),使用update方法完成:
  int update(String sql)int update(String sql, Object... params) 
  int update(String sql, Object param) 
  参数:sql:为需要执行的sql语句,
    params为参数
示例:
runner.update("delete from user where userName=?","用户名");     int rowEffects = runner.update("insert into user(userName,password,comment) values(?,?,?)", "用户名","密码","备注");

2.查询结果 
dbutils支持返回以下类型的结果:
   ArrayHandler :将结果集中第一行的数据转化成对象数组。返回值类型:Object[]
   ArrayListHandler将结果集中所有的数据转化成List。返回值类型:List<Object[]>
   BeanHandler :将Object中第一行的数据转化成类对象。返回值类型:T
   BeanListHandler :将Object中所有的数据转化成List,List中存放的是类对象。返回值类型:List<T>
   ColumnListHandler :将Object中某一列的数据存成List,List中存放的是 Object对象。返回值类型:List<Object>
   KeyedHandler :将Object中存成映射,key为某一列对应为Map。Map中存放的是数据。Map<关键字字段值,map<列名,字段值>>返回值类型:Map<Object,Map<String,Object>>
   MapHandler :将结果集中第一行的数据存成Map<列名,字段值>映射。返回值类型:Map<String,Object>
   MapListHandler :将结果集中所有的数据存成List。List中存放的是Map<列名,字段值>。返回值类型:List<Map<String,Object>>
   ScalarHandler :返回结果集中的第一行的指定列的一个值。返回值类型:Object
</div>
//返回ArrayHandler结果,第一行结果:Object[]  System.out.println("返回ArrayHandler结果......");  Object[] arrayResult = runner.query("select * from user", new ArrayHandler());  for (int i = 0; i < arrayResult.length; i++) {      System.out.print(arrayResult[i] + "    ");  }  System.out.println();        //返回ArrayListHandler结果,第一行结果:List<Object[]>  System.out.println("返回ArrayListHandler结果.........");  List<Object[]> arrayListResult = runner.query("select * from user", new ArrayListHandler());  for (int i = 0; i < arrayListResult.size(); i++) {      for (int j = 0; j < arrayListResult.get(i).length; j++) {          System.out.print(arrayListResult.get(i)[j]+"    ");      }      System.out.println();  }  System.out.println();        //返回bean  User user = runner.query("select * from user where userId=?", 1,new BeanHandler<User>(User.class));  Assert.assertEquals(user.getUserName(), "用户名");        //返回beanlist  System.out.println("返回BeanList结果......");  List<User> beanListResult = runner.query("select * from user", new BeanListHandler(User.class));  Iterator<User> iter_beanList = beanListResult.iterator();  while(iter_beanList.hasNext()){      System.out.println(iter_beanList.next().getUserName());  }        //返回指定列  System.out.println("返回ColumnList结果......");  List<Object> columnResult = runner.query("select * from user",new ColumnListHandler("userName"));  Iterator<Object> iter = columnResult.iterator();  while(iter.hasNext()){      System.out.println(iter.next());  }           //返回KeyedHandler结果:Map<Object,Map<String,Object>>:map的key为KeyedHandler指定  System.out.println("返回KeyedHandler结果.........");  Map<Object, Map<String, Object>> keyedResult = runner.query("select * from user", new KeyedHandler("userName"));  System.out.println(keyedResult.get("用户名").get("userId"));        //MapHandler  System.out.println("返回MapHandler结果.........");  Map<String, Object> mapResult = runner.query("select * from user", new MapHandler());  Iterator<String> iter_mapResult = mapResult.keySet().iterator();  while (iter_mapResult.hasNext()) {      System.out.print(mapResult.get(iter_mapResult.next())+"   ");  }  System.out.println();        //返回MapListHandler结果  System.out.println("返回MapListHandler结果.........");  List<Map<String,Object>> mapListResult = runner.query("select * from user", new MapListHandler());  for(int i=0;i<mapListResult.size();i++){      Iterator<String> values = mapListResult.get(i).keySet().iterator();      while(values.hasNext()){          System.out.print(mapListResult.get(i).get(values.next())+"   ");      }      System.out.println();  }     Object increaseId=runner.query("select last_insert_id()", new ScalarHandler());  System.out.println(increaseId);

附:其他代码
数据库代码
</div>
DROP TABLE IF EXISTS `user`;  CREATE TABLE `user` (    `comment` varchar(250) DEFAULT NULL,    `password` varchar(20) DEFAULT NULL,    `userName` varchar(20) NOT NULL,    `userId` int(11) NOT NULL AUTO_INCREMENT,    PRIMARY KEY (`userId`)  ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;  INSERT INTO `user` VALUES ('备注', '密码', '用户名', '1');  INSERT INTO `user` VALUES ('备注2', '密码2', '用户名2', '7');

User.java
package com.dbutils.model;     public class User {      private int userId;      private String userName;      private String password;      private String comment;             public int getUserId() {          return userId;      }      public void setUserId(int userId) {          this.userId = userId;      }      public String getUserName() {          return userName;      }      public void setUserName(String userName) {          this.userName = userName;      }      public String getPassword() {          return password;      }      public void setPassword(String password) {          this.password = password;      }      public String getComment() {          return comment;      }      public void setComment(String comment) {          this.comment = comment;      }  }

DbHelper.java
package com.dbutils.common;     import javax.sql.DataSource;     import org.apache.commons.dbcp.BasicDataSource;  import org.apache.commons.dbutils.QueryRunner;     public class DbHelper {      private static DataSource dataSource;      private DbHelper(){      }             public static QueryRunner getQueryRunner(){          if(DbHelper.dataSource==null){              //配置dbcp数据源              BasicDataSource dbcpDataSource = new BasicDataSource();              dbcpDataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull");              dbcpDataSource.setDriverClassName("com.mysql.jdbc.Driver");              dbcpDataSource.setUsername("root");              dbcpDataSource.setPassword("1234");              dbcpDataSource.setDefaultAutoCommit(true);              dbcpDataSource.setMaxActive(100);              dbcpDataSource.setMaxIdle(30);              dbcpDataSource.setMaxWait(500);              DbHelper.dataSource = (DataSource)dbcpDataSource;              System.out.println("Initialize dbcp...");          }          return new QueryRunner(DbHelper.dataSource);      }  }

原文地址:http://www.cnblogs.com/wushiqi54719880/archive/2011/06/23/2088022.html