DetachedCriteria with JOIN

Bjorn121 · **Joined:** Mon Jul 13, 2009 9:00 am **Posts:** 7

I've been looking for this quite a while, but haven't found a solution to my problem.

I have to use DetachedCriteria to retrieve all records from a table in my database. So far so good, but the problem is that I only want to retrieve those records, which can also be found in another table. Say for instance that I have a table "User", and a table "Customer". What do I have to do to retrieve all Users, who are also customers. SQL would be something like SELECT * FROM User INNER JOIN Customer ON User.UserID = Customer.UserID. (Stupid example, but I didn't have anything better to come up with :p).

Any idea how to do this with DetachedCriteria? Tia.

archetype · **Joined:** Wed Jul 29, 2009 12:55 am **Posts:** 1

Hi,

First and foremost, you can do the joining in the Hibernate Mapping. Anyway, I am not sure if this gonna work on your side:

Code:

DetachedCriteria.forClass(User.class)
  .add(Property.forName("userid")
    .eq(
      DetachedCriteria.forClass(Customer.class).setProjection(Property.forName("userid"))
    )
  )

I hope this help... Can anyone tell what is the performance on using this kind of approach? I think one of the reason that I came out on this idea because maybe the programmer don't want to alter the hibernate mapping and want it to make dynamically because you can add criteria on the fly. It's like you're creating your own Query Builder for Web using the following condition... i.e. <> >= <= = etc...

Thanks

Bjorn121 · **Joined:** Mon Jul 13, 2009 9:00 am **Posts:** 7

I solved it (don't remember how exactly), but the problem was that the generated query had quite an impact on performance.

Generated query was something like :

Code:

SELECT *
FROM User
WHERE User.UserID IN (SELECT UserID FROM Customer)
ORDER BY UserID ASC

So for each user he'll loop through the Customer table to find a matching record. Since there are about 270.000 results, that was a bit too much :-). I altered the code and now I'm using SQLQuery instead of Query.

Due to design decisions that were taken in the past, I can't create mappings between those objects.

napoleonit76 · **Joined:** Thu May 13, 2010 3:44 am **Posts:** 2

I am going to retrieve some records with the similar query but adding one condition to second sub-query:

Code:

SELECT * FROM User
WHERE User.UserID IN (SELECT UserID FROM Customer WHERE Customer.Type=VIP)
ORDER BY UserID ASC

So how can I do that in Hibernate by using Restriction, Projection etc?
Thanks!