Maven项目集成Mybatis 动态SQL

作者: 乘风破浪的姐姐 | 来源:发表于2021-12-21 13:45 被阅读0次

本文主要介绍 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成灵活的SQL语句，从而提高 SQL 语句的准确性以及开发人员的开发效率。

以表t_dy_member_info 为例：

image.png

pojo类：
package com.sc.pojo;

import lombok.Data;
import java.util.List;

@Data
public class TdyMemberInfo {
    private Long memberId;
    private String memberNo;
    private String mobile;
    private String openId;
    private String shopId;
    private List<Long> ids;

    public void setIds(List<Long> ids) {
        this.ids = ids;
    }
}

接口：

package com.sc.dao;

import com.sc.pojo.TdyMemberInfo;
import java.util.List;

public interface TdyMemberInfoMapper {
    TdyMemberInfo selectMemberByIf(TdyMemberInfo tdyMemberInfo);
    TdyMemberInfo selectMemberByIfWhere(TdyMemberInfo tdyMemberInfo);
    int updateMemberByIfSet(TdyMemberInfo tdyMemberInfo);
    TdyMemberInfo selectMemberByChoose(TdyMemberInfo tdyMemberInfo);
     List<TdyMemberInfo> selectMemberByForeach1(TdyMemberInfo tdyMemberInfo);
     List<TdyMemberInfo> selectMemberByForeach2(TdyMemberInfo tdyMemberInfo);
 
}

Mapper.xml文件：

<?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.sc.dao.TdyMemberInfoMapper">
    <resultMap id="memberInfo" type="TdyMemberInfo">
        <result property="memberId"   column="member_id"/>
        <result property="memberNo" column="member_no"/>
        <result property="mobile" column="mobile"/>
        <result property="openId" column="open_id"/>
        <result property="shopId" column="shop_id"/>
    </resultMap>
</mapper>

1、动态SQL--- if 语句

1）TdyMemberInfoMapper 接口中定义方法：

 TdyMemberInfo selectMemberByIf(TdyMemberInfo tdyMemberInfo);

2）TdyMemberInfoMapper.xml中SQL语句块：

   <!--动态IF语句-->
    <select id="selectMemberByIf" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
        select * from t_dy_member_info where
           <if test="memberNo !=null"><!--这里memberNo使用的是pojo中的属性-->
               member_no=#{memberNo}
           </if>
            <if test="mobile !=null">
                and  mobile = #{mobile}
             </if>
    </select>

对应：

select * from t_dy_member_info where member_no=#{memberNo} and mobile =#{mobile}

注意：
if标签中的test属性值，使用的是pojo中对应的属性
上述SQL中，如果 member_no为空时，则该语句就是：

 select * from t_dy_member_info where and mobile = ?

因为where后面多了and所以就会报错。
3）测试类

 @Test
    public void selectMemberByIf(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setMemberNo("202111231463");
        tdyMemberInfo.setMobile("18727460511");
        TdyMemberInfo  member = mapper.selectMemberByIf(tdyMemberInfo);
        System.out.println(member);
        sqlSession.close();
    }

2、动态SQL--- if+where 语句

1）TdyMemberInfoMapper 接口中定义方法：

 TdyMemberInfo selectMemberByIfWhere(TdyMemberInfo tdyMemberInfo);

2）TdyMemberInfoMapper.xml中SQL语句块：

  <!--动态IF+where 语句-->
    <select id="selectMemberByIfWhere" resultMap="memberInfo" resultType="TdyMemberInfo" parameterType="TdyMemberInfo">
        select * from t_dy_member_info
        <where>
              <if test="memberNo !=null">
               member_no=#{memberNo}
           </if>
            <if test="mobile !=null">
                and  mobile = #{mobile}
             </if>
        </where>
    </select>

上述SQL中，如果 member_no为空时，该语句正常， where 标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。
3）测试类

@Test
    public void selectMemberByIfWhere(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        tdyMemberInfo.setMemberNo("202111231466");
        tdyMemberInfo.setMobile("18727460505");
        TdyMemberInfo  member = mapper.selectMemberByIfWhere(tdyMemberInfo);
        System.out.println(member);
        sqlSession.close();
    }

3、动态SQL--- if+set 语句

1）TdyMemberInfoMapper 接口中定义方法：

 int updateMemberByIfSet(TdyMemberInfo tdyMemberInfo);

2）TdyMemberInfoMapper.xml中SQL语句块：

 <!--动态set+if 语句-->
    <update id="updateMemberByIfSet" parameterType="TdyMemberInfo" >
        update t_dy_member_info m
        <set>
            <if test="memberNo != null  and memberNo !='' ">
                m.member_no = #{memberNo},
            </if>
            <if test="mobile !=null and mobile !='' ">
                m.mobile = #{mobile}
            </if>
        </set>
       where m.member_id = #{memberId}
   </update>

如果第一个条件 memberNo 为空，set会自动将memberNo后的逗号去掉
那么 sql 语句为：

update t_dy_member_info u set u.mobile =? where member_id =?

如果第一个条件memberNo 不为空，
那么 sql 语句为：

update t_dy_member_info u set u.memberNo = ? ,u.mobile = ? where member_id =?

3）测试类

 @Test
    public void updateMemberByIfSet(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
//        tdyMemberInfo.setMemberNo("2021112311112");
        tdyMemberInfo.setMobile("18727460505");
        tdyMemberInfo.setMemberId(new Long(5));

        int  code = mapper.updateMemberByIfSet(tdyMemberInfo);
        System.out.println(code);
        System.out.println(tdyMemberInfo);
        sqlSession.close();
    }

4、动态SQL---choose(when,otherwise) 语句

