aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate & Date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate & Date" Watch "Hibernate & Date" New topic
Author

Hibernate & Date

Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
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.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
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.

Mark


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.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17259
    
    6

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 ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Just to make sure it isn't about the Date class.

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 ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Vinnie Jenks
Ranch Hand

Joined: Apr 26, 2004
Posts: 207
Good call, Paul, I hadn't thought to do this but it revealed the problem.

Here's what I see:



It was chopping a month off of the dates! Anyhow, it was a bug in the Calendar I wrote...even though it was showing the correct date it wasn't passing the correct date.

Die bugs die!

Thanks!
[ October 25, 2005: Message edited by: Mark Spritzler ]
Lee Theobald
Greenhorn

Joined: Aug 16, 2007
Posts: 3
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:
 
 
subject: Hibernate & Date