Sorry if this is a silly question. I want to retrieve objects from the database where I get last last two months of the objects (based on a property of the object). For example, if it's August, and there are objects in the database for April, May, June and July (but not August), I want to retrieve the objects from June and July. Is it possible to write an hql query which will do this in one step (without the particulary months being hard-coded)?

But of course. Asuming the java class is MyClass with a property myDateProperty, how about something like:

Sorry - I need to do that, but I'm trying to do it with a collection. I know I can set a "where" for retrieving the elements of a collection, but how can I specify the start and end dates in the collection mapping?

If that isn't possible, is it possible to restrict the number of objects returned by the database in the "where" attribute of a collection mapping? So I could, say, sort the results by date, but only retrieve the last 25 from the database?

Thanks for your help so far.

Ar you talking about in the mapping file? The where clause on the collections elements cannot be parameterized there.

You could set this to be a lazy collection and then filter the collecton based on the dates you want using session.filter();

Yes, I meant in the mapping file.

Wouldn't filter() load the data from the database, then filter it? (I realized applying a filter doesn't immediately load the lazy collection, but when it is loaded...)

I apologize, I guess I should have explained myself better in the first place - I have a collection of link objects in the database. However, for most of my pages I only want to display the links from the last two months that have links. I thought that perhaps I could store them all in one collection in the database, but only load the ones I was going to display as one collection, but still have the other accessible as another collection.

On another note, you guys are great with this message board. Really, getting a response within a few hours on an free$, open-source project is really something. Thanks!

no, of course not. The whole point of filter() is that it allows _efficient_ filtering, at the database. Please read the Hibernate manual.

I did read the javadocs. Session.filter() says:

Filters allow efficient access to very large lazy collections. (Executing the filter does not initialize the collection.)

About efficiency. Nothing about how the data is loaded from the database. :-(

I take it then, that there's no way to limit the number of results returned with the "where" attribute on a collection in a mapping file?

Having limits on the number of returned objects in the "where" attribute just don't make sense - does it ?

Are you maybe instead looking for session.createFilter(Object collection, String filter) ? This one would allow you to get a Query object back on which you can do setMaxResultSize() etc..

I was looking for a limit on the number of returned objects so I could have a collection of only the recent objects, but I see using filter and the Lifecycle interface might allow me to load only the recent objects as well.

But that returns me to my original query question. Is it possible to write a query like:

from LinkDAO where
dateCreated between max(LinkDAO.dateCreated) and
(max(LinkDAO.dateCreated) - 2 months)

?

of course. this is what subqueries are for.

1. How on earth can you do "max(LinkDAO.dateCreated) - 2 months", even with a subquery?
2. I can't use subqueries because I'm using MySQL, which doesn't support subqueries :-( Is there any other single statement way?

That doesn't work. I'm pretty sure "2 months" isn't something you can put in an sql statement. And even if it was, that produces sql which uses subqueries (yes, I tried it). But let's just say screw it, I'll use 2 queries - 1 to retrieve the date of the most recent entry, and the other will retrieve those entries.

Now I should have a lazily loaded collection with all the Link objects in it called AllLinks. And I'd like to use createFilter to initialize a second, non-persisted collection of the recent objects from the first collection called NewLinks. And I'd like to do that automatically when the object loads, because my web application follows a LoadData-DisplayData pattern. However, this appears to be impossible since the Lifecycle.onLoad() docs state: "It is illegal to access the Session from inside this method".

Is there actually a way to query the database automatically when an object is loaded?

Of course it produces subselects! (Actually, subselects work on some recent versions of MySQL.)

On some databases you can say things like " - 2 months". I assumed you knew your own equivalent platform syntax.



It is legal to re-enter the session in a UserType.