MyBatis框架 - 动态sql的意义 - 《SSM学习笔记》

什么是动态sql
sql+include标签代码实现
多条件查询
- <if>标签
- <where>标签
有选择的更新意义
- <set>标签
foreach的查询实现
- <foreach>标签
批量删除
按照指定的下标位置查询

什么是动态sql

可以定义代码片段,可以进行逻辑判断，可以进行循环处理(批量处理)，使用条件判断更为简单

sql+include标签代码实现

MySqlMapper.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.chentianyu.mapper.MySqlMapper">
    <!--定义代码片段-->
    <sql id="allUsers"><!--注意这个-->
        id,username,birthday,sex,address
    </sql>
    <!--查询用户全部信息-->
    <select id="getAll" resultType="users"><!--注意这个-->
        select <include refid="allUsers"></include> from users
    </select>
</mapper>

MySqlMapper.java

package com.chentianyu.mapper;

import com.chentianyu.pojo.Users;

import java.util.List;

public interface MySqlMapper {
    List<Users> getAll();
}

测试类

package com.chentianyu.test;

import com.chentianyu.mapper.MySqlMapper;
import com.chentianyu.mapper.UsersMapper;
import com.chentianyu.pojo.Users;
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.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 *
 */
public class MyTest {
    MySqlMapper sqlMapper;
    @Before
    public void test01() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        sqlSession = factory.openSession();
        uMapper = sqlSession.getMapper(UsersMapper.class);
    }
    @After
    public void test03(){
        sqlSession.close();
    }    
    @Test
    public void testSqlGetAll(){
        sqlMapper = sqlSession.getMapper(MySqlMapper.class);
        List<Users> all = sqlMapper.getAll();
        all.forEach(users -> System.out.println(users));
    }
}

多条件查询

`<if>`标签

<if>: 进行条件判断

`<where>`标签

<where>: 进行多条件拼接，在查询，删除，更新

<?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.chentianyu.mapper.MySqlMapper">
    <!--定义代码片段-->
    <sql id="allUsers">
        id,username,birthday,sex,address
    </sql>
...
    <!--
    //按指定条件进行多条件查询
    List<Users> getByCondition(Users users);
    根据实体类中的成员变量是否有值来决定是否添加条件
    -->
    <select id="getByCondition" resultType="users" parameterType="users">
        select <include refid="allUsers"></include> from users
        <where><!--相当于1=1-->
            <if test="userName != null and userName !='' ">
                and username like concat('%',#{userName},'%')
            </if>
            <if test="birthday != null">
                and birthday=#{birthday}
            </if>
            <if test="sex != null and sex != ''">
                and sex=#{sex}
            </if>
            <if test="address != null and address != ''">
                and address like concat('%',#{address},'%')
            </if>
        </where>
    </select>
</mapper>

全部都是null

@Test
public void testSqlWhereIf(){
    Users users = new Users();
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    List<Users> list = sqlMapper.getByCondition(users);
    list.forEach(users1 -> System.out.println(users1));
}

Opening JDBC Connection
Created connection 851912430.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@32c726ee]
==>  Preparing: select id,username,birthday,sex,address from users
==> Parameters: 
<==    Columns: id, username, birthday, sex, address
<==        Row: 1, 王五, 2000-09-10, 1, 安徽
<==        Row: 2, haha6, 2000-01-02, 2, 北京
<==        Row: 3, 张三, 2000-07-03, 1, 北京
<==        Row: 31, 李明, 2001-02-05, 1, 大兴
<==        Row: 32, 小明, 2001-02-05, 1, 大兴
<==        Row: 33, 小明1, 2001-02-05, 1, 大兴
<==      Total: 6

如果我们添加sex性别

@Test
public void testSqlWhereIf(){
    Users users = new Users();
    users.setSex("2");
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    List<Users> list = sqlMapper.getByCondition(users);
    list.forEach(users1 -> System.out.println(users1));

}

Opening JDBC Connection
Created connection 851912430.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@32c726ee]
==>  Preparing: select id,username,birthday,sex,address from users WHERE sex=?
==> Parameters: 2(String)
<==    Columns: id, username, birthday, sex, address
<==        Row: 2, haha6, 2000-01-02, 2, 北京
<==      Total: 1

添加一个模糊名字

@Test
public void testSqlWhereIf(){
    Users users = new Users();
    users.setSex("1");
    users.setUserName("明");
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    List<Users> list = sqlMapper.getByCondition(users);
    list.forEach(users1 -> System.out.println(users1));

}

Opening JDBC Connection
Created connection 851912430.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@32c726ee]
==>  Preparing: select id,username,birthday,sex,address from users WHERE username like concat('%',?,'%') and sex=?
==> Parameters: 明(String), 1(String)
<==    Columns: id, username, birthday, sex, address
<==        Row: 31, 李明, 2001-02-05, 1, 大兴
<==        Row: 32, 小明, 2001-02-05, 1, 大兴
<==        Row: 33, 小明1, 2001-02-05, 1, 大兴
<==      Total: 3

有选择的更新意义

`<set>`标签

<set>: 有选择的进行更新处理，至少更新一列。

以往的update语句，它确实更新了

@Test
public void testUpdate01() throws ParseException {
    Users users = new Users();
    users.setId(31);
    users.setUserName("星期三");
    int num = uMapper.update(users);
    System.out.println(num);
    //切记: 手工提交
    sqlSession.commit();
}

但是看到数据库

id:31,username:星期三,birthday:null,sex:null,address:null

