MyBatis使用：动态SQL

1、目标

本文的主要目标是使用MyBatis的动态SQL

2、最好使用@Param注解

@Data
public class BaseEntity {

    protected String id;

    protected Integer createUserId;

    protected String createDateTime;

}

@Data
public class News extends BaseEntity {

    private String title;

}

@RestController
@RequiredArgsConstructor
@Log4j2
@RequestMapping("/news")
public class NewsController {

    private final NewsService newsService;

    @PutMapping("/updateByList")
    public String updateByList(@RequestBody News news) {
        newsService.updateByList(news);
        return "updateByList success";
    }

}

@Service
@RequiredArgsConstructor
@Log4j2
public class NewsService {

    private final NewsMapper newsMapper;

    public void updateByList(News news) {
        newsMapper.updateByList(news);
    }
}

@Mapper
public interface NewsMapper {
    void updateByList(@Param("news") News news);
}

每个参数都加上@Param注解

<?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.lwc.mapper.NewsMapper">
    <update id="updateByList">
        update news set title = #{news.title}
        where id = #{news.id}
    </update>
</mapper>

mapper.xml文件中#{}取出News对象的某一个属性，用#{news.属性名字}得到

3、返回值是List集合，resultType写List集合的泛型

@GetMapping("/getNewsList")
public List<News> getNewsList(@RequestParam("title") String title) {
    return newsService.getNewsList(title);
}

public List<News> getNewsList(String title) {
   return newsMapper.getNewsList(title);
}

List<News> getNewsList(@Param("title") String title);

mapper接口的返回值类型是List集合，泛型是News

