springboot+mybatis-plus-join+mysql实现连表查询
1. 简介
Mybatis是目前比较主流的持久层框架，使用非常广泛。Mybatis-Plus是基于Mybatis增强工具包，越来越受到开发人员的喜爱。
在使用Mybatis-Plus开发时，简单的crud可以直接使用其提供的接口或使用条件构造器。但对于关联表的查询，不论Mybatis还是Mybatis-Plus都需要自定义sql实现。
Mybatis-Plus-Join基于Mybatis-Plus继续增强，提供了连表查询操作，简单易用。
Mybatis-Plus-Join地址：https://gitee.com/best_handsome/mybatis-plus-join
2. Mybatis-Plus相关博客
SpringBoot + SpringSecurity + Mybatis-Plus + JWT实现分布式系统认证和授权
SpringBoot + SpringSecurity + Mybatis-Plus + JWT + Redis 实现分布式系统认证和授权（刷新Token和Token黑名单）
SpringBoot + Mybatis-Plus 实现多数据源简单示例
SpringBoot + Layui +Mybatis-plus实现简单后台管理系统（内置安全过滤器）
SpringBoot + Dubbo + Zookeeper +Mybatis-Plus + Mysql 搭建简单示例工程
3. 初始化数据库
CREATE DATABASE `mpj`;
USE `mpj`;
CREATE TABLE IF NOT EXISTS `t_user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`username` varchar(10) COMMENT '用户名称',
`sex` char(1) COMMENT '性别',
`phone` varchar(11) COMMENT '手机号',
PRIMARY KEY (`id`)
) COMMENT = '用户信息表';
INSERT INTO `t_user`(`id`, `username`, `sex`, `phone`) VALUES (1, '张三', '1', '13500000000');
INSERT INTO `t_user`(`id`, `username`, `sex`, `phone`) VALUES (2, '李四', '0', '18311111111');
CREATE TABLE IF NOT EXISTS `t_shipping_address` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` int COMMENT '用户ID',
`address` varchar(255) COMMENT '地址',
`is_default` char(1) DEFAULT 'f' COMMENT '是否默认',
PRIMARY KEY (`id`),
INDEX `idx_user_id`(`user_id`) USING BTREE
) COMMENT = '收货地址表';
INSERT INTO `t_shipping_address`(`id`, `user_id`, `address`, `is_default`) VALUES (1, 1, '陕西省西安市雁塔区', 't');
INSERT INTO `t_shipping_address`(`id`, `user_id`, `address`, `is_default`) VALUES (2, 1, '陕西省西安市未央区', 'f');
INSERT INTO `t_shipping_address`(`id`, `user_id`, `address`, `is_default`) VALUES (3, 2, '陕西省西安市莲湖区', 't');
4. 示例代码
- 创建项目
- 修改pom.xml
<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.c3stones</groupId>
<artifactId>mybatis-plus-join-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>mybatis-plus-join-demo</name>
<description>Mybatis-plus Join Demo</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.1</version>
<relativePath />
</parent>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.github.yulichang</groupId>
<artifactId>mybatis-plus-join</artifactId>
<version>1.2.4</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- 添加配置文件application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/mpj?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull
username: root
password: 123456
# Mybatis-plus配置
mybatis-plus:
mapper-locations: classpath:mapper/*.xml
global-config:
db-config:
id-type: AUTO
configuration:
# 打印sql
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
- 配置Mybtis-Plus分页配置类
/**
* Mybatis-plus 配置类
*
* @author CL
*/
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor paginationInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
}
- 创建实体
/**
* 用户信息
*
* @author CL
*/
@Data
@EqualsAndHashCode(callSuper = false)
@TableName(value = "t_user")
public class User extends Model<User> {
private static final long serialVersionUID = 1L;
@TableId
private Integer id; // ID
@TableField(condition = SqlCondition.LIKE)
private String username; // 用户名称
private String sex; // 性别
private String phone; // 手机号
}
/**
* 收货地址信息
*
* @author CL
*/
@Data
@EqualsAndHashCode(callSuper = false)
@TableName(value = "t_shipping_address")
public class ShippingAddress extends Model<ShippingAddress> {
private static final long serialVersionUID = 1L;
@TableId
private Integer id; // ID
private Integer userId; // 用户ID
private String address; // 地址
private String isDefault; // 是否默认
}
- 创建Mapper
/**
* 用户 Mapper
*
* @author CL
*/
@Mapper
public interface UserMapper extends MPJBaseMapper<User> {
}
/**
* 收货地址 Mapper
*
* @author CL
*/
@Mapper
public interface ShippingAddressMapper extends MPJBaseMapper<ShippingAddress> {
}
- 创建Service
继承MPJBaseService类。
/**
* 用户 Service
*
* @author CL
*/
public interface UserService extends MPJBaseService<User> {
}
- 创建Service实现
继承MPJBaseServiceImpl类。
/**
* 用户 Service实现
*
* @author CL
*/
@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, User> implements UserService {
}
- 创建用户收货地址DTO
/**
* 用户收货地址 DTO
*
* @author CL
*/
@Data
@EqualsAndHashCode(callSuper = true)
public class UserShippingAddressDto extends User {
private static final long serialVersionUID = 1L;
private String address; // 地址
private String def; // 是否默认
}
- 创建Controller
/**
* 用户 Controller
*
* @author CL
*/
@RestController
@RequestMapping(value = "/user")
public class UserController {
@Autowired
private UserService userService;
/**
* 查询用户列表数据
*
* @return
*/
@RequestMapping(value = "/listData")
public List<User> listData() {
return userService.list(Wrappers.emptyWrapper());
}
/**
* 查询用户分页数据
*
* @return
*/
@RequestMapping(value = "/pageData")
public IPage<User> pageData(long current, long size, User user) {
return userService.page(new Page<>(current, size), Wrappers.query(user));
}
/**
* 查询用户默认收货地址
*
* @return
*/
@RequestMapping(value = "/default")
public UserShippingAddressDto userDefaultShippingAddress(UserShippingAddressDto userShippingAddressDto) {
return userService.selectJoinOne(UserShippingAddressDto.class, MPJWrappers.<User>lambdaJoin()
.selectAll(User.class)
.select(ShippingAddress::getAddress)
.selectAs(ShippingAddress::getIsDefault, UserShippingAddressDto::getDef)
.leftJoin(ShippingAddress.class, ShippingAddress::getUserId, User::getId)
.eq(User::getId, userShippingAddressDto.getId())
.eq(ShippingAddress::getIsDefault, "t")
.last("limit 1"));
}
/**
* 查询用户收货地址分页数据
*
* @return
*/
@RequestMapping(value = "/pageAddressData")
public IPage<UserShippingAddressDto> pageUserShippingAddressData(long current, long size, UserShippingAddressDto userShippingAddressDto) {
// Mybatis-plus 对count进行了优化，因此需要关掉优化关联统计sql，详见：https://gitee.com/best_handsome/mybatis-plus-join/issues/I55FIU
return userService.selectJoinListPage(new Page<>(current, size).setOptimizeCountSql(false), UserShippingAddressDto.class, MPJWrappers.<User>lambdaJoin()
.selectAll(User.class)
.select(ShippingAddress::getAddress)
.selectAs(ShippingAddress::getIsDefault, UserShippingAddressDto::getDef)
.leftJoin(ShippingAddress.class, ShippingAddress::getUserId, User::getId)
.eq(Objects.nonNull(userShippingAddressDto.getId()), User::getId, userShippingAddressDto.getId())
.like(StringUtils.isNotEmpty(userShippingAddressDto.getUsername()), User::getUsername, userShippingAddressDto.getUsername())
.eq(StringUtils.isNotEmpty(userShippingAddressDto.getDef()), ShippingAddress::getIsDefault, userShippingAddressDto.getDef()));
}
/**
* 统计用户收货地址数量
*
* @return
*/
@RequestMapping(value = "/countAddress")
public long countUserShippingAddress(UserShippingAddressDto userShippingAddressDto) {
return userService.selectJoinCount(MPJWrappers.lambdaJoin()
.select(ShippingAddress::getAddress)
.leftJoin(ShippingAddress.class, ShippingAddress::getUserId, User::getId)
.eq(User::getId, userShippingAddressDto.getId()));
}
}
- 创建启动类
/**
* 启动类
*
* @author CL
*/
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
5. 单元测试
- 使用Eclipse进行单元测试时，如果出现
No tests found with test runner Junit5，原因是配置的JUnit版本和使用的JUnit不一致。右键Debug As->Debug Configurations...->Test runner，选择JUnit4即可。
/user/listData接口测试
/**
* UserController 单元测试
*
* @author CL
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserControllerTest {
@Autowired
private UserController userController;
private MockMvc mockMvc;
@Before
public void setup() {
mockMvc = MockMvcBuilders.standaloneSetup(userController).build();
}
/**
* /user/listData 接口测试
*/
@Test
public void listDataTest() throws Exception {
mockMvc.perform(MockMvcRequestBuilders.get("/user/listData")).andExpect(status().isOk()) // 增加断言
.andExpect(jsonPath("$[0].id").value(1)) // 增加断言
.andExpect(jsonPath("$[1].id").value(2)) // 增加断言
.andDo(print()) // 打印结果
.andReturn();
}
}
测试结果：
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/listData
Parameters = {}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#listData()
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = [{"id":1,"username":"张三","sex":"1","phone":"13500000000"},{"id":2,"username":"李四","sex":"0","phone":"18311111111"}]
Forwarded URL = null
Redirected URL = null
Cookies = []
/user/pageData接口测试
/**
* UserController 单元测试
*
* @author CL
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserControllerTest {
@Autowired
private UserController userController;
private MockMvc mockMvc;
@Before
public void setup() {
mockMvc = MockMvcBuilders.standaloneSetup(userController).build();
}
/**
* /user/pageData 接口测试
*/
@Test
public void pageDataTest() throws Exception {
mockMvc.perform(MockMvcRequestBuilders.get("/user/pageData").param("current", "1").param("size", "10"))
.andExpectAll(status().isOk()).andExpect(jsonPath("$.total").value(2))
.andDo(print()) // 打印结果
.andReturn();
mockMvc.perform(
MockMvcRequestBuilders.get("/user/pageData").param("current", "1").param("size", "10").param("id", "1"))
.andExpect(status().isOk()).andExpect(jsonPath("$.total").value(1))
.andExpect(jsonPath("$.records[0].id").value(1))
.andDo(print()) // 打印结果
.andReturn();
mockMvc.perform(MockMvcRequestBuilders.get("/user/pageData").param("current", "1").param("size", "10")
.param("username", "张")).andExpect(status().isOk()).andExpect(jsonPath("$.total").value(1))
.andExpect(jsonPath("$.records[0].username").value("张三"))
.andDo(print()) // 打印结果
.andReturn();
}
}
测试结果：
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/pageData
Parameters = {current=[1], size=[10]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#pageData(long, long, User)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = {"records":[{"id":1,"username":"张三","sex":"1","phone":"13500000000"},{"id":2,"username":"李四","sex":"0","phone":"18311111111"}],"total":2,"size":10,"current":1,"orders":[],"optimizeCountSql":true,"searchCount":true,"countId":null,"maxLimit":null,"pages":1}
Forwarded URL = null
Redirected URL = null
Cookies = []
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/pageData
Parameters = {current=[1], size=[10], id=[1]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#pageData(long, long, User)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = {"records":[{"id":1,"username":"张三","sex":"1","phone":"13500000000"}],"total":1,"size":10,"current":1,"orders":[],"optimizeCountSql":true,"searchCount":true,"countId":null,"maxLimit":null,"pages":1}
Forwarded URL = null
Redirected URL = null
Cookies = []
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/pageData
Parameters = {current=[1], size=[10], username=[张]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#pageData(long, long, User)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = {"records":[{"id":1,"username":"张三","sex":"1","phone":"13500000000"}],"total":1,"size":10,"current":1,"orders":[],"optimizeCountSql":true,"searchCount":true,"countId":null,"maxLimit":null,"pages":1}
Forwarded URL = null
Redirected URL = null
Cookies = []
/user/default接口测试
/**
* UserController 单元测试
*
* @author CL
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserControllerTest {
@Autowired
private UserController userController;
private MockMvc mockMvc;
@Before
public void setup() {
mockMvc = MockMvcBuilders.standaloneSetup(userController).build();
}
/**
* /user/default 接口测试
*/
@Test
public void userDefaultShippingAddressTest() throws Exception {
mockMvc.perform(MockMvcRequestBuilders.get("/user/default").param("id", "1")).andExpect(status().isOk())
.andExpect(jsonPath("$.def").value("t"))
.andDo(print()) // 打印结果
.andReturn();
mockMvc.perform(MockMvcRequestBuilders.get("/user/default").param("id", "999"))
.andExpect(status().is2xxSuccessful())
.andDo(print()) // 打印结果
.andReturn();
}
}
测试结果：
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/default
Parameters = {id=[1]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#userDefaultShippingAddress(UserShippingAddressDto)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = {"id":1,"username":"张三","sex":"1","phone":"13500000000","address":"陕西省西安市雁塔区","def":"t"}
Forwarded URL = null
Redirected URL = null
Cookies = []
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/default
Parameters = {id=[999]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#userDefaultShippingAddress(UserShippingAddressDto)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = []
Content type = null
Body =
Forwarded URL = null
Redirected URL = null
Cookies = []
/user/pageAddressData接口测试
/**
* UserController 单元测试
*
* @author CL
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserControllerTest {
@Autowired
private UserController userController;
private MockMvc mockMvc;
@Before
public void setup() {
mockMvc = MockMvcBuilders.standaloneSetup(userController).build();
}
/**
* /user/pageAddressData 接口测试
*/
@Test
public void pageUserShippingAddressDataTest() throws Exception {
mockMvc.perform(MockMvcRequestBuilders.get("/user/pageAddressData").param("current", "1").param("size", "10"))
.andExpect(status().isOk()).andExpect(jsonPath("$.total").value(3))
.andDo(print()) // 打印结果
.andReturn();
}
}
测试结果：
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/pageAddressData
Parameters = {current=[1], size=[10]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#pageUserShippingAddressData(long, long, UserShippingAddressDto)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = {"records":[{"id":1,"username":"张三","sex":"1","phone":"13500000000","address":"陕西省西安市未央区","def":"f"},{"id":1,"username":"张三","sex":"1","phone":"13500000000","address":"陕西省西安市雁塔区","def":"t"},{"id":2,"username":"李四","sex":"0","phone":"18311111111","address":"陕西省西安市莲湖区","def":"t"}],"total":3,"size":10,"current":1,"orders":[],"optimizeCountSql":false,"searchCount":true,"countId":null,"maxLimit":null,"pages":1}
Forwarded URL = null
Redirected URL = null
Cookies = []
/user/countAddress接口测试
/**
* UserController 单元测试
*
* @author CL
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserControllerTest {
@Autowired
private UserController userController;
private MockMvc mockMvc;
@Before
public void setup() {
mockMvc = MockMvcBuilders.standaloneSetup(userController).build();
}
/**
* /user/countAddress 接口测试
*/
@Test
public void countUserShippingAddressTest() throws Exception {
mockMvc.perform(MockMvcRequestBuilders.get("/user/countAddress").param("id", "1")).andExpect(status().isOk())
.andExpect(content().string("2"))
.andDo(print()) // 打印结果
.andReturn();
mockMvc.perform(MockMvcRequestBuilders.get("/user/countAddress").param("id", "2")).andExpect(status().isOk())
.andExpect(content().string("1"))
.andDo(print()) // 打印结果
.andReturn();
}
}
测试结果：
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/countAddress
Parameters = {id=[1]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#countUserShippingAddress(UserShippingAddressDto)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = 2
Forwarded URL = null
Redirected URL = null
Cookies = []
MockHttpServletRequest:
HTTP Method = GET
Request URI = /user/countAddress
Parameters = {id=[2]}
Headers = []
Body = <no character encoding set>
Session Attrs = {}
Handler:
Type = com.c3stones.controller.UserController
Method = com.c3stones.controller.UserController#countUserShippingAddress(UserShippingAddressDto)
Async:
Async started = false
Async result = null
Resolved Exception:
Type = null
ModelAndView:
View name = null
View = null
Model = null
FlashMap:
Attributes = null
MockHttpServletResponse:
Status = 200
Error message = null
Headers = [Content-Type:"application/json"]
Content type = application/json
Body = 1
Forwarded URL = null
Redirected URL = null
Cookies = []
