第四章：mybatis-plus自定义查询SQL的方式

方式一:通过在mapper类中增加注入

1.1在UserMappr中增加自定义方法

import com.baomidou.mybatisplus.core.conditions.Wrapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.baomidou.mybatisplus.core.toolkit.Constants;

import com.mesui.service.data.User;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.annotations.Select;

import java.util.List;

import java.util.Map;

public interface UserMapper extends BaseMapper<User> {

@Select("select * from user ${ew.customSqlSegment}") //${ew.customSqlSegment}是固定格式

List<Map<String,Object>> selectAllUser(@Param(Constants.WRAPPER)Wrapper<User> wrapper);

}

1.2生成测试类

@Test

public void testQueryStucture(){

QueryWrapper<User> wrapper= new QueryWrapper<User>();

wrapper.apply("length(name)={0} or length(name)={1}",3,4);

wrapper.select(User.class,info -> (info.getColumn().equals("id")));

//userMapper.selectList(wrapper);

List<Map<String,Object>> maps= userMapper.selectAllUser(wrapper);

maps.forEach(System.out::println);

}

1.3测试结果

2022-10-08 11:12:51.676 DEBUG 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : ==> Preparing: select * from user WHERE (length(name)=? or length(name)=?)

2022-10-08 11:12:51.709 DEBUG 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : ==> Parameters: 3(Integer), 4(Integer)

2022-10-08 11:12:51.742 TRACE 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : <== Columns: id, name, age, email

2022-10-08 11:12:51.742 TRACE 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : <== Row: 1, Jone, 18, test1@baomidou.com

2022-10-08 11:12:51.746 TRACE 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : <== Row: 2, Jack, 20, test2@baomidou.com

2022-10-08 11:12:51.746 TRACE 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : <== Row: 3, Tom, 28, test3@baomidou.com

2022-10-08 11:12:51.746 DEBUG 19344 --- [ main] c.m.s.mapper.UserMapper.selectAllUser : <== Total: 3

{name=Jone, id=1, age=18, email=test1@baomidou.com}

{name=Jack, id=2, age=20, email=test2@baomidou.com}

{name=Tom, id=3, age=28, email=test3@baomidou.com}

方法二：通过xml方式

2.1首先需要在application.yml指定mapper xml文件的位置

mybatis-plus:

mapper-locations: classpath*:/mapper/*.xml

2.2在mapper类中增加查询方法

import com.baomidou.mybatisplus.core.conditions.Wrapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.baomidou.mybatisplus.core.toolkit.Constants;

import com.mesui.service.data.User;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.annotations.Select;

import java.util.List;

import java.util.Map;

public interface UserMapper extends BaseMapper<User> {

@Select("select * from user ${ew.customSqlSegment}")

List<User> selectAllUser(@Param(Constants.WRAPPER)Wrapper<User> wrapper);

List<User> selectUsers(@Param(Constants.WRAPPER)Wrapper<User> wrapper);

}

2.3生成对应的mapper xml，在application.yml配置的目录中

<?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.mesui.service.mapper.UserMapper">

<select id="selectUsers" resultType="com.mesui.service.data.User">

select id,name,age from user ${ew.customSqlSegment}

</select>

</mapper>

2.4 生成test方法

@Test

public void testQueryUsers(){

QueryWrapper<User> wrapper= new QueryWrapper<User>();

wrapper.apply("length(name)={0} or length(name)={1}",3,4);

wrapper.select(User.class,info -> (info.getColumn().equals("id")));

//userMapper.selectList(wrapper);

List<User> maps= userMapper.selectUsers(wrapper);

maps.forEach(System.out::println);

}

2.5 测试结果

2022-10-08 12:57:40.209 DEBUG 10324 --- [ main] c.m.s.mapper.UserMapper.selectUsers : ==> Preparing: select id,name,age from user WHERE (length(name)=? or length(name)=?)

2022-10-08 12:57:40.244 DEBUG 10324 --- [ main] c.m.s.mapper.UserMapper.selectUsers : ==> Parameters: 3(Integer), 4(Integer)

2022-10-08 12:57:40.284 DEBUG 10324 --- [ main] c.m.s.mapper.UserMapper.selectUsers : <== Total: 3

User(id=1, name=Jone, age=18, email=null)

User(id=2, name=Jack, age=20, email=null)

User(id=3, name=Tom, age=28, email=null)