使用模板方法模式简化JDBC操作
在使用JDBC时，会重复的写很多重复的代码，例如
Java代码
1. Connection conn = null;
2.PreparedStatement ps = null;
3.ResultSet rs = null;
4. String sql="insert into t_user(username,brithday) values(?,?)";
5.try {
6. conn = JdbcUtils.getConnection();
7. ps = conn.prepareStatement(sql);
8.
9.
10.} catch (SQLException e) {
11. throw new DaoException(e.getMessage(), e);
12.} finally {
13. JdbcUtils.free(rs, ps, conn);
14.}
15.
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql="insert into t_user(username,brithday) values(?,?)";
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
这部分代码在数据库操作方法中都会有。因此我们可以把这部分不变的内容提取出来，作为一个公用的方法。
例如，我们的增，删，改操作可以这样写
Java代码
1./**
2. * 增，删，改方法
3. * @param sql
4. * @param args sql参数
5. * @return
6. */
7. public int update(String sql, Object[] args) {
8. Connection conn = null;
9. PreparedStatement ps = null;
10. ResultSet rs = null;
11. try {
12. conn = JdbcUtils.getConnection();
13. ps = conn.prepareStatement(sql);
14. for (int i = 0; i < args.length; i++)
15. ps.setObject(i + 1, args[i]);
16. return ps.executeUpdate();
17. } catch (SQLException e) {
18. throw new DaoException(e.getMessage(), e);
19. } finally {
20. JdbcUtils.free(rs, ps, conn);
21. }
22. }
/**
* 增，删，改方法
* @param sql
* @param args sql参数
* @return
*/
public int update(String sql, Object[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++)
ps.setObject(i + 1, args[i]);
return ps.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
最麻烦的就是返回一个对象的操作了。因为我不知道要返回的对象是什么，所以在往对象里设值的时候就不确定了。因此我们可以在这个类里面定义一个抽象的方法，具体怎么实现，它的子类知道。
因此我们的这个类就可以这样设计了
Java代码
1.package com.zzg.jdbc.base;
2.
3.import java.sql.Connection;
4.import java.sql.PreparedStatement;
5.import java.sql.ResultSet;
6.import java.sql.SQLException;
7.import java.util.ArrayList;
8.import java.util.List;
9.
10.import com.zzg.jdbc.exception.DaoException;
11.import com.zzg.jdbc.util.JdbcUtils;
12.
13.public abstract class BaseDao {
14.
15. /**
16. * 增，删，改方法
17. * @param sql
18. * @param args sql参数
19. * @return
20. */
21. public int update(String sql, Object[] args) {
22. Connection conn = null;
23. PreparedStatement ps = null;
24. ResultSet rs = null;
25. try {
26. conn = JdbcUtils.getConnection();
27. ps = conn.prepareStatement(sql);
28. for (int i = 0; i < args.length; i++)
29. ps.setObject(i + 1, args[i]);
30. return ps.executeUpdate();
31. } catch (SQLException e) {
32. throw new DaoException(e.getMessage(), e);
33. } finally {
34. JdbcUtils.free(rs, ps, conn);
35. }
36. }
37.
38. /**
39. * 返回一个对象
40. * @param <T>
41. * @param sql
42. * @param args
43. * @return
44. */
45. public <T> T find(String sql, Object[] args) {
46. Connection conn = null;
47. PreparedStatement ps = null;
48. ResultSet rs = null;
49. try {
50. conn = JdbcUtils.getConnection();
51. ps = conn.prepareStatement(sql);
52. for (int i = 0; i < args.length; i++)
53. ps.setObject(i + 1, args[i]);
54. rs = ps.executeQuery();
55. T t = null;
56. if (rs.next()) {
57. t = rowMapper(rs);
58. }
59. return t;
60. } catch (SQLException e) {
61. throw new DaoException(e.getMessage(), e);
62. } finally {
63. JdbcUtils.free(rs, ps, conn);
64. }
65. }
66.
67. /**
68. * 返回一个List
69. * @param <T>
70. * @param sql
71. * @param args
72. * @return
73. */
74. public <T> List<T> list(String sql, Object[] args) {
75. Connection conn = null;
76. PreparedStatement ps = null;
77. ResultSet rs = null;
78. try {
79. conn = JdbcUtils.getConnection();
80. ps = conn.prepareStatement(sql);
81. for (int i = 0; i < args.length; i++)
82. ps.setObject(i + 1, args[i]);
83. rs = ps.executeQuery();
84. T t = null;
85. List<T> list = new ArrayList<T>();
86. while (rs.next()) {
87. t = rowMapper(rs);
88. list.add(t);
89. }
90. return list;
91. } catch (SQLException e) {
92. throw new DaoException(e.getMessage(), e);
93. } finally {
94. JdbcUtils.free(rs, ps, conn);
95. }
96. }
97.
98. abstract protected <T> T rowMapper(ResultSet rs) throws SQLException;
99.
100.}
package com.zzg.jdbc.base;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.zzg.jdbc.exception.DaoException;
import com.zzg.jdbc.util.JdbcUtils;
public abstract class BaseDao {
/**
* 增，删，改方法
* @param sql
* @param args sql参数
* @return
*/
public int update(String sql, Object[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++)
ps.setObject(i + 1, args[i]);
return ps.executeUpdate();
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
/**
* 返回一个对象
* @param <T>
* @param sql
* @param args
* @return
*/
public <T> T find(String sql, Object[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++)
ps.setObject(i + 1, args[i]);
rs = ps.executeQuery();
T t = null;
if (rs.next()) {
t = rowMapper(rs);
}
return t;
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
/**
* 返回一个List
* @param <T>
* @param sql
* @param args
* @return
*/
public <T> List<T> list(String sql, Object[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++)
ps.setObject(i + 1, args[i]);
rs = ps.executeQuery();
T t = null;
List<T> list = new ArrayList<T>();
while (rs.next()) {
t = rowMapper(rs);
list.add(t);
}
return list;
} catch (SQLException e) {
throw new DaoException(e.getMessage(), e);
} finally {
JdbcUtils.free(rs, ps, conn);
}
}
abstract protected <T> T rowMapper(ResultSet rs) throws SQLException;
}
在使用时我们的类只需要继承上面那个类就可以了。
Java代码
1.package com.zzg.jdbc.dao.impl;
2.
3.import java.sql.ResultSet;
4.import java.sql.SQLException;
5.import java.util.List;
6.
7.import com.zzg.jdbc.base.BaseDao;
8.import com.zzg.jdbc.dao.UserDao;
9.import com.zzg.jdbc.domain.User;
10.
11.public class UserDaoImpl extends BaseDao implements UserDao {
12.
13. @Override
14. public User findUser(int id) {
15. String sql = "select *from t_user where id=?";
16. Object[] args = new Object[] { id };
17. User user = super.find(sql, args);
18. return user;
19. }
20.
21. @Override
22. public List<User> listUser(String username) {
23. String sql = "select *from t_user where username=?";
24. Object[] args = new Object[] { username };
25. List<User> list = super.list(sql, args);
26. for (User u : list) {
27. System.out.println(u.getId());
28. }
29. return list;
30. }
31.
32. @Override
33. protected Object rowMapper(ResultSet rs) throws SQLException {
34. User user = new User();
35. user.setId(rs.getInt("id"));
36. user.setUsername(rs.getString("username"));
37. user.setBrithday(rs.getDate("brithday"));
38. return user;
39. }
40.
41.}
package com.zzg.jdbc.dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.zzg.jdbc.base.BaseDao;
import com.zzg.jdbc.dao.UserDao;
import com.zzg.jdbc.domain.User;
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public User findUser(int id) {
String sql = "select *from t_user where id=?";
Object[] args = new Object[] { id };
User user = super.find(sql, args);
return user;
}
@Override
public List<User> listUser(String username) {
String sql = "select *from t_user where username=?";
Object[] args = new Object[] { username };
List<User> list = super.list(sql, args);
for (User u : list) {
System.out.println(u.getId());
}
return list;
}
@Override
protected Object rowMapper(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setBrithday(rs.getDate("brithday"));
return user;
}
}
