src/org/nutz/dao/impl/sql/SqlTemplate.java
查看文件 @ b4c8c64
1 | +package org.nutz.dao.impl.sql; | |
2 | + | |
3 | +import java.lang.reflect.Array; | |
4 | +import java.sql.Connection; | |
5 | +import java.sql.ResultSet; | |
6 | +import java.sql.SQLException; | |
7 | +import java.util.ArrayList; | |
8 | +import java.util.Collection; | |
9 | +import java.util.HashMap; | |
10 | +import java.util.List; | |
11 | +import java.util.Map; | |
12 | +import java.util.Map.Entry; | |
13 | + | |
14 | +import org.nutz.castor.Castors; | |
15 | +import org.nutz.dao.Dao; | |
16 | +import org.nutz.dao.Sqls; | |
17 | +import org.nutz.dao.entity.Entity; | |
18 | +import org.nutz.dao.entity.Record; | |
19 | +import org.nutz.dao.sql.Sql; | |
20 | +import org.nutz.dao.sql.SqlCallback; | |
21 | + | |
22 | +/** | |
23 | + * 仿照Spring JdbcTemplate实现nutz的SqlTemplate,方便sql的调用 | |
24 | + * | |
25 | + * @author hzl7652(hzl7652@sina.com) | |
26 | + */ | |
27 | +public class SqlTemplate { | |
28 | + | |
29 | + private Dao dao; | |
30 | + | |
31 | + public SqlTemplate() { | |
32 | + | |
33 | + } | |
34 | + | |
35 | + public SqlTemplate(Dao dao) { | |
36 | + this.dao = dao; | |
37 | + } | |
38 | + | |
39 | + public void setDao(Dao dao) { | |
40 | + this.dao = dao; | |
41 | + } | |
42 | + | |
43 | + /** | |
44 | + * 执行一个SQL更新操作(如插入,更新或删除语句)。 | |
45 | + * | |
46 | + * @param sql | |
47 | + * 包含变量占位符的SQL | |
48 | + * @param params | |
49 | + * 参数map,无参数时,可为null | |
50 | + * | |
51 | + * @return SQL 语句所影响的行数 | |
52 | + */ | |
53 | + public int update(String sql, Map<String, Object> params) { | |
54 | + return update(sql, null, params); | |
55 | + } | |
56 | + | |
57 | + /** | |
58 | + * 执行一个SQL更新操作(如插入,更新或删除语句)。 | |
59 | + * | |
60 | + * @param sql | |
61 | + * 包含变量占位符的SQL | |
62 | + * @param vars | |
63 | + * 变量map,无参数时,可为null | |
64 | + * @param params | |
65 | + * 参数map,无参数时,可为null | |
66 | + * | |
67 | + * @return SQL 语句所影响的行数 | |
68 | + */ | |
69 | + public int update(String sql, Map<String, Object> vars, Map<String, Object> params) { | |
70 | + Sql sqlObj = createSqlObj(sql, params); | |
71 | + | |
72 | + execute(sqlObj, vars, params); | |
73 | + | |
74 | + return sqlObj.getUpdateCount(); | |
75 | + } | |
76 | + | |
77 | + /** | |
78 | + * 执行一个SQL查询操作,结果为一个int形数值。 | |
79 | + * <p> | |
80 | + * | |
81 | + * @param sql | |
82 | + * 包含变量占位符的SQL | |
83 | + * @param params | |
84 | + * 参数map,无参数时,可为null | |
85 | + * | |
86 | + * @return int数值,当查询为null时返回0 | |
87 | + */ | |
88 | + public int queryForInt(String sql, Map<String, Object> params) { | |
89 | + | |
90 | + return queryForInt(sql, null, params); | |
91 | + } | |
92 | + | |
93 | + /** | |
94 | + * 执行一个SQL查询操作,结果为一个int形数值。 | |
95 | + * | |
96 | + * @param sql | |
97 | + * 包含变量占位符的SQL | |
98 | + * @param vars | |
99 | + * 变量map,无参数时,可为null | |
100 | + * @param params | |
101 | + * 参数map,无参数时,可为null | |
102 | + * | |
103 | + * @return int数值,当查询为null时返回0 | |
104 | + */ | |
105 | + public int queryForInt(String sql, Map<String, Object> vars, Map<String, Object> params) { | |
106 | + Sql sqlObj = createSqlObj(sql, params); | |
107 | + | |
108 | + sqlObj.setCallback(Sqls.callback.integer()); | |
109 | + | |
110 | + execute(sqlObj, vars, params); | |
111 | + | |
112 | + return sqlObj.getInt(); | |
113 | + } | |
114 | + | |
115 | + /** | |
116 | + * 执行一个SQL查询操作,结果为一个long形数值。 | |
117 | + * | |
118 | + * @param sql | |
119 | + * 包含变量占位符的SQL | |
120 | + * @param params | |
121 | + * 参数map,无参数时,可为null | |
122 | + * | |
123 | + * @return long数值,当查询为null时返回0 | |
124 | + */ | |
125 | + public long queryForLong(String sql, Map<String, Object> params) { | |
126 | + | |
127 | + return queryForLong(sql, null, params); | |
128 | + } | |
129 | + | |
130 | + /** | |
131 | + * 执行一个SQL查询操作,结果为一个long形数值。 | |
132 | + * | |
133 | + * @param sql | |
134 | + * 包含变量占位符的SQL | |
135 | + * @param vars | |
136 | + * 变量map,无参数时,可为null | |
137 | + * @param params | |
138 | + * 参数map,无参数时,可为null | |
139 | + * | |
140 | + * @return long数值,当查询为null时返回0 | |
141 | + */ | |
142 | + public long queryForLong(String sql, Map<String, Object> vars, Map<String, Object> params) { | |
143 | + Sql sqlObj = createSqlObj(sql, params); | |
144 | + | |
145 | + sqlObj.setCallback(Sqls.callback.longValue()); | |
146 | + | |
147 | + execute(sqlObj, vars, params); | |
148 | + | |
149 | + Long result = sqlObj.getObject(Long.class); | |
150 | + | |
151 | + return result == null ? 0 : result; | |
152 | + } | |
153 | + | |
154 | + /** | |
155 | + * 执行一个SQL查询操作,结果为给定对象类型的对象,适用于明确SQL查询结果的类型。 | |
156 | + * | |
157 | + * @param sql | |
158 | + * 包含变量占位符的SQL | |
159 | + * @param params | |
160 | + * 参数map 无参数时,可为null | |
161 | + * @param classOfT | |
162 | + * 对象类型,SQL查询结果所对应的类型,如Date.class等 | |
163 | + * | |
164 | + * @return 对象,无查询结果时返回null | |
165 | + */ | |
166 | + public <T> T queryForObject(String sql, Map<String, Object> params, Class<T> classOfT) { | |
167 | + return queryForObject(sql, null, params, classOfT); | |
168 | + } | |
169 | + | |
170 | + /** | |
171 | + * 执行一个SQL查询操作,结果为给定对象类型的对象,适用于明确SQL查询结果的类型。 | |
172 | + * | |
173 | + * @param sql | |
174 | + * 包含变量占位符的SQL | |
175 | + * @param vars | |
176 | + * 变量map,无参数时,可为null | |
177 | + * @param params | |
178 | + * 参数map,无参数时,可为null | |
179 | + * @param classOfT | |
180 | + * 对象类型,SQL查询结果所对应的类型,如Date.class等 | |
181 | + * | |
182 | + * @return 对象,无查询结果时返回null | |
183 | + */ | |
184 | + public <T> T queryForObject(String sql, | |
185 | + Map<String, Object> vars, | |
186 | + Map<String, Object> params, | |
187 | + Class<T> classOfT) { | |
188 | + Sql sqlObj = createSqlObj(sql, params); | |
189 | + | |
190 | + sqlObj.setCallback(new SqlCallback() { | |
191 | + | |
192 | + @Override | |
193 | + public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException { | |
194 | + if (null != rs && rs.next()) | |
195 | + return rs.getObject(1); | |
196 | + return null; | |
197 | + } | |
198 | + }); | |
199 | + | |
200 | + execute(sqlObj, vars, params); | |
201 | + | |
202 | + return sqlObj.getObject(classOfT); | |
203 | + } | |
204 | + | |
205 | + /** | |
206 | + * 执行一个SQL查询操作,结果为给定实体的对象。 | |
207 | + * | |
208 | + * @param sql | |
209 | + * 包含变量占位符的SQL | |
210 | + * @param params | |
211 | + * 参数map,无参数时,可为null | |
212 | + * @param entity | |
213 | + * 实体类型,无参数时,可为null | |
214 | + * | |
215 | + * @return 对象,无查询结果时返回null | |
216 | + */ | |
217 | + public <T> T queryForObject(String sql, Map<String, Object> params, Entity<T> entity) { | |
218 | + return queryForObject(sql, null, params, entity); | |
219 | + } | |
220 | + | |
221 | + /** | |
222 | + * 执行一个SQL查询操作,结果为给定实体的对象。 | |
223 | + * | |
224 | + * @param sql | |
225 | + * 包含变量占位符的SQL | |
226 | + * @param vars | |
227 | + * 变量map,无参数时,可为null | |
228 | + * @param params | |
229 | + * 参数map,无参数时,可为null | |
230 | + * @param entity | |
231 | + * 实体类型 | |
232 | + * | |
233 | + * @return 对象,无查询结果时返回null | |
234 | + */ | |
235 | + public <T> T queryForObject(String sql, | |
236 | + Map<String, Object> vars, | |
237 | + Map<String, Object> params, | |
238 | + Entity<T> entity) { | |
239 | + Sql sqlObj = createSqlObj(sql, params); | |
240 | + | |
241 | + sqlObj.setCallback(Sqls.callback.entity()); | |
242 | + sqlObj.setEntity(entity); | |
243 | + | |
244 | + execute(sqlObj, vars, params); | |
245 | + | |
246 | + return sqlObj.getObject(entity.getType()); | |
247 | + } | |
248 | + | |
249 | + /** | |
250 | + * 执行一个SQL查询操作,结果为Record的对象。 | |
251 | + * | |
252 | + * @param sql | |
253 | + * 包含变量占位符的SQL | |
254 | + * @param params | |
255 | + * 参数map,无参数时,可为null | |
256 | + * | |
257 | + * @return Record对象,无查询结果时返回null | |
258 | + */ | |
259 | + public Record queryForRecord(String sql, Map<String, Object> params) { | |
260 | + return queryForRecord(sql, null, params); | |
261 | + } | |
262 | + | |
263 | + /** | |
264 | + * 执行一个SQL查询操作,结果为Record的对象。 | |
265 | + * | |
266 | + * @param sql | |
267 | + * 包含变量占位符的SQL | |
268 | + * @param vars | |
269 | + * 变量map,无参数时,可为null | |
270 | + * @param params | |
271 | + * 参数map,无参数时,可为null | |
272 | + * @return Record对象,无查询结果时返回null | |
273 | + */ | |
274 | + public Record queryForRecord(String sql, Map<String, Object> vars, Map<String, Object> params) { | |
275 | + Sql sqlObj = createSqlObj(sql, params); | |
276 | + | |
277 | + sqlObj.setCallback(Sqls.callback.record()); | |
278 | + | |
279 | + execute(sqlObj, vars, params); | |
280 | + | |
281 | + return sqlObj.getObject(Record.class); | |
282 | + } | |
283 | + | |
284 | + /** | |
285 | + * 执行一个SQL查询操作,结果为一组对象。 | |
286 | + * | |
287 | + * @param sql | |
288 | + * 包含变量占位符的SQL | |
289 | + * @param params | |
290 | + * 参数map,无参数时,可为null | |
291 | + * @param entity | |
292 | + * 对象类型,无参数时,可为null | |
293 | + * | |
294 | + * @return 对象列表,无查询结果时返回长度为0的List对象 | |
295 | + */ | |
296 | + public <T> List<T> query(String sql, Map<String, Object> params, Entity<T> entity) { | |
297 | + return query(sql, null, params, entity); | |
298 | + } | |
299 | + | |
300 | + /** | |
301 | + * 执行一个SQL查询操作,结果为一组对象。 | |
302 | + * | |
303 | + * @param sql | |
304 | + * 包含变量占位符的SQL | |
305 | + * @param vars | |
306 | + * 变量map,无参数时,可为null | |
307 | + * @param params | |
308 | + * 参数map,无参数时,可为null | |
309 | + * @param entity | |
310 | + * 对象类型 | |
311 | + * | |
312 | + * @return 对象列表,无查询结果时返回长度为0的List对象 | |
313 | + */ | |
314 | + public <T> List<T> query(String sql, | |
315 | + Map<String, Object> vars, | |
316 | + Map<String, Object> params, | |
317 | + Entity<T> entity) { | |
318 | + Sql sqlObj = createSqlObj(sql, params); | |
319 | + | |
320 | + sqlObj.setCallback(Sqls.callback.entities()); | |
321 | + sqlObj.setEntity(entity); | |
322 | + | |
323 | + execute(sqlObj, vars, params); | |
324 | + | |
325 | + return sqlObj.getList(entity.getType()); | |
326 | + } | |
327 | + | |
328 | + /** | |
329 | + * 执行一个SQL查询操作,结果为一组对象。 | |
330 | + * | |
331 | + * @param sql | |
332 | + * 包含变量占位符的SQL | |
333 | + * @param vars | |
334 | + * 变量map,无参数时,可为null | |
335 | + * @param params | |
336 | + * 参数map,无参数时,可为null | |
337 | + * @param classOfT | |
338 | + * 对象类型 | |
339 | + * | |
340 | + * @return 对象列表,无查询结果时返回长度为0的List对象 | |
341 | + */ | |
342 | + public <T> List<T> queryForList(String sql, | |
343 | + Map<String, Object> vars, | |
344 | + Map<String, Object> params, | |
345 | + final Class<T> classOfT) { | |
346 | + Sql sqlObj = createSqlObj(sql, params); | |
347 | + | |
348 | + sqlObj.setCallback(new SqlCallback() { | |
349 | + | |
350 | + @Override | |
351 | + public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException { | |
352 | + List<T> list = new ArrayList<T>(); | |
353 | + while (rs.next()) { | |
354 | + T result = Castors.me().castTo(rs.getObject(1), classOfT); | |
355 | + list.add(result); | |
356 | + } | |
357 | + return list; | |
358 | + } | |
359 | + }); | |
360 | + | |
361 | + execute(sqlObj, vars, params); | |
362 | + | |
363 | + return sqlObj.getList(classOfT); | |
364 | + } | |
365 | + | |
366 | + /** | |
367 | + * 执行一个SQL查询操作,结果为Record对象列表。 | |
368 | + * | |
369 | + * @param sql | |
370 | + * 包含变量占位符的SQL | |
371 | + * @param vars | |
372 | + * 变量map,无参数时,可为null | |
373 | + * @param params | |
374 | + * 参数map,无参数时,可为null | |
375 | + * | |
376 | + * @return Record列表,无查询结果时返回长度为0的List对象 | |
377 | + */ | |
378 | + public List<Record> queryRecords(String sql, | |
379 | + Map<String, Object> vars, | |
380 | + Map<String, Object> params) { | |
381 | + Sql sqlObj = createSqlObj(sql, params); | |
382 | + | |
383 | + sqlObj.setCallback(Sqls.callback.records()); | |
384 | + | |
385 | + execute(sqlObj, vars, params); | |
386 | + | |
387 | + return sqlObj.getList(Record.class); | |
388 | + } | |
389 | + | |
390 | + /** | |
391 | + * 设置sql参数并执行sql。 | |
392 | + */ | |
393 | + private void execute(Sql sqlObj, Map<String, Object> vars, Map<String, Object> params) { | |
394 | + if (vars != null) | |
395 | + sqlObj.vars().putAll(vars); | |
396 | + | |
397 | + if (params != null) | |
398 | + sqlObj.params().putAll(params); | |
399 | + | |
400 | + dao.execute(sqlObj); | |
401 | + } | |
402 | + | |
403 | + /** | |
404 | + * 创建Sql对象。 | |
405 | + * <p> | |
406 | + * 在这里处理Array Collection类型参数,方便SQL IN 表达式的设置 | |
407 | + * | |
408 | + * @param sql | |
409 | + * 包含变量占位符的SQL | |
410 | + * @param params | |
411 | + * 参数map,无参数时,可为null | |
412 | + * | |
413 | + * @return Sql对象 | |
414 | + */ | |
415 | + private Sql createSqlObj(String sql, Map<String, Object> params) { | |
416 | + | |
417 | + if (params == null) | |
418 | + return Sqls.create(sql); | |
419 | + | |
420 | + Map<String, Object> newParams = new HashMap<String, Object>(params); | |
421 | + String newSql = sql; | |
422 | + for (Entry<String, Object> entry : params.entrySet()) { | |
423 | + String paramName = entry.getKey(); | |
424 | + Object paramObj = entry.getValue(); | |
425 | + | |
426 | + if (paramObj.getClass().isArray()) { | |
427 | + int len = Array.getLength(paramObj); | |
428 | + StringBuilder inSqlExp = new StringBuilder(); | |
429 | + for (int i = 0; i < len; i++) { | |
430 | + String inParamName = paramName + i; | |
431 | + inSqlExp.append("@").append(paramName).append(i).append(","); | |
432 | + newParams.put(inParamName, Array.get(paramObj, i)); | |
433 | + } | |
434 | + inSqlExp.deleteCharAt(inSqlExp.length() - 1); | |
435 | + newSql = newSql.replaceAll("@" + paramName, inSqlExp.toString()); | |
436 | + newParams.remove(paramName); | |
437 | + } | |
438 | + | |
439 | + if (paramObj instanceof Collection) { | |
440 | + Collection<?> collection = (Collection<?>) paramObj; | |
441 | + Object[] paramVals = collection.toArray(); | |
442 | + StringBuilder inSqlExp = new StringBuilder(); | |
443 | + for (int i = 0, len = paramVals.length; i < len; i++) { | |
444 | + String inParamName = paramName + i; | |
445 | + inSqlExp.append("@").append(paramName).append(i).append(","); | |
446 | + newParams.put(inParamName, paramVals[i]); | |
447 | + } | |
448 | + inSqlExp.deleteCharAt(inSqlExp.length() - 1); | |
449 | + newSql = newSql.replaceAll("@" + paramName, inSqlExp.toString()); | |
450 | + newParams.remove(paramName); | |
451 | + } | |
452 | + | |
453 | + } | |
454 | + params.clear(); | |
455 | + params.putAll(newParams); | |
456 | + return Sqls.create(newSql); | |
457 | + } | |
458 | + | |
459 | +} |
test/org/nutz/dao/test/sqls/SqlTemplateTest.java
查看文件 @ b4c8c64
1 | +package org.nutz.dao.test.sqls; | |
2 | + | |
3 | +import static org.junit.Assert.assertEquals; | |
4 | +import static org.junit.Assert.assertTrue; | |
5 | + | |
6 | +import java.sql.Timestamp; | |
7 | +import java.util.HashMap; | |
8 | +import java.util.List; | |
9 | +import java.util.Map; | |
10 | + | |
11 | +import org.junit.Test; | |
12 | +import org.nutz.dao.entity.Record; | |
13 | +import org.nutz.dao.impl.sql.SqlTemplate; | |
14 | +import org.nutz.dao.test.DaoCase; | |
15 | +import org.nutz.dao.test.meta.Pet; | |
16 | +import org.nutz.lang.Lang; | |
17 | + | |
18 | +public class SqlTemplateTest extends DaoCase { | |
19 | + | |
20 | + private SqlTemplate sqlTemplate; | |
21 | + | |
22 | + @Override | |
23 | + protected void before() { | |
24 | + if (sqlTemplate == null) | |
25 | + sqlTemplate = new SqlTemplate(dao); | |
26 | + } | |
27 | + | |
28 | + @Test | |
29 | + public void testUpdate() { | |
30 | + pojos.initPet(); | |
31 | + dao.insert(Pet.create(1)); | |
32 | + String sql = "UPDATE $table SET name=@name"; | |
33 | + | |
34 | + Map<String, Object> vars = new HashMap<String, Object>(); | |
35 | + vars.put("table", "t_pet"); | |
36 | + | |
37 | + Map<String, Object> param = new HashMap<String, Object>(); | |
38 | + param.put("name", "Peter"); | |
39 | + sqlTemplate.update(sql, vars, param); | |
40 | + | |
41 | + List<Pet> pets = dao.query(Pet.class, null); | |
42 | + | |
43 | + assertEquals("Peter", pets.get(0).getName()); | |
44 | + } | |
45 | + | |
46 | + @Test | |
47 | + public void testQueryForInt() { | |
48 | + pojos.initPet(); | |
49 | + dao.insert(Pet.create(1)); | |
50 | + int petCount = sqlTemplate.queryForInt("SELECT COUNT(*) FROM t_pet", null); | |
51 | + assertEquals(1, petCount); | |
52 | + | |
53 | + petCount = sqlTemplate.queryForInt("SELECT COUNT(*) FROM t_pet WHERE 1=2 ", null); | |
54 | + assertEquals(0, petCount); | |
55 | + | |
56 | + } | |
57 | + | |
58 | + @Test | |
59 | + public void testQueryForLong() { | |
60 | + pojos.initPet(); | |
61 | + dao.insert(Pet.create(1)); | |
62 | + long petCount = sqlTemplate.queryForLong("SELECT COUNT(*) FROM t_pet", null); | |
63 | + assertEquals(1, petCount); | |
64 | + | |
65 | + petCount = sqlTemplate.queryForLong("SELECT COUNT(*) FROM t_pet WHERE 1=2 ", null); | |
66 | + assertEquals(0, petCount); | |
67 | + | |
68 | + } | |
69 | + | |
70 | + @Test | |
71 | + public void testQueryForObjectClassOfT() { | |
72 | + pojos.initPet(); | |
73 | + Pet pet = Pet.create("papa"); | |
74 | + Timestamp createTime = new Timestamp(System.currentTimeMillis()); | |
75 | + pet.setBirthday(createTime); | |
76 | + | |
77 | + dao.insert(pet); | |
78 | + String sql = "SELECT birthday FROM t_pet"; | |
79 | + Timestamp dbCreateTime = sqlTemplate.queryForObject(sql, null, Timestamp.class); | |
80 | + assertEquals(createTime, dbCreateTime); | |
81 | + | |
82 | + String sql1 = "SELECT birthday FROM t_pet WHERE 1=2"; | |
83 | + dbCreateTime = sqlTemplate.queryForObject(sql1, null, Timestamp.class); | |
84 | + assertTrue(dbCreateTime == null); | |
85 | + } | |
86 | + | |
87 | + @Test | |
88 | + public void testQueryForObjectEntityOfT() { | |
89 | + pojos.initPet(); | |
90 | + Pet pet = Pet.create("papa"); | |
91 | + dao.insert(pet); | |
92 | + | |
93 | + String sql = "SELECT * FROM t_pet"; | |
94 | + Pet p2 = sqlTemplate.queryForObject(sql, null, dao.getEntity(Pet.class)); | |
95 | + assertEquals(pet.getName(), p2.getName()); | |
96 | + | |
97 | + String sql1 = "SELECT * FROM t_pet WHERE 1=2"; | |
98 | + Pet p3 = sqlTemplate.queryForObject(sql1, null, dao.getEntity(Pet.class)); | |
99 | + assertTrue(p3 == null); | |
100 | + } | |
101 | + | |
102 | + @Test | |
103 | + public void testQueryForRecord() { | |
104 | + pojos.initPet(); | |
105 | + Pet pet = Pet.create("papa"); | |
106 | + dao.insert(pet); | |
107 | + | |
108 | + String sql = "SELECT name,age FROM $table WHERE id = @id"; | |
109 | + | |
110 | + Map<String, Object> vars = new HashMap<String, Object>(); | |
111 | + vars.put("table", "t_pet"); | |
112 | + | |
113 | + Map<String, Object> params = new HashMap<String, Object>(); | |
114 | + params.put("id", pet.getId()); | |
115 | + | |
116 | + Record re = sqlTemplate.queryForRecord(sql, vars, params); | |
117 | + | |
118 | + assertEquals(pet.getName(), re.getString("name")); | |
119 | + } | |
120 | + | |
121 | + @Test | |
122 | + public void testQuery() { | |
123 | + pojos.initPet(); | |
124 | + dao.insert(Pet.create(4)); | |
125 | + | |
126 | + String sql = "SELECT * FROM t_pet"; | |
127 | + List<Pet> pets = sqlTemplate.query(sql, null, dao.getEntity(Pet.class)); | |
128 | + | |
129 | + assertEquals(4, pets.size()); | |
130 | + | |
131 | + assertEquals("pet_00", pets.get(0).getName()); | |
132 | + } | |
133 | + | |
134 | + @Test | |
135 | + public void testQueryForList() { | |
136 | + pojos.initPet(); | |
137 | + dao.insert(Pet.create(4)); | |
138 | + | |
139 | + String sql = "SELECT name FROM t_pet"; | |
140 | + | |
141 | + List<String> names = sqlTemplate.queryForList(sql, null, null, String.class); | |
142 | + | |
143 | + assertTrue(names.contains("pet_00")); | |
144 | + | |
145 | + String sql1 = "SELECT name FROM t_pet WHERE 1=2"; | |
146 | + | |
147 | + names = sqlTemplate.queryForList(sql1, null, null, String.class); | |
148 | + | |
149 | + assertTrue(names.isEmpty()); | |
150 | + } | |
151 | + | |
152 | + @Test | |
153 | + public void testQueryRecords() { | |
154 | + pojos.initPet(); | |
155 | + dao.insert(Pet.create(4)); | |
156 | + | |
157 | + String sql = "SELECT name FROM t_pet"; | |
158 | + | |
159 | + List<Record> res = sqlTemplate.queryRecords(sql, null, null); | |
160 | + | |
161 | + assertEquals("pet_00", res.get(0).getString("name")); | |
162 | + | |
163 | + } | |
164 | + | |
165 | + @Test | |
166 | + public void testSqlInExp() { | |
167 | + pojos.initPet(); | |
168 | + dao.insert(Pet.create(4)); | |
169 | + | |
170 | + String sql = "SELECT name FROM t_pet WHERE id IN (@ids)"; | |
171 | + | |
172 | + Map<String, Object> params = new HashMap<String, Object>(); | |
173 | + params.put("ids", Lang.array(1, 2, 3, 4)); | |
174 | + | |
175 | + List<String> names = sqlTemplate.queryForList(sql, null, params, String.class); | |
176 | + assertTrue(names.size() == 4); | |
177 | + assertTrue(names.contains("pet_00")); | |
178 | + | |
179 | + params = new HashMap<String, Object>(); | |
180 | + params.put("ids", Lang.list(1, 2, 3, 4)); | |
181 | + | |
182 | + names = sqlTemplate.queryForList(sql, null, params, String.class); | |
183 | + assertTrue(names.size() == 4); | |
184 | + assertTrue(names.contains("pet_00")); | |
185 | + | |
186 | + } | |
187 | +} |