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); } }