Unable to write criteria with reference classes

Question

We have entity like this-

College has many Departments.
NGO can visit many Colleges.

We have to write criteria to get- list of five colleges in which any of the department's fest happened recently and visited by NGO name "xyz".

Note: At the time of visit we have to maintain some other record as well,so we have to make oneToMany relationship of both NGO and College with Visit Entity class,rather than manyToMany relationship between College and Visit.

So our domain(Entity) become like this:-

class College{
@OneToMany(mappedBy="visitedTo")
List<Visit> collegeVisites=new ArrayList<Visit>();
@OneToMany(mappedBy="college")
List<Department> departments=new ArrayList<Department>();
}

class NGO{
@OneToMany(mappedBy="visitedBy")
List<Visit> ngoVisites=new ArrayList<Visit>();
@Column
String name;
}

class Department{
@ManyToOne
College college;
@Column
Date festDate;
}

class Visit{
@ManyToOne
College visitedTo;
@ManyToOne
NGO visitedBy;
}

I done this by using java code first i fetched all Department order by recently organised fest,then retrieve desire colleges following some java,as mentioned below.That is not good practice since we are unnecessary fetching all departments from db.Can anyone provide me criteria which can give me only five colleges-

Criteria cr=session.createCriteria(Department.class);
List<Department> departments=cr.add(Restriction.addOrder(Order.desc("festDate"))).list();

List<College> colleges=new ArrayList<College>();
for(Department department:departments){
for(Visit visit :department.getCollege().getCollegeVisites()){
if(visit.getName().equals("xyz")&& !colleges.contains(department.getCollege()){
colleges.add(department.getCollege())
break;
}
if(colleges.size()=>5){
break;
}
}

@StefanSteinegger I have attached my code in question.please help me to find solution.I am new in Criteria. — , Feb 16 '16 at 12:10

score 0 · Answer 1 · answered Feb 17 '16 at 07:17

0

Criteria criteria = session.createCriteria(Department.class)
.createAlias("college", "college")
.createAlias("college.collegeVisites", "collegeVisites")
.add(Restrictions.eq("collegeVisites.name","xyz" ))
.addOrder(Order.desc("festDate"))
.setMaxResults(5);

answered Feb 17 '16 at 07:17

Amit

36
7

This is almost correct. It doesn't work correctly when there are multiple departments in the same collage where the festDate is in the range. Then, the same college is found twice (or even more) and setmaxResults(5) doesn't return five colleges anymore. The requirements say "list of five colleges". This is nor easy to fix. It requires subqueries. The problem is that the max results and the order by needs to be on the same query or subquery, but this always causes the issue. – Stefan Steinegger Feb 17 '16 at 09:55

score 0 · Answer 2 · answered Feb 18 '16 at 07:46

0

Below code resolved my problem.Thanks Amit.

Criteria criteria = session.createCriteria(Collage.class)
.createAlias("collegeVisites", "collegeVisites")
.createAlias("departments", "departments")
.add(Restrictions.eq("collegeVisites.name","xyz" ))
.addOrder(Order.desc("departments.festDate"))
.setMaxResults(5);

answered Feb 18 '16 at 07:46

@Stefan Steinegger .exactly on joinning over ordered department,hibernate giving duplicate collages,is there any hibernate session method to get only uniqe result.can you please help us. – Feb 18 '16 at 07:51
The problem is order by festDate (which is in departments) and max results of collages. You could group by collages and order by min(festDate). This is probably the simplest solution. – Stefan Steinegger Feb 18 '16 at 13:09

score 0 · Accepted Answer · answered Feb 18 '16 at 13:40

Unfotunately, I'm a NHibernate user and C# programmer, so bear with me it might have some syntax problems here.

Criteria collageQuery = session
  .createCriteria(Collage.class, "collages")
  .createAlias("collageVisites", "collageVisites")
  .createAlias("departments", "departments")
  .setProjection(Projections.projectionList()
    .add(Projections.groupProperty("collages.id"))
    .add(Projections.min("departments.festDate", "minfestDate")))
  .add(Restrictions.eq("collegeVisites.name","xyz" ))
  .addOrder(Order.desc("minfestDate"))
  .setMaxResults(5);

Should create a query like this (DBMS specific, probably SQL Server syntax here):

SELECT TOP (5) 
  collage.id,
  min(department.festDate)
FROM collage
  inner join collageVisites ...
  inner join department
WHERE collageVisites.name = "xyz"
GROUP BY collage.id
ORDER BY min(department.festDate)

It returns the collage ids and the min(festDate) (because of the order by, this is required). You could try to get full collages back, but it is not so easy. At the end it will be easier to get the collages with session.get(id) afterwards, they are only five anyway.

When ever we applying condition over reference entity and fetching data of parent table and there is a case of duplicacy.Is subqueries only solution of this or session is also provide some method to handle this? — , Feb 19 '16 at 13:16
You can use distinct (as in SQL), but it is hard to control because it depends on the column that are selected. You can also use the "distinct root transformer", which is also hard to control because it tries to fix it in memory after query execution and doesn't work well with setMaxResults. Read more here: http://stackoverflow.com/questions/300491/how-to-get-distinct-results-in-hibernate-with-joins-and-row-based-limiting-pagi and here http://stackoverflow.com/questions/10731723/how-to-add-distinct-in-hibernate-criteria — Stefan Steinegger, Feb 22 '16 at 09:35

Unable to write criteria with reference classes

3 Answers3