1）TdyMemberInfoMapper 接口中定义方法：

TdyMemberInfo selectMemberByChoose(TdyMemberInfo tdyMemberInfo);

2）TdyMemberInfoMapper.xml中SQL语句块：

 <!--动态where+ choose+when/otherwise 语句-->
    <select id="selectMemberByChoose" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
        select * from t_dy_member_info m
        <where>
            <choose>
                <when test="memberNo != null  and memberNo !='' ">
                    m.member_no = #{memberNo}
                </when>
                <when test="mobile !=null and mobile !='' ">
                    and   m.mobile = #{mobile}
                </when>
                <otherwise>
                    and   m.member_id = #{memberId}
                </otherwise>
            </choose>
        </where>
    </select>

上述SQL中有三个条件，memberNo,mobile,member_id，只能选择一个作为查询条件当member_no为空时，mobile/member_id不为空则where会自动去掉 and。
如果 memberNo不为空，那么查询语句为：

select * from t_dy_member_info where  memberNo=?

如果 memberNo为空，那么看mobile 是否为空，如果不为空，那么语句为

select * from t_dy_member_info where  mobile =?;

如果 mobile 为空，那么查询语句为

select * from t_dy_member_info where member_id =?

3）测试类

 @Test
    public void selectMemberByChoose(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
//        tdyMemberInfo.setMemberNo("202111231463039461702434821");
//        tdyMemberInfo.setMobile("18727460505");
        tdyMemberInfo.setMemberId(new Long(5));
        TdyMemberInfo  member = mapper.selectMemberByChoose(tdyMemberInfo);
        System.out.println(member);
        sqlSession.close();
    }

5、动态SQL---SQL 片段

有时候某个 sql 语句用到的特别多，为了增加代码的重用性，简化代码，就可以将这些代码抽取出来，在需要用到的地方直接调用。
比如：上述SQL中经常根据member_no 和mobile来进行联合查询，那么就把这个代码抽取出来，如下：

<sql id="selectSql">
        <if test="memberNo !=null">
            member_no=#{memberNo}
        </if>
        <if test="mobile !=null">
            and  mobile = #{mobile}
        </if>
    </sql>

引用 sql 片段

 <!--动态IF语句-->
    <select id="selectMemberByIf" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
        select * from t_dy_member_info where
        <include refid="selectSql"/>
    </select>

    <!--动态IF+where 语句-->
    <select id="selectMemberByIfWhere" resultMap="memberInfo" resultType="TdyMemberInfo" parameterType="TdyMemberInfo">
        select * from t_dy_member_info
        <where>
            <include refid="selectSql"/>
        </where>
    </select>

注意：在 sql 片段中最好不要包括 where

6、动态SQL---foreach 语句（方式1）

1）TdyMemberInfoMapper 接口中定义方法：

 List<TdyMemberInfo> selectMemberByForeach1(TdyMemberInfo tdyMemberInfo);

2）TdyMemberInfoMapper.xml中SQL语句块：

    <select id="selectMemberByForeach1" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
        select * from t_dy_member_info m
        <where>
            <foreach collection="ids" item="id" separator="or" open="and (" close=")">
               member_id = #{id}
            </foreach>
        </where>
    </select>

上述foreach语句用来改写

 select * from t_dy_member_info where member_id=47 or member_id=48 or member_id=49

3）测试类

  @Test
    public void selectMemberByForeach1(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        List<Long> ids = new ArrayList<>();
        ids.add(new Long(47));
        ids.add(new Long(48));
        ids.add(new Long(49));

        tdyMemberInfo.setIds(ids);
        List<TdyMemberInfo>  memberList = mapper.selectMemberByForeach1(tdyMemberInfo);
        for(TdyMemberInfo memberInfo:memberList){
            System.out.println(memberInfo);
        }
        sqlSession.close();
    }

7、动态SQL---foreach 语句（方式2）

1）TdyMemberInfoMapper 接口中定义方法：

 List<TdyMemberInfo> selectMemberByForeach2(TdyMemberInfo tdyMemberInfo);

2）TdyMemberInfoMapper.xml中SQL语句块：

    <select id="selectMemberByForeach2" resultType="TdyMemberInfo" parameterType="TdyMemberInfo" resultMap="memberInfo">
        select * from t_dy_member_info m
        <where>
            <foreach collection="ids" item="id" separator="," open="and member_id in (" close=")">
                #{id}
            </foreach>
        </where>
    </select>

collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串

上述foreach语句用来改写

select * from t_dy_member_info m WHERE member_id in ( ? , ? , ? )

3）测试类

  @Test
    public void selectMemberByForeach2(){
        SqlSession sqlSession =  MyBatisUtils.getSqlSession();
        TdyMemberInfoMapper mapper = sqlSession.getMapper(TdyMemberInfoMapper.class);
        TdyMemberInfo tdyMemberInfo = new TdyMemberInfo();
        List<Long> ids = new ArrayList<>();
        ids.add(new Long(47));
        ids.add(new Long(48));
        ids.add(new Long(49));

        tdyMemberInfo.setIds(ids);
        List<TdyMemberInfo>  memberList = mapper.selectMemberByForeach2(tdyMemberInfo);
        for(TdyMemberInfo memberInfo:memberList){
            System.out.println(memberInfo);
        }
        sqlSession.close();
    }

Maven项目集成Mybatis 动态SQL

1、动态SQL--- if 语句

2、动态SQL--- if+where 语句

3、动态SQL--- if+set 语句

4、动态SQL---choose(when,otherwise) 语句

5、动态SQL---SQL 片段

6、动态SQL---foreach 语句（方式1）

7、动态SQL---foreach 语句（方式2）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读