4.MybatisPlus 自定义sql及分页查询

1.自定义sql

版本需要大于3.0.7

1.1.使用注解式

1.1.1.在UserMapper.java中添加自定义方法

package com.mp.dao;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.mp.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Description 数据访问层
 * @Author LL
 * @Date 2020-03-22 12:31
 */
public interface UserMapper extends BaseMapper<User> {

    //注解式
    @Select("select * from user ${ew.customSqlSegment}")
    List<User> selectAll(@Param(Constants.WRAPPER)Wrapper<User> wrapper);
}

1.1.2.测试

package com.mp;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mp.dao.UserMapper;
import com.mp.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;

/**
 * @Description 测试类，自定义sql
 * @Author LL
 * @Date 2020-03-22 18:35
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class CustomSqlTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 自定义sql，注解式
     */
    @Test
    public void myTest(){
        LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();//方式三
        lambdaQuery.like(User::getName, "雨")
                .lt(User::getAge, 40);
        List<User> users = userMapper.selectAll(lambdaQuery);
        users.forEach(System.out::println);
    }
}

1.2.使用xml方式

1.2.1.在配置文件中指定xml文件所在位置

# mapper.xml文件位置
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

1.2.2.在UserMapper.java文件中添加自定义方法

package com.mp.dao;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.mp.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;

/**
 * @Description 数据访问层
 * @Author LL
 * @Date 2020-03-22 12:31
 */
public interface UserMapper extends BaseMapper<User> {

    //注解式
    @Select("select * from user ${ew.customSqlSegment}")
    List<User> selectAll(@Param(Constants.WRAPPER)Wrapper<User> wrapper);

    //xml文件式，myabtis-plus方式
    List<User> selectAll2(@Param(Constants.WRAPPER)Wrapper<User> wrapper);

    //xml文件式，myabtis原生方式
    List<User> selectAll3(User user);
    
}

1.2.3.在resources包下新建mapper包，并添加UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mp.dao.UserMapper">

    <select id="selectAll2" resultType="com.mp.entity.User">
      select * from user ${ew.customSqlSegment}
    </select>

    <select id="selectAll3" resultType="com.mp.entity.User">
      select * from user where name like CONCAT('%', #{name}, '%') and age &lt;=#{age};
    </select>

</mapper>

1.2.4.在测试类中测试

package com.mp;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.mp.dao.UserMapper;
import com.mp.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;

/**
 * @Description 测试类，自定义sql
 * @Author LL
 * @Date 2020-03-22 18:35
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class CustomSqlTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 自定义sql，注解式
     */
    @Test
    public void myTest(){
        LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();//方式三
        lambdaQuery.like(User::getName, "雨")
                .lt(User::getAge, 40);
        List<User> users = userMapper.selectAll(lambdaQuery);
        users.forEach(System.out::println);
    }

    /**
     * 自定义sql，mybatis-plus的xml中sql方式
     */
    @Test
    public void myTest2(){
        LambdaQueryWrapper<User> lambdaQuery = Wrappers.lambdaQuery();//方式三
        lambdaQuery.like(User::getName, "雨")
                .lt(User::getAge, 40);
        List<User> users = userMapper.selectAll2(lambdaQuery);
        users.forEach(System.out::println);
    }

    /**
     * 自定义sql，mybatis的xml中sql方式
     */
    @Test
    public void myTest3(){
        User user = new User();
        user.setName("雨").setAge(40);
        List<User> users = userMapper.selectAll3(user);
        users.forEach(System.out::println);
    }

}

2.分页查询

添加分页配置类 MybatisPlusConfig.java 配置内容如下：

package com.mp.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Description mybatis-plus配置类
 * @Author LL
 * @Date 2020-03-22 22:40
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

2.1.mybatis-plus分页

package com.mp;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mp.dao.UserMapper;
import com.mp.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Map;

/**
 * @Description 测试类，分页测试
 * @Author LL
 * @Date 2020-03-22 22:45
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class PageTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 分页查询
     */
    @Test
    public void selectPage(){
        QueryWrapper<User> query = Wrappers.query();
        query.ge("age", 20);
        Page<User> page = new Page<>(1, 2);
        IPage<User> userIPage = userMapper.selectPage(page, query);
         //返回map类型
//        IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, query);
        System.out.println("总记录数："+userIPage.getTotal());
        System.out.println("总页数："+userIPage.getPages());
        List<User> users = userIPage.getRecords();
        users.forEach(System.out::println);
    }
}

2.2.自定义分页

2.2.1.在UserMapper.java中添加分页方法

package com.mp.dao;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mp.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @Description 数据访问层
 * @Author LL
 * @Date 2020-03-22 12:31
 */
public interface UserMapper extends BaseMapper<User> {

    //自定义分页查询，方式一
    IPage<User> selectUserPage(Page<User> page, @Param(Constants.WRAPPER)Wrapper<User> wrapper);
      //返回map类型
//    IPage<Map<String, Object>> selectUserPage(Page<User> page, @Param(Constants.WRAPPER)Wrapper<User> wrapper);

    //自定义分页查询，方式二
    IPage<Map<String, Object>> selectUserPage2(Page<User> page, @Param("age") Integer age);
      //返回对象类型
//    IPage<User> selectUserPage2(Page<User> page, @Param("age") Integer age);

}

2.2.2.在UserMapper.xml中添加SLQ

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mp.dao.UserMapper">

    <select id="selectUserPage" resultType="com.mp.entity.User">
       select * from user ${ew.customSqlSegment}
    </select>

    <select id="selectUserPage2" resultType="map">
      select * from user where age &gt;=#{age}
    </select>

</mapper>

2.2.4.在测试类中添加测试方法并测试

package com.mp;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mp.dao.UserMapper;
import com.mp.entity.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.List;
import java.util.Map;

/**
 * @Description 测试类，分页测试
 * @Author LL
 * @Date 2020-03-22 22:45
 */
@SpringBootTest
@RunWith(SpringRunner.class)
public class PageTest {

    @Autowired
    private UserMapper userMapper;

    /**
     * 自定义分页查询，方式一
     */
    @Test
    public void selectMyPage(){
        QueryWrapper<User> query = Wrappers.query();
        query.ge("age", 30);
        Page<User> page = new Page<>(1, 2);
        IPage<User> userIPage = userMapper.selectUserPage(page, query);
//        IPage<Map<String, Object>> userIPage = userMapper.selectUserPage(page, query);
        System.out.println("总记录数："+userIPage.getTotal());
        System.out.println("总页数："+userIPage.getPages());
        List<User> users = userIPage.getRecords();
//        List<Map<String, Object>> users = userIPage.getRecords();
        users.forEach(System.out::println);
    }

    /**
     * 自定义分页查询，方式二
     */
    @Test
    public void selectMyPage2(){
        Page<User> page = new Page<>(1, 2);
        IPage<Map<String, Object>> userIPage = userMapper.selectUserPage2(page, 30);
//        IPage<User> userIPage = userMapper.selectUserPage2(page, 30);
        System.out.println("总记录数："+userIPage.getTotal());
        System.out.println("总页数："+userIPage.getPages());
        List<Map<String, Object>> users = userIPage.getRecords();
//        List<User> users = userIPage.getRecords();
        users.forEach(System.out::println);
    }
}