<select id="getNewsList" resultType="com.lwc.entity.News">
     select id, title, create_user_id createUserId, create_date_time createDateTime from news where title like CONCAT('%', #{title}, '%')
</select>

mapper.xml文件中resultType写List集合的泛型就可以了

4、where标签、if标签、where条件拼接

@GetMapping("/getNewsListByTitleAndUserId")
public List<News> getNewsListByTitleAndUserId(@RequestParam(value = "title", required = false) String title) {
    Integer userId = UserIdThreadLocal.getUserId();
    return newsService.getNewsListByTitleAndUserId(title, userId);
}

public List<News> getNewsListByTitleAndUserId(String title, Integer userId) {
    return newsMapper.getNewsListByTitleAndUserId(title, userId);
}

List<News> getNewsListByTitleAndUserId(@Param("title") String title, @Param("userId") Integer userId);

<select id="getNewsListByTitleAndUserId" resultType="com.lwc.entity.News">
    select id, title, create_user_id createUserId, create_date_time createDateTime from news
    <where>
        <if test="title != null and title != ''">
            and title like CONCAT('%', #{title}, '%')
        </if>
        <if test="userId != null and userId != ''">
            and create_user_id &gt;= #{userId}
        </if>
    </where>
</select>

mapper.xml文件中可以使用where标签、if标签

where的两个查询条件可能都没有，因此不需要加where，所以用where标签

if标签如果test为true就拼接条件，它会自动去除and

字符串拼接用CONCAT函数

大于等于在xml文件可以用>=

大于是>

小于等于是<=

小于是<

测试结果：

where的两个条件同时满足

where只有一个条件满足，自动去除and

where没有条件满足，自动去除where

5、foreach标签遍历

@PutMapping("/updateNewsByIds")
public String updateNewsByIds() {
    newsService.updateNewsByIds();
    return "updateNewsByIds success";
}

public void updateNewsByIds() {
    Map<Map<String, Object>, List<List<String>>> map = new HashMap<>();
    Map<String, Object> tempMap = new HashMap<>();
    tempMap.put("create_user_id", 100);
    tempMap.put("create_date_time", new Date(System.currentTimeMillis()));
    List<String> idList = new ArrayList<>();
    idList.add("a2435411095207fea9f78e8e1d1565bb");
    idList.add("63a95e17fac7b24eabd5b48bbd540c00");
    idList.add("30aa5db26bedaaa308693a80c6fe6116");
    // hutool包可以按照指定大小分割List集合
    List<List<String>> list = ListUtil.partition(idList, 2);
    map.put(tempMap, list);

    tempMap = new HashMap<>();
    tempMap.put("create_user_id", 101);
    tempMap.put("create_date_time", new Date(System.currentTimeMillis()));
    idList = new ArrayList<>();
    idList.add("2aaf3ca4bc6bac9dcf31d3fef655c5b7");
    idList.add("24b4c856aee3deafa5b1b752db0f75ca");
    idList.add("276e60e8a3eb352c5a4c9809c502c0f4");
    // hutool包可以按照指定大小分割List集合
    list = ListUtil.partition(idList, 2);
    map.put(tempMap, list);
    newsMapper.updateNewsByIds(map);
}

id都是Stream流的分组得到的，这里简化了

由于where id in ()这里的参数个数有1000个数的限制，因此需要将这个List集合按照指定的大小分割，hutool包的ListUtil.partition可以进行分割List集合的操作，为了方便检验这里每个List集合分割成只有2个元素

<!--hutool-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.17</version>
</dependency>

这是hutool包的依赖

void updateNewsByIds(@Param("map") Map<Map<String, Object>, List<List<String>>> map);

mapper接口对入参这个map设置@Param(“map”)

<update id="updateNewsByIds">
    <foreach collection="map" index="fieldMap" item="idList" separator=";">
        <foreach collection="idList" item="idSubList" separator=";">
            update news set
            <foreach collection="fieldMap" index="fieldName" item="fieldValue" separator=",">
                ${fieldName} = #{fieldValue}
            </foreach>
            where id in
            <foreach collection="idSubList" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </foreach>
    </foreach>
</update>

mapper.xml文件中用foreach标签对map进行遍历，separator=“;” 表示用分号将多个sql语句分开，open="(“表示遍历开始加上左括号，close=”)"表示遍历结束加上右括号

foreach标签遍历List集合：index是下标，item是List集合指定下标的元素

foreach标签遍历Map：index是map的key，item是map的value

注意：

mysql：允许在单个Statement对象中执行多个SQL语句这种批量操作需要设置allowMultiQueries=true，并且多个SQL语句需要用分号分开

url: jdbc:mysql://ip:port/数据库名字?useUnicode=true&allowMultiQueries=true

oracle：oracle用存储过程，open=“BEGIN” close=“;END;” separator=“;”

测试结果：

执行多个update操作用分号分开，这里设置的id的最大个数是2

6、update标签可以增加字段或者删除字段

@PutMapping("/addColumn")
public String addColumn(@RequestParam("columnName") String columnName) {
    newsService.addColumn(columnName);
    return "addColumn success";
}

public void addColumn(String columnName) {
    newsMapper.addColumn(columnName);
}

void addColumn(@Param("columnName") String columnName);

<update id="addColumn">
    alter table news add ${columnName} varchar(100)
</update>

7、分页查询

@GetMapping("/getPage")
public List<News> getPage(@RequestParam("size") Integer size, @RequestParam("current") Integer current) {
    return newsService.getPage(size, current);
}

public List<News> getPage(int size, int current) {
    int startRow = (current - 1) * size;
    return newsMapper.getPage(startRow, size);
}

List<News> getPage(@Param("startRow") Integer startRow, @Param("size") Integer size);

对于mysql：

<select id="getPage" resultType="com.lwc.entity.News">
    select id, title, create_user_id createUserId, create_date_time createDateTime from news limit #{startRow}, #{size}
</select>

对于oracle没有limit，因此用到ROWNUM表示记录的行数

SELECT * FROM (
    SELECT a.*, ROWNUM rnum FROM (
        SELECT * FROM my_table ORDER BY some_column
    ) a
    WHERE ROWNUM <= end_row
) WHERE rnum > start_row;

start_row 是起始行的索引，即 (页码current - 1) * 页大小size

end_row是结束行的索引，即 页码current * 页大小size

优化：

<select id="getPage" resultType="com.lwc.entity.News">
    select id, title, create_user_id createUserId, create_date_time createDateTime from news where id > #{id} order by id desc limit #{size}
</select>

使用主键（通常是 id）进行分页，通过存储最后一条记录的主键来优化查询，而不是使用 OFFSET

limit xx, size 会扫描前xx个记录，因此会很慢，所以先找到第xx个大的记录的id然后where条件筛选