I'm having a little bit of trouble figuring out why while querying records by a date isn't working correctly for me.
I'm using Hibernate 3.0.5 and mysql 4.0.x.
I have a method where I pass a date and internally it queries for between the date passed in and one day prior.
In use, I would have passed this method something like this:
The query that Hibernate generates looks like this:
...which is great, that's exactly what I want....however it doesn't pull any data...the list comes up empty.
When I step through the debugger and watch the values...I can see that the date range is clearly correct when it is passed into the Critera query...and if I do this manually against the database:
...I pull up data just as I would have expected Hibernate to do.
What am I doing wrong?
How can I make this work correctly w/o destroying database portability w/ Hibernate? I'm quite new to Hibernate and still fairly new to Java and don't understand all the semantics w/ the Date type yet.
Originally posted by Mark Spritzler: The big thing I'd point out is that time plays a role in the query. Each of those dates is as of midnight on those days. So only records on 10/20/2005 will be returned.
Yeah, I realize that...in fact when I omit the date on the end of my manually-executed query it should be going from midnight-to-midnight of each day, in which case the records are definitely there. They're dated for sometime around 3pm on the 20th.
I even tried going back a few days to get even more of the records from that month...it won't pull anything.
I forgot to mention...I'm pretty unfamiliar w/ mysql too...but according to the Hibernate documentation - I should be OK.
One thing the Date constructor you are using is deprecated.
getToDate returns a Date class.
I'd use the Calendar.getInstance().set(int year, int month, int day) method to create a Calender then call getTime() to return a Date class of that date. Just to make sure it isn't about the Date class.
But your query does look correct to me.
Mark [ October 24, 2005: Message edited by: Mark Spritzler ]
Hmm. I was thinking something simmilar. Hibernate ultimately maps every date to a Timestamp (despite you asking for a java.util.Date or java.sql.Date) - which is something to do with different Date data type precisions between DBs I think. But that should only be an issue if you were using the date for a Java comparison (equals will fail, for example if you compare a Date with a Timestamp). But since the comparison is failing after the date has been translated into native SQL it can't be that. Except perhaps that MySQL's BETWEEN function will fail if the two variables are not of the same type - though I'm not sure how the types would end up different.
Looking through the application I am currently working on, we use this kind of comparison all over the place without issue. Can you trace the DB operation? I don't know if MySQL has this capabilities, but you might like to try setting the Hibernate logging levels so it logs the values it is binding (looking at the values in a debugger can be misleading because of the involvement of CGLIB). [ October 25, 2005: Message edited by: Paul Sturrock ]
Sorry to resurrect an old topic but I thought I'd add some extra info that should help anyone that's coming into this thread via a search engine (like I was).
If you are are using more recent versions of Hibernate and Log4J, setting the org.hibernate.type package to display logs with a DEBUG level isn't going to help you. In the latest versions you have to use TRACE to see the parameterts in the logs. E.g.
In your log4j.properties:
And then in your log you should see something like: