后台SQL语句中嵌套时间查询某时间段内数据

叙：在做一个关于按照开始时间到结束时间，查询这一时间段内的表数据时遇到了一些问题，就是开始时间和结束时间的注入到SQL语句中方式以及运行SQL语句的运行等，尝试了许多次最后终于搞定了~

---

---

背景

---

代码目的

首先，代码编写的目的是连表查询，查询几个表中的数据进行相互印证，最终得到符合条件的数据，在此过程中需要有个调价就是根据某张表中的时间进行判断取值，得到某个时间段内的数据；

---

代码已有数据

数据表：
数据库表：A表、B表
两张表中存在一些数据，重点的几个列：A表中有个“env_id”、“sampler_name”（这两个都是不怎么重要的，也没有太多的难点，本章中并不对这个作为重点），B表中有“sample_time”，重点注意的是sample_time这个列的数据，这是一个数据类型为datetime的数据列，存放是的某个作业的时间点，下边要进行时间查询的判断依据就是这个列的数据；

方法中传递过来的参数：
1、 jsonObject对象，这个里封装了前台传过来的开始时间（startTime)和截至时间（endTime），即是查询某个时间段内的生成的数据；
2、 beginSize、pageSize这两个是分页所用到的索引码、页码两个数据；

---

尝试

失败的代码

Dao层实现类（数据持久层代码）：

@Override
	public Pagination<JSONObject> findAllList(int beginSize, int pageSize, JSONObject jsonObject) {
		StringBuffer s = new StringBuffer();
		s.append("SELECT t1.id_ SamplerId, t1.sampler_name Name, t1.sample_date Date, t2.meal_ Type ");
		s.append("FROM A t1, B t2 ");
		s.append("WHERE t1.env_id = '"+jsonObject.get("envId")+"' ");
		if (jsonObject.get("title") != null && StringUtils.isNotBlank(jsonObject.get("title").toString())) {
			s.append("and t1.sampler_name like '%" + jsonObject.get("title").toString() + "%' ");
		}
		
		if (jsonObject.get("startTime") != null && StringUtils.isNotBlank(jsonObject.get("startTime").toString())) {
			if (jsonObject.get("endTime") != null && StringUtils.isNotBlank(jsonObject.get("endTime").toString())) {
				s.append("AND t2.sample_time BETWEEN '" + jsonObject.get("startTime").toString() + "' AND '" + jsonObject.get("endTime").toString() + "' ");
			}
		}
		s.append("AND t1.id_ = t2.info_id ");
		
		String sql = s.toString();
		
		SQLQuery query = this.createSqlQuery(sql);
		long total = query.list().size();
		if (total == 0) {
			return SimplePagination.emptySplitPageResult();
		}

		query.setMaxResults(pageSize);
		query.setFirstResult(beginSize);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

		List<Map<String, Object>> querylist = getJdbcTemplate().queryForList(sql);

		List<JSONObject> result = new ArrayList<>();

		// Map-->JSONObject
		for (Map<String, Object> map : querylist) {
			JSONObject json = new JSONObject();
			json.put("date", (Date) map.get("Date"));
			json.put("name", (String) map.get("Name"));
			json.put("type", (String) map.get("Type"));
			json.put("id", (Long) map.get("SamplerId"));
			result.add(json);
		}
		return new SimplePagination<>(beginSize, pageSize, total, result);
	}

private SQLQuery createSqlQuery(String sql) {
		SQLQuery query = this.currentSession().createSQLQuery(sql);
		return query;
	}

在这个代码段里尝试过SQL语句中拼接参数、占位符等方法，发现均是行不通，研究后发现主要原因在于SQL语句的执行方法有问题，因为一直使用的是createSqlQuery(sql) 来执行的SQL语句，总是会报出语法错误什么的，最后换成了spring封装的JDBC的模板，就是JdbcTemplate接口来执行的SQL语句，并对参数的注入进行了处理方可，最终运行成功的代码如下文；

---

成功的代码

@Override
	public Pagination<JSONObject> findAllList(int beginSize, int pageSize, JSONObject jsonObject) {
		StringBuffer s = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		s.append("from (");
		s.append("SELECT t1.id_ SamplerId, t1.sampler_name Name, t1.sample_date Date, t2.meal_ Type ");
		s.append("FROM ent_product_sample_info t1, ent_product_sample_detail t2 ");
		s.append("WHERE t1.env_id = '"+jsonObject.get("envId")+"' ");
		s.append("AND t1.id_ = t2.info_id ");
		if (jsonObject.get("startTime") != null) {
			s.append("AND t1.sample_date >= ? ");
			param.add(jsonObject.getDate("startTime"));
		}
		if (jsonObject.get("endTime") != null) {
			s.append("AND t1.sample_date <= ? ");
			param.add(jsonObject.getDate("endTime"));
		}
		s.append(")t");
		
		Object[] para = param.toArray(new Object[param.size()]);

		long total = this.getJdbcTemplate().queryForObject("select count(1)" + s.toString(), para, long.class);
		if (total == 0) {
			//返回一个空白分页结果 === 这是已经封装好的方法，大家可以跳过此段代码~
			return SimplePagination.emptySplitPageResult();
		}
		s.append(" limit " + beginSize + "," +pageSize);
		
		List<Map<String, Object>> querylist = getJdbcTemplate().queryForList("select *" + s.toString(),para);

		List<JSONObject> result = new ArrayList<>();

		// Map-->JSONObject
		for (Map<String, Object> map : querylist) {
			JSONObject json = new JSONObject();
			json.put("date", (Date) map.get("Date"));
			json.put("name", (String) map.get("Name"));
			json.put("type", (String) map.get("Type"));
			json.put("id", (Long) map.get("SamplerId"));
			result.add(json);
		}
		return new SimplePagination<>(beginSize, pageSize, total, result);
	}

注意：
1、 创建一个list集合存放下文中要运用到的参数；
2、将分页前的所有SQL语句使用括号括起来，作为一个查询表；
3、 SQL语段中每有一个占位符 ?，就会在其SQL段落下给之前创建的list对象写入相应的数据；
4、 在这个实例中，往list集合中写入时间设定的参数时需要根据对应的数据库表中的时间的那一列数据类型进行调整，我的数据库表B中的时间列类型是datetime，因此，在写入时是以param.add(jsonObject. getDate(“endTime”));的形式写入的；
5、将list集合转化成JdbcTemplate接口可使用的数组类型集合；
Object[] para = param.toArray(new Object[param.size()]);
6、调用JdbcTemplate方法接口的queryForList方法，将括号内的SQL语句转化成字符串形式并在其前面加上 select count(1) 作为第一个参数，转化成数组的参数据集合作为第二个参数，第三个参数是查询结果的类型，此时我查询得是符合查询语句的数据条数，应用Long类型的；
7、 加上分页语句；
8、再次调用JdbcTemplate方法接口的queryForList方法，只传入两个参数，第一个是 "select * " 加上（+）toString的SQL语句，第二个还是参数数组，这个查询结果不言而喻就是我们需要的数据了，返回值类型是 List<Map<String, Object>> 集合；
9、剩下的就没什么好讲的了。。。。。

---

总结

最终完成了此功能，但之前的错误的原因并不是很明白，有的说是 createSqlQuery 不支持那样写，自己再尝试时通过createSqlQuery方法加上占位符方法时总是发现数据传不进去，很是奇怪，希望能有人指点一下。

---

《END》