Join across multiple unrelated tables with Criteria API?

jamesconradshea · **Posted:** Thu Mar 11, 2004 1:16 pm

I'm doing a persistence framework evaluation and am currently working with Hibernate 2.1.2. One of the aspects I'm looking at is the degree to which we can eliminate embedded SQL from our application code, so I'd like to make use of the Criteria API as much as possible.

The following code works.

Code:

Query query = session.createSQLQuery(
    "select distinct {ci.*} from carrier_invoice ci join cdr_" + aClient.getId()
    + " cd on ci.carrier_invoice_id=cd.carrier_invoice_id "
    + "where cd.service_id=:aService and ci.billing_period=:anInvoiceDate",
    new String[]{
        "ci"
    },
    new Class[]{
        CarrierInvoice.class
    }
);
query.setParameter("aService", aService.getId());
query.setParameter("anInvoiceDate", anInvoiceDate);
return (CarrierInvoice) query.uniqueResult();

In English, what I need to do is, given a "Client", "Service" and an invoice Date, find, in the Client's "CDR" table, the "CarrierInvoice" whose id appears in all the rows corresponding to the Service and invoice Date, and then create that CarrierInvoice from the CARRIER_INVOICE table.
(Yes, we have a few dynamic table names, but that's a whole other story...)

I'd like to do the same thing with the Criteria API but haven't figured out the right syntax or if it is even possible. Any suggestions?

Thanks,
Jim

emmanuel · **Posted:** Fri Mar 12, 2004 2:29 pm

I'm not sure distict is doable in criteria API

jamesconradshea · **Posted:** Fri Mar 12, 2004 4:00 pm

I'm pretty sure I read somewhere else in this forum that "distinct" was not yet supported, but that's less important than the join capability.

Jim

michael · **Posted:** Sat Mar 13, 2004 8:32 am

No, joins without relations are not supported in the Criteria API. Arbitary joins are not even possible in HQL yet.

gavin · **Posted:** Sat Mar 13, 2004 11:37 am

Quote:

Arbitary joins are not even possible in HQL yet.

Arbitrary inner joins are possible.

gburcher · **Joined:** Wed Jun 28, 2006 8:58 am **Posts:** 9

I know this thread is old, but I see Gavin stating you can do arbitrary inner joins in Criteria queries. How? I am struggling to find a way to do that. I see how to add arbitrary constraints to the WHERE clause via Restrictions.sqlRestriction(), but can't find how to do something similar to join into the FROM clause.

Specifically, say I want to add this SQL fragment to the FROM clause of a Criteria query:

Code:

INNER JOIN Foo ON Foo.object_id = {alias}.id

How can I do that?

Greg