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
联系我，非常感谢。
