Spring Data JPA多表聯(lián)合查詢的實例演示

更新時間：2025年09月18日 11:17:51 作者：UndefinedMan

本文主要介紹了Spring Data JPA多表聯(lián)合查詢的實例演示,包括一對一、一對多、多對一、多對多關(guān)系,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

目前市場上對于ORM框架選型一般只有兩種, 一種是Mybatis/MybatisPlus, 另一種就是JPA, 之前參與的項目都是使用MybatisPlus沒有系統(tǒng)的了解JPA, 而到新的項目組中使用的是JPA正當(dāng)學(xué)習(xí)時發(fā)現(xiàn)網(wǎng)絡(luò)上對于JPA的文章紛繁雜亂, 尤其是對JPA多表聯(lián)查使我看的摸不到頭腦, 所以本著方便自己日后學(xué)習(xí)以及分享開發(fā)經(jīng)驗的出發(fā)點, 寫下這篇文章.

Mysql版本: Mysql8.0

一. 簡單復(fù)習(xí)數(shù)據(jù)庫表之間的關(guān)系

表之間存在哪幾種關(guān)系?

數(shù)據(jù)庫表之間存在多種關(guān)系: 一對一(例子: 一夫一妻), 一對多(例子: 用戶與賬單), 多對多(例子: 學(xué)生與教師)

各個關(guān)系如何關(guān)聯(lián)起來?

一對一: 建立外鍵

一對多: 一對多的關(guān)系我們在多的一方放入一(id)來唯一標(biāo)識

多對多: 多對多的關(guān)系通過一張關(guān)系表來維持多多關(guān)系, 關(guān)系表存儲了兩個表的主鍵作為外鍵

綜上所述: JPA的多表查詢就是基于外鍵來實現(xiàn)的(實際上JPA并沒有對多表查詢做封裝, 完全使用的是底層Hibernate框架的多表查詢實現(xiàn))

Hibernate對多表聯(lián)合查詢的策略: 通過@OneToOne, @OneToMany, @ManyToOne, @ManyToMany注解自動維護表之間的關(guān)聯(lián)關(guān)系, 在CRUD時如果設(shè)計到關(guān)聯(lián)表自動建立外鍵關(guān)系對關(guān)聯(lián)表也執(zhí)行相關(guān)操作, 同時提供多個屬性支撐懶加載, 過刪除等功能.

二. 實現(xiàn)多表聯(lián)查

在application.peoperties中添加如下配置

server.port=9000
#mysql驅(qū)動
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#mysql的jdbc連接
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/${your.database}
#用戶名
spring.datasource.username=root
#密碼
spring.datasource.password=${your.password}
#jpa配置, 顯示執(zhí)行的sql語句
spring.jpa.show-sql=true
#設(shè)置hibernate的方言 MySQL5Dialect
spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect
#自動創(chuàng)建表
spring.jpa.hibernate.ddl-auto=update

包結(jié)構(gòu):

1. 一對一 (OneToOne)

建立兩張表consumer(用戶表), account(賬戶表), 我們規(guī)定一個用戶只能有一個賬戶, 一個賬戶只能屬于一個用戶, 這樣便有了一對一的關(guān)系, 一對一的關(guān)系分為單向一對一, 雙向一對一.

首先設(shè)計這兩個表(因為已經(jīng)設(shè)置了自動創(chuàng)建表所以不用創(chuàng)建數(shù)據(jù)庫表只需要創(chuàng)建實體類映射)

public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Integer custId;

    @Column(name = "cust_name")
    private String custName;

    @Column(name = "cust_address")
    private String custAddress;
}

@Entity
@Table(name = "account")
@Data
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "password")
    private String password;
}

1. 單向一對一

在Consumer中添加對Account的關(guān)聯(lián)

    @OneToOne(targetEntity = Account.class,
            cascade = CascadeType.ALL,
            fetch = FetchType.EAGER,
            optional = false,
            orphanRemoval = false)
    @JoinColumn(name = "account_id")
    private Account account;

這里主要介紹一下@OneToOne()中屬性的含義

1. targetEntity: 目標(biāo)對象, 也就是 "和誰一對一"默認(rèn)值是注解標(biāo)注的對象

