1

I have this query and it is working as i expected

SELECT 
  COUNT(id) as views_per_day,
  view_date::timestamp::date,
  extract(dow from view_date::timestamp::date) as day_index
FROM details 
WHERE view_date > current_date - interval '6' day
GROUP BY view_date::timestamp::date
ORDER BY view_date::timestamp::date;

I am using Hibernate in my project but i am a Hibernate newbie. What i want to know is how can i use functions like

`extract(dow from view_date::timestamp::date)`, 
`interval '6' day` `view_date::timestamp::date`

in Hibernate or is there a way to run this as a raw sql without security vulnerabilities ? Any help would be appreciated

Vikrant Kashyap
  • 6,398
  • 3
  • 32
  • 52
Tartar
  • 5,149
  • 16
  • 63
  • 104
  • try to write native queries in hibernate and use any native function it to get the results. you may google it (How to write sql Native queries in Hibernate) @Tartar – Vikrant Kashyap Jul 18 '16 at 08:57
  • 2
    Look into using `createNativeQuery()` which will execute a raw SQL query. I believe you'll need to go down this road, since the Postgres functions you are trying to use are not standard HQL. – Tim Biegeleisen Jul 18 '16 at 08:59
  • 1
    Another option is to define this as a view in the database, so you can query it without calling the functions directly. – RealSkeptic Jul 18 '16 at 09:06

2 Answers2

0

Try to use the @Formula-annotation like here.

@Formula("extract(dow from view_date::timestamp::date)")
Community
  • 1
  • 1
Grim
  • 1,938
  • 10
  • 56
  • 123
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/13040525) – theblindprophet Jul 18 '16 at 13:11
  • 1
    Blind Prophet, i do not understand. The question is clear, no need to critique or request for clarification. This is an answer! – Grim Jul 18 '16 at 19:29
0

or use createNativeQuery() and you can use your query as it is.

Rom
  • 67
  • 1
  • 2
  • 12