[FIXED] List returning the same row from postgresdb

Issue

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

Solution

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

Answered By - T.Huss
Answer Checked By - Pedro (JavaFixing Volunteer)