2. cascade: 設(shè)置關(guān)聯(lián)操作, 定義了當(dāng)主實體做了一些持久化操作時是否級聯(lián)到關(guān)系實體上

有多個可選值 :

類型	含義	示例說明
ALL	級聯(lián)所有操作	相當(dāng)于 PERSIST + MERGE + REMOVE + REFRESH + DETACH
PERSIST	級聯(lián)保存	當(dāng)保存主實體時，如果關(guān)聯(lián)實體未被保存，則自動保存
MERGE	級聯(lián)合并	更新主實體時，也同步更新關(guān)聯(lián)實體的狀態(tài)（適用于脫離狀態(tài)的對象）
REMOVE	級聯(lián)刪除	刪除主實體時，同時刪除其關(guān)聯(lián)實體
REFRESH	級聯(lián)刷新	刷新主實體時，也刷新關(guān)聯(lián)實體的狀態(tài)（從數(shù)據(jù)庫重新加載數(shù)據(jù)）
DETACH	級聯(lián)分離	主實體從持久化上下文中移除時，關(guān)聯(lián)實體也一并被分離

3. fetch: 定義了是否使用懶加載模式, 默認(rèn)是即刻加載, 懶加載為LAZY

4. optional: 是否允許標(biāo)注對象為空, 默認(rèn)true允許為空

5. orphanRemoval: 是否啟用過刪除, 如果啟用當(dāng)關(guān)系表屬性為null時直接刪除數(shù)據(jù)庫表中對應(yīng)的關(guān)聯(lián)數(shù)據(jù)

@JoinColumn()定義了生成的外鍵名

這樣就將Customer與Account關(guān)聯(lián)起來了.之后在CRUD時Customer時會自動將Account CRUD前提是定義了允許的關(guān)聯(lián)操作等級.

先進行數(shù)據(jù)插入:

@SpringBootTest
public class JPAOneToOneTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)1");
        Account account = new Account();
        account.setName("測試賬戶1");
        customer.setAccount(account);
        customerDao.save(customer);
        System.out.println("---------保存成功----------");
    }
}

數(shù)據(jù)查詢(級聯(lián)):

@SpringBootTest
public class JPAOneToOneTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)1");
        Account account = new Account();
        account.setName("測試賬戶1");
        customer.setAccount(account);
        customerDao.save(customer);
        System.out.println("---------保存成功----------");
    }

    @Test
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(1).get());
    }
}

刪除(級聯(lián)):

@SpringBootTest
public class JPAOneToOneTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)1");
        Account account = new Account();
        account.setName("測試賬戶1");
        customer.setAccount(account);
        customerDao.save(customer);
        System.out.println("---------保存成功----------");
    }

    @Test
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(1).get());
    }

    @Test
    public void test03() {
        customerDao.deleteById(1);
        System.out.println("----------刪除成功----------");
    }
}

修改: 對于修改如果開啟了過刪除, 當(dāng)設(shè)置account為null時會刪除掉account表中沒有被關(guān)聯(lián)的數(shù)據(jù), 注意前提是optinal = true, orphanRemoval = true

@SpringBootTest
public class JPAOneToOneTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)1");
        Account account = new Account();
        account.setName("測試賬戶1");
        customer.setAccount(account);
        customerDao.save(customer);
        System.out.println("---------保存成功----------");
    }

    @Test
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(1).get());
    }

    @Test
    public void test03() {
        customerDao.deleteById(1);
        System.out.println("----------刪除成功----------");
    }

    @Test
    public void test04() {
        Customer customer = new Customer();
        customer.setCustId(2);
        customer.setCustName("測試數(shù)據(jù)2");
        customer.setAccount(null);
        customerDao.save(customer);
    }
}

2. 雙向一對一

如果也在Account中添加@OneToOne() @JoinColumn()這種定義一對一關(guān)系就會再次建立一個外鍵關(guān)聯(lián)Customer, 造成的問題就是互相關(guān)聯(lián)導(dǎo)致兩個表中的數(shù)據(jù)都無法刪除, 可以使用@OneToOne(mappedBy = ...)來解決這個問題, mappedBy的意思就是指定這個關(guān)聯(lián)關(guān)系的維護者, 簡單來說就是總體上只維護一個關(guān)聯(lián)關(guān)系.

    /**
     * mappedBy, 用來標(biāo)識在一個雙向關(guān)系中哪一方是關(guān)系的維護方
     */
    @OneToOne(mappedBy = "account")
    private Customer customer;

