Spring中jdbcTemplate的用法范例（一）

Spring中jdbcTemplate的用法实例（一）

一、首先配置JdbcTemplate；

要使用Jdbctemplate 对象来完成jdbc 操作。通常情况下，有三种种方式得到JdbcTemplate 对象。?
????? 第一种方式：我们可以在自己定义的DAO 实现类中注入一个DataSource 引用来完 成JdbcTemplate 的实例化。也就是它是从外部“注入” DataSource 到DAO 中，然后 自己实例化JdbcTemplate，然后将DataSource 设置到JdbcTemplate 对象中。?
????? 第二种方式： 在 Spring 的 IoC 容器中配置一个 JdbcTemplate 的 bean，将 DataSource 注入进来，然后再把JdbcTemplate 注入到自定义DAO 中。?
??????第三种方式: Spring 提供了 org.springframework.jdbc.core.support.JdbcDaoSupport 类 ， 这 个 类 中 定 义 了 JdbcTemplate 属性，也定义了DataSource 属性，当设置DataSource 属性的时候，会创 建jdbcTemplate 的实例，所以我们自己编写的DAO 只需要继承JdbcDaoSupport 类， 然后注入DataSource 即可。提倡采用第三种方法。虽然下面的用法中采用了前两种方法

????

? 配置方法有3种：

????? 1、

1. public?class?UserServiceImpl?implements?UserService?{??
2. ??
3. ????private?JdbcTemplate?jdbcTemplate;??
4. ??????
5. ????public?JdbcTemplate?getJdbcTemplate()?{??
6. ????????return?jdbcTemplate;??
7. ????}??
8. ??
9. ????????????????//注入方法1?????
10. ????public?void?setJdbcTemplate(JdbcTemplate?jdbcTemplate)?{??
11. ????????this.jdbcTemplate?=?jdbcTemplate;??
12. ????}??
13. ??
14. ???????????????//其它方法这里省略……??
15. }??

?spring配置文件为：

1. <bean?id="jdbcTemplate"?class="org.springframework.jdbc.core.JdbcTemplate">??
2. ????????<property?name?=?"dataSource"?ref="dataSource">??
3. </bean>??
4. <bean?id="userService"?class="com.hxzy.account.jdbcTemplate.UserServiceImpl">??
5. ?????<property?name="jdbcTemplate"?ref="jdbcTemplate"/>??
6. </bean>??

?

?? 方法2、

1. public?class?UserServiceImpl?implements?UserService?{??
2. ??
3. ????????private?JdbcTemplate?jdbcTemplate;??
4. ??????????
5. ????????//注入方法2??
6. ????????public?void?setDataSource(DataSource?dataSource)?{??
7. ???????????????????this.jdbcTemplate?=?new?JdbcTemplate(dataSource);??
8. ????????}??
9. ???????
10. ???????//其它方法省略……??
11. }??

??

?spring配置文件为：

1. <bean?id="userService"?class="com.hxzy.account.jdbcTemplate.UserServiceImpl">??
2. ???????<property?name="dataSource"?ref="dataSource"/>??
3. </bean>??

?

方法3：继承JdbcDaoSupport，其内部有个JdbcTemplate ，需要注入DataSource 属性来实例化。

1. public?class?UserDaoImpl?extends?JdbcDaoSupport?implements?UserDao?{??
2. ??
3. ????@Override??
4. ????public?void?save(User?user)?{??
5. ????????String?sql?=?null;??
6. ????????this.getJdbcTemplate().update(sql);??
7. ????}??
8. ????????//其它方法省略……??
9. }??

?

spring配置文件：

?

1. <bean?id="userDao"?class="com.hxzy.account.jdbcTemplate.UserDaoImpl">??
2. ???????????<property?name="dataSource"?ref="dataSource"/>??
3. </bean>??

?

?

二、常用方法使用

【注意：】jdbcTemplate 中的sql均是用“?”做占位符的

domain User：

1. public?class?User?{??
2. ????private?int?id;??
3. ????private?String?username;??
4. ????private?String?password;??
5. ????private?String?sex;??
6. ??????????????
7. ???????????????//setter和getter方法省略……??
8. }??

???

?

UserServiceImpl ：

如果采用第三种方式，则下面的用法中将方法中的 jdbcTemplate 换成 this.getJdbcTemplate()即可。

?

