Wer kann dieses SQL in Criteria umwandeln?

rapthor · **Joined:** Wed Nov 01, 2006 2:17 pm **Posts:** 78

Hallo,

ich habe eine Tabelle USER die in 2 JOIN TABLES referenziert wird. Es gibt einmal USER_ROLE und USER_GROUP, also jeweils many-to-many-Relationen.

Ich suche nun alle USER, die bestimmte GROUPs und ROLEs besitzen. Mit folgendem SQL-Query kann ich schonmal arbeiten, aber ich hätte sehr gern ein passendes Hibernate-Criteria dafür:

Code:

SELECT * from user WHERE user.id IN (
   SELECT u.id
   FROM user u 
   JOIN user_role ur ON u.id=ur.user_id AND (ur.role_id=1 OR ur.role_id=2) 
   JOIN user_group ug ON u.id=ug.user_id AND (ug.group_id=1 OR ug.group_id=2) 
   HAVING COUNT( DISTINCT ur.role_id ) = 2 
     AND COUNT( DISTINCT ug.group_id ) = 2 GROUP BY name;
)

Kennt sich da jemand gut aus und kann mir da weiterhelfen das zu konvertieren?

Die Tabellen:

Code:

USER 
id name 
1  frank 

ROLE 
id name 
1 admin 
2 general 

GROUPS 
id name 
1 groupA 
2 groupB 

USER_ROLE 
user_id role_id 
1 1 
1 2 

USER_GROUP 
user_id group_id 
1 1 
1 2 

rapthor · **Joined:** Wed Nov 01, 2006 2:17 pm **Posts:** 78

Hat jemand eine Idee, wie man diesen SQL-Query in ein Criteria verpacken könnte?

rapthor · **Joined:** Wed Nov 01, 2006 2:17 pm **Posts:** 78

Naiv würde ich folgende Kombination versuchen, aber ich denke nicht dass das funktioniert:

Code:

 List users = session.createCriteria(User.class)
     .createCriteria("roles", CriteriaImpl.INNER_JOIN)
         .add( Restrictions.eq("role", roleone) )
         .add( Restrictions.eq("role", roletwo) ) 
    .createCriteria("groups", CriteriaImpl.INNER_JOIN)
         .add( Restrictions.eq("group", groupone) ) 
        .add( Restrictions.eq("group", grouptwo) )
     .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
     .list();

roles und groups sind Attributnamen im User-Objekt, die auf Listen zeigen (Many-To-Many).

Die erste Zeile soll alle User auswählen, die zweite soll die Rollen einschränken (roleone UND roletwo zur gleichen Zeit), die nächste Zeile soll die Gruppen einschränken.

1) Ist INNER_JOIN überhaupt richtig für meine Anforderungen?
2) Ist es richtig, zwei Restrictions nacheinander hinzuzufügen, um festzulegen, dass beide gleichzeitig gelten sollen?
3) Ist der ResultTransformer richtig angewendet?

rapthor · **Joined:** Wed Nov 01, 2006 2:17 pm **Posts:** 78

Ich glaube ich bin nahe dran.

Ich versuche es mit einer Hibernate-Conjunction, die mir alle User liefern soll, die Role1 und Role2 haben sollen. Jedoch zeigt der generierte SQL-Code "where role_id=1 AND role_id=2" was natürlich nicht korrekt sein kann.

Sobald ich aber eine Hibernate-Disjunction verwende, bekomme ich "role_id=1 OR role_id=2", was auch falsch ist. Ich möchte am Ende doch eine Expression erhalten, die mir sicherstellt, dass Role1 UND Role2 in der roles-Collection enthalten sind!

Code:

Criteria crit = session.createCriteria(User.class);

      Criteria subcrit = crit.createCriteria("roles", CriteriaImpl.INNER_JOIN);

      Conjunction every = Expression.conjunction();
      every.add( Restrictions.ideq(roleone.getId));
      every.add( Restrictions.ideq(roletwo.getId));

      subcrit.add(every);

      crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);     
return crit.list(); 

LaLiLuna · **Posted:** Mon Oct 08, 2007 3:39 pm

Das geht leider nicht, dafür müsste man zweimal Aliase bilden können (Dein SQL ist auch falsch)

Mit HQL würde es gehen.

Allenfalls eine Subquery würde mit Criteria gehen, wenn Du die Objekte in der Subquery zählst, die entweder "role1 oder role2" haben und alle <> 2 ausschließt.

rapthor · **Joined:** Wed Nov 01, 2006 2:17 pm **Posts:** 78

LaLiLuna wrote:

Allenfalls eine Subquery würde mit Criteria gehen, wenn Du die Objekte in der Subquery zählst, die entweder "role1 oder role2" haben und alle <> 2 ausschließt.

Eine Subquery mit Criteria und eine Hauptquery mit HQL meinst du? Oder zwei Abfragen mit Criteria?

Es wäre natürlich schön, einen HQL-Befehl zu haben, der mir das SQL im ersten Beitrag ersetzt, aber was ist denn am ersten bitte falsch?

Lieber wäre mir aber alles mit Criteria erledigen zu können.

LaLiLuna · **Posted:** Tue Oct 09, 2007 5:36 am

Du schreibst
die Role1 und Role2

aber Dein SQL sucht nach oder. "Oder" wäre relativ unproblematisch aber "und" würde bei der Query bedeuten, dass Dein SQL so aussieht (ungefährt)
select * from a inner join b as b1 on b1.a_id=a.id where b1.role='role1'
inner join b as b2 on b2.a_id=a.id where b2.role="role2"

Das geht auch mit HQL.

In Criteria könntest Du mit Subquery (heißt auch detachedCriteria) arbeiten.
ungefähr so sieht die SQL query aus

select from a where a.id in (select b.a_id from b where b.role in (role1,role2)
group by b.a_id having count(b.a_id) = 2

rapthor · **Joined:** Wed Nov 01, 2006 2:17 pm **Posts:** 78

Ich habe da etwas interessantes gefunden: Restrictions.sizeGe(...). Das scheint mir das zu tun, was du durch die Sub-Query angedeutet hast. Die Objekte zu zählen ....

Damit überprüfe ich also in der Haupt-Criteria, ob die resultierenden Objekte mindestens 2 Rollen besitzen. Allerdings ist damit noch nicht der Fall ausgeschlossen, dass ein User eine der beiden Rollen zwar besitzt, aber zufällig noch eine weitere, von mir nicht gewünschte Rolle (also auch 2 Rollen). Diese Fälle werden leider nicht aussortiert.

Denn ich möchte, dass die User mindestens die zwei von mir ausgewählten Rollen besitzen (gern auch weitere).

Ich frage mich, ob man die ungewünschten Fälle noch irgendwie ausschließen kann.

Code:

Criteria crit = session.createCriteria(User.class);
      Criteria subcrit = crit.createCriteria("roles", CriteriaImpl.INNER_JOIN);
       crit.add(Restrictions.sizeGe("roles", 2));

       Disjunction any= Restrictions.disjunction();
       any.add( Restrictions.ideq(roleone.getId));
       any.add( Restrictions.ideq(roletwo.getId));

       subcrit.add(any);

       crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);      return crit.list();

Ideen?