這里只測試查詢, 整體與單向一樣, 只是方向不同

@SpringBootTest
public class JPAOneToOneAccountTest {

    @Autowired
    private AccountDao accountDao;

    @Test
    public void test01() {

        System.out.println("Account: " + accountDao.findById(3));

    }
}

這里輸出時會報錯:

java.lang.StackOverflowError
	at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:449)
	at java.lang.StringBuilder.append(StringBuilder.java:142)
	at com.yangyang.jpa.entity.Customer.toString(Customer.java:16)
	at java.lang.String.valueOf(String.java:2994)
	at java.lang.StringBuilder.append(StringBuilder.java:137)
	at com.yangyang.jpa.entity.Account.toString(Account.java:11)
	at java.lang.String.valueOf(String.java:2994)
	at java.lang.StringBuilder.append(StringBuilder.java:137)
	at com.yangyang.jpa.entity.Customer.toString(Customer.java:16)

可以看出是一個棧溢出, 原因是兩個toString循環(huán)調(diào)用了, 因為輸出的前提都是調(diào)用各自的toString, 我們重寫toString來解決一下

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", customer=" + customer.getCustName() +
                '}';
    }

2. 一對多(OneToMany)

建立Message表, 依然自動生成

/**
 * 一對多, 多的一方
 */
@Entity
@Table(name = "message")
@Data
public class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;


    @Column(name = "info")
    private String info;
}

Customer和Message一對多的關(guān)系, 一個用戶對應(yīng)多個消息, 一個消息只屬于一個用戶.

現(xiàn)在對一對多的關(guān)系進行關(guān)聯(lián), 這里@JoinColumn(name)設(shè)置的是放在多的一斷一的字段名

@Entity
@Table(name = "customer")
@Data
public class Customer {


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Integer custId;

    @Column(name = "cust_name")
    private String custName;

    @Column(name = "cust_address")
    private String custAddress;


    // 一對一
    @OneToOne(targetEntity = Account.class,
            cascade = CascadeType.ALL,
            fetch = FetchType.EAGER,
            optional = true,
            orphanRemoval = true)
    @JoinColumn(name = "account_id")
    private Account account;
    
    
    // 一對多
    @OneToMany(cascade = CascadeType.ALL
            , fetch = FetchType.EAGER
            , orphanRemoval = false)
    @JoinColumn(name = "customer_id")
    private List<Message> messages;
}

插入(級聯(lián)):

@SpringBootTest
public class JPAOneToManyTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)3");
        List<Message> messages = new ArrayList<>();
        messages.add(new Message("haha"));
        messages.add(new Message("hehe"));
        customer.setMessages(messages);
        customerDao.save(customer);

    }
}

查詢(級聯(lián))

@SpringBootTest
public class JPAOneToManyTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)3");
        List<Message> messages = new ArrayList<>();
        messages.add(new Message("haha"));
        messages.add(new Message("hehe"));
        customer.setMessages(messages);
        customerDao.save(customer);

    }

    @Test
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(4));
    }
}

刪除(級聯(lián))

@SpringBootTest
public class JPAOneToManyTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)3");
        List<Message> messages = new ArrayList<>();
        messages.add(new Message("haha"));
        messages.add(new Message("hehe"));
        customer.setMessages(messages);
        customerDao.save(customer);

    }

    @Test
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(4));
    }


    @Test
    public void test03() {
        customerDao.deleteById(4);
        System.out.println("----------刪除成功----------");
    }
}

修改(級聯(lián))

@SpringBootTest
public class JPAOneToManyTest {

    @Autowired
    private CustomerDao customerDao;

    @Test
    public void test01() {
        Customer customer = new Customer();
        customer.setCustName("測試數(shù)據(jù)3");
        List<Message> messages = new ArrayList<>();
        messages.add(new Message("haha"));
        messages.add(new Message("hehe"));
        customer.setMessages(messages);
        customerDao.save(customer);

    }

