jpa 从postgresdb返回相同行的列表

7ajki6be  于 2022-11-14  发布在  其他
关注(0)|答案(2)|浏览(134)

我遇到了一个问题,在一个列表中,只有第一行被多次返回,尽管SQL查询显示返回的是不同的行。我的jpa查询是:

@Query(value = "SELECT BK.ISBN," +
            "BK.AUTHOR, " +
            "BK.ID, " +
            "BK.PUBLISHER, " +
            "BK.GENRE, " +
            "BK.NUMBER_OF_PAGES, " +
            "BK.WEIGHT, " +
            "BK.PRICE, " +
            "BK.BOOK_TYPE, " +
            "BK.TITLE, " +
            "RV.STAR_RATING, " +
            "RV.REVIEW_DESCRIPTION, " +
            "RV.ID, " +
            "RV.ISBN, " +
            "RV.ID AS REVIEWID " +
            "FROM BOOKSTORE.BOOKS BK " +
            "INNER JOIN BOOKSTORE.REVIEWS RV " +
            "ON BK.ISBN = RV.ISBN " +
            "WHERE BK.ISBN = :ISBN",
            nativeQuery = true
    )
    List<BookAndReview> getBookAndReviews(String ISBN);

对象是:

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
public class BookAndReview implements Serializable {

    @Id
    @Column(name = "ID")
    Integer id;

    @Column(name = "REVIEWID")
    Integer reviewId;

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

    @Column(name = "STAR_RATING")
    float starRating;

    @Column(name = "REVIEW_DESCRIPTION")
    String reviewDescription;

    @Column(name = "AUTHOR")
    String author;

    @Column(name = "PUBLISHER")
    String publisher;

    @Column(name = "GENRE")
    String genre;

    @Column(name = "NUMBER_OF_PAGES")
    Integer numberOfPages;

    @Column(name = "WEIGHT")
    String weight;

    @Column(name = "PRICE")
    float price;

    @Column(name = "BOOK_TYPE")
    String bookType;

    @Column(name = "TITLE")
    String title;

}

我通过简单地调用方法在控制器中返回这个,但是响应返回以下内容两次:

[
    {
        "id": 1,
        "reviewId": 1,
        "starRating": 4.3,
        "reviewDescription": "Test description",
        "author": "Tom Hindle",
        "publisher": "Cornerstone",
        "genre": "Fiction",
        "numberOfPages": 464,
        "weight": "319",
        "price": 7.49,
        "bookType": "Paperback",
        "title": "A Fatal Crossing",
        "isbn": "9781529157840"
    },
    {
        "id": 1,
        "reviewId": 1,
        "starRating": 4.3,
        "reviewDescription": "Test description",
        "author": "Tom Hindle",
        "publisher": "Cornerstone",
        "genre": "Fiction",
        "numberOfPages": 464,
        "weight": "319",
        "price": 7.49,
        "bookType": "Paperback",
        "title": "A Fatal Crossing",
        "isbn": "9781529157840"
    }
]

两个对象中的评论描述是相同的,尽管在数据库中我有与每本书相关的不同评论。我还没有发布控制器代码,但是这只是调用存储库并返回BookAndReview的列表

omqzjyyz

omqzjyyz1#

需要将以下命令添加到sql中。

GROUP BY BK.ISBN

sql根据过滤器BK.ISBN返回正确的结果

i5desfxk

i5desfxk2#

将查询更改为使用JPA连接。

@OneToMany
@JoinColumn(name = "ISBN",referencedColumnName = "ISBN", insertable = false, updatable = false)
List<Reviews> reviews;

查询已更改为使用JPA

List<BookAndReview> getBookAndReviewByISBN(@Param("ISBN") String ISBN);

相关问题