首先,可以在PL/SQL 中写 SQL 语句,试验 得出的 结果集,resultSet 是否 是我们所需要的,然后 利用 PL/SQL 的一个功能,右击,生成Java 代码类型的SQL语句,放到 Java后台中去执行。
public String searchCal(){
String whereStr ="where 1=1 " ;
/* 按日期查询 */
String startDateStr = (String) this.getParameter("t_startDate", String.class);
String endDateStr = (String) this.getParameter("t_endDate", String.class);
SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-DD");
if (startDateStr != null && !"".equals(startDateStr)) {
if (StringUtils.hasText(startDateStr)) {
whereStr+=" and to_date('"+startDateStr+"','YYYY-MM-DD')<=t.f_created \n";
}
}
if (endDateStr != null && !"".equals(endDateStr)) {
if (StringUtils.hasText(endDateStr)) {
whereStr+=" and to_date('"+endDateStr+"','YYYY-MM-DD')>=t.f_created \n";
}
}
/* 点对应ID只显示对应ID的对应客户 */
String cityName = (String) this.getParameter("t_cityName", String.class);
if (cityName != null && !"".equals(cityName)) {
whereStr+=" and t.f_city_name='"+cityName+ "'\n";
}
String sql =
"select t.f_city_name as cityName,count(*) as all_cnt\n" +
" ,sum(case when t.f_status='未通过' then 1 else 0 end) as err_cnt\n" +
" ,sum(case when t.f_status='通过' then 1 else 0 end) as ok_cnt\n" +
" ,sum(case when (select months_between( t.f_checked_time,t.f_created ) months from dual)>=1 then 1 else 0 end) as oneMonth_cnt\n" +
" ,sum(case when (select months_between( t.f_checked_time,t.f_created ) months from dual)>=2 then 1 else 0 end) as twoMonth_cnt\n" +
"from t_front_maintain t\n" +
whereStr +
"group by t.f_city_name";
System.out.println("sql"+sql);
list = jdbcTemplate.queryForList(sql) ;
getRequest().setAttribute("list", list);
return "listCal";
}
由于getRequest().setAttribute(“list”,list);中设置了属性
在jsp文件中
<c:forEach var="item" items="${ list}" varStatus="status">
<tr align="center">
<td nowrap="nowrap" align="center"><!-- 序号-->${ status.count}</td>
<td nowrap="nowrap" align="center"><!-- 地市 -->
${ item['cityName']}
</td>
<td nowrap="nowrap" align="center"><!-- 工单总数 -->
${ item['all_cnt']}
</td>
<td nowrap="nowrap" align="center"><!-- 未通过总数 -->
${ item['err_cnt']}
</td>
<td nowrap="nowrap" align="center"><!-- 通过时间超过一月数量 -->
${ item['oneMonth_cnt']}
</td>
<td nowrap="nowrap" align="center"><!-- 通过时间超过两个月数量 -->
${ item['twoMonth_cnt']}
</td>
</tr>
</c:forEach>