    @Test
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(4));
    }


    @Test
    public void test03() {
        customerDao.deleteById(4);
        System.out.println("----------刪除成功----------");
    }

    @Test
    public void test04() {
        Customer customer = new Customer();
        customer.setCustId(5);
        Message message = new Message();
        message.setId(3);
        message.setInfo("更改數(shù)據(jù)1");
        Message message1 = new Message();
        message1.setId(4);
        message1.setInfo("更改數(shù)據(jù)2");
        List<Message> messages = new ArrayList<>();
        messages.add(message);
        messages.add(message1);
        customer.setMessages(messages);
        customerDao.save(customer);
    }
}

3. 多對一(ManyToOne)

message與customer之間是多對一的關(guān)系

定義多對一關(guān)系:

@Entity
@Table(name = "message")
@Data
public class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;


    @Column(name = "info")
    private String info;

    public Message(String info) {
        this.info = info;
    }

    public Message() {
    }

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "customer_id")
    private Customer customer;
}

以查詢(級聯(lián))為例, 其他同一對多

@SpringBootTest
public class JPAManyToOneTest {

    @Autowired
    private MessageDao messageDao;

    @Test
    public void test01() {
        System.out.println(messageDao.findById(3));
    }
}

4. 多對多

多對多關(guān)系分為單向多對多, 雙向多對多

首先還是建立測試表: role, 依然是自動生成

@Entity
@Table(name = "role")
@Data
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;


    @Column(name = "role_name")
    private String rName;

}

仔細想一下, 其實多對多關(guān)系與一對多關(guān)系很相似, 問題就在于多對多關(guān)系被拆分了, 拆分為兩個表對一個中間表的一對多關(guān)系, 那么關(guān)鍵就在于定義一個中間關(guān)系表, 同時JPA也會自動為我們維護這個關(guān)系表無需自己建立.

現(xiàn)在來定義多對多的關(guān)系

@Entity
@Table(name = "customer")
@Data
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Integer custId;

    @Column(name = "cust_name")
    private String custName;

    @Column(name = "cust_address")
    private String custAddress;


    // 一對一
    @OneToOne(targetEntity = Account.class,
            cascade = CascadeType.ALL,
            fetch = FetchType.EAGER,
            optional = true,
            orphanRemoval = true)
    @JoinColumn(name = "account_id")
    private Account account;


    // 一對多
    @OneToMany(cascade = CascadeType.ALL
            , fetch = FetchType.EAGER
            , orphanRemoval = false)
    @JoinColumn(name = "customer_id")
    private List<Message> messages;
    
    
    // 多對多
    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinTable(
            name = "customer_role_relation", // 中間關(guān)系表名
            joinColumns = {@JoinColumn(name = "c_id")}, // 當(dāng)前表的關(guān)聯(lián)外鍵名
            inverseJoinColumns = {@JoinColumn(name = "r_id")} // 另一個多表的關(guān)聯(lián)外鍵名
    )
    private List<Role> role;
}

著重說一下@JoinTable, 因為需要維護一個關(guān)聯(lián)表, 所以不能使用@JoinColumn, 其中三個參數(shù)的意義是name: 關(guān)系表表名, joinColumns: 當(dāng)前表關(guān)聯(lián)外鍵名, inverseJoinColumns: 另一個表的關(guān)聯(lián)外鍵名

插入(級聯(lián)):

@SpringBootTest
public class JPAManyToManyTest {

    @Autowired
    private CustomerDao customerDao;


    @Test
    public void test01() {
        List<Role> rols = new ArrayList<>();
        Role role = new Role();
        role.setRName("測試role1");
        Role role1 = new Role();
        role1.setRName("測試role2");
        rols.add(role);
        rols.add(role1);
        Customer customer = new Customer();
        customer.setCustName("多對多測試數(shù)據(jù)1");
        customer.setRole(rols);
        customerDao.save(customer);
    }
}

查詢(級聯(lián))

@SpringBootTest
public class JPAManyToManyTest {

    @Autowired
    private CustomerDao customerDao;


    @Test
    public void test01() {
        List<Role> rols = new ArrayList<>();
        Role role = new Role();
        role.setRName("測試role1");
        Role role1 = new Role();
        role1.setRName("測試role2");
        rols.add(role);
        rols.add(role1);
        Customer customer = new Customer();
        customer.setCustName("多對多測試數(shù)據(jù)1");
        customer.setRole(rols);
        customerDao.save(customer);
    }

