复杂mapper之foreach、if、choose、when动态sql

foreach五大标签：

  1. item 　　   表示集合中每一个元素进行迭代时的别名，随便起的变量名；
  2. index          也就是索引，用于表示在迭代过程中，每次迭代到的位置；
  3. open　　    表示该语句以什么开始，常用“(”；
  4. separator    表示在每次进行迭代之间以什么符号作为分隔符，常用“,”；
  5. close　　    表示以什么结束，常用“)”；
  6. collection    该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，默认使用list

对于collection如果dao层接口没有用@Param注解时，默认使用list作为集合。如果是数组则为araary！

open、close、separate标签，不在标签中明确就得在mapper循环前后加上()，以及英文逗号

集合示例

<select id="findTotal" parameterType="java.util.ArrayList"  resultType="long">
        <!--select * from tab_route where cid = ? and rname like ? -->
      select count(*) from tab_route where 1=1
      <foreach collection="list" item="id"  index="index"  >
            <if test="index==0" >
                <if test="id!=0">
                    and cid= #{id}
                </if>
            </if>
      </foreach>
        <foreach collection="list" item="id"  index="index"  >
            <if test="index==1" >
                <if test="id!=null and id.length() > 0">
                    and rname like "%"#{id}"%"
                </if>
            </if>
        </foreach>
  </select>

数组示例

 <select id="query"  parameterType="Integer[]" resultType="Route">
        <!--select * from t_route where cid = ? and rname like ?  limit ? , ? -->
        select * from t_route where 1=1
        <foreach collection="array" item="id"  index="index"  >
            <if test="index==0" >
                <if test="id!=0">and cid =#{id}</if>
            </if>
        </foreach>
        limit
        <foreach collection="array" item="id"  index="index"  >
            <if test="index==1" >#{id}</if>
        </foreach>
        ,
        <foreach collection="array" item="id"  index="index"  >
            <if test="index==2" >#{id}</if>
        </foreach>
    </select>

choose、when、otherwise动态sql

<select id="UserActiveSearch_choose" parameterType="User" resultMap="UserVO">
		SELECT 
			*
		FROM t_user 
		<where>
			<choose>
				<when test="username != null and username != ''">
					username = #{username}
				</when>
				<when test="email != null and email != ''">
					AND email = #{email}
				</when>
				<otherwise>
					AND id = #{id}
				</otherwise>
			</choose>
		</where>
	</select>

trim 

1.if-where

因为采用了Mapper代理开发，我们可以通过写xml的形式来编写我们的SQL，动态SQL的特性也就在这一举动中所蕴育，在原有的Mapper文件里我们进行如下改造，让平平无奇的SQL焕然一新：

<select id="selByCondition" resultMap="rm">
    select *
    from mybatis  
    <where>
    <if test="status !=null">
       and STATUS=#{STATUS}
    </if>
    <if test="companyName !=null and companyName !=''">
    and company_name like #{companyName}
    </if>
    <if test="bracdName !=null and bracdName !=''">
    and bracd_name like #{bracdName}
    </if>
    </where>
</select>

“<where>标签可以自动帮我们去掉and”，这样，不管查询的条件怎么变，我跟着这个逻辑流程走就不会出现SQL语法毛病而导致查询不出来的毛病啦，因为null的情况已经被if所过滤掉了，真是太哇塞了！

2.choose-when-ortherwise

对于从多个条件中选择一个的单条件查询的场景，利用分支嵌套就可以实现动态选择单条件：

在MyBatis的Mapper代理中，<choose>相当于switch，<when>相当于case

<select id="selByCondition2" resultMap="rm">
    select *
    from mybatis where
    <choose>
        <when test="status !=null">
            STATUS=#{STATUS}
        </when>
        <when test="companyName !=null and companyName !=''">
            company_name like #{companyName}
        </when>
        <when test="bracdName !=null and bracdName !=''">
            bracd_name like #{bracdName}
        </when>
        <otherwise>1=1</otherwise>
    </choose>
</select>

与多条件查询不同的是，SQL语句中只会有一个分支生效

当用户一个条件都不选时，可以在<otherwise>中写上1=1让语法成立，反之，若选择了条件则会返回正常结果

3.foreach

对于批量删除的场景，传统的方法是通过in关键字结合占位符来确定，就像这样

where id in (?,?,?)

但对于动态的场景，批量的数量永远是不确定的，这就导致还需要去改SQL里的占位符数量啊，又是一件麻烦事

PS:MyBatis会将数组参数封装成一个Map集合，默认情况（K-V）array=数组

下面使用了@Param注解改变了map集合中默认的key

于是MyBatis中的<foreach>解决了这一麻烦：

本质是通过遍历的形式，批量删除的数据是由id数组或者集合来决定，collection属性决定了要遍历哪个数组/集合，item属性则来存放选出的元素，并把它放在占位符里，separator属性表示分隔符

<delete id="deleteById">
    delete frpm mybatis where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">
        #{id}
    </foreach>;
