2

I am a Hibernate newbie and i have this below query. It is working as i expected. These two tables are not associated. Is there a way to get the same result by using Criteria API or how can i run this query via Hibernate ? Any help would be appreciated.

SELECT p.title, c.content
  FROM posts p
  LEFT JOIN comments c ON p.id = c.post_id
  WHERE p.status = 'A' AND (p.title iLIKE '%r%' OR c.content iLIKE '%r%');
Tartar
  • 3,946
  • 11
  • 54
  • 90

1 Answers1

0

Criteria API needs a path between entities, so I'm not sure this join could be done using Criteria API. Better do it with HQL if you have Hibernate >= 5.1:

select p.title, c.content
from org.example.Posts p
left outer join org.example.Comments c
on p.id = c.id
where p.status = 'A' AND (lower(p.title) LIKE '%r%' OR lower(c.content) LIKE '%r%');

Still, you could stick to using SQL queries with Hibernate or better still, create association between Posts and Comments.

Community
  • 1
  • 1
borowis
  • 1,171
  • 9
  • 16
  • Having problems with implementing since i am a newbie. Could you please provide an example for left join ? – Tartar Nov 10 '16 at 14:59
  • I added an example. 2 notes: it requires latest hibernate and HQL doesn't support case insensitive operator ilike, so one has to explicitly lowercase – borowis Nov 10 '16 at 15:07
  • I do not have the latest version and i have to do it with Criteria API. I know it does not make sense since there is no path between entities but i didnt design the db and entities. – Tartar Nov 10 '16 at 15:42
  • And there is no relations in db as well (that is insane) so i think the example you have provided also does not work for me. I need an example with DetachedCriteria. – Tartar Nov 10 '16 at 15:45
  • It would be nice. Thanks for your help. – Tartar Nov 10 '16 at 15:49
  • hey, @Tartar, I tried writing a left join with detached criteria and failed, sorry. Because I'm not sure how I can reference c.content from subselect. So, at the moment I'm not sure if that's possible with Criteria API or not. Perhaps someone could provide a better answer – borowis Nov 10 '16 at 16:30