SQL操作与查询详解：SQLTemplate使用教程-优快云博客

	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	+}

	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	+}

nutz dao 操作