How can I map an object to Java Object through SQL native query using Jpa Repository?

Question

I tried several solutions to my SQL query but it seems like I miss something. I want to get a List<Product> from a nativeQuery.

And I have a relationship between my User entity and Product entity as One to Many.

Here is my both entites -> Product

@Entity
@Data
@Table(name = "product")
@NoArgsConstructor
@AllArgsConstructor
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @CreationTimestamp
    @Column(updatable = false)
    private Timestamp createdDate;

    @UpdateTimestamp
    private Timestamp lastModifiedDate;

    private String imageURL;

    private Long productCode;

    @Size(min = 3,max = 100)
    private String productName;

    @Size(min = 5,max = 100)
    private String details;

    private BigDecimal price;

    private ProductCategory productCategory;

}

User ->

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true,nullable = false)
    private String phoneNumber;

    @Size(min = 5, max = 25, message = "Username length should be between 5 and 25 characters")
    @Column(unique = true, nullable = false)
    private String userName;

    @CreationTimestamp
    @Column(updatable = false)
    private Timestamp createdDate;

    @UpdateTimestamp
    private Timestamp lastModifiedDate;

    @Column(unique = true, nullable = false)
    @NotNull
    private String email;

    @Size(min = 5, message = "Minimum password length: 5 characters")
    @NotNull
    private String password;


    @OneToMany(fetch = FetchType.LAZY,cascade = CascadeType.ALL,orphanRemoval = true)
    private List<Product> products;

    @Transient
    @OneToMany(fetch = FetchType.LAZY,mappedBy = "product",cascade = CascadeType.ALL,orphanRemoval = true)
    private List<ProductInquiry> productInquiries;

    private Role role;

}

Here in this query I need to return all products associated with the given user_id.

 @Query(value = "SELECT  new  egecoskun121.com.crm.model.entity.Product(p.ID,p.CREATED_DATE,p.LAST_MODIFIED_DATE,p.IMAGEURL,p.PRODUCT_CODE,p.PRODUCT_NAME,p.DETAILS,p.PRICE,p.PRODUCT_CATEGORY)    FROM PRODUCT  AS p WHERE  {SELECT PRODUCT_ID FROM USERS_PRODUCTS WHERE USER_ID=:id }",nativeQuery = true)
    List<Product> findAllProductsById(@Param("id")Long id);

maybe you can check this question, https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection — dadan, Oct 28 '22 at 09:54
Just write a query to return the collection for the user. You are making things overly complex. — M. Deinum, Oct 28 '22 at 17:31
I am trying to get a collection for all the products that user have with a specific username. @M.Deinum — Ege Coskun, Oct 31 '22 at 13:28
So write that query. You already have the entities, what you are doing is overly complex and can be solved with a simple query. — M. Deinum, Oct 31 '22 at 14:13

Davide D'Alto · Answer 1 · 2022-10-28T14:13:23.910

0

The problem is that you are using a HQL query but you've set native=true. Setting native=true means that you want to run a SQL query.

This HQL query should work:

@Query("select p from User u join u.products p WHERE u.id = :id")
List<Product> findAllProductsById(@Param("id")Long id);

edited Oct 28 '22 at 14:13

answered Oct 28 '22 at 12:39

Davide D'Alto

7,421
2
16
30

I tried another solution as dadan commented. But now I am getting an in **Could not locate appropriate constructor on class** – Ege Coskun Oct 28 '22 at 14:02
1

Well... the result of the query doesn't match the parameters of the constructor in the class. But why do you need to call the constructor? isn't my solution simpler? – Davide D'Alto Oct 28 '22 at 14:05

How can I map an object to Java Object through SQL native query using Jpa Repository?

1 Answers1