JDBC封装-DBUtils
阅读 (363256)一、DBUtils简介
DBUtils是Apache Commons组件中的一员,开源免费!
DBUtils是对JDBC的简单封装,但是它还是被很多公司使用!
DBUtils的Jar包:dbutils.jar
DBUtils的作用
简化JDBC的代码
Stu.java
public class Stu {
private int sid;
private String sname;
private int age;
private String gender;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
@Override
public String toString() {
return "Stu [sid=" + sid + ", sname=" + sname + ", age=" + age
+ ", gender=" + gender + "]";
}
public Stu() {
super();
// TODO Auto-generated constructor stub
}
public Stu(int sid, String sname, int age, String gender) {
super();
this.sid = sid;
this.sname = sname;
this.age = age;
this.gender = gender;
}
}
增
@Test
public void addStu(Stu stu) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
String sql = "insert into t_user values(?,?,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, stu.getSid());
pstmt.setString(2, stu.getSname());
pstmt.setInt(3, stu.getAge());
pstmt.setString(4, stu.getGender());
pstmt.executeUpdate();
} catch(Exception e) {
//处理异常
} finally {
//关闭
}
}
改
@Test
public void updateStu(Stu stu) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
String sql = "update t_stu set sname=?, age=?, gender=? where sid=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(4, stu.getSid());
pstmt.setString(1, stu.getSname());
pstmt.setInt(2, stu.getAge());
pstmt.setString(3, stu.getGender());
pstmt.executeUpdate();
} catch(Exception e) {
//处理异常
} finally {
//关闭
}
}
删
@Test
public void deleteStu(int sid) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
String sql = "delete from t_stu where sid=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, sid);
pstmt.executeUpdate();
} catch(Exception e) {
//处理异常
} finally {
//关闭
}
}
查
public Stu load(int sid) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
String sql = "select * from t_stu where sid=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, sid);
rs = pstmt.executeQuery();
if(!rs.next()) return null;
/*
* 需要把rs转换成Stu对象
* rs --> javabean
*/
Stu stu = new Stu();
stu.setSid(rs.getInt("sid"));
stu.setSname(rs.getString("sname"));
stu.setAge(rs.getInt("age"));
stu.setGender(rs.getString("gender"));
return stu;
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
//关闭
}
}
二、DBUtils核心类
DbUtils:都是静态方法,一系列的close()方法;
QueryRunner:
update():执行insert、update、delete;
query():执行select语句;
batch():执行批处理。
三、QueryRunner之更新
QueryRunner的update()方法可以用来执行insert、update、delete语句。
- 创建QueryRunner
构造器:QueryRunner();
- update()方法
int update(Connection con, String sql, Object… params)
@Test
public void fun1() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "insert into user values(?,?,?)";
qr.update(JdbcUtils.getConnection(), sql, "u1", "zhangSan", "123");
}
还有另一种方式来使用QueryRunner
- 创建QueryRunner
构造器:QueryRunner(DataSource)
- update()方法
int update(String sql, Object… params)
这种方式在创建QueryRunner时传递了连接池对象,那么在调用update()方法时就不用再传递Connection了。
@Test
public void fun2() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into user values(?,?,?)";
qr.update(sql, "u1", "zhangSan", "123");
}
四、ResultSetHandler
我们知道在执行select语句之后得到的是ResultSet,然后我们还需要对ResultSet进行转换,得到最终我们想要的数据。你可以希望把ResultSet的数据放到一个List中,也可能想把数据放到一个Map中,或是一个Bean中。
DBUtils提供了一个接口ResultSetHandler,它就是用来ResultSet转换成目标类型的工具。你可以自己去实现这个接口,把ResultSet转换成你想要的类型。
DBUtils提供了很多个ResultSetHandler接口的实现,这些实现已经基本够用了,我们通常不用自己去实现ResultSet接口了。
MapHandler:单行处理器!把结果集转换成Map<String,Object>,其中列名为键!
MapListHandler:多行处理器!把结果集转换成List<Map<String,Object>>;
BeanHandler:单行处理器!把结果集转换成Bean,该处理器需要Class参数,即Bean的类型;
BeanListHandler:多行处理器!把结果集转换成List
ColumnListHandler:多行单列处理器!把结果集转换成List
ScalarHandler:单行单列处理器!把结果集转换成Object。一般用于聚集查询,例如select count(*) from tab_student。
Map处理器
Bean处理器
Column处理器
Scalar处理器
五、QueryRunner之查询
QueryRunner的查询方法是:
public
public
query()方法会通过sql语句和params查询出ResultSet,然后通过rh把ResultSet转换成对应的类型再返回。
@Test
public void fun1() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student where number=?";
Map<String,Object> map = qr.query(sql, new MapHandler() , "S_2000");
System.out.println(map);
}
@Test
public void fun2() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
List<Map<String,Object>> list = qr.query(sql, new MapListHandler() );
for(Map<String,Object> map : list) {
System.out.println(map);
}
}
@Test
public void fun3() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student where number=?";
Student stu = qr.query(sql, new BeanHandler<Student>(Student.class) , "S_2000");
System.out.println(stu);
}
@Test
public void fun4() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
List<Student> list = qr.query(sql, new BeanListHandler<Student>(Student.class)) ;
for(Student stu : list) {
System.out.println(stu);
}
}
@Test
public void fun5() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
List<Object> list = qr.query(sql, new ColumnListHandler("name")) ;
for(Object s : list) {
System.out.println(s);
}
}
@Test
public void fun6() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select count(*) from tab_student";
Number number = (Number)qr.query(sql, new ScalarHandler() );
int cnt = number.intValue() ;
System.out.println(cnt);
}
六、QueryRunner之批处理
QueryRunner还提供了批处理方法:batch()。
我们更新一行记录时需要指定一个Object[]为参数,如果是批处理,那么就要指定Object[][]为参数了。即多个Object[]就是Object[][]了,其中每个Object[]对应一行记录:
@Test
public void fun10() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "insert into tab_student values(?,?,?,?)";
Object[][] params = new Object[10][]; //表示 要插入10行记录
for(int i = 0; i < params.length; i++) {
params[i] = new Object[]{"S_300" + i, "name" + i, 30 + i, i%2==0?"男":"女"};
}
qr.batch (sql, params);
}