Spring JDBCTemplate Query方法查询_JAVA_编程开发_程序员俱乐部

在内部建立内联类实现RowMapper接口

class="highlighter-j">
1. package?hysteria.contact.dao.impl;
3. import?java.sql.ResultSet;
4. import?java.sql.SQLException;
5. import?java.sql.Types;
6. import?java.util.List;
8. import?org.springframework.jdbc.core.JdbcTemplate;
9. import?org.springframework.jdbc.core.RowMapper;
11. import?hysteria.contact.dao.ItemDAO;
12. import?hysteria.contact.domain.Item;
14. public?class?ItemDAOImpl?implements?ItemDAO?{
15. ?private?JdbcTemplate?jdbcTemplate;
17. ?public?void?setJdbcTemplate(JdbcTemplate?jdbcTemplate)?{
18. ??this.jdbcTemplate?=?jdbcTemplate;
19. ?}
21. ?public?Item?insert(Item?item)?{
22. ??String?sql?=?"INSERT?INTO?items(user_id,name,phone,email)?VALUES(?,?,?,?)";
23. ??Object[]?params?=?new?Object[]{item.getUserId(),item.getName(),item.getPhone(),item.getEmail()};
24. ??int[]?types?=?new?int[]{Types.INTEGER,Types.VARCHAR,Types.CHAR,Types.VARCHAR};
25. ??jdbcTemplate.update(sql,params,types);
26. ??return?item;
27. ?}
29. ?public?Item?update(Item?item)?{
30. ??String?sql?=?"UPDATE?items?SET?name?=??,?phone?=??,?email?=???WHERE?id?=??";
31. ??Object[]?params?=?new?Object[]?{item.getName(),item.getPhone(),item.getEmail(),item.getId()};
32. ??int[]?types?=?new?int[]?{Types.VARCHAR,Types.CHAR,Types.VARCHAR,Types.VARCHAR,Types.INTEGER};
33. ??jdbcTemplate.update(sql,params,types);
35. ??return?item;
36. ?}
38. ?public?void?delete(Item?item)?{
39. ??String?sql?=?"DELETE?FROM?items?WHERE?id?=??";
40. ??Object[]?params?=?new?Object[]?{item.getId()};
41. ??int[]?types?=?new?int[]{Types.INTEGER};
42. ??jdbcTemplate.update(sql,params,types);
43. ?}
45. ?public?Item?findById(int?id)?{
46. ??String?sql?=?"SELECT?*?FROM?items?WHERE?id?=??";
47. ??Object[]?params?=?new?Object[]?{id};
48. ??int[]?types?=?new?int[]?{Types.INTEGER};
49. ??List?items?=?jdbcTemplate.query(sql,params,types,new?ItemMapper());
50. ??if(items.isEmpty()){
51. ???return?null;
52. ??}
53. ??return?(Item)items.get(0);
54. ?}
56. ?public?List<Item>?findAll()?{
57. ??String?sql?=?"SELECT?*?FROM?items";
58. ??return?jdbcTemplate.query(sql,new?ItemMapper());
59. ?}
62. ?public?List<Item>?findAllByUser(int?user_id)?{
63. ??String?sql?=?"SELECT?*?FROM?items?WHERE?user_id?=??";
64. ??Object[]?params?=?new?Object[]{user_id};
65. ??int[]?types?=?new?int[]{Types.INTEGER};
66. ??List?items?=?jdbcTemplate.query(sql,params,types,new?ItemMapper());
67. ??return?items;
68. ?}
70. ?protected?class?ItemMapper?implements?RowMapper?{
72. ??public?Object?mapRow(ResultSet?rs,?int?rowNum)?throws?SQLException?{
73. ???Item?item?=?new?Item();
74. ???item.setId(rs.getInt("id"));
75. ???item.setUserId(rs.getInt("user_id"));
76. ???item.setName(rs.getString("name"));
77. ???item.setPhone(rs.getString("phone"));
78. ???item.setEmail(rs.getString("email"));
80. ???return?item;
81. ??}
83. ?}
86. }