JDBC总结4中，我们的查询依然是放在每个DAO中，而不是DBHelp类中，这样还是不够简化的！下面简化一下：
-
在util包中建立一个接口:
-
123456789101112131415
packagecom.hanchao.util;importjava.sql.ResultSet;importjava.sql.SQLException;/************************ @author：han* @version：1.0* @created：2015-10-11************************/publicinterfaceRowMapper {publicObject mapRow(ResultSet rs)throwsSQLException;}
3.在dao包中，
|
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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
package
com.hanchao.dao;
import
java.util.List;
import
com.hanchao.entity.Account;
import
com.hanchao.util.DBHelp;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public
class
AccountDao {
private
DBHelp dbHelp =
new
DBHelp();
/**
* 保存
* *******************
* @param accout
* @return
* *******************
* @author：wind
* 2015-10-11 下午9:31:28
* *******************
*/
public
int
save(Account accout) {
String sql =
"insert into t_account(username,password,money,enable) value(?,?,?,?)"
;
return
dbHelp.executeSQL(sql, accout.getUsername(),accout.getMoney(),accout.getMoney(),accout.isEnable());
}
/**
* update
* *******************
* @param account
* @return
* *******************
* @author：wind
* 2015-10-11 下午9:37:53
* *******************
*/
public
int
update(Account account) {
String sql =
"update t_account set username=?,password=?,money=?,enable=? where id = ?"
;
return
dbHelp.executeSQL(sql, account.getUsername(),account.getPassword(),account.getMoney(),account.isEnable(),account.getId());
}
/**
* detele
* *******************
* @param id
* @return
* *******************
* @author：wind
* 2015-10-11 下午9:56:24
* *******************
*/
public
int
delete(
int
id) {
String sql =
"delete from t_account where id = ?"
;
return
dbHelp.executeSQL(sql, id);
}
/**
* 根据ID查找对象
* *******************
* @param id
* @return
* *******************
* @author：wind
* 2015-10-11 下午10:05:46
* *******************
*/
public
Account findById(
int
id) {
String sql =
"select id,username,password,money,enable from t_account where id = ?"
;
return
(Account) dbHelp.executeQueryForObject(
new
AccountMapper(), sql, id);
}
/**
* 查找所有
* *******************
* @return
* *******************
* @author：wind
* 2015-10-11 下午10:17:03
* *******************
*/
public
List<Account> findAll() {
String sql =
"select id,username,password,money,enable from t_account"
;
return
dbHelp.executeQueryForList(
new
AccountMapper(), sql);
}
}
|
实现类：实现接口的方法
|
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
|
package
com.hanchao.dao;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
com.hanchao.entity.Account;
import
com.hanchao.util.RowMapper;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public
class
AccountMapper
implements
RowMapper{
@Override
public
Object mapRow(ResultSet rs)
throws
SQLException {
Account account =
new
Account();
account.setId(rs.getInt(
"id"
));
account.setUsername(rs.getString(
"username"
));
account.setPassword(rs.getString(
"password"
));
account.setMoney(rs.getFloat(
"money"
));
account.setEnable(rs.getBoolean(
"enable"
));
return
account;
}
}
|
4.那我们的DBhelp如何写呢？
|
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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
|
package
com.hanchao.util;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.ArrayList;
import
java.util.List;
import
com.hanchao.entity.Account;
/***********************
* 帮助类
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public
class
DBHelp {
private
static
final
String DRIVER =
"com.mysql.jdbc.Driver"
;
private
static
final
String URL =
"jdbc:mysql:///mydb"
;
private
static
final
String DB_NAME =
"root"
;
private
static
final
String DB_PASSWORD =
"root"
;
public
Connection getConnection()
throws
SQLException, ClassNotFoundException {
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL,DB_NAME,DB_PASSWORD);
return
conn;
}
public
int
executeSQL(String sql ,Object...args) {
Connection conn =
null
;
PreparedStatement stat =
null
;
int
rows =
0
;
try
{
conn = getConnection();
stat = conn.prepareStatement(sql);
for
(
int
i =
0
; i < args.length; i++) {
stat.setObject(i+
1
, args[i]);
}
rows = stat.executeUpdate();
if
(rows >
0
) {
System.out.println(
"operate successfully!"
);
}
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
finally
{
this
.close(conn, stat);
}
return
rows;
}
public
void
close(ResultSet rs,Connection conn , PreparedStatement stat) {
try
{
if
(rs !=
null
) {
rs.close();
}
}
catch
(SQLException e) {
e.printStackTrace();
}
finally
{
try
{
if
(stat !=
null
) {
stat.close();
}
}
catch
(SQLException e) {
e.printStackTrace();
}
finally
{
try
{
if
(conn !=
null
) {
conn.close();
}
}
catch
(SQLException e) {
e.printStackTrace();
}
}
}
}
public
void
close(Connection conn , PreparedStatement stat) {
this
.close(
null
,conn, stat);
}
/**
* 根据ID查找对象
* *******************
* @param rm
* @param sql
* @param args
* @return
* *******************
* @author：wind
* 2015-10-11 下午11:05:58
* *******************
*/
public
Object executeQueryForObject(RowMapper rm,String sql ,Object...args) {
Connection conn =
null
;
PreparedStatement stat =
null
;
Object result =
null
;
ResultSet rs =
null
;
try
{
conn = getConnection();
stat = conn.prepareStatement(sql);
for
(
int
i =
0
; i < args.length; i++) {
stat.setObject(i+
1
,args[i]);
}
rs = stat.executeQuery();
if
(rs.next()) {
result = (Account) rm.mapRow(rs);
}
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
finally
{
close(rs, conn, stat);
}
return
result;
}
/**
* 查找集合
* *******************
* @param rm
* @param sql
* @param args
* @return
* *******************
* @author：wind
* 2015-10-11 下午11:12:49
* *******************
*/
public
List executeQueryForList(RowMapper rm,String sql,Object...args) {
List list =
new
ArrayList();
Connection conn =
null
;
PreparedStatement stat =
null
;
ResultSet rs =
null
;
try
{
conn = getConnection();
stat = conn.prepareStatement(sql);
for
(
int
i =
0
; i < args.length; i++) {
stat.setObject(i+
1
, args[i]);
}
rs = stat.executeQuery();
while
(rs.next()) {
Object obj = rm.mapRow(rs);
list.add(obj);
}
}
catch
(ClassNotFoundException e) {
e.printStackTrace();
}
catch
(SQLException e) {
e.printStackTrace();
}
finally
{
this
.close(rs,conn, stat);
}
return
list;
}
}
|
5.测试一下：
|
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
|
package
com.hanchao.test;
import
java.util.List;
import
com.hanchao.dao.AccountDao;
import
com.hanchao.entity.Account;
/***********************
* @author：han
* @version：1.0
* @created：2015-10-11
***********************
*/
public
class
Test {
public
static
void
main(String[] args) {
//增
/* Account account = new Account();
account.setEnable(true);
account.setMoney(20f);
account.setUsername("hanchao1");
account.setPassword("1234561");
AccountDao accountDao = new AccountDao();
accountDao.save(account);*/
//改
/* Account account = new Account();
account.setId(8);
account.setEnable(true);
account.setMoney(21f);
account.setUsername("hanchao2dd1");
account.setPassword("1234562dsds2");
AccountDao accountDao = new AccountDao();
accountDao.update(account);*/
//删
/* AccountDao accountDao = new AccountDao();
accountDao.delete(2);*/
//查询
/* AccountDao accountDao = new AccountDao();
Account account = accountDao.findById(8);
if (account != null) {
System.out.println("id:" + account.getId() + ",name:" + account.getUsername());
} else {
System.out.println("not exist!");
}*/
//查询
AccountDao accountDao =
new
AccountDao();
List<Account> list = accountDao.findAll();
for
(
int
i =
0
; i < list.size(); i++) {
System.out.println(
"id:"
+ list.get(i).getId() +
" ,name:"
+ list.get(i).getUsername());
}
}
}
|
这样，我们的DBhelp类就是一个彻底的工具类了，虽然不够优化，但是，可以实现基本的工具类的功能了！
本文转自韩立伟 51CTO博客，原文链接：http://blog.51cto.com/hanchaohan/1701978，如需转载请自行联系原作者
