jsva中 数据库行转列 合并数据 在jsp展示

1)jsp

<!-- 数据展示 -->
     <c:set var="i" value="1" />
     <c:forEach var="bean" items="${beanList}">
      <c:forEach var="bean1" items="${bean.children}" varStatus="s1">
       <c:forEach var="bean2" items="${bean1.children}" varStatus="s2">
        <c:forEach var="bean3" items="${bean2.children}" varStatus="s3">
         <c:forEach var="bean4" items="${bean3.children}" varStatus="s4">
          <tr>
           <c:if
            test="${s1.count==1&&s2.count==1&&s3.count==1&&s4.count==1}">
            <td rowspan="${bean.length}" class="td3">
             ${bean.measuretype1}
            </td>
           </c:if>
           <c:if test="${s2.count==1&&s3.count==1&&s4.count==1}">
            <td rowspan="${bean1.length}" class="td3">
             ${bean1.measuretype2}
            </td>
           </c:if>
           <c:set var="i" value="${i+1}" />
           <c:if test="${s3.count==1&&s4.count==1}">
            <td rowspan="${bean2.length}"
             <c:if test="${i%2==0}"> class="td2"</c:if>>
             ${bean2.departmentname}
            </td>
           </c:if>
           <c:if test="${s4.count==1}">
            <td rowspan="${bean3.length}"
             <c:if test="${i%2==0}"> class="td2"</c:if>>
             ${bean3.measurename}
            </td>
           </c:if>

            </tr>
         </c:forEach>
        </c:forEach>
       </c:forEach>
      </c:forEach>
     </c:forEach>

 

2)action

public ModelAndView daily_monitor(HttpServletRequest request,
   HttpServletResponse response, Object obj) throws Exception {
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("statdate", request.getParameter("statdate"));
  params.put("warnlight", request.getParameter("warnlight"));
  params.put("department", request.getParameter("department"));
  
  params.put("beanList", beanList);
  params.put("dateList", reportService.searchStatdateStringList());
  
  
    params.put("departmentList", reportService.searchDepartmentMapList(params));

  log.debug("业务逻辑一切正常!");
  return new ModelAndView(dailyMonitor, params);
 }

 

 

3)service

public List<Map<String, Object>> searchDailyMonitorMapList(
   Map<String, Object> params) throws DaoException {
  String statdate = String.valueOf(params.get("statdate"));
  String warnlight = String.valueOf(params.get("warnlight"));
  String department = String.valueOf(params.get("department"));
  try {
   dateFormat.parse(statdate);
  } catch (Exception e) {
   log.debug("\"statdate\"参数不存在,启用默认参数!");
   long time;
   try {
    String dateStr = dateFormat01.format(new Date());
    time = dateFormat.parse(dateStr).getTime() - 24 * 60 * 60;
    params.put("statdate", dateFormat.format(new Date(time)));
   } catch (Exception e1) {
    e1.printStackTrace();
   }
  }
  if (null == warnlight || "".equals(warnlight)) {
   params.put("warnlight", null);
  }
  if (null == department || "".equals(department)) {
   params.put("department", null);
  }
  List<Map<String, Object>> beans = null;
  beans = reportDao.searchDailyMonitorMapList(params, true);
  LinkedList<String> keyList = new LinkedList<String>();

　　//合并数据的字段名  
  keyList.add("measuretype1");
  keyList.add("measuretype2");
  keyList.add("departmentname");
  keyList.add("measurename");
  return VerticalMerger.transform(keyList, beans);
 }

 

3)dao:

public List<Map<String, Object>> searchDailyMonitorMapList(
   Map<String, Object> params, boolean toLowerCase)
   throws DaoException {
  Map<String, Object> bean = null;
  List<Map<String, Object>> mapList = null;
  List<Map<String, Object>> beanList = null;
  params.put("fmt", "([[:digit:]]+)[.]$");
  mapList = baseSupport.getList("searchDailyMonitorMapList", params);
  if (!toLowerCase) {
   return mapList;
  }

　　// 工具类  转小写
  return LowerCaseUtillity.toLowerCase(mapList);
 }

 

4)SQL

<select id="searchDailyMonitorMapList" resultClass="java.util.HashMap">
  <![CDATA[
   SELECT distinct p1.orderno,
       P4.CODENAME AS MEASURETYPE1,
                   P6.CODENAME AS MEASURETYPE2,
                   p1.measuretype2 as measuretype3,
                   P1.MEASURENAME,
                   CASE
                     WHEN P1.DATASOURCES = '12' THEN
                      P5.MEASUREDESC
                     WHEN p1.measureunit='0' THEN
                     REGEXP_REPLACE(TO_CHAR(P2.MEASUREVALUE*100,
                                             'fm999999999999999990.00999999'),
                                     #fmt#,
                                     '\1')||'%'
                     ELSE
                      REGEXP_REPLACE(TO_CHAR(P2.MEASUREVALUE,
                                             'fm999999999999999990.999999'),
                                     #fmt#,
                                     '\1')
                   END AS MEASUREVALUE,
                   P2.WARNLIGHT,
                   p2.manageidea,
                   P3.DEPARTMENTNAME,
                   P1.MEASUREDESC,
                   p1.measureunit
     FROM F_IC_MEASURE_INFO P1
        LEFT JOIN F_IC_MEASURE_WARN P2
        ON p1.measurecode=p2.measurecode
        AND p2.statdate = to_date(#statdate#, 'yyyy-mm-dd')
     LEFT JOIN F_IC_DESCMEASURE_INFO P5 ON P2.MEASURECODE = P5.MEASURECODE
                                       AND P2.WARNLIGHT = P5.WARNLIGHT
    LEFT JOIN CD_IC_DEPARTMENTCODE P3 ON p1.departmentcode=p3.departmentcode, CD_IC_CODE P4,
    CD_IC_CODE P6
    WHERE P1.MEASURETYPE1 = P4.CODE
      AND P4.CODETYPE = 'measuretype1'
      AND P1.MEASURETYPE2 = P6.CODE
      AND P6.CODETYPE = 'measuretype2'
      AND p1.summarytype = '0'
      AND P1.BEGINTIME <= to_date(#statdate#, 'yyyy-mm-dd')
      and p1.endtime >= to_date(#statdate#, 'yyyy-mm-dd')
   ]]>
  <dynamic>

 

<!--查询条件-->
   <isNotNull property="warnlight">
    <![CDATA[
      AND p2.warnlight = #warnlight#
     ]]>
   </isNotNull>
   <isNotNull property="department">
    <![CDATA[
      AND p1.departmentcode = #department#
     ]]>
   </isNotNull>
  </dynamic>
  <![CDATA[
      ORDER BY p1.measuretype2, p1.orderno, p4.codename, p6.codename, p1.measurename, p3.departmentname
  ]]>
 </select>

 

转载于:https://www.cnblogs.com/square198901/archive/2012/02/28/2371769.html