</delete>

有人会问为啥这里只有一个#{id}，我的属性字段不止这一个呀？此id非彼id他是一个数组/集合

三、多表操作

多表之间的关系有一对一，一对多，多对一，多对多，每一种都有建表的原则,以用户-订单模型为例

利用传统的方法进行多表查询无非是通过id来连接表然后封装返回结果，MyBatis中也是如此，我们在Mapper文件中写好表字段之间的映射关系，定义好类型即可，只不过这一过程有点复杂，但一次配好之后即可极大减少硬编码问题，提高效率

1.一对一

一个用户有一张订单

首先还是那套路，建好实体类，写好接口方法，配置Mapper文件，而多表操作的麻烦点就在于配置文件，这里通过例子细说一下

1.先把表写好

CREATE TABLE orders (
id INT PRIMARY KEY ,
ordertime VARCHAR(20) NOT NULL DEFAULT '',
total DOUBLE,
uid INT);
INSERT INTO orders VALUES(1,2020,2000,1);
INSERT INTO orders VALUES(2,2021,3000,2);
INSERT INTO orders VALUES(3,2022,4000,3);
CREATE TABLE USER (
id INT PRIMARY KEY ,
username VARCHAR(50) NOT NULL DEFAULT '',
passwords VARCHAR(50) NOT NULL DEFAULT '');
INSERT INTO USER VALUES(1,'lyy',333);
INSERT INTO USER VALUES(2,'myy',444);
INSERT INTO USER VALUES(3,'xyy',555);

2.写Mapper配置文件

在写实体类时，要把一个实体写到另一个实体的属性里面，这样才体现关联性，就比如“订单是所用户拥有的”，正因为这种关系我们才会在订单实体类里面写上private User user;这一属性，这样根据id连接的两个实体才能完美对接！

就像这样：

通过<association>把两张表对应的实体类连接起来,只不过是主键ID要用单独的标签

  • property: 当前实体(order)中的属性名称(private User user)

  • javaType: 当前实体(order)中的属性的类型(User)

这两个user有着本质上的却别，就好像前者是在一个人的名字，后者正是被叫的那个人，MyBatis好像就利用了这一特性，通过标签的形式连接了两个实体

<select id="findAll" resultMap="orderMap">
   SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
</select>

SQL环节和原来没什么区别，同样也是通过resultMap把字段和属性映射封装

2.一对多

一个用户有多张订单

首先，在原有的User实体中得加上一个表示“用户有哪些订单的属性”private List<Order> orderList;，目的是为了把订单的信息封装到用户的这个属性里，在Mapper文件中体现：

<collection property="orderList" ofType="order">
    <!--封装order的数据-->
    <id column="oid" property="id"></id>
    <result column="ordertime" property="ordertime"></result>
    <result column="total" property="total"></result>
</collection>

  • property:集合名称，User实体中的orderlist属性

  • ofType：当前集合中的数据类型，就是order实体

然后就是写一对多的SQL：

<select id="findAll" resultMap="userMap">
   SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>

总结来看，一对多相比于一对一就是在那个“一”中增添了封装“多”的属性而已，然后稍微调整一下SQL

3.多对多

多用户多角色

多对多的建表原则是引入一张中间表，用于维护外键，就是一张表通过中间表找到另一张表

和一对多的模型类似，先在User实体类中增添一个“用户具备哪些角色”的属性private List<Role> roleList;其次配置Mapper文件：

<collection property="roleList" ofType="role">
   <id column="roleId" property="id"></id>
   <result column="roleName" property="roleName"></result>
   <result column="roleDesc" property="roleDesc"></result>
</collection>

多表的连接是靠的中间表，这点在Mapper文件中通过映射实现，具体是把两张外表的id（userId和roleId）在id标签中配置成同一个属性，就像这样：

<id column="userId" property="id"></id>
<id column="roleId" property="id"></id>

SQL环节就得用多对多的套路了

<select id="findUserAndRoleAll" resultMap="userRoleMap">
    SELECT * FROM USER u,user-role ur,role r WHERE u.id=ur.userId AND ur.roleId=r.id
</select>

回想进行多表操作时MyBatis为我们带来了什么？他确实减少了很多硬编码，我每一次新的SQL只需要在标签里改几个属性就可以，只要理清字段与属性的映射关系，在MyBatis中进行多表操作就是一个“对号入座”

四、注解开发

针对于简单的CRUD注解开发可以极大地提升效率，顾名思义就是把SQL写在注解里

查询（@Select）：

添加（@Insert）：

修改（@Update）:

删除（@Delete） ： int [] ids  原始数组

Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

第六届·2024 MindSpore 量子计算黑客松热身赛赛题解读

avatar 华为云开发者联盟
cover

连升三级！openGauss单机版从2.1.0经停3.0.0升级至5.0.0

avatar 华为云开发者联盟
cover

带你熟悉CCE集群增强型CPU管理策略enhanced-static

avatar 华为云开发者联盟