扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
这篇文章主要讲解了Spring boot2基于Mybatis实现多表关联查询的方法，内容清晰明了，对此有兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。
成都创新互联公司凭借在网站建设、网站推广领域领先的技术能力和多年的行业经验，为客户提供超值的营销型网站建设服务，我们始终认为：好的营销型网站就是好的业务员。我们已成功为企业单位、个人等客户提供了成都做网站、网站建设服务，以良好的商业信誉，完善的服务及深厚的技术力量处于同行领先地位。
模拟业务关系：
一个用户user有对应的一个公司company，每个用户有多个账户account。
spring boot 2的环境搭建见上文：spring boot 2整合mybatis
一、MySQL创表和模拟数据sql
CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `company_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `company` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `user` VALUES (1, 'aa', 1), (2, 'bb', 2); INSERT INTO `company` VALUES (1, 'xx公司'), (2, 'yy公司'); INSERT INTO `account` VALUES (1, '中行', 1), (2, '工行', 1), (3, '中行', 2);
二、创建实体
public class User {
private Integer id;
private String name;
private Company company;
private List accounts;
//getter/setter 这里省略...
}
public class Company {
private Integer id;
private String companyName;
//getter/setter 这里省略...
}
public class Account {
private Integer id;
private String accountName;
//getter/setter 这里省略...
} 三、开发Mapper
方法一：使用注解
1、AccountMapper.java
package com.example.demo.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.example.demo.entity.Account;
public interface AccountMapper {
/*
* 根据用户id查询账户信息
*/
@Select("SELECT * FROM `account` WHERE user_id = #{userId}")
@Results({
@Result(property = "accountName", column = "name")
})
List getAccountByUserId(Long userId);
} 2、CompanyMapper.java
package com.example.demo.mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.example.demo.entity.Company;
public interface CompanyMapper {
/*
* 根据公司id查询公司信息
*/
@Select("SELECT * FROM company WHERE id = #{id}")
@Results({
@Result(property = "companyName", column = "name")
})
Company getCompanyById(Long id);
}3、UserMapper.java
package com.example.demo.mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Many;
import com.example.demo.entity.User;
public interface UserMapper {
/*
* 一对一查询
* property：查询结果赋值给此实体属性
* column：对应数据库的表字段，做为下面@One(select方法的查询参数
* one：一对一的查询
* @One(select = 方法全路径) ：调用的方法
*/
@Select("SELECT * FROM user WHERE id = #{id}")
@Results({
@Result(property = "company", column = "company_id", one = @One(select = "com.example.demo.mapper.CompanyMapper.getCompanyById"))
})
User getUserWithCompany(Long id);
/*
* 一对多查询
* property：查询结果赋值给此实体属性
* column：对应数据库的表字段，可做为下面@One(select方法)的查询参数
* many：一对多的查询
* @Many(select = 方法全路径) ：调用的方法
*/
@Select("SELECT * FROM user WHERE id = #{id}")
@Results({
@Result(property = "id", column = "id"),//加此行，否则id值为空
@Result(property = "accounts", column = "id", many = @Many(select = "com.example.demo.mapper.AccountMapper.getAccountByUserId"))
})
User getUserWithAccount(Long id);
/*
* 同时用一对一、一对多查询
*/
@Select("SELECT * FROM user")
@Results({
@Result(property = "id", column = "id"),
@Result(property = "company", column = "company_id", one = @One(select = "com.example.demo.mapper.CompanyMapper.getCompanyById")),
@Result(property = "accounts", column = "id", many = @Many(select = "com.example.demo.mapper.AccountMapper.getAccountByUserId"))
})
List getAll();
} 方法二：使用XML
参考上文spring boot 2整合mybatis配置application.properties和mybatis-config.xml等后，
以上面的getAll()方法为例，UserMapper.xml配置如下：
<?xml version="1.0" encoding="UTF-8" ?>
四、控制层
package com.example.demo.web;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
@RestController
public class UserController {
@Autowired
private UserMapper userMapper;
//请求例子：http://localhost:9001/getUserWithCompany/1
/*请求结果：{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":null}*/
@RequestMapping("/getUserWithCompany/{id}")
public User getUserWithCompany(@PathVariable("id") Long id) {
User user = userMapper.getUserWithCompany(id);
return user;
}
//请求例子：http://localhost:9001/getUserWithAccount/1
/*请求结果：{"id":1,"name":"aa","company":null,"accounts":[{"id":1,"accountName":"中行"},{"id":2,"accountName":"工行"}]}*/
@RequestMapping("/getUserWithAccount/{id}")
public User getUserWithAccount(@PathVariable("id") Long id) {
User user = userMapper.getUserWithAccount(id);
return user;
}
//请求例子：http://localhost:9001/getUserWithAccount/1
/*请求结果：[{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":[{"id":1,"accountName":"中行"},
{"id":2,"accountName":"工行"}]},{"id":2,"name":"bb","company":{"id":2,"companyName":"yy公司"},"accounts":[{"id":3,"accountName":"中行"}]}]*/
@RequestMapping("/getUsers")
public List getUsers() {
List users=userMapper.getAll();
return users;
}
} 看完上述内容，是不是对Spring boot2基于Mybatis实现多表关联查询的方法有进一步的了解，如果还想学习更多内容，欢迎关注创新互联行业资讯频道。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网