八、动态SQL

一、动态SQL

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

1.if标签

  • If标签可通过test属性(传递过来的数据)的表达式进行判断。 如果为true标签执行。
  • 在where 后面添加1=1横成立条件

  如果不加这个条件,此刻empName为空的时候,SQL拼接出错select * from t_emp where and age = ? and sex = ? and email = ? 因为where和and连用 

①DynamicSQLMapper接口

/**
 * 多条件查询
 */
List<Emp> getEmp(Emp emp);

②DynamicSQLMapper映射文件

<?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.atguigu.mybatis.mapper.DynamicSQLMapper">
    <select id="getEmp" resultType="Emp">
        select * from t_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>
        <if test="email != null and email!=''">
            and email = #{email}
        </if>
    </select>
</mapper>

③测试

@Test
public void t1() {
   SqlSession sqlSession = SqlSessionUtils.getSqlSession();
   DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
   Emp emp = new Emp();
   emp.setEmpName("wang");
   List<Emp> emp1 = mapper.getEmp(emp);
   System.out.println(emp1);
}

2.where标签

  • where和if结合
  • 如果所有if标签都不满足,不会在SQL语句拼接where关键字

    如果有if标签满足。会自动添加where关键字,并把条件前面多余的and/or去掉

  • <?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.atguigu.mybatis.mapper.DynamicSQLMapper">
        <select id="getEmp" resultType="Emp">
            select * from t_emp
            <where>
                <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>
                <if test="email != null and email!=''">
                    and email = #{email}
                </if>
            </where>
    
        </select>
    </mapper>
    

    3.trim

  • trim用于去掉或添加标签中的内容
  • 常用属性
  • prefix:trim标签内容的前面添加某些内容

    suffix:在trim标签内容后面添加某些内容

    prefixOverrides:在trim标签内容前面去掉某些内容

    suffixOverrides:在trim标签内容后面去掉某些内容

  • 若trim中的标签都不满足条件,则trim标签没有任何效果,也就是只剩下select * from t_emp

<trim prefix="where" suffixOverrides="and|or"> 在trim前面加上where,去掉最后面的and

<?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.atguigu.mybatis.mapper.DynamicSQLMapper">
    <select id="getEmp" resultType="Emp">
        select * from t_emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="empName != null and empName!=''">
                 emp_name = #{empName} and
            </if>
            <if test="age != null and age!=''">
                 age = #{age} and
            </if>
            <if test="sex != null and sex!=''">
                 sex = #{sex} and
            </if>
            <if test="email != null and email!=''">
                 email = #{email} and
            </if>
        </trim>

    </select>
</mapper>

4.choose、when、otherwise

  • choose、when、otherwise相当于if…else if…else
  • when至少一个,otherwise至多一个
<?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.atguigu.mybatis.mapper.DynamicSQLMapper">
    <select id="getEmp" resultType="Emp">
        select * from t_emp
        <where>
            <choose>
                <when test="empName != null and empName!=''">
                    emp_name = #{empName}
                </when>
                <when test="age != null and age!=''">
                    age = #{age}
                </when>
                <when test="sex != null and sex!=''">
                    sex = #{sex}
                </when>
                <when test="email != null and email!=''">
                    email = #{email}
                </when>
                <otherwise>
                    did = 1
                </otherwise>
            </choose>

        </where>

    </select>
</mapper>

只要when有一个条件成立,when后面的都不执行。When条件都不成立,执行otherwise

5.foreach

  • 属性
  • collection:设置要循环的数组或集合

    item:表示集合或数组的每一个数据

    separator:设置循环之间的分隔符,分隔符前后默认有一个空格

    open:设置foreach标签内容开始符

    close:设置foreach标签内容的结束符

  1. 批量删除
  • 通过数组实现批量删除
  • 除了实体类和Map集合之外,其他参数方式手动加上@Param("xxx")

在DynamicSQLMapper接口里

/**
 * 通过数组实现批量删除
 * 除了实体类对象和Map集合,参数其他方式手动加上@Param("eids")
 */
int deleteByArray(@Param("eids") Integer[] eids);

在DynamicSQLMapper映射文件里

<?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.atguigu.mybatis.mapper.DynamicSQLMapper">

    <delete id="deleteByArray">
        delete from t_emp where eid in
        <foreach collection="eids" item="eid" separator="," open="(" close=")">
            #{eid}
        </foreach>
    </delete>
</mapper>

测试

@Test
public void t1() {
   SqlSession sqlSession = SqlSessionUtils.getSqlSession();
   DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
   int deleteCount = mapper.deleteByArray(new Integer[]{5, 6});
   System.out.println("删除成功:"+deleteCount+"条");
}

2.批量删除 

①在DynamicSQLMapper接口

/**
 * 通过List实现批量添加
 */
int insertByList(@Param("emps") List<Emp> emps);

②在DynamicSQLMapper映射文件

实体类对象可以通过#{属性名}得到属性值,如果传过来的是List,#{item.属性名}

<?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.atguigu.mybatis.mapper.DynamicSQLMapper">
    <insert id="insertByList">
        insert into t_emp values
        <foreach collection="emps" item="emp" separator=",">
            (null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
        </foreach>
    </insert>

</mapper>

 ③测试

   @Test
   public void t1() {
      SqlSession sqlSession = SqlSessionUtils.getSqlSession();
      DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
      Emp emp1 = new Emp(null,"a",1,"男","123@321.com");
      Emp emp2 = new Emp(null,"b",1,"男","123@321.com");
      Emp emp3 = new Emp(null,"c",1,"男","123@321.com");
      List<Emp> emps = Arrays.asList(emp1,emp2,emp3);
      int i = mapper.insertByList(emps);
      System.out.println("成功添加:"+i+"条");
   }

二、SQL片段 

      <sql id="empColumns">eid,emp_name,age,sex,email</sql>

  • sql片段,可以记录一段公共的sql片段,在使用的地方通过include标签进行引入
  • 声明sql片段:<sql>标签
  • 引用sql片段:<include>标签  <include refid="empColumns"></include>
<?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.atguigu.mybatis.mapper.DynamicSQLMapper">
    <sql id="empColumns">eid,emp_name,age,sex,email</sql>
    <select id="getEmp" resultType="Emp">
        select <include refid="empColumns"></include>from t_emp
        <where>
            <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>
            <if test="email != null and email!=''">
                and email = #{email}
            </if>
        </where>

    </select>
</mapper>

 

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值