Oracle+SSM项目查询指定时间段的数据

系统管理员 2024-02-18 21:06 61阅读 0赞

映射文件中SQL的写法：

<select id="getDataByMap" parameterType="Map" resultMap="BaseResultMap">
      select <include refid="Base_Column_List"/> from table 
       WHERE 1 = 1
       <if test="archiveid != null and archiveid !=''">
            AND ARCHIVEID = #{archiveid}
        </if>
            
         <if test="beginDate!=null and beginDate !=''">
         and <![CDATA[CREATED_DATE>=to_date(#{beginDate}, 'yyyy-mm-dd') ]]>
        </if>
        
        <if test="endDate!=null and endDate !=''">
        and <![CDATA[CREATED_DATE<=to_date(#{endDate}, 'yyyy-mm-dd') ]]>  
        </if>
         ORDER BY  CREATED_DATE DESC
     </select>

控制层的写法：

int page = Integer.parseInt(pageNumber);
            int size = Integer.parseInt(pageSize);
            String beginDate=req.getParameter("beginTime");
            String endDateStr=req.getParameter("endTime");
            String endDate=DateUtil.getSpecifiedDayAfter(endDateStr);
            Map<String, Object>map=new HashMap<String,Object>();
            map.put("archiveid", archiveId);
            map.put("beginDate", beginDate);
            map.put("endDate", endDate);
            PageInfo<T> pageInfo = ytjService.getDataByMap(map,page, size);
            List<T> spo2s = pageInfo.getList();
            Gson gson = new Gson();
            JSONObject result = new JSONObject();
            result.put("rows", gson.toJson(spo2s));
            result.put("total", spo2s.size());
            ResponseUtil.write(resp, result);

该控制层结合了分页查询，在结束日期处理上，为防止查询不出截止日期当天的数据，将endDate延后一天。