如何根据多条件限定进行查询

为了实现同行车的查询在规定的时间段内，根据车牌号码、车辆颜色、卡口列表是vachar类型的，还得排除不选的颜色的车辆

1.

package com.ia.lhc.controller;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ia.lhc.config.ConstantUtils;
import com.ia.lhc.config.DataSourceTypeManager;
import com.ia.lhc.config.DataSources;
import com.ia.lhc.form.AccompanyCarForm;
import com.ia.lhc.httpclient.HttpUtils;
import com.ia.lhc.model.AccompanyCarDetailEntity;
import com.ia.lhc.model.AccompanyCarEntity;
import com.ia.lhc.model.BasicCar;
import com.ia.lhc.model.BasicTrailEntity;
import com.ia.lhc.model.DevicesAreaEntity;
import com.ia.lhc.model.DevicesEntity;
import com.ia.lhc.model.ErrorMsgEntity;
import com.ia.lhc.model.MultiSpaceTimeEntity;
import com.ia.lhc.model.VehicleInfoEntity;
import com.ia.lhc.response.AccompanyCarDetailResponse;
import com.ia.lhc.response.AccompanyCarResponse;
import com.ia.lhc.response.FreqCarDetailResponse;
import com.ia.lhc.response.MultiSpaceTimeDetailResponse;
import com.ia.lhc.service.AccompanyCarService;
import com.ia.lhc.service.CarInfoService;
import com.ia.lhc.service.DevicesService;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * @author wang.w.w
 *  1.哪些参数是必选的哪些是可选的
 *  2.数据的查询和排除指定颜色的车
 */
@Controller
public class AccompanyCarController {

    @Autowired
    private DevicesService devicesService;

    @Autowired
    private CarInfoService carInfoService;

    @Autowired
    private AccompanyCarService accompanyCarService;

    /**
     * LOG信息
     */
    private Logger logger = Logger.getLogger(this.getClass().getName()); 

    /**
     * 同行车分析查询
     * @param accompanyCarForm
     * @return response
     */
    @RequestMapping(value = "/getPeerCar", method=RequestMethod.POST)
    @ResponseBody
    public AccompanyCarResponse getAccompanyCar(@RequestBody AccompanyCarForm accompanyCarForm){


        // 返回前台类
        AccompanyCarResponse response = new AccompanyCarResponse();
        // 实例化结果集
        List<AccompanyCarEntity> accompanyCarEntityList = new ArrayList<AccompanyCarEntity>();
        //用户传进来的参数      
        String starttime = accompanyCarForm.getStartdate();
        String endtime = accompanyCarForm.getEnddate();

//      String platenumber = accompanyCarForm.getPlatenumber();
//      String colors = accompanyCarForm.getColors();           //车牌颜色
//      String[] devicesId = accompanyCarForm.getDevicesId();   //得到卡口列表
//      String[] removecartype = accompanyCarForm.getRemovecartype(); //排除的车辆颜色

        //传进来的yyyy-MM-dd转换为yyyyMMdd
        try {
            String transferStartTime = transferFormat1(starttime);
            String transferEndTime = transferFormat1(endtime);  
            accompanyCarForm.setStartdate(transferStartTime);
            accompanyCarForm.setEnddate(transferEndTime);

        } catch (ParseException e) {
            logger.info("时间格式转为字符串格式错误！");
            e.printStackTrace();
        }
        // 查询
        DataSourceTypeManager.set(DataSources.DATASOURCE2);
        accompanyCarEntityList = accompanyCarService.getAccompanyCarList(accompanyCarForm);
        if (accompanyCarEntityList.size() > 0 && accompanyCarEntityList!=null ) {           
            //1，遍历每一个同行车的列表 2.去获取同行车里面的车牌号再去获取车辆的基本信息 3，再去拿到获取该车辆是不是违章和是否是强盗车
            BasicCar carInfo2 = new BasicCar();
            for(AccompanyCarEntity accompanyCarEntity:accompanyCarEntityList){  
                //这部分是把概率小数转为%形式
                String rank = accompanyCarEntity.getRank();
                double parseDouble = (Double.parseDouble(rank))*100;
                String rankStr =  String.valueOf(parseDouble)+"%";
                accompanyCarEntity.setRank(rankStr);
                // 查询车辆违法信息
                DataSourceTypeManager.set(DataSources.DATASOURCE1); 
                carInfo2 = carInfoService.getCarInfo(accompanyCarEntity.getPlatenumber2());
                if(carInfo2!=null){
                    //把根据车牌号查询到的是否违章信息和是否强盗车信息保存到实体中
                    accompanyCarEntity.setRobberyCarFlag(carInfo2.getRobberyCarFlag());
                    accompanyCarEntity.setPeccancyInfo(carInfo2.getPeccancyInfo());
                }
                else{
                    accompanyCarEntity.setRobberyCarFlag("");
                    accompanyCarEntity.setPeccancyInfo("");
                }

            }   
            response.setAccompanyCarEntityList(accompanyCarEntityList);
            response.setCode(ConstantUtils.RETURN_RESULT_SUCCESS_CODE_0);
            response.setMsg("同行车查询成功！");

        }else {
            response.setCode(ConstantUtils.RETURN_RESULT_FAIL_CODE_1);
            response.setMsg("未找到同行车信息！");
            response.setAccompanyCarEntityList(new ArrayList<AccompanyCarEntity>());
        }

        return response;
    }

2.service层

1，接口

package com.ia.lhc.service;

import java.util.List;
import com.ia.lhc.form.AccompanyCarForm;
import com.ia.lhc.model.AccompanyCarDetailEntity;
import com.ia.lhc.model.AccompanyCarEntity;

public interface AccompanyCarService {

