<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.6.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-webflux</artifactId>
<version>2.6.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.assertj</groupId>
<artifactId>assertj-core</artifactId>
<version>3.21.0</version>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>2.0.8</version>
</dependency>
</dependencies>
server:
port: 8080
spring:
application:
name: demo07
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT&useSSL=false
username: root
password: 123456
main:
allow-circular-references: true
devtools:
restart:
enabled: true
mybatis:
configuration:
map-underscore-to-camel-case: true
mybatis-plus:
mapper-locations: classpath:/mapper/*Mapper.xml
logging:
level:
com:
ychen:
mybatis: debug
@Configuration
@MapperScan("com.ychen.mybatis.mapper")
public class MybatisPlusConfig {
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
}
}
@Data
public class Children {
private Long id;
private String name;
private Long userId;
}
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
}
@Mapper
public interface UserMapper extends BaseMapper<User> {
/**
* 3.x 的 page 可以进行取值,多个入参记得加上注解
* 自定义 page 类必须放在入参第一位
* 返回值可以用 IPage<T> 接收 也可以使用入参的 MyPage<T> 接收
* <li> 3.1.0 之前的版本使用注解会报错,写在 xml 里就没事 </li>
* <li> 3.1.0 开始支持注解,但是返回值只支持 IPage ,不支持 IPage 的子类</li>
*
* @param myPage 自定义 page
* @return 分页数据
*/
// @Select("select * from user where (age = #{pg.selectInt} and name = #{pg.selectStr}) or (age = #{ps.yihao} and name = #{ps.erhao})")
MyPage<User> mySelectPage(@Param("pg") MyPage<User> myPage, @Param("ps") ParamSome paramSome);
@ResultMap("userChildrenMap")
@Select("<script>select u.id,u.name,u.email,u.age,c.id as \"c_id\",c.name as \"c_name\",c.user_id as \"c_user_id\" " +
"from user u " +
"left join children c on c.user_id = u.id " +
"<where>" +
"<if test=\"selectInt != null\"> " +
"and u.age = #{selectInt} " +
"</if> " +
"<if test=\"selectStr != null and selectStr != ''\"> " +
"and c.name = #{selectStr} " +
"</if> " +
"</where>" +
"</script>")
MyPage<UserChildren> userChildrenPage(MyPage<UserChildren> myPage);
MyPage<User> mySelectPageMap(MyPage<User> pg, Map<String, Object> map);
List<User> mySelectMap(Map<String, Object> param);
List<User> myPageSelect(MyPage<User> myPage);
List<User> iPageSelect(IPage<User> myPage);
List<User> rowBoundList(RowBounds rowBounds, Map<String, Object> map);
}
<?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.ychen.mybatis.mapper.UserMapper">
<resultMap id="userChildrenMap" type="com.ychen.mybatis.model.UserChildren">
<id column="id" property="id"/>
<result column="age" property="age"/>
<result column="email" property="email"/>
<result column="name" property="name"/>
<collection property="c" ofType="com.ychen.mybatis.entity.Children" columnPrefix="c_">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="user_id" property="userId"/>
</collection>
</resultMap>
<select id="mySelectPage" resultType="com.ychen.mybatis.entity.User">
select *
from user
where (age = #{pg.selectInt} and name = #{pg.selectStr})
or (age = #{ps.yihao} and name = #{ps.erhao})
</select>
<select id="mySelectPageMap" resultType="com.ychen.mybatis.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
<select id="mySelectMap" resultType="com.ychen.mybatis.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
<select id="myPageSelect" resultType="com.ychen.mybatis.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like '%'||#{name}||'%'
</if>
</where>
</select>
<select id="iPageSelect" resultType="com.ychen.mybatis.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
<select id="rowBoundList" resultType="com.ychen.mybatis.entity.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
</mapper>
# 接口
public interface IUserService extends IService<User> {
}
# 实现
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
@RestController
public class TestController {
@Resource
private UserMapper mapper;
/**
* 分页查询
* @return
*/
@RequestMapping("/test0")
@ResponseBody
public String test0(){
// 分页查询
Page<User> page = new Page<>(1, 3);
// 查询age为1，并按照升序排序
Page<User> result = mapper.selectPage(page, Wrappers.<User>lambdaQuery().ge(User::getAge, 1).orderByAsc(User::getAge));
result.getRecords().forEach(System.out::println);
// 验证
// assertThat(result.getTotal()).isGreaterThan(3);
// assertThat(result.getRecords().size()).isEqualTo(3);
return "success";
}
/**
* 分页查询
* @return
*/
@RequestMapping("/test1")
@ResponseBody
public String test1(){
// 分页查询
Page<User> page = new Page<>(1, 5);
// 升序排序
page.addOrder(OrderItem.asc("age"));
// age为20，根据name模糊查询
Page<User> userIPage = mapper.selectPage(page, Wrappers.<User>lambdaQuery().eq(User::getAge, 20).like(User::getName, "Jack"));
// 打印出查询结果
userIPage.getRecords().forEach(System.out::println);
return "success";
}
/**
* 序列化
* @return
*/
@RequestMapping("/test2")
@ResponseBody
public String test2(){
// 分页查询
Page<User> page = new Page<>(1, 5);
// 升序排序
page.addOrder(OrderItem.asc("age"));
// age为20，根据name模糊查询
Page<User> userIPage = mapper.selectPage(page, Wrappers.<User>lambdaQuery().eq(User::getAge, 20).like(User::getName, "Jack"));
String json = JSON.toJSONString(page);
System.out.println(json);
Page<User> page1 = JSON.parseObject(json, new TypeReference<Page<User>>() {});
List<User> records1 = page1.getRecords();
records1.forEach(System.out::println);
return "success";
}
/**
* 分页查询
* @return
*/
@RequestMapping("/test3")
@ResponseBody
public String test3(){
MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
ParamSome paramSome = new ParamSome(20, "Jack");
MyPage<User> userMyPage = mapper.mySelectPage(myPage, paramSome);
userMyPage.getRecords().forEach(System.out::println);
return "success";
}
/**
* 连接查询
* @return
*/
@RequestMapping("/test4")
@ResponseBody
public String test4(){
MyPage<UserChildren> myPage = new MyPage<>(1, 5);
myPage.setSelectInt(18).setSelectStr("Jack");
MyPage<UserChildren> userChildrenMyPage = mapper.userChildrenPage(myPage);
List<UserChildren> records = userChildrenMyPage.getRecords();
records.forEach(System.out::println);
return "success";
}
/**
* 连接查询
* @return
*/
@RequestMapping("/test5")
@ResponseBody
public String test5(){
MyPage<UserChildren> myPage = new MyPage<>(1, 5);
myPage = new MyPage<UserChildren>(1, 5).setSelectInt(18);
MyPage<UserChildren> userChildrenMyPage = mapper.userChildrenPage(myPage);
List<UserChildren> records = userChildrenMyPage.getRecords();
records.forEach(System.out::println);
return "success";
}
/**
* 打印
*/
// private <T> void print(List<T> list) {
// if (!CollectionUtils.isEmpty(list)) {
// list.forEach(System.out::println);
// }
// }
/**
* 模糊查询
* 测试失败
* @return
*/
@RequestMapping("/test6")
@ResponseBody
public String test6(){
MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
mapper.mySelectPageMap(myPage, Maps.newHashMap("name", "%a"));
myPage.getRecords().forEach(System.out::println);
return "success";
}
/**
* 模糊查询
* 测试成功
* @return
*/
@RequestMapping("/test7")
@ResponseBody
public String test7(){
mapper.mySelectMap(Maps.newHashMap("name", "%a%")).forEach(System.out::println);
return "success";
}
/**
* 模糊查询
* 测试成功
* @return
*/
@RequestMapping("/test8")
@ResponseBody
public String test8(){
MyPage<User> page = new MyPage<>(1, 5);
page.setName("a");
mapper.myPageSelect(page).forEach(System.out::println);
return "success";
}
/**
* 分页查询
* @return
*/
@RequestMapping("/test9")
@ResponseBody
public String test9(){
IPage<User> page = new Page<User>(1, 5) {
private String name = "%";
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
};
List<User> list = mapper.iPageSelect(page);
// System.out.println("list.size=" + list.size());
// System.out.println("page.total=" + page.getTotal());
return "success";
}
/**
* 分页查询
* @return
*/
@RequestMapping("/test10")
@ResponseBody
public String test10(){
//使用三参数的构造器创建Page对象
//第三个参数isSearchCount：传true则查询总记录数;传false则不查询总记录数（既不进行count查询）
Page<User> page = new Page<>(1,3,false);
Page<User> result = mapper.selectPage(page, Wrappers.<User>lambdaQuery().ge(User::getAge, 20));
//assertThat(result.getRecords().size()).isEqualTo(3);
//因为没有进行count查询，total值为0
//assertThat(result.getTotal()).isEqualTo(0);
return "success";
}
/**
* 模糊查询
* @return
*/
@RequestMapping("/test11")
@ResponseBody
public String test11(){
RowBounds rowBounds = new RowBounds(0, 5);
List<User> list = mapper.rowBoundList(rowBounds, Maps.newHashMap("name", "%"));
System.out.println("list.size=" + list.size());
return "success";
}
/**
* 分组
* @return
*/
@RequestMapping("/test12")
@ResponseBody
public String test12(){
LambdaQueryWrapper<User> lq = new LambdaQueryWrapper<>();
lq.select(User::getAge).groupBy(User::getAge);
for (User user : mapper.selectList(lq)) {
System.out.println(user.getAge());
}
return "success";
}
@Autowired
IUserService userService;
@RequestMapping("/test13")
@ResponseBody
public String test13(){
LambdaQueryChainWrapper<User> wrapper2 = userService.lambdaQuery();
wrapper2.like(User::getName, "a");
userService.page(new Page<>(1, 10), wrapper2.getWrapper()).getRecords().forEach(System.out::print);
return "success";
}
@RequestMapping("/test14")
@ResponseBody
public String test14(){
userService.lambdaQuery().like(User::getName, "a").list().forEach(System.out::println);
Page page = userService.lambdaQuery().like(User::getName, "a").page(new Page<>(1, 10));
page.getRecords().forEach(System.out::println);
return "success";
}
}
