Mybatis多条件查询

题目
在EmpMapper接口中增加如下方法:
//根据动态条件查询符合条件的员工
List<Emp> getEmpByCondition(条件类);
 
测试如下功能:
1)请查询 员工所在部门为2,工资在3000-5000的员工。
2)请查询 员工入职时间超过5年以上,工资在3000-5000的员工。

代码示例
EmpMapper.xml:
这里仅列出主要方法代码。

<!--这里用了两种大于、小于、大于等于、小于等于的写法,仅为练习-->
<select id="getEmpByCondition" parameterType="Map" resultMap="EmpMap">
   select * from emp
   <trim prefix="where" suffixOverrides="and">

   <!--入职时间区间-->
   <if test="beginHireDate!=null or endHireDate!=null">
      <choose>
         <!--开始入职时间和结束入职时间相同-->
         <when test="beginHireDate==endHireDate">
             hireDate=#{beginHireDate} and
         </when>
         <when test="beginHireDate!=endHireDate">
            <![CDATA[ hireDate>=#{beginHireDate} and hireDate<=#{endHireDate} and]]>
         </when>
         <when test="beginHireDate!=null and endHireDate==null">
             <![CDATA[ hireDate>=#{beginHireDate} and]]>
         </when>
         <when test="beginHireDate==null and endHireDate!=null">
             <![CDATA[hireDate<=#{endHireDate} and]]>
         </when>
       </choose>
    </if>

    <!--工资区间-->
    <if test="beginSalary!=null or endSalary!=null">
        <!--开始工资和初始工资相同-->
       <choose>
          <when test="beginSalary==endSalary">
              salary =#{beginSalary} and
          </when>
          <when test="beginSalary!=endSalary">
              salary >=#{beginSalary} and salary <=#{endSalary} and
          </when>
          <when test="beginSalary!=null and endSalary==null">
              salary >=#{beginSalary} and
          </when>
          <when test="beginSalary==null and endSalary!=null">
              salary <=#{endSalary} and
          </when>
        </choose>
      </if>

      <!--入职时间年长-->
      <if test="hirePerTime!=null and hirePerTimeCon!=null">
         <choose>
            <when test="hirePerTimeCon=='gt'">
                TIMESTAMPDIFF(YEAR,hireDate,curdate())>#{hirePerTime} and
            </when>
            <when test="hirePerTimeCon=='ge'">
                TIMESTAMPDIFF(YEAR,hireDate,curdate())>=#{hirePerTime} and
            </when>
            <when test="hirePerTimeCon=='lt'">
                TIMESTAMPDIFF(YEAR,hireDate,curdate())<#{hirePerTime} and
            </when>
            <when test="hirePerTimeCon=='le'">
                TIMESTAMPDIFF(YEAR,hireDate,curdate())<=#{hirePerTime} and
            </when>
         </choose>
      </if>

      <!--根据部门编号查询员工-->
      <if test="deptnoList!=null">
         deptno in
         <foreach collection="list" item="deptno" open="(" separator="," close=")">
                    #{deptno}
         </foreach>
       </if>
  </trim>
</select>

EmpController.java:

    @RequestMapping(value="/getEmpByCondition", method= RequestMethod.POST)
    @ResponseBody
    public Map<String,Object> getEmpByCondition(@RequestParam(value="deptno",required = false) List deptno,
                                                @RequestParam(value="beginHireDate",required = false) String beginHireDate,
                                                @RequestParam(value="endHireDate",required = false) String endHireDate,
                                                @RequestParam(value="hirePerTime",required = false) Integer hirePerTime,
                                                @RequestParam(value="hirePerTimeCon",required = false) String hirePerTimeCon,
                                                @RequestParam(value="beginSalary",required = false) Double beginSalary,
                                                @RequestParam(value="endSalary",required = false) Double endSalary){
        Map<String,Object> conditions = new HashMap<>();
        Map<String,Object> returnMap = new HashMap<>();

        try {
            if(deptno!=null)
                conditions.put("deptno",deptno);
            if(beginHireDate!=null){
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                conditions.put("beginHireDate",format.parse(beginHireDate));
            }
            if(endHireDate!=null){
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                conditions.put("endHireDate",format.parse(endHireDate));
            }
            if(beginSalary!=null)
                conditions.put("beginSalary",beginSalary);
            if(endSalary!=null)
                conditions.put("endSalary",endSalary);
            //入职时间年长
            if(hirePerTime!=null&&hirePerTimeCon!=null){
                conditions.put("hirePerTime",hirePerTime);
                conditions.put("hirePerTimeCon",hirePerTimeCon);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }

        List<Employee> elist = employeeService.getEmpByCondition(conditions);
        if(elist.size()!=0){
            returnMap.put("message","findUserByDeptno successfully!");
            returnMap.put("employeeList",elist);
        }else{
            returnMap.put("message","can not find user");
        }
        return returnMap;
    }
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 1. Java基础部分 基础部分的顺序:基本语法,类相关的语法,内部类的语法,继承相关的语法,异常的语法,线程的语...
    子非鱼_t_阅读 31,779评论 18 399
  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 135,009评论 19 139
  • 一. Java基础部分.................................................
    wy_sure阅读 3,839评论 0 11
  • 亲爱的九(4)班的同学们: 你们已经进入初三毕业冲刺,但是,我从绝大部分同学身上真的没有看到毕业班的影子,我真...
    默默ks阅读 228评论 3 3
  • 写下来讲出去
    010ed0d5a362阅读 199评论 0 0