数据库连接池与DBUtils工具

一、查询方式

package cn.itcast.chapter10.example;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbutils.ResultSetHandler;
public class BaseDao {
// 优化查询
public static Object query(String sql, ResultSetHandler<?> rsh,
Object… params) throws SQLException {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 定义一个返回结果
Object obj = null;
try {
// 获得连接
conn = JDBCUtils.getConnection();
// 预编译sql
pstmt = conn.prepareStatement(sql);
// 将参数设置进去
for (int i = 0; params != null && i < params.length; i++)
{
pstmt.setObject(i + 1, params[i]);
}
// 发送sql
rs = pstmt.executeQuery();
// 让调用者去实现对结果集的处理
obj = rsh.handle(rs);
} catch (Exception e) {
// 出现异常，返回封装的异常信息
return new Exception(e.getMessage());
}finally {
// 释放资源
JDBCUtils.release(rs, pstmt, conn);
}
return obj;
}
}

二、在cn.itcast.chapter 10.example包下创建实体类User, 使用该类来封装User对象，具体实现方式如下：

package cn.itcast.chapter10.example;
public class User {
private int id;
private String name;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}

三.在cnitcast.chapter10.example 包下创建类ResultSetTest1， 该类用于演示BeanHandler类对结果集的处理，具体代码如下：

package cn.itcast.chapter10.example;
import java.sql.SQLException;
import org.apache.commons.dbutils.handlers.BeanHandler;
public class ResultSetTest1 {
public static void testBeanHandler() throws SQLException {
BaseDao basedao = new BaseDao();
String sql = “select * from user where id=?”;
// 获取查询结果
Object object = basedao.query(sql, new BeanHandler(User.class), 1);
// 判断查询结果，如果是User类就进行打印，否则打印查询的结果信息
if (object!=null && object instanceof User){
User user= (User) object;
System.out.print(“id为1的User对象的name值为：” + user.getName());
}else {
System.out.println("查询结果为空: "+object);
}
}
public static void main(String[] args) throws SQLException {
testBeanHandler();
}
}