MyBatis-动态SQL

共计 13180 个字符，预计需要花费 33 分钟才能阅读完成。

1、MyBatis 动态 sql

1.1、什么是动态 sql

Mybatis 的映射文件中，前面我们的 SQL 都是比较简单的，有些时候业务逻辑复杂时，我们的 SQL 是动态变化的。

在实际应用开发过程中，我们往往需要写复杂的 SQL 语句，需要拼接，而拼接 SQL 语句又稍微不注意，由于引号，空格等缺失可能都会导致错误。

Mybatis 提供了动态 SQL，也就是可以根据用户提供的参数，动态决定查询语句依赖的查询条件或 SQL 语句的内容。

1.2、开发前的准备

新建项目：mb003

完成基础配置

2、if 标签 - 上

if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中，通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

2.1、需求分析

多条件查询：根据用户名和住址查询用户信息

2.2、案例实现

2.2.1、编写 IUserDao.java 文件

package com.tianyi.dao;
 
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
 
import java.util.List;
 
public interface IUserDao {List<User> findByUsers(User user);
}

2.2.2、编写 IUserDao.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.tianyi.dao.IUserDao">
    <select id="findByUsers" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.User">
        select * from user where 1=1
        <if test="username!=null and username !=''">
            and username like #{username}
        </if>
        <if test="address!=null and address !=''">
            and address like #{address}
        </if>
    </select>
</mapper>

3、if 标签 - 下

3.1、案例实现

3.1.1、where 1= 1 作用

A、where 的条件为永真
select * from table1 where 1=1
与
select * from table1
完全没有区别，甚至还有其他许多写法，1<>2，‘a’=’a’,‘a’<>’b’，
其目的就只有一个，where 的条件为永真，得到的结果就是未加约束条件的。

在 SQL 注入时会用到这个，例如
select * from table1 where name=’lala’
给强行加上
select * from table1 where name=’lala’or 1=1
这就又变成了无约束的查询了。

B、在后台写不定数量的查询条件下，便于规范语句，增加灵活性
例如一个查询可能有 name,age,class 约束，也可能没有，那该如何处理呢？
以 java 为例：
String sql =“select * from table a”;
前台传来的过滤条件中 name, age, class 不一定都存在

B1、在不使用 where 1= 1 的情况下

if(params.containsKey("name")){String key = params.get("name").toString();
    sql+="where a.name='"+key +"'";
}
if(params.containsKey("age")){String key = params.get("age").toString();
    sql+="where a.age='"+key +"'";
}
if(params.containsKey("class")){String key = params.get("class").toString();
    sql+="where a.class ='"+key +"'";
}

这样同时存在两个属性及以上就会发生冲突

B2、当时用 where 1=1 的时候

String sql =“select * from table a where 1=1”;
 
if(params.containsKey("name")){String key = params.get("name").toString();
    sql+="and a.name='"+key +"'";
}
if(params.containsKey("age")){String key = params.get("age").toString();
    sql+="and a.age='"+key +"'";
}
if(params.containsKey("class")){String key = params.get("class").toString();
    sql+="and a.class ='"+key +"'";
}

这样同时存在两个属性及以上时就不会发生冲突

3.1.2、编写 MbTest.java 文件

package com.tianyi.test;
 
import com.tianyi.dao.IUserDao;
        import com.tianyi.javabean.QueryUser;
        import com.tianyi.javabean.User;
        import org.apache.ibatis.io.Resources;
        import org.apache.ibatis.session.SqlSession;
        import org.apache.ibatis.session.SqlSessionFactory;
        import org.apache.ibatis.session.SqlSessionFactoryBuilder;
        import org.junit.After;
        import org.junit.Before;
        import org.junit.Test;
 
        import java.io.InputStream;
        import java.util.Date;
        import java.util.List;
 
