java mybatis分页查询语句_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 {

//注解式

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

List selectAll(@Param(Constants.WRAPPER)Wrapper 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 lambdaQuery = Wrappers.lambdaQuery();//方式三

lambdaQuery.like(User::getName, "雨")

.lt(User::getAge, 40);

List 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 {

//注解式

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

List selectAll(@Param(Constants.WRAPPER)Wrapper wrapper);

//xml文件式，myabtis-plus方式

List selectAll2(@Param(Constants.WRAPPER)Wrapper wrapper);

//xml文件式，myabtis原生方式

List selectAll3(User user);

}

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

select * from user ${ew.customSqlSegment}

select * from user where name like CONCAT('%', #{name}, '%') and age <=#{age};

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 lambdaQuery = Wrappers.lambdaQuery();//方式三

lambdaQuery.like(User::getName, "雨")

.lt(User::getAge, 40);

List users = userMapper.selectAll(lambdaQuery);

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

}

/**

* 自定义sql，mybatis-plus的xml中sql方式

*/

@Test

public void myTest2(){

LambdaQueryWrapper lambdaQuery = Wrappers.lambdaQuery();//方式三

lambdaQuery.like(User::getName, "雨")

.lt(User::getAge, 40);

List 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 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 query = Wrappers.query();

query.ge("age", 20);

Page page = new Page<>(1, 2);

IPage userIPage = userMapper.selectPage(page, query);

//返回map类型

// IPage> mapIPage = userMapper.selectMapsPage(page, query);

System.out.println("总记录数："+userIPage.getTotal());

System.out.println("总页数："+userIPage.getPages());

List 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 {

//自定义分页查询，方式一

IPage selectUserPage(Page page, @Param(Constants.WRAPPER)Wrapper wrapper);

//返回map类型

// IPage> selectUserPage(Page page, @Param(Constants.WRAPPER)Wrapper wrapper);

//自定义分页查询，方式二

IPage> selectUserPage2(Page page, @Param("age") Integer age);

//返回对象类型

// IPage selectUserPage2(Page page, @Param("age") Integer age);

}

2.2.2.在UserMapper.xml中添加SLQ

select * from user ${ew.customSqlSegment}

select * from user where age >=#{age}

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 query = Wrappers.query();

query.ge("age", 30);

Page page = new Page<>(1, 2);

IPage userIPage = userMapper.selectUserPage(page, query);

// IPage> userIPage = userMapper.selectUserPage(page, query);

System.out.println("总记录数："+userIPage.getTotal());

System.out.println("总页数："+userIPage.getPages());

List users = userIPage.getRecords();

// List> users = userIPage.getRecords();

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

}

/**

* 自定义分页查询，方式二

*/

@Test

public void selectMyPage2(){

Page page = new Page<>(1, 2);

IPage> userIPage = userMapper.selectUserPage2(page, 30);

// IPage userIPage = userMapper.selectUserPage2(page, 30);

System.out.println("总记录数："+userIPage.getTotal());

System.out.println("总页数："+userIPage.getPages());

List> users = userIPage.getRecords();

// List users = userIPage.getRecords();

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

}

}