This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes Object Relational Mapping and the fly likes Convert SQL to Criteria please Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Convert SQL to Criteria please" Watch "Convert SQL to Criteria please" New topic
Author

Convert SQL to Criteria please

Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
Hi,

I got a nice SQL query that works fine on 2 join tables. What I want to achieve is an equivalent Criteria-API-Command.

The SQL query gets me all USERs that belong to certain GROUPS and ROLEs. USER_GROUP and USER_ROLE are each a many-to-many association between USER and GROUP/ROLE.

So this is the query:



Can anybody think of an Criteria command to achieve the same result?
You would really help!

Thanks in advance!
[ October 05, 2007: Message edited by: Hans Gerlich ]
Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
No idea? By the way .. this is of course SQL not yet HQL.
Edvins Reisons
Ranch Hand

Joined: Dec 11, 2006
Posts: 364
Why not Show Some Effort first? The Hibernate Reference has a chapter on the Criteria interface.
Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
Originally posted by Edvins Reisons:
Why not Show Some Effort first? The Hibernate Reference has a chapter on the Criteria interface.


Okay sorry, in deed I looked at the docs but find it hard to solve my problem, which seems to be somehow more special.

The first attempt I would take was this, but I assume this will not work:



The first line should give me all users, the second line shall restrict the roles (roleone AND roletwo at the same time), the next line should then restrict the groups in the same way.

1) I don't know if the INNER_JOIN would be right.
2) Is adding two Restrictions equal to "apply Restriction one AND two"?
3) I read about setting a result transformer is necessary to get correct results. Hope this is the right one, but will try others when the lines before are okay.

I understood quite all of the basic examples of the Hibernate tutorial. But for my current problem it seems hard to find a solution for.

Thanks in advance.
Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
I found out that at least this Criteria gets me Users that have Role TWO. However the first Restriction is overwritten/ignored:

Edvins Reisons
Ranch Hand

Joined: Dec 11, 2006
Posts: 364
I think it is starting to look like the thing that you need: add a logical operator (I think it's an "or") and maybe fine-tune other options, and it should work...
[ October 05, 2007: Message edited by: Edvins Reisons ]
Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
I am trying to add a Hibernate-Conjunction in order to get all users that have Role 1 AND Role 2. But the generated SQL says something like "where role_id=1 AND role_id=2" which is not correct.

However if I use a Hibernate-Disjunction instead, it gets me "role_id=1 OR role_id=2" which is an OR-Expression. How to build an expression that checks that every role_id is present in the roles-collection?


[ October 06, 2007: Message edited by: Hans Gerlich ]
Edvins Reisons
Ranch Hand

Joined: Dec 11, 2006
Posts: 364
Can you use Projections.countDistinct() here?
Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
Sorry, I dont understand. "countDistinct(...)" results in a number, not in User-objects.
Edvins Reisons
Ranch Hand

Joined: Dec 11, 2006
Posts: 364
True. My idea was to put it into a boolean expression like
Restrictions.eq(); I don't quite see how.
Another thing that I see worth trying is Restrictions.sizeEq() with
DISTINCT_ROOT_ENTITY.
Hans Gerlich
Greenhorn

Joined: Oct 03, 2007
Posts: 7
Originally posted by Edvins Reisons:
True. My idea was to put it into a boolean expression like
Restrictions.eq(); I don't quite see how.
Another thing that I see worth trying is Restrictions.sizeEq() with
DISTINCT_ROOT_ENTITY.


A brilliant idea! I tried Restrictions.sizeGe() due to I want to have all users that have at least the specified roles. That works almost perfectly except for one thing: It gets me also users that have at least one of "roleone" or "roletwo" AND any other role. "greater or equal" is set to "2" in my example, so every user that has one of the specified roles and ANY other is processed. This is not what I want.



May be you have another hint for me ...
Edvins Reisons
Ranch Hand

Joined: Dec 11, 2006
Posts: 364
Looking back at the original SQL, I think it is a matter of ordering your restrictions: first an OR condition on the two roles, then a count.
And, if all else fails, one can express the restrictions in SQL.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Convert SQL to Criteria please