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);
}
}