    /**
     * 获取同行车分析
     * @return List<AccompanyCarEntity>
     */
    public List<AccompanyCarEntity> getAccompanyCarList(AccompanyCarForm accompanyCarForm);


}

3.实现类

package com.ia.lhc.service.impl;

import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ia.lhc.form.AccompanyCarForm;
import com.ia.lhc.mapper.AccompanyCarMapper;
import com.ia.lhc.model.AccompanyCarEntity;
import com.ia.lhc.service.AccompanyCarService;

@Service
public class AccompanyCarServiceImpl implements AccompanyCarService{

    @Autowired
    private AccompanyCarMapper accompanyCarMapper;

    /**
     * 获取同行车分析
     */
    @Override
    public List<AccompanyCarEntity> getAccompanyCarList(AccompanyCarForm accompanyCarForm) {

//      String str = String.join(",", accompanyCarForm.getDevicesId());
//      accompanyCarForm.setDevices(str);
        String[] devicesId = accompanyCarForm.getDevicesId();   

        List<AccompanyCarEntity> list = accompanyCarMapper.getAccompanyCarList(accompanyCarForm);   
        //为了防止报空指针，如果当数组的第一个数等于1说明卡口列表就是为空
        if(devicesId!=null && devicesId.length > 0){
            List<AccompanyCarEntity> resultList = new ArrayList<AccompanyCarEntity>();

            for(AccompanyCarEntity accompanyCarEntity : list){
                String deviceIDs = accompanyCarEntity.getDeviceIDs();    //数据库的字符串
                String[] strArr = accompanyCarForm.getDevicesId();       //前台传过来的字符串数组
                int i = 0;
                for( ; i < strArr.length; i++){
                    int indexNum = deviceIDs.indexOf(strArr[i]);    
                    if(indexNum<0){
                        break;
                    }
                }
                if(i>=strArr.length){
                    resultList.add(accompanyCarEntity);
                }
            }   
            return resultList;
        }
        else{
            return list;
        }


    }
}

mapper接口类

package com.ia.lhc.mapper;

import java.util.List;
import com.ia.lhc.form.AccompanyCarForm;
import com.ia.lhc.model.AccompanyCarEntity;

public interface AccompanyCarMapper {

    /**
     * 获取同行车分析
     * @param accompanyCarForm
     * @return
     */
    List<AccompanyCarEntity> getAccompanyCarList(AccompanyCarForm accompanyCarForm);



}

mappering映射sql

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ia.lhc.mapper.AccompanyCarMapper" >
  <resultMap id="BaseResultMap" type="com.ia.lhc.model.AccompanyCarEntity" >
    <result column="id" property="id" jdbcType="INTEGER" />
    <result column="areaId" property="areaId" jdbcType="INTEGER" />
    <result column="date" property="date" jdbcType="VARCHAR" />
    <result column="platenumber1" property="platenumber1" jdbcType="VARCHAR" />
    <result column="platecolor1" property="platecolor1" jdbcType="VARCHAR" />
    <result column="platenumber2" property="platenumber2" jdbcType="VARCHAR" />
    <result column="platecolor2" property="platecolor2" jdbcType="VARCHAR" />
    <result column="deviceIDs" property="deviceIDs" jdbcType="VARCHAR" />
    <result column="accompanyTime" property="accompanyTime" jdbcType="NUMERIC" />
    <result column="rank" property="rank" jdbcType="NUMERIC" />
    <result column="accompanyCount" property="accompanyCount" jdbcType="INTEGER" />
  </resultMap>

  <sql id="Base_Column_List">
    id,areaId,date,platenumber1,platecolor1,platenumber2,platecolor2,deviceIDs,accompanyTime,rank,accompanyCount
  </sql>

  <!-- 获取同行车分析 -->
  <select id="getAccompanyCarList" resultMap="BaseResultMap" parameterType="com.ia.lhc.form.AccompanyCarForm">
    SELECT
    <include refid="Base_Column_List" />
    FROM
        iabc_ttr_accompanycar AC
    WHERE  
       AC.date between #{startdate} and #{enddate} 

    <if test="platenumber !=null and platenumber !=''">
        AND (AC.platenumber1 =#{platenumber})
    </if>
    <if test="colors!=null and colors!=''">
        AND (AC.platecolor1  = #{colors})
    </if>

  </select>

</mapper>