看看日志

Opening JDBC Connection
Created connection 1375394559.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@51fadaff]
==>  Preparing: update users set username=?,birthday=?,sex=?,address=? where id=?
==> Parameters: 星期三(String), null, null, null, 31(Integer)
<==    Updates: 1

你发现每个都更新了

这个null直接被更到了?中这样就不好了，我想要非空更新,空不更新

public interface MySqlMapper {
    List<Users> getAll();

    int UpdateSet(Users users);
}

<?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.chentianyu.mapper.MySqlMapper">
....

    <update id="UpdateSet" parameterType="users">
        update users 
    <set>
        <if test="userName != null and userName != ''">
            username=#{userName},
        </if>    
        <if test="birthday != null">
            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>
</mapper>

测试

@Test
public void testSqlUpdateSet(){
    Users users = new Users();
    users.setId(32);
    users.setSex("2");
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    int num = sqlMapper.UpdateSet(users);
    System.out.println(num);
    sqlSession.commit();
}

若<set>中你只有空的值

@Test
public void testSqlUpdateSet(){
    Users users = new Users();
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    int num = sqlMapper.UpdateSet(users);
    System.out.println(num);
    sqlSession.commit();
}

会报错

### SQL: update users            where id=?

foreach的查询实现

`<foreach>`标签

<foreach>：用来进行循环遍历，完成循环条件查询，批量删除最常用,批量增加，批量更新

sql语句的循环

select * from users where id in (2,4,6);

<?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.chentianyu.mapper.MySqlMapper">
    <!--定义代码片段-->
    <sql id="allUsers">
        id,username,birthday,sex,address
    </sql>
    ...
    <!--
    //查询多个指定id的用户信息
    List<Users> getByIds(Integer[] arr);
    简单类型其实不用写;只有实体类是必须写的，其他都不用写
    -->
    <select id="getByIds" resultType="users">
        select <include refid="allUsers"></include> from users where id in(
            <foreach collection="array" item="id" separator=",">
                #{id}
            </foreach>
        )
    </select>
</mapper>

可以这么来修改

<?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.chentianyu.mapper.MySqlMapper">
    <!--定义代码片段-->
    <sql id="allUsers">
        id,username,birthday,sex,address
    </sql>
    ...
    <!--
    //查询多个指定id的用户信息
    List<Users> getByIds(Integer[] arr);
    简单类型其实不用写;只有实体类是必须写的，其他都不用写
    -->
    <select id="getByIds" resultType="users">
        select <include refid="allUsers"></include> from users where id in
            <foreach collection="array" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
    </select>
</mapper>

代码测试

@Test
public void testGetByIds(){
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    Integer[] arr = {2,4,6,8,31};
    List<Users> list = sqlMapper.getByIds(arr);
    list.forEach(users -> System.out.println(users));
}

<foreach>参数详解

collection: 用来指定入参类型，如果是List集合，则为list;如果是Map集合,则为map;如果是数组,则为array
item: 每次循环遍历出来的值或对象
separator: 多个值或对象或语句之间的分隔符
open:整个循环外面的前
close: 整个循环外面的后

批量删除

public interface MySqlMapper {
....
    //批量删除
    int deleteBatch(Integer[] arr);
}

<?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.chentianyu.mapper.MySqlMapper">
...

    <!--
    //批量删除
    int deleteBatch(Integer[] arr);
    -->
    <delete id="deleteBatch" >
        delete from users where id in
        <foreach collection="array" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
</mapper>

@Test
public void testDeleteBatch(){
    sqlMapper = sqlSession.getMapper(MySqlMapper.class);
    Integer[] arr = {2,4,6,8,31};
    int num = sqlMapper.deleteBatch(arr);
    System.out.println(num);
    sqlSession.commit();
}

Opening JDBC Connection
Created connection 124058278.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@764faa6]
==>  Preparing: delete from users where id in ( ? , ? , ? , ? , ? )
==> Parameters: 2(Integer), 4(Integer), 6(Integer), 8(Integer), 31(Integer)
<==    Updates: 2
2

按照指定的下标位置查询

/**
 * 数据访问层的接口,规定了数据库中可进行的各种操作
 */
public interface UsersMapper {
    ...
    //按照指定的下标位置查询
    List<Users> selectIndex(Date begin,Date end);
}

<?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.chentianyu.mapper.UsersMapper">
    ....
    <!--指定参数下标位置查询
    List<Users> selectIndex(Date begin,Date end);-->
    <select id="selectIndex" resultType="users">
        select * from users where birthday between #{arg0} and #{arg1}
    </select>
</mapper>

@Test
public void testBESelect() throws ParseException {
    UsersMapper sqlMapper = sqlSession.getMapper(UsersMapper.class);
    List<Users> list = sqlMapper.selectIndex(sf.parse("2001-01-01"), sf.parse("2022-01-01"));
    list.forEach(users -> System.out.println(users));
}

Opening JDBC Connection
Created connection 2019826979.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@78641d23]
==>  Preparing: select * from users where birthday between ? and ?
==> Parameters: 2001-01-01 00:00:00.0(Timestamp), 2022-01-01 00:00:00.0(Timestamp)
<==    Columns: id, username, birthday, sex, address
<==        Row: 32, 小明, 2001-02-05, 2, 大兴
<==        Row: 33, 小明1, 2001-02-05, 1, 大兴
<==      Total: 2

这个指定下标要比@Param更省事,无论是@Param还是指定下标都不如入参是map

动态sql的意义