Mybatis中动态sql的使用

动态 SQL 是 MyBatis 的强大特性之一,使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。Mbatis-Plus封装了一些固定的单表的查询，对于一些复杂的关联还得使用sql进行查询 。
常见的标签有以下几种：

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

1. if 标签

if标签可以对进行传入的参数进行判断.

<!--定义结果集-->
    <resultMap id="baseResultMap" type="com.elite.mybatis.entity.Person">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
    </resultMap>
    <!--定义sql片段-->
    <sql id="selectList">
          select id,name,age,email from person
    </sql>
    <!--if标签-->
    <select id="selectPersonByName" resultMap="baseResultMap">
          <include refid="selectList"></include>
          where 1=1
          <if test="name!=null and name!= ''">
             and  name like  CONCAT('%',CONCAT(#{name},'%'))
          </if>
    </select>

测试

public class TestDynamicSql {

    SqlSession sqlSession =null;
    @Before
    public void init() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        sqlSession = sqlSessionFactory.openSession();
    }
    /**
     * 查询人员信息
     */
    @Test
    public void testIf()  {

        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        List<Person> personList = personMapper.selectPersonByName(null);
        personList.forEach(p->{
            System.out.println(p.toString());
        });
        PersonMapper personMapper1 = sqlSession.getMapper(PersonMapper.class);
        List<Person> personList1 = personMapper.selectPersonByName("elite");
        personList1.forEach(p->{
            System.out.println(p.toString());
        });
    }
}

2.choose、when、otherwise

choose可以根据条件进行判断加上条件进行查询。

/**
     * selectPersonByNameAndAge
     */
    /**
     * 查询人员信息
     */
    @Test
    public void testChoose() {
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        System.out.println("================传入名字按名字查询===============================");
        List<Person> personList = personMapper.selectPersonByNameAndAge("elite",null);
        personList.forEach(p -> {
            System.out.println(p.toString());
        });
        System.out.println("==================传入年龄按年龄查询=============================");
        List<Person> personList1 = personMapper.selectPersonByNameAndAge(null,24);
        personList1.forEach(p -> {
            System.out.println(p.toString());
        });
        System.out.println("===============都不传入按默认的id=1查询返回================================");
        List<Person> personList2 = personMapper.selectPersonByNameAndAge(null,null);
        personList2.forEach(p -> {
            System.out.println(p.toString());
        });
    }

3. trim、where、set

前边拼接sql的地方可能一个条件都不加的情况下，我们需要写一个where 1=1才可以，这几个标签就可以解决这个问题。

    <select id="selectPersonById" resultMap="baseResultMap">
        <include refid="selectList"></include>
        <where>
            <if test="id != null and id!= '' ">
                id = #{id}
            </if>
        </where>
    </select>

测试代码

   /**
     * 查询人员信息
     */
    @Test
    public void testWhere() {
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        System.out.println("================不传入id===============================");
        List<Person> personList = personMapper.selectPersonById(null);
        personList.forEach(p -> {
            System.out.println(p.toString());
        });
        System.out.println("================传入id===============================");
        List<Person> personList1 = personMapper.selectPersonById(1L);
        personList1.forEach(p -> {
            System.out.println(p.toString());
        });
    }

测试set

 <!--set标签-->
    <update id="updatePersonById">
        update person
        <set>
            <if test="name != null">name=#{name},</if>
            <if test="age != null">age=#{age},</if>
            <if test="email != null">email=#{email}</if>
        </set>
        where id=#{id}
    </update>

测试

 /**
     * 测试更新
     */
    @Test
    public void testSet() {
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        Person p = new Person();
        p.setId(1L);
        p.setName("testset");
        p.setEmail("testset@qq.com");
        personMapper.updatePersonById(p);
        System.out.println(personMapper.selectPerson(1L).toString());
        //Person{id=1, name='testset', age=22, email='testset@qq.com'}
    }

4. foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历。

mapperxml

<!--foreach-->
    <select id="selectPersonByIds" resultMap="baseResultMap">
        <include refid="selectList"></include>
        <where>
            <foreach item="item" index="index" collection="ids"
                     open="id in (" separator="," close=")">
                #{item}
            </foreach>
        </where>
    </select>

测试

 /**
     * 测试更新
     */
    @Test
    public void testForeach() {
        PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
        Long[] ids = new Long[]{1L,2L};
        personMapper.selectPersonByIds(ids).forEach(person -> {
            System.out.println(person.toString());
        });
    }

测试结果

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@12b08d6]
==>  Preparing: select id,name,age,email from person WHERE id in ( ? , ? )
==> Parameters: 1(Long), 2(Long)
<==    Columns: id, name, age, email
<==        Row: 1, elite, 22, elite@qq.com
<==        Row: 2, elite2, 24, elite2@qq.com
<==      Total: 2
Person{id=1, name='elite', age=22, email='elite@qq.com'}
Person{id=2, name='elite2', age=24, email='elite2@qq.com'}