Commons-dbutils 查询

3、查询结果转pojo对象列表

commons-dbuitls查询功能的例子

使用mysql数据库，创建表：

CREATE TABLE `student` (
  `userId` int(11) NOT NULL,
  `userName` varchar(30) NOT NULL,
  `gender` char(1) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

2、初始化数据

userId	userName	gender	age
1	张三	M	20
2	李四	M	21
3	王五	M	22
4	小明	M	6
5	小丽	F	9

3、查询结果转pojo对象列表

public void queryBeanList(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";

    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<List<Student>> handler = new BeanListHandler<Student>(Student.class);
    List<Student> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }

    if (null == result) {
        return;
    }
    for (Student student : result) {
        System.out.println(student);
    }
}

4、查询结果转pojo单个对象

public void queryBean(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";

    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Student> handler = new BeanHandler<Student>(Student.class);
    Student result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }

    System.out.println(result);
}

5、将查询结果转换成map对象列表

public void queryMapList(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";

    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<List<Map<String, Object>>> handler = new MapListHandler();
    List<Map<String, Object>> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }

    if (null == result) {
        return;
    }
    for (Map<String, Object> map : result) {
        System.out.println(map);
    }
}

6、查询结果转换成一个map对象

public void queryMap(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";

    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Map<String, Object>> handler = new MapHandler();
    Map<String, Object> result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }

    System.out.println(result);
}

7、自定义resultSetHandler例子

public void queryCustomHandler(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";

    // 新实现一个ResultSetHandler
    ResultSetHandler<List<Student>> handler = new ResultSetHandler<List<Student>>() {
        @Override
        public List<Student> handle(ResultSet resultset)
                throws SQLException {
            List<Student> result = new ArrayList<Student>();
            while (resultset.next()) {
                Student student = new Student();
                student.setUserId(resultset.getInt("userId"));
                student.setUserName(resultset.getString("userName"));
                student.setGender(resultset.getString("gender"));
                student.setAge(resultset.getInt("age"));
                result.add(student);
            }

            return result;
        }

    };

    QueryRunner run = new QueryRunner(ds);
    List<Student> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }

    if (null == result) {
        return;
    }
    for (Student student : result) {
        System.out.println(student);
    }
}

3、查询结果转pojo对象列表