Using native query with jpa repsitory

Asked Oct 01 '18 at 08:30

Active Oct 02 '18 at 00:16

Viewed 67 times

@Repository
public interface SiteRiskRepository extends JpaRepository<SiteRisk, Long> {
@Query(value="SELECT  DATE_TRUNC('day',updated_on)+date_part('hour',updated_on)::int/2 * INTERVAL '2 hour',avg(risk_value) FROM site_risk_tbl WHERE updated_on BETWEEN :fromDate AND :toDate and site_id = :siteId group by 1 order by 1",nativeQuery = true)

    public List<AggttRisk> getSiteRiskTrend(@Param("siteId") String siteId, @Param("fromDate") Date fromDate,
            @Param("toDate") Date toDate);
}

//projection class
public interface AggttRisk {
    String getRiskValue();
    String getUpdatedOn();  
}

Giving me error:

nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: Not all named parameters have been set: [:int] [SELECT DATE_TRUNC('day',updated_on)+date_part('hour',updated_on)::int/2 * INTERVAL '2 hour',avg(risk_value) FROM site_risk_tbl WHERE updated_on BETWEEN :fromDate AND :toDate and site_id = :siteId group by 1 order by 1]]

P.S.: the query works fine while fired on pgadmin.

edited Oct 01 '18 at 12:48

asked Oct 01 '18 at 08:30

Swati Shinde

probably getting confused with `::int` - is it needed? – Scary Wombat Oct 01 '18 at 08:34
Yes, it is needed.If removed, giving nullPointerException – Swati Shinde Oct 01 '18 at 08:37
does it work in pgadmin? – Scary Wombat Oct 01 '18 at 08:40
yes,it worked there. – Swati Shinde Oct 01 '18 at 08:44
so I would guess that the NPE is something else. Anyway see https://stackoverflow.com/questions/4169582/escaping-the-colon-character-in-jpa-queries – Scary Wombat Oct 01 '18 at 08:49
the solution you provided is not working,hence no duplicate. – Swati Shinde Oct 01 '18 at 09:13
Please edit your question with what you have tried from the duplicate and what errors it is producing – Scary Wombat Oct 02 '18 at 00:16
It is giving null pointer exception – Swati Shinde Oct 03 '18 at 05:15
Please edit your question with what you have tried from the duplicate and what errors it is producing – Scary Wombat Oct 03 '18 at 05:18
Okay. i did a workaround and finally cam with the solution. I used List of Object class to gather the objects returned by query and it worked. thanks! – Swati Shinde Oct 03 '18 at 05:59
@Query(value="SELECT DATE_TRUNC('day',updated_on)+CAST(date_part('hour',updated_on) as Integer)/2 * INTERVAL '2 hour' as updated_on,avg(risk_value) as risk_value FROM site_risk_tbl WHERE updated_on BETWEEN :fromDate AND :toDate and site_id = :siteId group by 1 order by 1",nativeQuery = true) public List – Swati Shinde Oct 03 '18 at 06:02
Please unmark it as duplicate. – Swati Shinde Oct 03 '18 at 06:03
As stated in the duplicate I posted, the colons in `::int` need to be escaped such as `select * from component where data \\:\\:json ->> ?1 = ?2` – Scary Wombat Oct 03 '18 at 06:09

Using native query with jpa repsitory

0 Answers0