DbUtils使用

前言

 本文介绍了如何使用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

		//返回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);

附:其他代码

数据库代码

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/
关于作者:专注于Java企业运用、海量数据处理、hadoop、数字图像处理等。
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,如有问题,可以通过wushiqi54719880@126.com  联系我,非常感谢。

posted @ 2011-06-23 13:07  红枫落叶  阅读(3661)  评论(0编辑  收藏  举报