一般我们将sql语句的具体实现放在service/impl下的xxxServiceImpl中,
最好加上在方法开头加上@Override,@Override表示对父类方法的重写,
写上还可以避免方法名和参数写错。
代码如下:
@Override
// 稿件统计-获取入库稿件列表
public List<Map<String, Object>> bankScriptList(Integer roleId, Integer type, Integer num) throws ParseException {
String bTime = TimeUtils.getByType(type, num);
String eTime = TimeUtils.getByType(4, num);
List<Map<String, Object>> list1 = new ArrayList<>();
String aString = "select distinct(sp.script_origin_id),sp.module_id from script_process as sp left join script_module as sm on sp.script_origin_id =sm.script_origin_id where sp.user_id IN ("
+ "" + com.util.StringUtils.convertListToString(userIdList(departmentIdList(roleId))) + ")"
+ " and sp.isdelete = 0 and sm.isdelete = 0 and sm.script_status_id in (19,20) AND sm.update_time > '"
+ bTime + "' AND sm.update_time < '" + eTime + "'";
Query query = this.dao.getSession().createSQLQuery(aString);
query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
list1 = query.list();
return list1;
}
bTime、eTime是我自己在TimeUtils定义的开始时间和结束时间,
将sql语句写在aString字符串中,
然后用dao.getSession().createSQLQuery(aString)方法执行对aString
语句的查询,再通过query.setResultTransformer()将query的结果转化为map,
然后将query的结果转化为list返回,即将map放到list中返回。
query.setResultTransformer()的代码如下:
/**
* Set a strategy for handling the query results. This can be used to change
* "shape" of the query result.
*
* @param transformer The transformer to apply
* @return this (for method chaining)
*/
public Query setResultTransformer(ResultTransformer transformer);
Transformers的代码如下:
package org.hibernate.transform;
final public class Transformers {
private Transformers() {}
/**
* Each row of results is a <tt>Map</tt> from alias to values/entities
*/
public static final AliasToEntityMapResultTransformer ALIAS_TO_ENTITY_MAP =
AliasToEntityMapResultTransformer.INSTANCE;
/**
* Each row of results is a <tt>List</tt>
*/
public static final ToListResultTransformer TO_LIST = ToListResultTransformer.INSTANCE;
/**
* Creates a resulttransformer that will inject aliased values into
* instances of Class via property methods or fields.
*/
public static ResultTransformer aliasToBean(Class target) {
return new AliasToBeanResultTransformer(target);
}
}
然后我们需要在service/impl下的xxxService中写上一句来注明这个方法:
public List<Map<String, Object>> bankScriptList(Integer roleId, Integer type, Integer num) throws ParseException;
之后我们就可以在xxxController中调用这个sql语句的查询方法,比如:
List<Map<String, Object>> lists = userWorkStaticsService.bankScriptList(roleId, type, num);
List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();
//然后用for()方法遍历lists中的map对象
for (Map<String, Object> map : lists) {
//新建map1,将查询结果put到map1中;
Map<String, Object> map1 = new HashMap<>();
map1.put("draftNum", map.get("draftNum"));
map1.put("processingNum", map.get("processingNum"));
map1.put("waitPublishNum", map.get("waitPublishNum"));
map1.put("waitCheckNum", map.get("waitCheckNum"));
list1.add(map1);
}
然后将上面的list1返给前端,让前端解析出结果显示出来就可以了。