SpringMVC框架下的 部分代码：
Controller控制器：
@Resource
ReviewTitleService reviewTitleService;//调用ReviewTitleService 类时，先定义一个对象
Gson gson = new Gson();//调用Gson 时，先定义一个对象
/**
* 获得职称评审通过率
* @param request
* @param department
* @param year
* @return
*/
@ResponseBody
@RequestMapping(value = "getRate", produces = "application/json; charset=utf-8")
public String getRate(HttpServletRequest request,
@RequestParam(required = false) String department,
@RequestParam(required = false) String year){
return gson.toJson(reviewTitleService.getRate(department, year));
}
Service接口:
/**
* 获得评审表通过率
* @param department
* @param year
*/
public List<Map<String, Object>> getRate(String department, String year){
try {
String sql = "SELECT s.FIRSTDEPARTMENT as department, t.YEAR as year, "
+ "SUM(CASE WHEN t.STATE>=50 AND t.STATE<60 THEN 1 ELSE 0 END) as reportNmuber, "
+ "SUM(CASE WHEN t.STATE>=60 THEN 1 ELSE 0 END) as throughNumber, "
+ "SUM(CASE WHEN t.STATE>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN t.STATE>=50 THEN 1 ELSE 0 END) as rate "
+ "FROM rt_review_title t "
+ "LEFT JOIN staff s ON s.CODE=t.CODE where 1=1 ";
if (year != null && !"".equals(year)) {
sql += "and t.year = '" + year + "' ";
}
if (department != null && !"".equals(department)) {
sql += "and s.FIRSTDEPARTMENT = '" + department + "' ";
}
sql += "GROUP BY t.YEAR,s.FIRSTDEPARTMENT";
System.out.println(sql);
List<Map<String, Object>> result = hibernateDao.findSql(sql);
System.out.println(result);
return result;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}