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中中字段的左右空格。
QueryRunner
构造 QueryRunner : 有两种方法来构造QueryRunner 一种是无参数的 一种是有DataSource类型参数的构造方法
1:QueryRunner() 2:QueryRunner(javax.sql.DataSource ds)
这里使用DataSource 来构造它 使用连接池来构造它的好处就是 我们无需管Connection 对象的创建与关闭了,后面调用QueryRunner对象的方法也无需传入Connection对象了
- /**
- * dbcp连接池的用法
- */
- public class DBUtils {
- private static Properties properties;
- private static DataSource dataSource;
- private static Connection conn;
- static {
- try {
- properties = new Properties();
- //载入dbcp的配置文件
- properties.load(DBUtils.class.
- .getResourceAsStream("/dbcpconfig.properties"));
- //创建个BasicDataSourceFactory对象用于创建连接池对象
- BasicDataSourceFactory b = new BasicDataSourceFactory();
- //把properties对象设置给BasicDataSourceFactory
- dataSource = b.createDataSource(properties);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- public static DataSource getdataSource() {
- return dataSource;
- }
- }
注意:需要导入dbcp连接池的jar commons-dbcp-1.2.1.jar 和所依赖的 commons-pool-1.2.jar 和commons-collections-3.1.jar
这样我们就可以构造个queryRunner对象了
QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
QueryRunner 主要方法 :
- //更新一列
- queryRunner.update("update Users set username=?,password=? where id = ?", new String[]{"11","22","1"});
- //删除一列
- queryRunner.update("delete from Users where id =?", 1);
如果要 使用 QueryRunner的查询方法 也就是query()方法的话 那么就需要有对象来接收查询出来的ResultSet
用什么来接收呢? 要用到 实现ResultSetHandler 接口的类
1. BeanHandler 返回个entitybean 对象
- //指定返回哪个 entitybean
- ResultSetHandler rsh = new BeanHandler(Users.class);
- QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
- Users users = (Users) queryRunner.query("select * from Users where id=?",2, rsh);
- System.out.println(users.getUsername());
2. BeanListHandler 返回个List List里装的是entitybean 对象
- QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
- ResultSetHandler rsh = new BeanListHandler(Users.class);
- List<Users> list = (List) queryRunner.query(
- "select * from Users ", rsh);
- for(Users user:list){
- System.out.println(user.getUsername());
- }
3. ArrayListHandler 返回List 对象 list里装的为Object数组
- QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
- // 使用ArrayListHandler 实现类来存放结果会返回ArrayList对象
- ResultSetHandler rsh = new ArrayListHandler();
- //list里装的是对象数组
- List<Object[]> list = (List) queryRunner.query(
- "select * from Users ", rsh);
- for(Object[] user:list) {
- for(Object obj:user)
- {
- System.out.println(obj);
- }
- }
4. ColumnListHandler 返回List 对象
注意: 构造 ColumnListHandler() 对象时候如果 是无参数的话 像这样 ResultSetHandler rsh = new ColumnListHandler(); 查询出来的List 里放的类型为主键的类型 值为主键的值;
- ResultSetHandler rsh = new ColumnListHandler();
- List<Integer> list = (List) queryRunner.query(
- "select * from Users ", rsh);
- //System.out.println(list.get(0).getClass().getSimpleName());
- for(Integer in:list){
- System.out.println(in);
- }
构造 ColumnListHandler() 对象时候如果 是有参数的话 ,像这样 ResultSetHandler rsh = new ColumnListHandler(主键值); 那么查询出来的List 里放的是就是这个主键对应的一行的记录;
- ResultSetHandler rsh = new ColumnListHandler(2);
- List<String> list = (List) queryRunner.query(
- "select * from Users ", rsh);
- for(String in:list){
- System.out.println(in);
- }
5. KeyedHandler 根据键返回Map
- ResultSetHandler rsh = new KeyedHandler();
- //map里装的key为主键的值 value 为主键对应的那行记录名称和记录
- Map map = (Map) queryRunner.query(
- "select id,username,password from Users ", rsh);
- //根据主键值出去那一列
- // Map t = (Map) map.get(2);
- System.out.println(map.get(2));
6. MapHandler 根据键返回Map
- QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource());
- ResultSetHandler rsh = new MapHandler();
- Map map = (Map) queryRunner.query(
- "select * from Users ", rsh);
- System.out.println(map);
下面给出一个完整的例子,建立三个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);
- }
- }
- }