public class MbTest {private InputStream in ;
    private SqlSessionFactory factory;
    private SqlSession session;
    private IUserDao userDao;
    @Test
    public void findByUsers(){User u=new User();
        u.setUsername("王 %");
        u.setAddress("% 昌平 %");
        List<User> users=userDao.findByUsers(u);
        for(User user: users){System.out.println(user);
        }
    }
    @Before
    // 在测试方法执行之前执行
    public void init()throws Exception {//1. 读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3. 创建 SqlSession 工厂对象
        factory = builder.build(in);
        // 4. 创建 SqlSession 对象
        session = factory.openSession(true);
        //5. 创建 Dao 的代理对象
        userDao = session.getMapper(IUserDao.class);
 
    }
    @After// 在测试方法执行完成之后执行
    public void destroy() throws Exception{//session.commit();
        //7. 释放资源
        session.close();
        in.close();}
}

4、where 标签

为了简化上面 where 1= 1 的条件拼装，我们可以采用 标签来简化开发。

4.1、需求分析

多条件查询：根据用户名和住址查询用户信息

4.2、案例实现

4.2.1、修改 IUserDao.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.tianyi.dao.IUserDao">
    <select id="findByUsers" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.User">
      select * from user
      <where>
        <if test="username!=null and username !=''">
            and username like #{username}
        </if>
        <if test="address!=null and address !=''">
            and address like #{address}
        </if>
      </where>
    </select>
</mapper>

5、choose 标签

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。MyBatis 提供了 choose 元素，按顺序判断 when 中的条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的 sql。类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

if 是与 (and) 的关系，而 choose 是或（or）的关系。

5.1、需求分析

多条件查询：根据用户名、住址查询、性别查询用户信息

5.2、案例实现

5.2.1、修改 IUserDao.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.tianyi.dao.IUserDao">
    <select id="findByUsers" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.User">
      select * from user
      <where>
        <choose>
         <when test="username!=null and username !=''">
            and username like #{username}
         </when>
         <when test="address!=null and address !=''">
            and address like #{address}
         </when>
          <otherwise>
            and sex="男"
          <otherwise>  
        </choose>
      </where>
    </select>
</mapper>

6、foreach 标签

foreach 标签主要用于构建 in 条件，可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中。

collection：collection 属性的值有三个分别是 list、array、map 三种，分别对应的参数类型为：List、数组、map 集合。item：表示在迭代过程中每一个元素的别名
index：表示在迭代过程中每次迭代到的位置（下标）open：前缀
close：后缀
separator：分隔符，表示迭代时每个元素之间以什么分隔

6.1、需求分析

范围查询时：根据多个 id（1，5，15）查询用户信息

6.2、案例实现

6.2.1、List 封装参数 id，编写 IdsList.java 文件

package com.tianyi.javabean;
 
import java.io.Serializable;
import java.util.List;
 
public class IdsList implements Serializable {private List<Integer> ids;
 
    public List<Integer> getIds() {return ids;
    }
 
    public void setIds(List<Integer> ids) {this.ids = ids;
    }
}

6.2.2、编写 IUserDao.java 文件

package com.tianyi.dao;
 
        import com.tianyi.javabean.IdsList;
        import com.tianyi.javabean.QueryUser;
        import com.tianyi.javabean.User;
 
        import java.util.List;
 
public interface IUserDao {List<User> findByIds(IdsList ids);
}

6.2.3、编写 IUserDao.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.tianyi.dao.IUserDao">
    <select id="findByIds" resultType="com.tianyi.javabean.User" parameterType="com.tianyi.javabean.IdsList">
        select * from user
        <where>
            <if test="ids!=null and ids.size()>0">
                <foreach collection="ids" open="id in (" close=")" separator="," item="ids" >
                    #{ids}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

6.2.4、编写 MbTest.java 文件

package com.tianyi.test;
 
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
import com.tianyi.javabean.User;
import com.tianyi.javabean.User_old;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
 
