Spring Data JPA query to select all value from one join table

Question

I have problem to select all value from one table and few other columns using Spring Data JPA. I am using PostgreSql database and when I send query through PgAdmin I get values I want, but if I use it in Spring Boot Rest returns only one table values (subquery not working). What I am doing wrong?

@Query(value = "SELECT item.*, MIN(myBid.bid) AS myBid, (SELECT MIN(lowestBid.bid) AS lowestbid FROM bids lowestBid WHERE lowestBid.item_id = item.item_id GROUP BY lowestBid.item_id) FROM item JOIN bids myBid ON item.item_id = myBid.item_id WHERE myBid.user_id = :user_id GROUP BY item.item_id", nativeQuery = true)
public List<Item> findAllWithDescriptionQuery(@Param("user_id") UUID userId);

Added Item class

@Data
@Entity(name = "item")
public class Item {
    @Id
    @GeneratedValue
    private UUID itemId;

    @NotNull
    @Column(name = "title")
    @Size(max = 255)
    private String title;

    @NotNull
    @Column(name = "description")
    private String description;


    @NotNull
    @Column(name = "created_user_id")
    private UUID createdUserId;
}

What is the Item class looking like? – Philipp Merkle Sep 04 '18 at 08:06 — Philipp Merkle, Sep 04 '18 at 08:06
@PhilippMerkle, I have added Item class – PrEto Sep 04 '18 at 08:10 — PrEto, Sep 04 '18 at 08:10

Philipp Merkle · Accepted Answer · 2018-09-04T10:38:32.060

1

The result from your native query cannot simply be mapped to entities due to the in-database aggregation performed to calculate the MIN of own bids, and the MIN of other bids. In particular, your Item entity doesn't carry any attributes to hold myBid or lowestbid.

What you want to return from the query method is therefore a Projection. A projection is a mere interface with getter methods matching exactly the fields returned by your query:

public interface BidSummary {

  UUID getItem_id();

  String getTitle();

  String getDescription();

  double getMyBid();

  double getLowestbid();

}

Notice how the query method returns the BidSummary projection:

@Query(value = "SELECT item.*, MIN(myBid.bid) AS myBid, (SELECT MIN(lowestBid.bid) AS lowestbid FROM bids lowestBid WHERE lowestBid.item_id = item.item_id GROUP BY lowestBid.item_id) FROM item JOIN bids myBid ON item.item_id = myBid.item_id WHERE myBid.user_id = :user_id GROUP BY item.item_id", nativeQuery = true)
public List<BidSummary> findOwnBids(@Param("user_id") UUID userId);

edited Sep 04 '18 at 10:38

answered Sep 04 '18 at 10:08

Philipp Merkle

2,555
2
11
22

Thank you for your answer. Should I write a Projection in my Item class or ItemRepository? – PrEto Sep 04 '18 at 11:03
The ``BidSummary`` interface could be placed next to your ``Item`` entity, i.e. in the same package. The corresponding query method can be placed in any JPA repository, like the ``ItemRepository``. The ``BidsRepository``, however, appears to be better suited. – Philipp Merkle Sep 04 '18 at 11:16
Do I need to use some kind of annotations? – PrEto Sep 04 '18 at 11:34
I am getting this error: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111 – PrEto Sep 04 '18 at 11:53
The error is because Hibernate doesn't know how to map UUIDs to a Postgres data type. See here for a solution: https://stackoverflow.com/a/28193142/7492402 – Philipp Merkle Sep 04 '18 at 12:20
Thank you for your answers. Everything works as I need. I accept you answer. Thank you again for help. – PrEto Sep 04 '18 at 13:54
Glad I could help. – Philipp Merkle Sep 04 '18 at 14:06

score 0 · Answer 2 · answered Sep 04 '18 at 08:36

0

Return type is List of Item objects and the query specified is having columns which are not part of return object. I recommend using appropriate Entity which full-fills your response type.

answered Sep 04 '18 at 08:36

jazz

509
7
16

Maybe you could show example how to join two tables with matching ids in both tables by using Entity. Because I couldn't make this join. – PrEto Sep 04 '18 at 09:13

Spring Data JPA query to select all value from one join table

2 Answers2