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对象列表