![cover](https://img-blog.csdnimg.cn/50be48b09693428daaeb2a7973642919.png)
MyBatisPlus——多表查询——多条件查询——分页查询
这四个注解由MyBatis提供,MyBatis-Plus只是对。Mapper之间的调用,需要找到表与表之间的映射关系,的操作进行了增强,对于多表的查询并没有做任何的修改。实现复杂关系映射,可以使用。组合完成复杂关系的配置。
·
1. MyBatis多表查询
实现复杂关系映射,可以使用@Results注解,@Result注解,@One注解,@Many注解
组合完成复杂关系的配置。
这四个注解由MyBatis提供,MyBatis-Plus只是对单表
的操作进行了增强,对于多表的查询并没有做任何的修改。
下边我们就一起实现以下
- 查询用户返回用户信息和订单信息
- 查询订单返回订单信息和用户信息
1.1 引入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.guo</groupId>
<artifactId>springboot-Mybatis-plus</artifactId>
<version>0.0.1-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<version>2.7.0</version>
<artifactId>spring-boot-starter-parent</artifactId>
<relativePath/>
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--SpringBoot框架web项目起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatisPlus集成SpringBoot起步依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<!--MySQL 驱动依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--druid 数据连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
1.2 核心配置文件
#连接数据库
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=123456
#数据源配置 ———— druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 配置日志输出格式——————打印SQL日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
1.3 启动上加注解@MapperScan
@MapperScan("com.guo.springboot.mapper")
1.4 创建实体User和Order
User.java
@TableName("t_user")
public class User {
@TableId(type = IdType.AUTO)
private int id;
private String name;
private String password;
private String birthday;
/**
* 使用MyBatisPlus的接口实现BaseMapper<T>类的时候必须要写这个注解 @TableField(exist = false)
* (告诉它这个属性在数据库中是不存在的,我们需要去做映射)
* */
@TableField(exist = false)
private List<Order> orders;
//此处省略Get和Set方法
}
Order.java
@TableName("t_order")
public class Order {
@TableId(type = IdType.AUTO)
private long id;
private Time orderTime;
private String total;
/**
* 使用MyBatisPlus的接口实现BaseMapper<T>类的时候必须要写这个注解 @TableField(exist = false)
* (告诉它这个属性在数据库中是不存在的,我们需要去做映射)
* */
@TableField(exist = false)
private User user;
//此处省略Get和Set方法
}
1.5 创建UserMapper和OrderMapper接口@One——@Many
Mapper之间的调用,需要找到表与表之间的映射关系,
一对一@One
一对多@Many
UserMapper.java
package com.guo.springboot.mapper;
import com.guo.springboot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapper extends BaseMapper<User>{
//根据用户id查看用户信息
@Select("select * from t_user where id=#{id}")
User selectById(int id);
//查询用户机器所有的订单
//Mapper之间的调用需要借助@Many()
//@Many 表示一对多
@Select("select * from t_user")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "name",property = "name"),
@Result(column = "password",property = "password"),
@Result(column = "id",property = "orders",javaType = List.class,
many = @Many(select = "com.guo.springboot.mapper.OrderMapper.selectByUid")
)
})
List<User> selectAllUserAndOrder();
}
OrderMapper.java
package com.guo.springboot.mapper;
import com.guo.springboot.entity.Order;
import com.guo.springboot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface OrderMapper {
//根据用户id查询订单
@Select("select * from t_order where uid = #{uid}")
Order selectByUid(int uid);
//查询所有的订单,同时查询订单的用户
//@One 表示一对一的关系
@Select("select * from t_order")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(column = "uid",property = "user",javaType = User.class,
one = @One(select = "com.guo.springboot.mapper.UserMapper.selectById")
),
})
List<Order> selectAllOrderAndUser();
}
1.6 创建Controller
UserController.java
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
@Autowired
private OrderMapper orderMapper;
@GetMapping("/userandorder")
public List userAndOrder(){
List<User> users = userMapper.selectAllUserAndOrder();
return users;
}
@GetMapping("userById")
public User userById(int id){
User user = userMapper.selectById(id);
return user;
}
@GetMapping("/orderanduser")
public List orderAndUser(){
return orderMapper.selectAllOrderAndUser();
}
}
1.7 运行访问URL结果截图
1.7.1 查询用户返回用户信息和订单信息
1.7.2 查询订单返回订单信息和用户信息
2. MyBatisPlus多条件查询——QueryWrapper
官网地址:https://baomidou.com/pages/10c804/#abstractwrapper
2.1 控制层调用MyBatisPlus中BaseMapper多条件查询
UserController.java
//多条件查询
@GetMapping("/user/find")
public List findByCond(){
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.eq("name","zhangsan"); //查找用户表中所有名字等于zhangsan的信息
return userMapper.selectList(userQueryWrapper);
}
2.2 运行访问URL结果截图
2.2.1 浏览器访问截图
2.2.2 访问SQL日志打印
3. MyBatisPlus分页查询
引入依赖(配置类中使用的Interceptor拦截器需要jar包)
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-extension</artifactId>
<version>3.5.1</version>
</dependency>
3.1 创建配置类MyBatisPlusConfig
MyBatisPlusConfig.java
package com.guo.springboot.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor paginationInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); //创建一个MybatisPlus拦截器
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);//分页的拦截器
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
}
3.2 创建控制层UserController
UserController.java
//分页查询
@GetMapping("/user/findByPage")
public IPage findByPage(){
//设置起始页和每页的条数
Page<User> userPage = new Page<>(0,2);
IPage iPage = userMapper.selectPage(userPage, null);
return iPage;
}
3.2 运行访问URL结果截图
3.3 访问SQL日志打印
更多推荐
所有评论(0)