public class MbTest {private InputStream in ;
    private SqlSessionFactory factory;
    private SqlSession session;
    private IUserDao userDao;
    @Test
    public void findByIds(){IdsList ids =new IdsList();
        List<Integer> id =new ArrayList();
        id.add(14);
        id.add(17);
        id.add(19);
        ids.setIds(id);
 
        List<User> users=userDao.findByIds(ids);
        for(User user: users){System.out.println(user.getId()+":"+user.getUsername());
        }
    }
    @Before
    // 在测试方法执行之前执行
    public void init()throws Exception {//1. 读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3. 创建 SqlSession 工厂对象
        factory = builder.build(in);
        // 4. 创建 SqlSession 对象
        session = factory.openSession(true);
        //5. 创建 Dao 的代理对象
        userDao = session.getMapper(IUserDao.class);
 
    }
    @After// 在测试方法执行完成之后执行
    public void destroy() throws Exception{//session.commit();
        //7. 释放资源
        session.close();
        in.close();}
}

7、set 标签

没有使用 if 标签时，如果有一个参数为 null，都会导致错误。当在 update 语句中使用 if 标签时，如果最后的 if 没有执行，则或导致逗号多余错误。使用 set 标签可以将动态的配置 set 关键字，和剔除追加到条件末尾的任何不相关的逗号。

7.1、需求分析

修改我们的指定记录

7.2、案例实现

7.2.1、编写 IUserDao.java

import java.util.List;
 
public interface IUserDao {int updateUser(User u);
}

7.2.2、编写 IUserDao.xml

<update id="updateUser" parameterType="com.tianyi.javabean.User">
        update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>

if 标签

<update id="updateUser" parameterType="com.tianyi.javabean.User">
  update user set 
  <if test="username!=null and username!='' ">
       username=#{username},
  </if>
  <if test="birthday.toString()!=null and birthday.toString()!='' ">
       birthday=#{birthday},
  </if>
  <if test="sex!=null and sex!='' ">
       sex=#{sex},
  </if>
  <if test="address!=null and address!='' ">
      address=#{address}
  </if>
  where id=#{id}
</update>

set+if 标签

    <update id="updateUser" parameterType="User">
        update user
        <set>
            <if test="username!=null and username!='' ">
                username=#{username},
            </if>
            <if test="birthday.toString()!=null and birthday.toString()!='' ">
                birthday=#{birthday},
            </if>
            <if test="sex!=null and sex!='' ">
                sex=#{sex},
            </if>
            <if test="address!=null and address!='' ">
                address=#{address}
            </if>
        </set>
        where id=#{id}
    </update>

7.2.3、编写 MbTest.java

package com.tianyi.test;
 
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
        import com.tianyi.javabean.User;
        import com.tianyi.javabean.User_old;
        import org.apache.ibatis.io.Resources;
        import org.apache.ibatis.session.SqlSession;
        import org.apache.ibatis.session.SqlSessionFactory;
        import org.apache.ibatis.session.SqlSessionFactoryBuilder;
        import org.junit.After;
        import org.junit.Before;
        import org.junit.Test;
 
        import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
        import java.util.List;
 
public class MbTest {private InputStream in ;
    private SqlSessionFactory factory;
    private SqlSession session;
    private IUserDao userDao;
  @Test
    public void updateUser(){User u=userDao.findById(11);
        u.setUsername("赵六");
        userDao.updateUser(u);
    }
    @Before
    // 在测试方法执行之前执行
    public void init()throws Exception {//1. 读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3. 创建 SqlSession 工厂对象
        factory = builder.build(in);
        // 4. 创建 SqlSession 对象
        session = factory.openSession(true);
        //5. 创建 Dao 的代理对象
        userDao = session.getMapper(IUserDao.class);
 
    }
    @After// 在测试方法执行完成之后执行
    public void destroy() throws Exception{//session.commit();
        //7. 释放资源
        session.close();
        in.close();}
}

8、trim 标签

trim 标记是一个格式化的标记，主要用于拼接 sql 的条件语句（前缀或后缀的添加或忽略），可以完成 set 或者是 where 标记的功能。

prefix：在 trim 标签内 sql 语句加上前缀
suffix：在 trim 标签内 sql 语句加上后缀
prefixOverrides：指定去除多余的前缀内容，如：prefixOverrides=“AND | OR”，去除 trim 标签内 sql 语句多余的前缀 "and" 或者 "or"。suffixOverrides：指定去除多余的后缀内容。

8.1、需求分析 -1

