设备支持多箱号查询，mybatis<if><foreach>标签

最新推荐文章于 2024-06-12 21:03:32 发布

原创最新推荐文章于 2024-06-12 21:03:32 发布 · 241 阅读

0 ·

CC 4.0 BY-SA版权

CLAA 专栏收录该内容

221 篇文章

订阅专栏

本文介绍了一个用于查询设备信息列表的RESTful API实现。通过解析HTTP请求参数，如项目编号、箱号、设备编号等，该API能够从数据库中检索出符合特定条件的设备信息，并返回给调用者。

/**
 * 查询设备信息列表
 * 
 * @param request
 * @param response
 * @return
 */
@RequestMapping("devList")
@ResponseBody
public List<OsmDev> orderList(HttpServletRequest request, HttpServletResponse response) throws Exception {
	//项目编号
	String projectid = request.getParameter("projectid");
	//箱号
	String boxnoStr = request.getParameter("boxno");
	List<String> boxnoList = new ArrayList<String>();
	if(boxnoStr !=null && !boxnoStr.equals("")){
		String[] boxnoArr = boxnoStr.split(",");
		for(String boxno : boxnoArr){
			boxnoList.add(boxno);
		}
	}
	//设备编号
	String deveui = request.getParameter("deveui");
	deveui = (deveui == null) ? "" : deveui.trim();
	deveui = deveui.trim().toLowerCase();
	//发货单编号
	String ordNo = request.getParameter("ordNo");
	
	//设备类型
	String devtype = request.getParameter("devtype");
	//设备状态
	String devStatusStr = request.getParameter("devStatus");
	Integer devStatus=-1;
	if(devStatusStr != null && !devStatusStr.trim().equals("")){
		devStatus = Integer.parseInt(devStatusStr);
	}
	//登记开始时间
	String beginTime = request.getParameter("beginTime");  
	String beginTimeFenmiao ="";
	if(beginTime !=null && !beginTime.trim().equals("") ){
		beginTimeFenmiao = beginTime + " "+"00:00:00";
	}
	//登记结束时间
	String endTime = request.getParameter("endTime");  
	String endTimeFenmiao ="";
	if(endTime !=null && !endTime.trim().equals("") ){
		endTimeFenmiao = endTime + " "+"23:59:59";
	}
	logger.info("deveui = "+deveui+", ordNo="+ordNo+",devStatus="+devStatus+", beginTime="+beginTime+", finishdate="+endTime);		
	projectid = (projectid == null) ? "" : projectid.trim();
	ordNo = (ordNo == null) ? "" : ordNo.trim();
	devtype = (devtype == null) ? "" : devtype.trim();
	deveui = (deveui == null) ? "" : deveui.trim();
	beginTime = (beginTime == null) ? "" : beginTime.trim();
	endTime = (endTime == null) ? "" : endTime.trim();
	DevQueryPara para = genDevQryParam(projectid.trim(),deveui.trim(),ordNo.trim(), devtype.trim(),devStatus,beginTimeFenmiao, endTimeFenmiao,boxnoList);
	return devService.queryAllOsmDevByPara(para);
}

private DevQueryPara genDevQryParam(String projectid,String deveui,String ordNo,String devtype,Integer devStatus,String beginTimeFenmiao,String endTimeFenmiao,List<String> boxnoList)
{
	DevQueryPara para = new DevQueryPara();
	para.setProjectid(projectid);
	para.setDeveui(deveui);
	para.setOrdNo(ordNo);
	para.setDevType(devtype);
	para.setDevStatus(devStatus);
	para.setBeginDate(DateUtil.parseDatetime(beginTimeFenmiao));
	para.setEndDate(DateUtil.parseDatetime(endTimeFenmiao));
	para.setBoxnoList(boxnoList);
	return para;
}

// 查询OsmDev信息
public List<OsmDev> queryAllOsmDevByPara(DevQueryPara p) throws Exception;


@Override
public List<OsmDev> queryAllOsmDevByPara(DevQueryPara p) throws Exception {
	return this.devMapper.queryAllOsmDevByPara(p);
}

// 查询OsmDev信息
public List<OsmDev> queryAllOsmDevByPara(@Param("p") DevQueryPara p) throws Exception;

<select id="queryAllOsmDevByPara" resultType="com.zte.claa.infiboss.app.model.osm.OsmDev">
	 SELECT t.DEVEUI AS deveui, 
			t.DEVTYPE AS devType, 
			t.ORDNO AS ordNo, 
			t.CLAANO AS claaNo, 
			t.DEVSTATUS AS devStatus, 
			t.OPTIME AS opTime, 
			t.REMARK AS remark,
			t.PROJECTID AS projectid,
			t.ADDRESS AS address,
			t.GPSLAT AS gpslat,
			t.GPSLNG AS gpslng,
			t.GPSALT AS gpsalt,
			t.BOXNO AS boxno
	   FROM osmdb.t_osm_dev t 
	  WHERE 1 = 1 
	  <if test='p.projectid != null and p.projectid != "" '>
		 AND t.PROJECTID = #{p.projectid, jdbcType=VARCHAR} 
	  </if>
	  <if test='p.devType != null and p.devType != "" '>
		 AND t.DEVTYPE = #{p.devType, jdbcType=VARCHAR} 
	  </if>
	  <if test='p.deveui != null and p.deveui != "" '>
		 AND t.DEVEUI = #{p.deveui, jdbcType=VARCHAR} 
	  </if>
	  <if test='p.ordNo != null and p.ordNo != "" '> 
		 AND t.DEVEUI IN (SELECT DISTINCT DEVEUI 
							FROM osmdb.t_osm_order_dev 
						   WHERE ORDNO = #{p.ordNo, jdbcType=VARCHAR}) 
	  </if>
	  <if test='p.devStatus != -1 and p.devStatus != 99 and p.devStatus != -99 '>
		 AND t.DEVSTATUS = #{p.devStatus, jdbcType=INTEGER} 
	  </if>
	  <if test='p.devStatus == 99'>
		 AND t.DEVSTATUS NOT IN (20,21) 
	  </if>
	  <!--  -99代表后台重新创建发货清单   -->
	  <if test='p.devStatus == -99'>
		 AND t.DEVSTATUS IN (11,13) 
	  </if>
	  <if test='p.beginDate != null'>
		 AND t.OPTIME &gt;= #{p.beginDate, jdbcType=DATE} 
	  </if>
	  <if test='p.endDate != null'>
		 AND t.OPTIME &lt;= #{p.endDate, jdbcType=DATE} 
	  </if>
	  <if test='p.boxnoList != null and p.boxnoList.size()>0'>
		 AND t.BOXNO in  
	  <foreach collection="p.boxnoList"  open="("  close=")" separator="," item="boxno">  
			 #{boxno, jdbcType=VARCHAR} 
	  </foreach>
	  </if>
	  ORDER BY t.DEVEUI 
	  LIMIT #{p.batch, jdbcType=INTEGER} OFFSET #{p.offset, jdbcType=INTEGER} 
</select>