1. ?????/**???
2. ?????*?创建表??
3. ?????*/???
4. ????public?void?create(String?tableName){?//tb_test1??
5. ????????jdbcTemplate.execute("create?table?"+tableName?+"?(id?integer,user_name?varchar2(40),password?varchar2(40))");??
6. ????}??
7. ??????
8. ????//jdbcTemplate.update适合于insert?、update和delete操作；??
9. ????/**???
10. ?????*?第一个参数为执行sql???
11. ?????*?第二个参数为参数数据???
12. ?????*/???
13. ????public?void?save3(User?user)?{??
14. ????????Assert.isNull(user,?"user?is?not?null");??
15. ????????jdbcTemplate.update("insert?into?tb_test1(name,password)?values(?,?)",???
16. ????????????????new?Object[]{user.getUsername(),user.getPassword()});??
17. ????}??
18. ??????
19. ????/**???
20. ?????*?第一个参数为执行sql???
21. ?????*?第二个参数为参数数据???
22. ?????*?第三个参数为参数类型???
23. ?????*/???
24. ????@Override??
25. ????public?void?save(User?user)?{??
26. ????????Assert.isNull(user,?"user?is?not?null");??
27. ????????jdbcTemplate.update(??
28. ????????????????"insert?into?tb_test1(name,password)?values(?,?)",???
29. ????????????????new?Object[]{user.getUsername(),user.getPassword()},???
30. ????????????????new?int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR}??
31. ????????????????);??
32. ????}??
33. ??
34. ????//避免sql注入??
35. ????public?void?save2(final?User?user)?{??
36. ????????Assert.isNull(user,?"user?is?not?null");??
37. ??????????
38. ????????jdbcTemplate.update("insert?into?tb_test1(name,password)?values(?,?)",???
39. ????????????????new?PreparedStatementSetter(){??
40. ??????????????
41. ????????????????????@Override??
42. ????????????????????public?void?setValues(PreparedStatement?ps)?throws?SQLException?{??
43. ????????????????????????ps.setString(1,?user.getUsername());??
44. ????????????????????????ps.setString(2,?user.getPassword());??
45. ????????????????????}??
46. ????????});??
47. ??????????
48. ????}??
49. ??????
50. ????public?void?save4(User?user)?{??
51. ????????Assert.isNull(user,?"user?is?not?null");??
52. ????????jdbcTemplate.update("insert?into?tb_test1(name,password)?values(?,?)",???
53. ?????????????????????????????new?Object[]{user.getUsername(),user.getPassword()});??
54. ????}??
55. ??????
56. ????//返回插入的主键??
57. ????public?List?save5(final?User?user)?{??
58. ??????????
59. ????????KeyHolder?keyHolder?=?new?GeneratedKeyHolder();??
60. ??
61. ????????jdbcTemplate.update(new?PreparedStatementCreator()?{??
62. ??????????????????????
63. ????????????????????????????????@Override??
64. ????????????????????????????????public?PreparedStatement?createPreparedStatement(Connection?connection)?throws?SQLException?{??
65. ????????????????????????????????????PreparedStatement?ps?=?connection.prepareStatement("insert?into?tb_test1(name,password)?values(?,?)",?new?String[]?{"id"});??
66. ????????????????????????????????????ps.setString(1,?user.getUsername());??
67. ????????????????????????????????????ps.setString(2,?user.getPassword());??
68. ????????????????????????????????????return?ps;??
69. ????????????????????????????????}??
70. ????????????????????????????},??
71. ????????????????keyHolder);??
72. ??????????
73. ????????return?keyHolder.getKeyList();??
74. ????}??
75. ??????
76. ????@Override??
77. ????public?void?update(final?User?user)?{??
78. ????????jdbcTemplate.update(??
79. ????????????????"update?tb_test1?set?name=？,password=？?where?id?=??",???
80. ????????????????new?PreparedStatementSetter(){??
81. ????????????????????@Override??
82. ????????????????????public?void?setValues(PreparedStatement?ps)?throws?SQLException?{??
83. ????????????????????????ps.setString(1,?user.getUsername());??
84. ????????????????????????ps.setString(2,?user.getPassword());??
85. ????????????????????????ps.setInt(3,?user.getId());??
86. ????????????????????}??
87. ????????????????}??
88. ????????);??
89. ????}??
90. ??
91. ????@Override??
92. ????public?void?delete(User?user)?{??
93. ????????Assert.isNull(user,?"user?is?not?null");??
94. ????????jdbcTemplate.update(??
95. ????????????????"delete?from?tb_test1?where?id?=??",???
96. ????????????????new?Object[]{user.getId()},???
97. ????????????????new?int[]{java.sql.Types.INTEGER});??
98. ????}??
99. ??
100. ????@Deprecated?//因为没有查询条件，所以用处不大??
101. ????public?int?queryForInt1(){??
102. ????????return?jdbcTemplate.queryForInt("select?count(0)?from?tb_test1");??
103. ????}??
104. ??????
105. ????public?int?queryForInt2(User?user){??
106. ????????return?jdbcTemplate.queryForInt("select?count(0)?from?tb_test1?where?username?=??"?,??
107. ????????????????new?Object[]{user.getUsername()});??
108. ????}??
109. ??????
110. ????//最全的参数3个??
111. ????public?int?queryForInt3(User?user){??
112. ????????return?jdbcTemplate.queryForInt("select?count(0)?from?tb_test1?where?username?=??"?,??
113. ????????????????new?Object[]{user.getUsername()},??
114. ????????????????new?int[]{java.sql.Types.VARCHAR});??
115. ????}??
116. ??????
117. ????//可以返回是一个基本类型的值??
118. ????@Deprecated??//因为没有查询条件，所以用处不大??
119. ????public?String?queryForObject1(User?user)?{??
120. ????????return?(String)?jdbcTemplate.queryForObject("select?username?from?tb_test1?where?id?=?100",??
121. ????????????????????????????????????????????????????String.class);??
122. ????}??
123. ??????
124. ????//可以返回值是一个对象??
125. ????@Deprecated?//因为没有查询条件，所以用处不大??
126. ????public?User?queryForObject2(User?user)?{??
127. ????????return?(User)?jdbcTemplate.queryForObject("select?*?from?tb_test1?where?id?=?100",?User.class);?//class是结果数据的java类型??
128. ????}??
129. ??????
130. ????@Deprecated?//因为没有查询条件，所以用处不大??
131. ????public?User?queryForObject3(User?user)?{??
132. ????????return?(User)?jdbcTemplate.queryForObject("select?*?from?tb_test1?where?id?=?100",???
133. ????????????????????new?RowMapper(){??
134. ??????
135. ????????????????????????@Override??
136. ????????????????????????public?Object?mapRow(ResultSet?rs,?int?rowNum)throws?SQLException?{??
137. ????????????????????????????User?user??=?new?User();??
138. ????????????????????????????user.setId(rs.getInt("id"));??
139. ????????????????????????????user.setUsername(rs.getString("username"));??
140. ????????????????????????????user.setPassword(rs.getString("password"));??
141. ????????????????????????????return?user;??
142. ????????????????????????}??
143. ????????????????????}??
144. ????????);???
145. ????}??
146. ??????
147. ????public?User?queryForObject4(User?user)?{??
148. ????????return?(User)?jdbcTemplate.queryForObject("select?*?from?tb_test1?where?id?=??",???
149. ????????????????????????????????????????????????????new?Object[]{user.getId()},??
150. ????????????????????????????????????????????????????User.class);?//class是结果数据的java类型??实际上这里是做反射，将查询的结果和User进行对应复制??
151. ????}??
152. ??????
153. ????public?User?queryForObject5(User?user)?{??
154. ????????return?(User)?jdbcTemplate.queryForObject(??
155. ????????????????"select?*?from?tb_test1?where?id?=??",???
156. ????????????????new?Object[]{user.getId()},??
157. ????????????????new?RowMapper(){??
158. ??
159. ????????????????????@Override??
160. ????????????????????public?Object?mapRow(ResultSet?rs,int?rowNum)throws?SQLException?{??
161. ????????????????????????User?user??=?new?User();??
162. ????????????????????????user.setId(rs.getInt("id"));??
163. ????????????????????????user.setUsername(rs.getString("username"));??
164. ????????????????????????user.setPassword(rs.getString("password"));??
165. ????????????????????????return?user;??
166. ????????????????????}??
167. ??????????????
168. ????????});?//class是结果数据的java类型??
169. ????}??
170. ??????
171. ????@Override??
172. ????public?User?queryForObject(User?user)?{??
173. ????????//方法有返回值??
174. ????????return?(User)?jdbcTemplate.queryForObject("select?*?from?tb_test1?where?id?=??",??
175. ????????????????new?Object[]{user.getId()},??
176. ????????????????new?int[]{java.sql.Types.INTEGER},???
177. ????????????????new?RowMapper()?{??
178. ??????????????
179. ????????????????????@Override??
180. ????????????????????public?Object?mapRow(ResultSet?rs,?int?rowNum)?throws?SQLException?{??
181. ????????????????????????User?user??=?new?User();??
182. ????????????????????????user.setId(rs.getInt("id"));??
183. ????????????????????????user.setUsername(rs.getString("username"));??
184. ????????????????????????user.setPassword(rs.getString("password"));??
185. ????????????????????????return?user;??
186. ????????????????????}??
187. ????????????????}??
188. ????????);??
189. ????}??
190. ??
191. ????@SuppressWarnings("unchecked")??
192. ????public?List<User>?queryForList1(User?user)?{??
193. ????????return?(List<User>)?jdbcTemplate.queryForList("select?*?from?tb_test1?where?username?=??",???
194. ????????????????????????????new?Object[]{user.getUsername()},??
195. ????????????????????????????User.class);??
196. ????}??
197. ??
198. ????@SuppressWarnings("unchecked")??
199. ????public?List<String>?queryForList2(User?user)?{??
200. ????????return?(List<String>)?jdbcTemplate.queryForList("select?username?from?tb_test1?where?sex?=??",???
201. ????????????????????????????new?Object[]{user.getSex()},??
202. ????????????????????????????String.class);??
203. ????}??
204. ??????
205. ????@SuppressWarnings("unchecked")??
206. ????//最全的参数查询??
207. ????public?List<User>?queryForList3(User?user)?{??
208. ????????return?(List<User>)?jdbcTemplate.queryForList("select?*?from?tb_test1?where?username?=??",??
209. ????????????????????????????new?Object[]{user.getUsername()},??
210. ????????????????????????????new?int[]{java.sql.Types.VARCHAR},??
211. ????????????????????????????User.class);??
212. ????}??
213. ??
214. ????//通过RowCallbackHandler对Select语句得到的每行记录进行解析，并为其创建一个User数据对象。实现了手动的OR映射。??
215. ????public?User?queryUserById4(String?id){??
216. ????????final?User?user??=?new?User();??
217. ??????????
218. ????????//该方法返回值为void??
219. ????????this.jdbcTemplate.query("select?*?from?tb_test1?where?id?=??",???
220. ????????????????new?Object[]?{?id?},???
221. ????????????????new?RowCallbackHandler()?{?????
222. ??????????????
223. ????????????????????@Override????
224. ????????????????????public?void?processRow(ResultSet?rs)?throws?SQLException?{?????
225. ????????????????????????User?user??=?new?User();??
226. ????????????user.setId(rs.getInt("id"));??
227. ????????????user.setUsername(rs.getString("username"));??
228. ????????????user.setPassword(rs.getString("password"));????
229. ????????????????????}?????
230. ????????});???
231. ??????????
232. ????????return?user;?????
233. ????}??
234. ??????
235. ????@SuppressWarnings("unchecked")??
236. ????@Override??
237. ????public?List<User>?list(User?user)?{??
238. ????????return?jdbcTemplate.query("select?*?from?tb_test1?where?username?like?'%?%'",???
239. ????????????????new?Object[]{user.getUsername()},???
240. ????????????????new?int[]{java.sql.Types.VARCHAR},???
241. ????????????????new?RowMapper(){??
242. ??????????????
243. ????????????????????@Override??
244. ????????????????????public?Object?mapRow(ResultSet?rs,?int?rowNum)?throws?SQLException?{??
245. ????????????????????????User?user??=?new?User();??
246. ????????????????????????user.setId(rs.getInt("id"));??
247. ????????????????????????user.setUsername(rs.getString("username"));??
248. ????????????????????????user.setPassword(rs.getString("password"));??
249. ????????????????????????return?user;??
250. ????????????????????}??
251. ????????});??
252. ????}??
253. ??
254. ????//批量操作????适合于增、删、改操作??
255. ????public?int[]?batchUpdate(final?List?users)?{??
256. ??????????
257. ????????int[]?updateCounts?=?jdbcTemplate.batchUpdate(??
258. ????????????????"update?tb_test1?set?username?=??,?password?=???where?id?=??",??
259. ????????????????new?BatchPreparedStatementSetter()?{??
260. ??????????????????????
261. ????????????????????????@Override??
262. ????????????????????????public?void?setValues(PreparedStatement?ps,?int?i)?throws?SQLException?{??
263. ????????????????????????????ps.setString(1,?((User)users.get(i)).getUsername());??
264. ????????????????????????????ps.setString(2,?((User)users.get(i)).getPassword());??
265. ????????????????????????????ps.setLong(3,?((User)users.get(i)).getId());??
266. ????????????????????????}??
267. ??????????????????????????
268. ????????????????????????@Override??
269. ????????????????????????public?int?getBatchSize()?{??
270. ????????????????????????????return?users.size();??
271. ????????????????????????}??
272. ????????????????}???
273. ????????);??
274. ??????????
275. ????????return?updateCounts;??
276. ????}??
277. ??????
278. ????//调用存储过程??
279. ????public?void?callProcedure(int?id){??
280. ????????this.jdbcTemplate.update("call?SUPPORT.REFRESH_USERS_SUMMARY(?)",?new?Object[]{Long.valueOf(id)});??
281. }??

?

其中，batchUpdate适合于批量增、删、改操作；

???????? update(…)：使用于增、删、改操作；

???????? execute（）：执行一个独立的sql语句，包括ddl语句；

?????????queryForInt ：查询出一个整数值

?

?

摘自：http://lehsyh.iteye.com/blog/1579737