This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate & Date

 
Vinnie Jenks
Ranch Hand
Posts: 207
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Vinnie Jenks
Ranch Hand
Posts: 207
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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 ]
 
Vinnie Jenks
Ranch Hand
Posts: 207
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic