Mybatis动态sql

最新推荐文章于 2024-09-09 20:21:34 发布

原创最新推荐文章于 2024-09-09 20:21:34 发布 · 918 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#java #数据库 #mysql

MyBatis框架的动态sql技术，是一种根据特定条件动态拼装sql语句的功能，它存在的意义是为了解决拼接sql语句字符串的痛点问题

9.1、if

if标签可以通过test属性的表达式进行判断，如果表达的结果为true，则标签内的内容会执行；反之不会被执行

/**
* 根据条件查询员工信息
* @param emp
* @return
*/
List<Emp> getEmpByCondition(Emp emp);


<select id="getEmpByCondition" resultType="emp">
    select * from emp where 1=1
    <if test="empName != null and empName !=''">
      and emp_name = #{empName}
    </if>

    <if test="age != null and age !=''">
       and age = #{age}
    </if>

    <if test="sex != null and sex !=''">
       and sex = #{sex}
    </if>
</select>

@Test
public void testGetEmpByCondition(){
    Emp emp = new Emp(null,null,22,null);
    List<Emp> emps = empMapper.getEmpByCondition(emp);
    emps.forEach(System.out::println);
}

9.2、where

where和if一般是结合使用：

⑴ 如果where标签中的if条件满足，则where标签会自动添加where关键字

⑵会自动将条件最前方多余的and去掉，注意：where标签不能去掉条件最后多余的and

⑶如果where标签中的if条件都不满足，则where标签没有任何功能，则不添加where关键字


<select id="getEmpByCondition" resultType="emp">
    select * from emp
    <where>
        <if test="empName != null and empName !=''">
            emp_name = #{empName}
        </if>

        <if test="age != null and age !=''">
            and age = #{age} and
        </if>

        <if test="sex != null and sex !=''">
            and sex = #{sex}
        </if>
    </where>
</select>

9.3 trim

trim用于去掉或者添加标签中的内容



<select id="getEmpByCondition" resultType="emp">
    select * from emp
    <trim prefix="where" prefixOverrides="and">
        <if test="empName != null and empName !=''">
             emp_name = #{empName}
        </if>

        <if test="age != null and age !=''">
            and age = #{age}
        </if>

        <if test="sex != null and sex !=''">
            and   sex = #{sex}
        </if>
    </trim>
</select>

9.4、choose、when、otherwise

choose、when、otherwise相当于if ...else if...else

switch case default


<select id="getEmpByChoose" resultType="emp">
    select * from emp
    <where>
        <choose>
            <when test="empName != null and empName !=''">
                emp_name = #{empName}
            </when>
            <when test="age != null and age !=''">
                and age = #{age}
            </when>
            <when test="sex != null and sex !=''">
                and sex = #{sex}
            </when>
        </choose>
    </where>
</select>

9.5、foreach

循环：批量，有规律

⑴批量的添加

/**
* 批量添加员工信息
* @param emps
*/
void insertMoreEmp(@Param("emps") List<Emp> emps);



<insert id="insertMoreEmp">
    insert into emp values
        <foreach collection="emps" item="emp" separator="," >
             (null,#{emp.empName},#{emp.age},#{emp.sex})
        </foreach>
</insert>

@Test
public void testInsertMoreEmp(){
    Emp emp1 = new Emp(null,"小明",22,"男");
    Emp emp2 = new Emp(null,"小红",22,"男");
    Emp emp3 = new Emp(null,"小马",22,"男");
    List<Emp> emps = Arrays.asList(emp1,emp2,emp3);
    empMapper.insertMoreEmp(emps);
}

⑵批量删除

第一种

/**
* 批量删除员工信息
* @param empIds
* @return
*/
Integer deleteMoreEmp(@Param("empIds") Integer[] empIds);


<delete id="deleteMoreEmp">
    delete from emp where emp_id in(
        <foreach collection="empIds" item="empId" separator=",">
            #{empId}
        </foreach>
        )
</delete>

@Test
public void testDeletMoreEmp(){
    Integer[] empIds = new Integer[]{10,11,12};
    Integer integer = empMapper.deleteMoreEmp(empIds);
    if(integer>0){
        System.out.println("删除成功");
    }else {
        System.out.println("删除失败");
    }
}

第二种


<delete id="deleteMoreEmp">
    delete from emp where emp_id in
    <foreach collection="empIds" item="empId" separator="," open="(" close=")">
        #{empId}
    </foreach>
</delete>

第三种


<delete id="deleteMoreEmp">
    delete from emp where
    <foreach collection="empIds" item="empId" separator="or" >
        emp_id = #{empId}
    </foreach>
</delete>