How to Map a Complex native Query Result Set to a Pojo Class in Spring Data JPA

Question

My Repository

 public interface ProductRepository extends JpaRepository<Product,Integer> { @Query(value = "select mp.*,u.name,p.name, p.ext_id from merchant_product mp join campaign c on c.product_id = mp.id and c.status in (4, 8)join product p on p.id = mp.product_id join user u on u.id = mp.user_id where mp.status = 4 and mp.availability = 'Y';", nativeQuery = true) List<Object> getAllProduct(); }

This is my Query in Spring Boot i am using Spring data JPA . I need to map this to a Pojo class . So that i can use it for further processing .

can anyone help me with this.

My pojo

@Data @AllArgsConstructor public class Product { @Id //data of merchant product table //data of user table private int id; private String name; private String ext_id; }

StanislavL · Accepted Answer · 2017-06-07 11:43:25Z

@Query(nativeQuery = true, name = "test", value = "select mp.* ...") @SqlResultSetMapping(name="test", classes = { @ConstructorResult(targetClass = Product.class, columns = {@ColumnResult(name="name"), @ColumnResult(name="id")}, @ColumnResult(name="ext_id")}) })

Add the SqlResultSetMapping to initialize mappings which columns from the query corresponding to the Pojo fields

How do i add it to a method @StansislavL its says class or method expected , if i add it on top of a method

farrellmr · Accepted Answer · 2017-06-07 11:52:59Z

Id create a constructor for the params you want on Product and call -

 public interface ProductRepository extends JpaRepository<Product,Integer> { @Query(value = "select new Product(u.name,p.name, p.ext_id) from merchant_product mp join campaign c on c.product_id = mp.id and c.status in (4, 8)join product p on p.id = mp.product_id join user u on u.id = mp.user_id where mp.status = 4 and mp.availability = 'Y';", nativeQuery = true) List<Product> getAllProduct(); }

You will need to define the params from the wildcard mp.* in your object for mapping

this dosent work the stack trace org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract
Sorry _ I hadnt noticed it was a native query as i hadnt scrolled along. Can you map in your JPA model?
Its a nativeQuery = true - so raw html - i thought it was jpa
@Rahul, I have the same use case , Are you able to find a answer to it?

Collectives™ on Stack Overflow

How to Map a Complex native Query Result Set to a Pojo Class in Spring Data JPA

2 Answers 2

1 Comment

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Related