Mybatis-Plus 自定义SQL

有的时候使用条件构造器自定义SQL满足不了我们的需求，我们既想使用 Wrapper,又想使用SQL,MP 对这种方式也提供了支持，MP 版本号应≥3.0.7。下面是该方案的实现记录：

实现方案一：Mapper接口中使用@Select注解

1、Mapper 示例代码如下：

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.paratera.protect.entity.Staff;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author 朱治龙
 * @date 2023-10-17 11:46:00
 */
public interface StaffMapper extends BaseMapper<Staff> {

    @Select("select * from staff ${ew.customSqlSegment}")
    List<Staff> selectAll(@Param(Constants.WRAPPER)Wrapper<Staff> wrapper);
}

2、调用示例代码：

    @Test
    void testSelfSQL() {
        LambdaQueryWrapper<Staff> lqw = Wrappers.lambdaQuery(Staff.class);
        lqw.eq(Staff::getName, "朱治龙").and(wq2 -> wq2.lt(Staff::getAge, 40).or().isNotNull(Staff::getEmail));
        List<Staff> staffList = staffMapper.selectAll(lqw);
        staffList.forEach(System.out::println);
    }

3、输出结果

DEBUG==>  Preparing: select * from staff WHERE (name = ? AND (age < ? OR email IS NOT NULL))
DEBUG==> Parameters: 朱治龙(String), 40(Integer)
TRACE<==    Columns: id, name, age, email, mobile, manager_id, create_time
TRACE<==        Row: 1714166763984199681, 朱治龙, 36, zhuzl@blsc.cn, 15084978453, 1088248166370832385, 2023-10-17 14:29:38
DEBUG<==      Total: 1
Staff(id=1714166763984199681, name=朱治龙, age=36, email=zhuzl@blsc.cn, mobile=15084978453, managerId=1088248166370832385, createTime=2023-10-17T14:29:38)

实现方案二：使用xml

1、配置xml文件的存放路径。再application.yml中添加 xml 文件的引用路径配置

mybatis-plus:
  mapper-locations:
  - classpath:/mapper/*Mapper.xml

2、在resources目录下添加 mapper 目录，并新建 mapper 文件，如文件名为 StaffMapper.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.paratera.protect.dao.StaffMapper">
    <select id="selectByXml" resultType="com.paratera.protect.entity.Staff">
        select * from staff ${ew.customSqlSegment}
    </select>
</mapper>

3、Mapper 接口中添加方法，示例代码如下：

package com.paratera.protect.dao;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.paratera.protect.entity.Staff;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author 朱治龙
 * @date 2023-10-17 11:46:00
 */
public interface StaffMapper extends BaseMapper<Staff> {
    

    List<Staff> selectByXml(@Param(Constants.WRAPPER)Wrapper<Staff> wrapper);
}

4、调用示例代码：

    @Test
    void testSelfSQL2() {
        LambdaQueryWrapper<Staff> lqw = Wrappers.lambdaQuery(Staff.class);
        lqw.eq(Staff::getName, "朱治龙").and(wq2 -> wq2.lt(Staff::getAge, 50).or().isNotNull(Staff::getEmail));
        List<Staff> staffList = staffMapper.selectByXml(lqw);
        staffList.forEach(System.out::println);
    }

5、输出结果

DEBUG==>  Preparing: select * from staff WHERE (name = ? AND (age < ? OR email IS NOT NULL))
DEBUG==> Parameters: 朱治龙(String), 50(Integer)
TRACE<==    Columns: id, name, age, email, mobile, manager_id, create_time
TRACE<==        Row: 1714166763984199681, 朱治龙, 36, zhuzl@blsc.cn, 15084978453, 1088248166370832385, 2023-10-17 14:29:38
DEBUG<==      Total: 1
Staff(id=1714166763984199681, name=朱治龙, age=36, email=zhuzl@blsc.cn, mobile=15084978453, managerId=1088248166370832385, createTime=2023-10-17T14:29:38)