    @Test
    @Transactional
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(6).get());
    }
}

刪除(級聯(lián)):

@SpringBootTest
public class JPAManyToManyTest {

    @Autowired
    private CustomerDao customerDao;


    @Test
    public void test01() {
        List<Role> rols = new ArrayList<>();
        Role role = new Role();
        role.setRName("測試role1");
        Role role1 = new Role();
        role1.setRName("測試role2");
        rols.add(role);
        rols.add(role1);
        Customer customer = new Customer();
        customer.setCustName("多對多測試數(shù)據(jù)1");
        customer.setRole(rols);
        customerDao.save(customer);
    }

    @Test
    @Transactional
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(6).get());
    }

    @Test
    public void test03() {
        customerDao.deleteById(6);
        System.out.println("----------刪除成功----------");
    }
}

刪除這里涉及到一個問題, 如果關(guān)聯(lián)表中存在其他引用關(guān)系例如另一個customer也對role有引用, 那么這次這次刪除就會失敗

修改(級聯(lián)):

@SpringBootTest
public class JPAManyToManyTest {

    @Autowired
    private CustomerDao customerDao;

    @Autowired
    private RoleDao roleDao;


    @Test
    public void test01() {
        List<Role> rols = new ArrayList<>();
        Role role = new Role();
        role.setRName("測試role1");
        Role role1 = new Role();
        role1.setRName("測試role2");
        rols.add(role);
        rols.add(role1);
        Customer customer = new Customer();
        customer.setCustName("多對多測試數(shù)據(jù)1");
        customer.setRole(rols);
        customerDao.save(customer);
    }

    @Test
    @Transactional
    public void test02() {
        System.out.println("Customer: " + customerDao.findById(6).get());
    }

    @Test
    public void test03() {
        customerDao.deleteById(6);
        System.out.println("----------刪除成功----------");
    }

    @Test
    @Transactional
    @Commit
    public void test04() {
//        Role role = new Role();
//        role.setId(3);
//        role.setRName("測試role3");
//        Role role1 = new Role();
//        role1.setId(4);
//        role1.setRName("測試role4");
        List<Role> roles = new ArrayList<>();
        Role role1 = roleDao.findById(3).get();
        roles.add(role1);
        Role role2 = roleDao.findById(4).get();
        roles.add(role2);
        Customer customer = new Customer();
        customer.setCustName("多對多測試數(shù)據(jù)2");
        customer.setRole(roles);
        customerDao.save(customer);
    }
}

這里注意一下, 修改時不可以手動對象id賦值來指定修改哪個字段, 因為在Hibernate中實體有四種狀態(tài):

Transient（瞬時態(tài)）	對象剛 new 出來，尚未與數(shù)據(jù)庫關(guān)聯(lián)，也未被 EntityManager 管理
Persistent（持久態(tài)）	已被 EntityManager 管理，比如通過 `find()`、`merge()` 獲取或保存的對象
Detached（游離態(tài)）	曾經(jīng)是持久態(tài)，但現(xiàn)在不再被 EntityManager 管理（例如 session 關(guān)閉后）
Removed（刪除態(tài)）	已從數(shù)據(jù)庫中刪除

我們直接賦值指定的對象被認(rèn)為是游離態(tài)的, 需要是持久態(tài)才可以進行更改.簡單來說就是在數(shù)據(jù)庫中查出來的才可以證明是存在的才可以進行關(guān)聯(lián).

補充: 想要關(guān)聯(lián)Role表中已經(jīng)存在的字段:

    /**
     * 補充, 想要關(guān)聯(lián)role表中已經(jīng)存在的字段
     */
    @Test
    @Transactional
    @Commit
    public void test05() {
        List<Role> rols = new ArrayList<>();
        Role role = roleDao.findById(3).get();
        rols.add(role);
        Role role2 = roleDao.findById(4).get();
        rols.add(role2);

        Customer customer = new Customer();
        customer.setCustName("補充測試數(shù)據(jù)");
        customer.setRole(rols);
        customerDao.save(customer);
    }