JPQL多表关联查询：如何返回指定字段获取会员借阅书籍信息

Hey there! Since you're new to JPQL, let's break down how to build the exact query you need to fetch the specific fields for borrowed books.

First, let's recap your entity relationships based on the code snippet you shared:

• loan has a one-to-one relationship with book via the Bookid field
• loan has a many-to-one relationship with member via the memberid field

To fetch the specific fields (book.title, book.id, loan.dueDate, member.firstname, member.id), you'll need to explicitly join these entities in your JPQL query and select only the columns you want. Here are two common approaches:

1. Query Returning Object Arrays #

This is a quick way to get the data, though you'll need to cast the array elements to the correct types when processing results:

SELECT b.id, b.title, l.dueDate, m.id, m.firstname
FROM loan l
JOIN l.Bookid b
JOIN l.memberid m

Notes on this query: #

• We use JOIN (inner join) here, which will only return loan records that have a valid associated book and member—perfect for fetching actual borrowed book records.
• Make sure the entity and field names match exactly what's in your Java classes: loan (your entity class name), Bookid (the field in loan linking to book), memberid (the field linking to member).

2. Type-Safe DTO Projection (Recommended) #

For cleaner, type-safe code, create a DTO (Data Transfer Object) class to hold the results. This avoids dealing with raw Object arrays and makes your code more maintainable.

First, create the DTO class:

public class BorrowedBookDetails {
    private Integer bookId;
    private String bookTitle;
    private String loanDueDate;
    private Integer memberId;
    private String memberFirstName;

    // Constructor matching the order of fields in your JPQL select
    public BorrowedBookDetails(Integer bookId, String bookTitle, String loanDueDate, Integer memberId, String memberFirstName) {
        this.bookId = bookId;
        this.bookTitle = bookTitle;
        this.loanDueDate = loanDueDate;
        this.memberId = memberId;
        this.memberFirstName = memberFirstName;
    }

    // Add getters for all fields (setters are optional unless you need them)
    public Integer getBookId() { return bookId; }
    public String getBookTitle() { return bookTitle; }
    public String getLoanDueDate() { return loanDueDate; }
    public Integer getMemberId() { return memberId; }
    public String getMemberFirstName() { return memberFirstName; }
}

Then use this DTO in your JPQL query with the new keyword (make sure to use the full package path of your DTO):

SELECT new com.yourpackage.BorrowedBookDetails(b.id, b.title, l.dueDate, m.id, m.firstname)
FROM loan l
JOIN l.Bookid b
JOIN l.memberid m

When you execute this query, you'll get a list of BorrowedBookDetails objects, each containing exactly the fields you need.

Quick Tips: #

• Java class naming convention uses PascalCase (e.g., Loan instead of loan, Book instead of book). While your current lowercase class names work, fixing this will make your code more readable and avoid confusion with SQL keywords.
• If you ever need to include loans that might not have an associated book/member (unlikely for borrowed books), replace JOIN with LEFT JOIN.

内容的提问来源于stack exchange，提问作者Rampage Joe