溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了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<Account> 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<Account> 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<User> getAll();
}方法二：使用XML
參考上文spring boot 2整合mybatis配置application.properties和mybatis-config.xml等后，
以上面的getAll()方法為例，UserMapper.xml配置如下：
<?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.example.demo.mapper.UserMapper" >
<resultMap id="UserMap" type="com.example.demo.entity.User">
<id column="id" jdbcType="INTEGER" property="id" />
<result property="name" column="name" jdbcType="VARCHAR" />
<!--封裝映射company表數據，user表與company表1對1關系，配置1對1的映射
association:用于配置1對1的映射
屬性property：company對象在user對象中的屬性名
屬性javaType：company屬性的java對象 類型
屬性column：user表中的外鍵引用company表
-->
<association property="company" javaType="com.example.demo.entity.Company" column="company_id">
<id property="id" column="companyid"></id>
<result property="companyName" column="companyname"></result>
</association>
<!--配置1對多關系映射
property：在user里面的List<Account>的屬性名
ofType:當前account表的java類型
column:外鍵
-->
<collection property="accounts" ofType="com.example.demo.entity.Account" column="user_id">
<id property="id" column="accountid"></id>
<result property="accountName" column="accountname"></result>
</collection>
</resultMap>
<select id="getAll" resultMap="UserMap" >
SELECT
u.id,u.name,c.id companyid, c.name companyname, a.id accountid,a.name accountname
FROM user u
LEFT JOIN company c on u.company_id=c.id
LEFT JOIN account a on u.id=a.user_id
</select>
</mapper>四、控制層
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<User> getUsers() {
List<User> users=userMapper.getAll();
return users;
}
}看完上述內容，是不是對Spring boot2基于Mybatis實現多表關聯查詢的方法有進一步的了解，如果還想學習更多內容，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
