How to access multiple tables in a single query using Hibernate Criteria? [duplicate]

Question

I have two tables to query, and so far I've come up with the following HQL-query:

From DriverEntity d 
where exists (
    From LicenceEntity l 
    where driverId = d.Id 
        and l.licenceType.id = '3'
        and l.validFrom > TO_DATE('2014-01-01', 'YYYY-MM-DD')
        and l.validFrom < TO_DATE('2014-04-17', 'YYYY-MM-DD')
        and l.validTo > TO_DATE('2014-07-02', 'YYYY-MM-DD')
        and l.validTo < TO_DATE('2095-07-12', 'YYYY-MM-DD')))

I'm querying two tables; one with licences and one with drivers. Each driver can have many licences.

The query works perfectly, but I would like to use Criteria instead to make it easier to edit when I add more search options.

What would this query look like using Criteria? I've looked into DetachedCriteria, but I don't understand it completely.

What have you tried? StackOverflow is a site for asking specific questions where you've tried something and have a problem. General "How do I do this" questions with no attempts shown tend to have a bad time and are somewhat offtopic. Try something and then ask a specific question if you have a problem — Avery
– Avery, Commented Jul 11, 2014 at 12:17
As an aside, why use criteria? My personal experience has been they lead to fragmentation... I'll take HQL over criteria anyday =) — corsiKa
– corsiKa, Commented Jul 11, 2014 at 17:27
I want to use Criteria since I'm planning to add a lot more Restrictions, and the HQL-queries got very complicated after a while. — trifri
– trifri, Commented Jul 14, 2014 at 7:13

chresse · Accepted Answer · 2014-07-11 12:14:11Z

0

you can add a the subquery with help of DetachedCriteria:

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Criteria criteria = Criteria.forClass(DriverEntity.class,"driver");
DetachedCriteria dc = DetachedCriteria.forClass(LicenceEntity.class,"licence");
dc.add(Property.forName("driver.id").eqProperty("licence.driverId"));
dc.add(Restrictions.between("validFrom", df.parse("2014-01-01"), df.parse("2014-04-17")));
dc.add(Restrictions.between("validTo", df.parse("2014-07-02"), df.parse("2095-07-12")));

criteria.add(Subqueries.exists(dc.setProjection(Projections.id())));

answered Jul 11, 2014 at 12:14

chresse

5,8873 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:11:02Z

0

You can use Restrictions :

criteria.add(Restrictions.between("dateField", fromDate, toDate));

For your query :

criteria.add(Restrictions.between("validFrom", vFd1, vFd2));
criteria.add(Restrictions.between("validTo", vTd1, vTd2));

Where

vFd1,2 and vTd1,2 will be java calender dates with time set to zero.

Ref :

https://stackoverflow.com/a/10074111/3603806

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jul 11, 2014 at 12:13

hityagi

5,2561 gold badge26 silver badges44 bronze badges

Comments

devops · Accepted Answer · 2014-07-11 12:24:45Z

0

according to Hibernate Developer Guide the developers should use CriteriaQuery instead of Criteria.

Look at this example thats explains how to create a multiple roots query or joins

answered Jul 11, 2014 at 12:24

devops

9,2016 gold badges51 silver badges77 bronze badges

Collectives™ on Stack Overflow

How to access multiple tables in a single query using Hibernate Criteria? [duplicate]

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related