Hibernate JOINTURE EXTERNE GAUCHE avec DetachedCriteria
Je suis en train de transformer le SQL suivant dans Hibernate DetachedCriteria:
SELECT
students0_.courseId AS courseId2_1_,
students0_.studentId AS studentId1_,
student1_.id AS id3_0_,
student1_.href AS href3_0_,
student1_.created AS created3_0_,
student1_.email AS email3_0_,
student1_.enabled AS enabled3_0_,
student1_.firstName AS firstName3_0_,
student1_.lastAccessed AS lastAcce8_3_0_,
student1_.lastName AS lastName3_0_,
student1_.password AS password3_0_,
student1_.role AS role3_0_,
student1_.username AS username3_0_
FROM
course_students students0_
INNER JOIN
users student1_
ON students0_.studentId=student1_.id
WHERE
students0_.courseId IN (
SELECT
this_.id
FROM
courses this_
LEFT OUTER JOIN
assignments assignment2_
ON this_.id=assignment2_.courseId
AND (
assignment2_.availabilityType='Available'
OR (
assignment2_.availabilityType='Range'
AND assignment2_.endDate>= NOW()
AND assignment2_.startDate<= NOW()
)
)
LEFT OUTER JOIN
courses course5_
ON assignment2_.courseId=course5_.id
INNER JOIN
course_students students6_
ON this_.id=students6_.courseId
LEFT OUTER JOIN
users student1_
ON students6_.studentId=student1_.id
WHERE
student1_.id = <id>
AND this_.endDate>= NOW()
AND this_.startDate<= NOW()
)
J'ai le code suivant (en commentaire de code sont différentes itérations que j'ai essayé):
public Collection<Course> findCoursesByStudent(Student student) {
Calendar currTime = Calendar.getInstance();
// DetachedCriteria subCriteria = DetachedCriteria.forClass(Assignment.class, "assignments");
//subCriteria.createCriteria("assignments", Criteria.FULL_JOIN);
//subCriteria.createAlias("assignments", "assignments");
Disjunction disjunction = Restrictions.disjunction();
disjunction.add(Restrictions.eq("assignments.availabilityType", AvailabilityType.Available));
Conjunction conjunction = Restrictions.conjunction();
conjunction.add(Restrictions.eq("assignments.availabilityType", AvailabilityType.Range));
conjunction.add(Restrictions.ge("assignments.endDate", currTime)).add(Restrictions.le("assignments.startDate", currTime));
disjunction.add(conjunction);
// subCriteria.add(disjunction);
DetachedCriteria criteria = DetachedCriteria.forClass(getPersistentClass());
criteria.createCriteria("students", "student").add(Restrictions.idEq(student.getId()));
criteria.add(Restrictions.ge("endDate", currTime)).add(Restrictions.le("startDate", currTime));
criteria.createAlias("assignments", "assignments", Criteria.LEFT_JOIN);
criteria.setFetchMode("assignments", FetchMode.JOIN).add(disjunction);
//criteria.createCriteria("assignments", "assignments", Criteria.LEFT_JOIN).add(disjunction);
return findByDetachedCriteria(criteria);
}
Mais j'ai deux lignes en double à la suite de ce et le SQL que hibernate utilise est le suivant:
select
students0_.courseId as courseId2_1_,
students0_.studentId as studentId1_,
student1_.id as id3_0_,
student1_.href as href3_0_,
student1_.created as created3_0_,
student1_.email as email3_0_,
student1_.enabled as enabled3_0_,
student1_.firstName as firstName3_0_,
student1_.lastAccessed as lastAcce8_3_0_,
student1_.lastName as lastName3_0_,
student1_.password as password3_0_,
student1_.role as role3_0_,
student1_.username as username3_0_
from
course_students students0_
inner join
users student1_
on students0_.studentId=student1_.id
where
students0_.courseId in (
select
this_.id
from
courses this_
left outer join
assignments assignment2_
on this_.id=assignment2_.courseId
left outer join
courses course5_
on assignment2_.courseId=course5_.id
inner join
course_students students6_
on this_.id=students6_.courseId
left outer join
users student1_
on students6_.studentId=student1_.id
where
student1_.id = ?
and this_.endDate>=?
and this_.startDate<=?
and (
assignment2_.availabilityType=?
or (
assignment2_.availabilityType=?
and assignment2_.endDate>=?
and assignment2_.startDate<=?
)
)
)
J'ai cherché partout pour trouver une solution. Toute aide sera appréciée. Je suis aussi en train d'essayer d'éviter d'utiliser les requêtes HQL.
Kia
OriginalL'auteur Kianosh | 2011-01-13
Vous devez vous connecter pour publier un commentaire.
Présence de doublons à partir d'un complexe hibernate critères de requête est un problème commun, et la meilleure solution est d'utiliser un résultat transformateur faite juste pour cette question:
Malheureusement, cela n'a pas d'interagir avec défilement et de recherche, mais il peut fonctionner dans votre cas.
Une meilleure solution pourrait être de remplacer une partie de vos restrictions avec
SubQuery.exists
pour obtenir une sous-sélection de vérifier l'existence d'allumettes à la place de la récupération de tous les matches, mais cela peut être douloureux à la figure.OriginalL'auteur Don Roby