mybatis 动态sql

介绍

https://www.cnblogs.com/ysocean/p/7289529.html

类似于与JSTL 基于OGNL表达式

• if
• choose（when，otherwise）
• trim（where，set）
• foreach

一：if

在动态 SQL 中所做的最通用的事情是包含部分 where 字句的条件

用and or拼接test的条件

 

 

<select id="findAll" resultType="peo">
    select * from pojo where 1=1
    <!--name是map中的key，不可改 是对参数的判断  该boolean表达式可用and or 拼接-->
    <if test="name!=null" > 
    and name=#{name}
    </if>
</select>

 

test表达式的坑

 单个字符比较需要变成字符串，可以外面用单引号，里面用双引号  或者字符转字符串 ：

.toString()

 

test表达式写法

eg:

where t.f_flag =1 <if test=" osType != null and osType != '' "> and t.os_type=#{osType} </if>

 

• test里面的表达式需要是个判断表达式，结果是true或者false
• 判断条件为property != xx或 property == xx 可以使用逻辑运算符和小括号等
• 字符串判断 !="" and !=null
• 数字判断只用判断是否!=null，不和空字符（“”）作比较
• 没有if-else，只有if， if可以嵌套

二：where

 

自动判断，如果为空，不执行

否则插入一个where 并且自动剔除第一个返回值的and或者or

 

<select id="findAll2" resultType="peo">
    select * from pojo
    <where>
    <!--name是map中的key，不可改-->
    <if test="name!=null" > 
    and name=#{name}
     </if>
    </where>
</select>

 

 

 

三：foreach

对象中的集合属性

pojo

public class UserVo {
    //封装多个用户的id
    private List<Integer> ids;
 ...

 

mapper

select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
    select * from user
    <where>
        <!--
            collection:指定输入对象中的集合属性
            item:每次遍历生成的对象
            open:开始遍历时的拼接字符串
            close:结束时拼接的字符串
            separator:遍历对象之间需要拼接的字符串
            select * from user where 1=1 and (id=1 or id=2 or id=3)
          -->
          <!--指向pojo的集合属性名-->
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id=#{id}
        </foreach>
    </where>
</select>

test

public void testSelectUserByListId(){
    String statement = "com.ys.po.userMapper.selectUserByListId";
    UserVo uv = new UserVo();
    List<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    //设置集合属性
    uv.setIds(ids);
    //传入对象等待mapper调用其中的集合属性
    List<User> listUser = session.selectList(statement, uv);
    for(User u : listUser){
        System.out.println(u);
    }
    session.close();
}

 

 

处理Map

<!--SELECT * FROM pojo WHERE name IN（name1，name2...）-->
<select id="findAll4" resultType="peo" >
    select * from pojo
   <where>
    <if test="args!=null">
        name in
    <!--设置一次#{}预编译即可，foreach设置好属性，如遍历谁？以什么开头和结尾？以什么为分隔符？然后自动遍历取值并编写sql，-->
    <!--collection要遍历的东西的名称（map的指向list的键名） item遍历出来的变量的代号 open以什么开头 separator设置分隔符 close以什么结尾-->
     <foreach collection="args" item="name"  open="(" separator="," close=")">
         #{name}
     </foreach>
    </if>
   </where>
</select>

 

 

接口

public ArrayList<People> findAll4(Map<String,Object> map);//测试在某个集合中查询查询，使用动态sql标签<foreach>

 

 

 

 

Test

//        利用动态sql标签实现列表查询IN（value1，value2）
//        思路：将待查询的信息用List集合装起来，在Map中用k-v映射List集合，foreach遍历Map（该指向List的键名）
        Map<String,Object> ForMap=new HashMap<String, Object>();
        List<String> l=new ArrayList<String>();
        l.add("小三");
        l.add("帅");
        ForMap.put("args",l);
        ArrayList<People> PEO =  dynamicSqlToPojo.findAll4(ForMap);
        for (People p:PEO
             ) {
            System.out.print(p.getId()+"\t");
            System.out.print(p.getName()+"\t");
            System.out.println(p.getSex());
        }

Map<k,v>的v无关接口方法返回值类型 那个是resultType来确定的 v为Object 存放List Set都可以

接口

List<Student> selectByTeacherId(Map<String,Object> map) throws Exception;

 

mapper

<select id="selectByTeacherId" resultType="Student">
    SELECT
    s.studentId,
    s. studentName
    FROM
    student s
    LEFT JOIN id_student_teacher st
    ON s.studentId = st.studentId
    WHERE
    st.teacherId IN
    <!--参数为Set类型-->
    <foreach collection="isSet" open="(" close=")" separator="," item="index">
        #{index}
    </foreach>
</select>

test

@Test
public void selectStudentByTeacherId() {
    try {
//存入的是set集合
        Set<Integer> set = new HashSet<>();
        set.add(2);
        set.add(3);

        Map<String,Object> m=new HashMap<>();
        m.put("isSet",set);
//返回值类型是List  
        System.out.println(studentMapper.selectByTeacherId(m));
        sqlSession.commit();
    } catch (Exception e) {
        e.printStackTrace();
        sqlSession.rollback();
    } finally {
        MybatisUtils.closeSqlSession();
    }
}

 

处理List

处理list和array默认collection为"list"和"array "，可以自动根据属性名匹配 或者 使用@param指定名称，默认值失效，使用指定的名称

collection="list"

 

test

List<User> userList = userMapper.findByIdList(Arrays.asList(2, 3));

interface

List<Student> selectByTeacherId(List<Integer> lit) throws Exception;

 

mapper

<select id="selectByTeacherId" resultType="Student">
    SELECT
            s.studentId,
          s. studentName
    FROM
        student s
    LEFT JOIN id_student_teacher st
    ON s.studentId = st.studentId
    WHERE
        st.teacherId IN
       <!--集合就行 Set 或者 List 类型-->
    <foreach collection="list" open="(" close=")" separator="," item="index">
        #{index}
    </foreach>

</select>

 

 

处理Array

collection="array"

interface

List<Student> selectByTeacherId(Integer[] integers) throws Exception;

mapper

<select id="selectByTeacherId" resultType="Student">
    SELECT
    s.studentId,
    s. studentName
    FROM
    student s
    LEFT JOIN id_student_teacher st
    ON s.studentId = st.studentId
    WHERE
    st.teacherId IN
    <!--集合就写 Set 或者 List 类型-->
    <foreach collection="array" open="(" close=")" separator="," item="index">
        #{index}
    </foreach>

</select>

test

System.out.println(studentMapper.selectByTeacherId(new Integer[]{2,3}));

console:

==> Preparing: SELECT s.studentId, s. studentName FROM student s LEFT JOIN id_student_teacher st ON s.studentId = st.studentId WHERE st.teacherId IN ( ? , ? )

==> Parameters: 2(Integer), 3(Integer)

<== Total: 4

[Student(studentId=2, studentName=隔壁老张, teachers=null), Student(studentId=3, studentName=隔壁老陈, teachers=null), Student(studentId=4, studentName=隔壁某男子, teachers=null), Student(studentId=4, studentName=隔壁某男子, teachers=null)]

 

对比

 

foreach减少xml中代码，但是java中需要相应代码

对比普通多参的Mapper.xml写法

 

<select id="findAll3" resultType="peo" >
    select * from pojo where name in
    (#{name1},#{name2})
</select>

通过传入map对象，取其中键值对，亦可执行集合查询

 

 

四：set

一定会加上set，不自动判断全空情况，so避免全空的情况下进入这里

 

<update id="updateUserById" parameterType="com.ys.po.User">
    update user u
        <set>
            <if test="username != null and username != ''">
                u.username = #{username},
            </if>
            <if test="sex != null and sex != ''">
                u.sex = #{sex}
            </if>
        </set>
     
     where id=#{id}
</update>

 

这样写，如果第一个条件 username 为空，那么 sql 语句为：update user u set u.sex=? where id=?

　　　　　　如果第一个条件不为空，那么 sql 语句为：update user u set u.username = ? ,u.sex = ? where id=?

如果最后一个带逗号，将逗号忽略。

如果元素内包含的内容都为空，则会出现sql语法错误。所以在使用元素进行字段信息更新时，要确保传入的更新字段不能都为空。

 

 

五：choose

想选择很多情况下的一种

类似switch-case-default if-else if-else

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
      select * from user
      <where>
          <choose>
              <when test="id !='' and id != null">
                  id=#{id}
              </when>
              <when test="username !='' and username != null">
                  and username=#{username}
              </when>
              <otherwise>
                  and sex=#{sex}
              </otherwise>
          </choose>
      </where>
  </select>

 

哪个满足执行哪个，从上至下

都不满足就执行otherwise

 

六：trim

trim标记是一个格式化的标记，可以完成set或者是where标记的功能

1》prefix="":前缀：trim标签体中是整个字符串拼串 后的结果，prefix给拼串后的整个字符串加一个前缀

2》prefixOverrides="":前缀覆盖： 去掉整个字符串前面多余的字符

3》suffix="":后缀，suffix给拼串后的整个字符串加一个后缀

4》suffixOverrides=""后缀覆盖：去掉整个字符串后面多余的字符

取代where

　select * from user 

　　<trim prefix="WHERE" prefixoverride="AND |OR">

　　　　<if test="name != null and name.length()>0"> AND name=#{name}</if>

　　　　<if test="gender != null and gender.length()>0"> AND gender=#{gender}</if>

　　</trim>

假如说name和gender的值都不为null的话打印的SQL为：select * from user where    name = 'xx' and gender = 'xx'

假如name和gender都为空，打印的是select * from user

👍也就是说，和where标签一样，标签里面如果为空，则不添加前缀和后缀

在标记的地方是不存在第一个and的

prefix：前缀　　　　　　

　　prefixoverride：去掉第一个and或者是or

 

取代set

update user

　　<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">

　　　　<if test="name != null and name.length()>0"> name=#{name} , </if>

　　　　<if test="gender != null and gender.length()>0"> gender=#{gender} ,  </if>

　　</trim>

　suffix：后缀　　

　　suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

 

 

使用动态group by：

select staffId,companyId,area
from user
            <trim prefix="group by " suffixOverrides=",">
                <if test="param.staffId != null">
                    t.staffId ,
                </if>
                <if test="param.companyId != null">
                    t.companyId ,
                </if>
                <if test="param.area">
                    area ,
                </if>;

 

若staffId，companyId，area 都不为空，最后查询出来的语句为

select staffId,companyId,area from user group by staffId，companyId，area