1

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;
}
Improve this question
2
  • What is the Item class looking like? Commented Sep 4, 2018 at 8:06
  • @PhilippMerkle, I have added Item class Commented Sep 4, 2018 at 8:10

2 Answers 2

Reset to default
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);
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you for your answer. Should I write a Projection in my Item class or ItemRepository?
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.
Do I need to use some kind of annotations?
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
The error is because Hibernate doesn't know how to map UUIDs to a Postgres data type. See here for a solution: stackoverflow.com/a/28193142/7492402
|
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.

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.