Hi everyone,
Well, I have two classes. One with the following code:
Code:
...
public class City implements Serializable, Comparable<City> {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
...
@OneToMany(mappedBy = "city")
private List<Hotel> hotels;
...
}
And other with this:
Code:
...
public class Hotel implements Serializable, Comparable<Hotel> {
private static final long serialVersionUID = 1L;
...
@ManyToOne
private City city;
...
}
As you can see, I just have a simple OneToMany association between City and Hotel. Well, here comes the problem. Suppose that I have to search the number of hotels for each city. So, I have three options.
First one - Just select all cities and then retrieve the number of hotels for each city exposing a method like this:
Code:
public int getNumberOfHotels() {
return (hotels != null) ? hotels.size() : 0 ;
}
But I discarded it. Because imagine a DB with 20000 cities, and each citie with 1000 hotels. So, due the fact of Hibernate by default uses the Lazy Loading on the Many side of the relation, this solution will use 1000 + 1 calls on the DB. One for each city.
Second one - Just select all cities and then retrieve the number of hotels for each city exposing a method like above, but instead of using Lazy Loading, now using Eager Loading. This is a very nice solution. Just one "select" on DB. But I had to retrieve a lot of information that I don't need at this moment.
Last one - Use Projections to retrieve that information. I've tried but I couldn't find a nice way. As I sad I've just needed to retrieve the city object with the number of hotels in it. And I did this:
Code:
ProjectionList pl = Projections.projectionList();
pl.add(Projections.groupProperty("id"));
pl.add(Projections.rowCount());
Criteria criteria = createCriteria();
criteria.createAlias("hotels", "hotel");
criteria.setProjection(pl);
objects = criteria.list();
But the result just is an ArrayList of Objects Array, which the first element of the array as a Long and the Second as an Integer. And it wasn't what I wanted. So after look over the internet and inside this forum I've found a way of dealing with this problem. I have to agree it's not a good way, but...
Code:
ProjectionList pl = Projections.projectionList();
pl.add(Projections.groupProperty("id").as("id"));
//
// Put here all city fields manually with tthe correct alias.
//
pl.add(Projections.rowCount().as("numberOfHotels"));
Criteria criteria = createCriteria();
criteria.createAlias("hotels", "hotel");
criteria.setProjection(pl);
criteria.setResultTransformer(new
AliasToBeanResultTransformer(CCity.class));
objects = criteria.list();
It works, but if you have to change something on city class, you have to add another projection with alias on this search. So here is my question, is there another way to retrieve this information, with the city obj and the number of hotels, without using AliasToBeanResultTransformer Class? Something like:
Code:
...
pl.add(Projections.groupProperty("this"));
pl.add(Projections.rowCount());
...
Thanks for you time.
Felipe Desiderati.
Login
FAQ
Search