Hi everybody,

I got this many-to-many bidirectional association:

EVENT <-> EVENT_CATEGORY <-> CATEGORY

So basically I would like to get some events from the category x, and get the categories of the event y.
I do it like this in Category.hbm.xml :



As a lot of people I would like to display events contained in a category by using a pagination system.
I know how to do it with firstResult and maxResult in the case I want to display all events, whatever is the category.

Actually, with this many-to-many association, I fetch all events of the category, get them sorted in a set, convert them to a list (it looks like it's not possible to use directly a list with the "order-by" parameter...), and do:



It means all events of a category are sorted, fetched, and then I use just some of them. Isn't it wrong ? A lot of data are fetched and not used...

Moreover, I have to double check if the firstIndex and lastIndex are not out the available values.

It would be better to use a pagination by fetching only usefull nodes.
But how can I do this in the case of a many-to-many association ?
Of course, I can write my own SQL query in CategoryDAO, defining the junctions between EVEN, EVENT_CATEGORY, CATEGORY, specify the category I need and combine it with firstResult, maxResult...

But, tell me if I'm wrong, isn't it the aim of Hibernate to simplify the job, and offer me a simple category.getEvents() method instead of implementing my own categoryDAO.fetchPage(page_number, result_size) as I said above...

Any idea so far ?
What's the best :
- fetch all results and then use just some of them
- write a method in the DAO, using firstResult, maxResult and do by myself the junctions
- or there is a better solution I'm not aware of ?

Thanks for any help, advice.

Maybe you can use the Session.createFilter() method. Something like this:



For more information read this:
http://www.hibernate.org/hib_docs/v3/re ... -filtering

Thanks for answering.
It looks like it could be the solution to my question.
But some others questions came:


- Considering that fact that I usually use hibernateTemplate, how can I work on the session ? (obviously it's recommended in the documentation to use hibernateTemplate instead of opening session directly)

- You propose some code that I can implement and use in my controller (ShowEventsController):



Which means I will have to write an access to a session or hibernateTemplate in my Controller. Isn't it not recommended at all, regarding to MVC, to put stuff about Storage in a Controller ?

Finally, my question is still pending: is it bad to retrieve all the events of category (by reference) to then use just of them ? (in term of rapidity, cpu and memory cost ?)

Isn't it possible to define the first result and maxresult values directly in the .hbm.xml file for the association ? If not, I would like to find a solution, which does not corrupt the MVC separation.

Any ideas ?

I am not so sure I know what HibernateTemplate is... It is usually mentioned together with Spring... Is it the Spring documentation that recommends this?



Where should it go otherwise? Isn't it the job of the controller to provide access to and operate on the data?



I guess that depends on how many there are. If you have 10 events nobody will notice.



The only thing that comes to my mind is to define a custom SQL for loading as described by http://www.hibernate.org/hib_docs/v3/re ... -load.html. But if you define the first result in the custom SQL you will never be able to load the rest of the events so I can't see how this would solve your problem.

You are right, I mixed up, hibernateTemplate is recommended in the Spring documentation...
It's because I use Spring with hibernate, I'm a bit confused with all those new terminologies.

The custom queries would have been really interesting if it were possible to use them dynamically with parameters, for example Limit start,end .

So, I think I will have to use the categoryDAO and write myself Query, filtered with firstResult, maxResult. Every time I search on google, it's the solution I get.

Because I might have 10 thousands or more events per category, and it will increase contiously by the time.

May be I can inject the result, a set of filtered events, in a new field of my category or in the opposite way, write a new method category.getSomeEvents(first, max) which is calling the DAO to do that properly.

Using Spring with hibernateTemplate and Hibernate, I found no other solution for filtering a query than to do that:



I haven't modified this method to do the junction with a precise category, but here is the idea. This method looks awfull for me. If anyone thinks of a nicer way to write this kind of filtered method with hibernateTemplate, I'm really interested.

Thanks a lot for your answers nordbor !