skip to Main Content

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

2

Answers


  1. Chosen as BEST ANSWER

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

  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search