1. org.apache.commons.dbutils (该包中的类主要帮助我们更便捷的操作JDBC)
2. org.apache.commons.dbutils.handlers(该包中的类都是实现org.apache.commons.dbutils.ResultSetHandler接口的实现类)
3. org.apache.commons.dbutils.wrappers(该包中的类主要是封装了对Sql结果集的操作)
使用这个DbUtils的一些优势:
1. 防止了资源的泄露,写一段JDBC的准备代码其实并不麻烦,但是那些操作确实是十分耗时和繁琐的,也会导致有时候数据库连接忘记关闭了导致异常难以追踪。
2. 干净整洁的持久化代码,把数据持久化到数据库的代码被打打削减,剩下的代码能够清晰简洁的表达你的操作目的。
3. 自动把ResultSets中的工具映射到JavaBean中,你不需要手动的使用Setter方法将列值一个个赋予相应的时日,Resultset中的每一个行都大表一个完成的Bean实体。
要学习如何使用这个框架,最简单的方式就是用它写个Demo-CRUD操作,让我们先做个准备动作在Mysql中建立一个测试专用表Visitor
1
2
3
4
5
6
7
8
9
10
|
/*创建Visitor*/
CREATE
TABLE
Visitor
(
Id
INT
(11)
NOT
NULL
AUTO_INCREMENT,
Name
VARCHAR
(1000)
NOT
NULL
,
Email
VARCHAR
(1000)
NOT
NULL
,
Status
INT
NOT
NULL
DEFAULT
1,
CreateTime DateTime,
PRIMARY
KEY
(Id)
)
|
建完表结构,我们就可以学习怎么利用框架中的Utils类帮助我们完成CRUD-DEMO,其实对于这个框架主要操作的是ResultSetHandler接口的实现类与QueryRunner类
创建对应的JavaBean实体类如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
package
david.apache.model;
import
java.text.SimpleDateFormat;
import
java.util.Date;
public
class
Visitor {
private
int
id;
private
String name;
private
String email;
private
int
status;
private
Date createTime;
public
Visitor() {
// TODO Auto-generated constructor stub
setCreateTime(
new
Date());
}
public
Visitor(String name, String email) {
this
.setName(name);
this
.setEmail(email);
this
.setStatus(
1
);
this
.setCreateTime(
new
Date());
}
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 getEmail() {
return
email;
}
public
void
setEmail(String email) {
this
.email = email;
}
public
int
getStatus() {
return
status;
}
public
void
setStatus(
int
status) {
this
.status = status;
}
public
Date getCreateTime() {
return
createTime;
}
public
void
setCreateTime(Date createTime) {
this
.createTime = createTime;
}
@Override
public
String toString() {
// TODO Auto-generated method stub
return
String.format(
"{Id: %d, Name: %s, Email: %s, CreateTime: %s}"
, getId(), getName(), getEmail(),
new
SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss"
).format(getCreateTime()));
}
}
|
首先我们先新建一个获取Connection的方法:
1
2
3
4
5
6
7
8
9
10
|
private
static
Connection getConnection() {
Connection conn =
null
;
try
{
Class.forName(CONNECTION_DRIVER_STR);
conn = DriverManager.getConnection(CONNECTION_STR,
"root"
,
"123456"
);
}
catch
(Exception e) {
e.printStackTrace();
}
return
conn;
}
|
新建方法(对于里面的自增字段,我们可以采用变通的方法来插入,使用select last_insert_id()方法)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
/*
* 新增Visitor, ScalarHandler的demo
*/
public
static
void
insertVisitor(Visitor visitor) {
Connection conn = getConnection();
QueryRunner qr =
new
QueryRunner();
String sql =
"insert into visitor (Name, Email, Status, CreateTime) values (?, ?, ?, ?)"
;
try
{
int
count = qr.update(conn, sql, visitor.getName(), visitor.getEmail(),
1
,
new
Date());
BigInteger newId = (BigInteger) qr.query(conn,
"select last_insert_id()"
,
new
ScalarHandler<BigInteger>(
1
));
visitor.setId(Integer.valueOf(String.valueOf(newId)));
System.out.println(
"新增"
+ count +
"条数据=>Id:"
+ newId);
}
catch
(SQLException e) {
e.printStackTrace();
}
}
|
大家可以看到操作的步骤其实很简单,也是写SQL可以了,对于自增字段我们通过select last_insert_id()的方法利用ScalarHandler<BigInteger>实体类来返回达到变通效果。
删除方法
1
2
3
4
5
6
7
8
9
10
11
12
|
public
static
void
deleteVisitor(
int
id) {
Connection conn = getConnection();
QueryRunner qr =
new
QueryRunner();
String sql =
"delete from visitor where status>0 and id=?"
;
try
{
int
count = qr.update(conn, sql, id);
System.out.println(
"删除"
+ count +
"条数据。"
);
}
catch
(SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
|
查询方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
public
static
Visitor retrieveVisitor(
int
id) {
Connection conn = getConnection();
Visitor visitor =
null
;
QueryRunner qr =
new
QueryRunner();
String sql =
"select * from visitor where status>0 and id=?"
;
try
{
visitor = (Visitor) qr.query(conn, sql,
new
BeanHandler<Visitor>(Visitor.
class
), id);
System.out.println(visitor);
return
visitor;
}
catch
(Exception e) {
e.printStackTrace();
}
return
visitor;
}
|
更新操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
public
static
void
updateVisitor(
int
id) {
Visitor visitor = retrieveVisitor(id);
System.out.println(
"更新前:"
+ visitor);
Connection conn = getConnection();
String updateFieldStr = visitor.getName();
QueryRunner qr =
new
QueryRunner();
String sql =
"update visitor set Name = ?, Email = ?, Status = ?, CreateTime = ? where status>0 and Id = ?"
;
if
(updateFieldStr.contains(
"updated"
)) {
updateFieldStr = updateFieldStr.substring(
0
, updateFieldStr.indexOf(
"updated"
));
}
else
{
updateFieldStr = updateFieldStr +
"updated"
;
}
visitor.setName(updateFieldStr);
try
{
int
count = qr.update(conn, sql,
new
Object[] { visitor.getName(), visitor.getName(), visitor.getStatus(),
visitor.getCreateTime(), visitor.getId() });
System.out.println(
"更新了"
+ count +
"条数据"
);
System.out.println(
"更新后:"
+ visitor);
}
catch
(SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
|
BeanListHandler方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
public
static
void
getVisitorList() {
Connection conn = getConnection();
QueryRunner qr =
new
QueryRunner();
String sql =
"select * from visitor where status>0"
;
try
{
List<Visitor> ls = qr.query(conn, sql,
new
BeanListHandler<Visitor>(Visitor.
class
));
for
(Visitor visitor : ls) {
System.out.println(visitor);
}
}
catch
(SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
|
MapHandler操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
public
static
void
getVisitWithMap(
int
id) {
Connection conn = getConnection();
QueryRunner qr =
new
QueryRunner();
String sql =
"select * from visitor where status>0 and id=?"
;
try
{
Map<String, Object> map = qr.query(conn, sql,
new
MapHandler(), id);
Integer visitorId = Integer.valueOf(map.get(
"Id"
).toString());
String visitorName = map.get(
"Name"
).toString();
String visitorEmail = map.get(
"Email"
).toString();
Integer visitorStatus = Integer.valueOf(map.get(
"Status"
).toString());
SimpleDateFormat sdf =
new
SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss"
);
Date visitorCreateTime = sdf.parse(map.get(
"CreateTime"
).toString());
Visitor visitor =
new
Visitor(visitorName, visitorEmail);
visitor.setId(visitorId);
visitor.setStatus(visitorStatus);
visitor.setCreateTime(visitorCreateTime);
System.out.println(visitor);
}
catch
(Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
|
MapListHandler方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
public
static
void
getVisitWithMapLs() {
Connection conn = getConnection();
QueryRunner qr =
new
QueryRunner();
String sql =
"select * from visitor where status>0"
;
try
{
List<Map<String, Object>> mapLs = qr.query(conn, sql,
new
MapListHandler());
for
(Map<String, Object> map : mapLs) {
Integer visitorId = Integer.valueOf(map.get(
"Id"
).toString());
String visitorName = map.get(
"Name"
).toString();
String visitorEmail = map.get(
"Email"
).toString();
Integer visitorStatus = Integer.valueOf(map.get(
"Status"
).toString());
SimpleDateFormat sdf =
new
SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss"
);
Date visitorCreateTime = sdf.parse(map.get(
"CreateTime"
).toString());
Visitor visitor =
new
Visitor(visitorName, visitorEmail);
visitor.setId(visitorId);
visitor.setStatus(visitorStatus);
visitor.setCreateTime(visitorCreateTime);
System.out.println(visitor);
}
}
catch
(Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
|
经过上面的几个示例,相信大家应该知道怎么用这个框架了吧~ 框架的官网地址
来自:http://www.cnblogs.com/daviddai/p/Apache_Common_DbUtils.html