修改我们的指定记录

8.2、案例实现

8.2.1、编写 IUserDao.xml 文件

<update id="updateUser" parameterType="User">
        update user
        <trim prefix="set" suffixOverrides=",">
            <if test="username!=null and username!='' ">
                username=#{username},
            </if>
            <if test="birthday.toString()!=null and birthday.toString()!='' ">
                birthday=#{birthday},
            </if>
            <if test="sex!=null and sex!='' ">
                sex=#{sex},
            </if>
            <if test="address!=null and address!='' ">
                address=#{address},
            </if>
        </trim>
        where id=#{id}
</update>

8.3、需求分析 -2

完成我们表的记录的添加

8.4、案例实现

8.4.1、编写 IUserDao.java

import java.util.List;
 
public interface IUserDao {int addUser(User u);
}

8.4.2、编写 IUserDao.xml

<insert id="addUser" parameterType="com.tyschool.mb002.javabean.User">
        <selectKey keyColumn="id" keyProperty="id" resultType="int">
            select last_insert_id()
        </selectKey>
        insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address})
    </insert>

    <insert id="addUser" parameterType="User">
        <selectKey keyColumn="id" keyProperty="id" resultType="int">
            select last_insert_id()
        </selectKey>
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null and username!='' ">
                username,
            </if>
            <if test="birthday.toString()!=null and birthday.toString()!='' ">
                birthday,
            </if>
            <if test="sex!=null and sex!='' ">
                sex,
            </if>
            <if test="address!=null and address!='' ">
                address
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null and username!='' ">
                #{username},
            </if>
            <if test="birthday.toString()!=null and birthday.toString()!='' ">
                #{birthday},
            </if>
            <if test="sex!=null and sex!='' ">
                #{sex},
            </if>
            <if test="address!=null and address!='' ">
                #{address}
            </if>
        </trim>
    </insert>

8.4.3、编写 MbTest.java

package com.tianyi.test;
 
import com.tianyi.dao.IUserDao;
import com.tianyi.javabean.IdsList;
import com.tianyi.javabean.QueryUser;
        import com.tianyi.javabean.User;
        import com.tianyi.javabean.User_old;
        import org.apache.ibatis.io.Resources;
        import org.apache.ibatis.session.SqlSession;
        import org.apache.ibatis.session.SqlSessionFactory;
        import org.apache.ibatis.session.SqlSessionFactoryBuilder;
        import org.junit.After;
        import org.junit.Before;
        import org.junit.Test;
 
        import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
        import java.util.List;
 
public class MbTest {private InputStream in ;
    private SqlSessionFactory factory;
    private SqlSession session;
    private IUserDao userDao;
  @Test
    public void addUser(){User u=new User();
        u.setUsername("李四");
        u.setBirthday(new Date());
        u.setSex("男");
        u.setAddress("北京海淀");
        userDao.addUser(u);
    }
    @Before
    // 在测试方法执行之前执行
    public void init()throws Exception {//1. 读取配置文件
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建构建者对象
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        //3. 创建 SqlSession 工厂对象
        factory = builder.build(in);
        // 4. 创建 SqlSession 对象
        session = factory.openSession(true);
        //5. 创建 Dao 的代理对象
        userDao = session.getMapper(IUserDao.class);
 
    }
    @After// 在测试方法执行完成之后执行
    public void destroy() throws Exception{//session.commit();
        //7. 释放资源
        session.close();
        in.close();}
}

9、sql 标签与 include 标签

include 标签可以将 Sql 中可将重复的 sql 提取出来，最终达到 sql 重用的目的。

9.1、sql 标签声明

<sql id="sqlSelectAll">
    select * from user
</sql>

9.2、include 标签提取

<select id="findByIds" resultType="com.tianyi.javabean.User_old" parameterType="com.tianyi.javabean.IdsList">
    <include refid="sqlSelectAll"></include>
    <where>
        <if test="ids!=null and ids.size()>0">
            <foreach collection="ids" open="id in (" close=")" separator="," item="ids" >
                #{ids}
            </foreach>
        </if>
    </where>
</select>