Hibernate - issue with criteria.createAlias() return information as if restriction was not specified

Question

I have the table student, that may have multiple enrolments, and each enrolment belongs to one enrolment institution.

If I search only adding student information, the query returns ok.

As for the enrolment, and enrolment institution, I know the filter is added to the query because for ex, for student 1, I have it associated to course c1 and c2. So, if I insert c3 as criteria, the query doesn't return any result, but if I add c1 or c2, the query will return both records, related to c1 and c2.

Any help is appreciated. Thank you in advance!

public List findByCriteria(Student student) {

    String firstName = student.getFirstName();
    String lastName = student.getFamilyName();
    String sexCode = student.getSexCode();
    Date dob = student.getDateOfBirth();
    Integer courseNo = 0;
    Integer collegeNo = 0;
    Integer latestEnrolmentYear = 0;
    if (student.getEnrolments() != null
            && student.getEnrolments().size() > 0) {
        Enrolment enrolment = (Enrolment) student.getEnrolments().get(0);
        if (enrolment.getCourseNo() != null) {
            courseNo = enrolment.getCourseNo();
        }
        if (enrolment.getEnrolmentInstitution().getInstNo() != null) {
            collegeNo = enrolment.getEnrolmentInstitution().getInstNo();
        }
        if (enrolment.getLatestEnrolmentYear() != null) {
            latestEnrolmentYear = enrolment.getLatestEnrolmentYear();
        }

    }
    List<Student> students = new ArrayList<Student>();
    getCurrentSession().enableFetchProfile("studentEnrolments");//required if we don't add "enrolment" or "enrolment institution" info as criteria

    Criteria criteria = getCurrentSession().createCriteria(Student.class, "student");

    if (!StringUtils.isBlank(lastName) && lastName.length() >= 0) {
        criteria.add(Restrictions.like("familyName", lastName,
                MatchMode.ANYWHERE));
    }

    if (!StringUtils.isBlank(firstName) && firstName.length() >= 0) {
        criteria.add(Restrictions.like("firstName", firstName,
                MatchMode.ANYWHERE));
    }

    if (!StringUtils.isBlank(sexCode)) {
        criteria.add(Restrictions.eq("sexCode", sexCode));
    }

    if (dob != null && !dob.equals(new Date())) {
        criteria.add(Restrictions.eq("dateOfBirth", dob));
    }

    if (courseNo > 0 || latestEnrolmentYear > 0 || collegeNo > 0) {

        criteria.createAlias("student.enrolments", "enrolment");                

        if (courseNo > 0) {
            criteria.add(Restrictions.eq("enrolment.courseNo", courseNo));
        }
        if (latestEnrolmentYear > 0) {
            criteria.add(Restrictions.eq("enrolment.latestEnrolmentYear", latestEnrolmentYear));        
        }

        if (collegeNo > 0) {            
            criteria.add(Restrictions.eq("enrolment.enrolmentInstitution.instNo", collegeNo));              
            criteria.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY); //otherwise, instNo criteria may create multiple rows per result
        }

        students = criteria.list();

        //needs to initialize because alias creation leads to lazy initialization (otherwise, search adding the fields below as criteria would fail)
        if (students.size() > 0) { 
            forceLazyInitialization(students);          
        }

    } else{

        getCurrentSession().enableFetchProfile("studentEnrolments");//required if we don't add "enrolment" or "enrolment institution" info as criteria      
        students = criteria.list();
        getCurrentSession().disableFetchProfile("studentEnrolments");   
    }



    return students;
}

score 1 · Accepted Answer · edited May 23 '17 at 11:50

If I understand correctly, you would expect, that the collection enrolments would be prefiltered, because the expected SQL query is:

SELECT ..
FROM Student 
JOIN Enrolment ON ...
WHERE Enrolment.courseNo = 'c1'

becuase this will for sure not return records where courseNo <> 'c2'

But this is not how it works. This select is effecting the root (student) selection... and the Enrolments are loaded ex post again... most likely lazily.

If you want to see only the courses 'c1' in enrolments you should filter them ex post, in runtime or use a filter:

19.1. Hibernate filters
Hibernate how to ignore @Where annotation (with some more description about filters)

Hibernate - issue with criteria.createAlias() return information as if restriction was not specified

1 Answers1