Mybatis-Plus自定义sql

测试数据库为 t_user:

实体类：

package com.example.mpdemo1.pojo;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;


@Data
@TableName("t_user")
public class User {

    /**
     * id
     */
    private Long id;

    /**
     * 用户名
     */
    private String username;

    /**
     * 密码
     */
    private String password;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 性别
     */
    private String sex;

    /**
     * 邮箱
     */
    private String email;
}

逻辑层:

package com.example.mpdemo1.service;


import com.baomidou.mybatisplus.extension.service.IService;
import com.example.mpdemo1.pojo.User;

import java.util.List;


public interface UserService extends IService<User> {

    void updateAgeByIds(List<Integer> ids);
}

package com.example.mpdemo1.service.ServiceImpl;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.mpdemo1.mapper.UserMapper;
import com.example.mpdemo1.pojo.User;
import com.example.mpdemo1.service.UserService;
import lombok.AllArgsConstructor;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@AllArgsConstructor
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    private final UserMapper userMapper;

    @Override
    public void updateAgeByIds(List<Integer> ids) {
        Integer age = 18;
        //构建条件
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<User>().in(User::getId,ids);
        //自定义SQL方法调用
        userMapper.updateAgeByIds(wrapper,age);
    }
}

Mapper：

package com.example.mpdemo1.mapper;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.example.mpdemo1.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    //@Param("ew") , Constants.WRAPPER = "ew"
    void updateAgeByIds(@Param(Constants.WRAPPER) LambdaQueryWrapper<User> wrapper, @Param("age") Integer age);

}

Mapper.xml：

当我们在xml文件中 用到wrapper时，动态拼接SQL的用法为：

1、${ew.customSqlSegment} ， 等同于 Where + queryWrapper中的查询条件 ；

2、${ew.sqlSegment}，等同于 queryWrapper中的查询条件；

<?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.example.mpdemo1.mapper.UserMapper">

    <update id="updateAgeByIds">
        update t_user set age = #{age} ${ew.customSqlSegment}
    </update>
</mapper>

使用junit测试方法：

package com.example.mpdemo1;

import com.example.mpdemo1.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

@SpringBootTest
class MPdemo1ApplicationTests {

    @Autowired
    private UserService userService;

    @Test
    void contextLoads() {
        System.out.println(userService.list());
    }

    /**
     * 方法测试
     */
    @Test
    public void updateTest(){
        // 拼接条件
        List<Integer> ids = List.of(1, 2);
        // 自定义方法 实现：根据id 批量修改 用户的年龄
        userService.updateByage(ids);
    }

}

执行测试方法后：

年龄都修改为18