List returning the same row from postgresdb-CodePudding

I have an issue where only the first row from my database is being returned multiple times within a list even though the SQL query shows different rows being returning. my jpa query is:

@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);

The object is:

@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;

}

i am returning this in the controller by simply calling the method but the response returns the following twice:

[
    {
        "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"
    }
]

the review description there is the same in both objects even though in the database i have different reviews related to each book. I havent posted the controller code however that is simply calling the the repository and returning a list of BookAndReview

CodePudding user response：

You need to add the following commands to your sql.

GROUP BY BK.ISBN

The sql returns the right result according to filter by BK.ISBN

CodePudding user response：

Changed the query to use JPA joins instead. Added the following to the bookAndReview object

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

Query changed to use JPA

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