?
Solved

MySql Date Query using Hibernate

Posted on 2004-10-29
15
Medium Priority
?
563 Views
Last Modified: 2012-05-05
The following two querystrings were used in my code to pull up objects which had date values greater than those specified.  I'm unsure how to format the date to query MySql I guess because both of these pull up every record regardless of date.  Any suggestions?

from com.PackageNamesHere as code where code.StartTime > 'Wed Oct 27 21:47:10 EST 2004'
from com.PackageNamesHere as code where code.StartTime > '1098931590421'

Thanks,
David
0
Comment
Question by:lomidien
  • 6
  • 5
  • 4
15 Comments
 
LVL 8

Expert Comment

by:kiranhk
ID: 12444909
Did you check this out

http://www.hibernate.org/80.html

Microsoft SQL Server
License:  Commercial
URL:  http://www.microsoft.com/sql/default.asp
Issues:  Version supported: 2000, but creating a dialect for version 7 should be easy.
 JDBC drivers: jTDS, open source, LGPL: also used with Sybase. If you are using triggers, Hibernate will get confused when the first result returned by a statement is "0" (in jTDS, use the parameter lastUpdateCount=true in the driver -- this is the default value, anyway).
 The following commercial drivers are known to work: JSQL , JTurbo, Weblogic jDriver. It is quite likely that you will be able to use DataDirect Connect for JDBC. There are some weird date-handling issues with this driver, so it is not officially supported. Don't bother with the DataDirect SequeLink driver; it has very serious problems. We have had great luck (not a single issue) with the inet drivers but they are relatively expensive.  
 Use some driver other than Microsoft's own! If you use the MS JDBC driver, these are the issues you will face: Blobs and Clobs are not supported. Problems with Dates: MSSQL when you search for dates wants to search by seconds and milliseconds. When you convert a SQL date to a Java Timestamp object, and then back again, you seem to lose the milliseconds. The seconds match, but not the milliseconds. So don't use date's as part of a composite key/primary key. Reportedly, the Microsoft JDBC driver is quite slow, you have to add sendStringParametersAsUnicode=false to JDBC URL to increase performance. You must always use selectMethod=cursor. You may get a "Reread" error when reading tables with NVARCHAR or Images. You can solve that issue by proxying the MS JDBC Driver: MSJdbcProxy.
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12444929
Try

from com.PackageNamesHere as code where code.StartTime > '2004-10-27'
0
 

Author Comment

by:lomidien
ID: 12444967
It's actually MySql server, so I'm not sure if that url applies.  :(

zzynx,

I tried that, but not much luck...I've also tried the queries I posted but minus the "'" on each end of the date.  I'm stressing now....very little left on this to go and my deployment is supposed to be today.  YIKES!!!!!

Any other ideas?

Thanks,
David
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 37

Expert Comment

by:zzynx
ID: 12444972
forgot the time part:
     from com.PackageNamesHere as code where code.StartTime > '2004-10-27 21:47:10'
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12444987
What type is StartTime?

DATE: YYYY-MM-DD (Four digit year followed by two digit month and date)
TIME: hh:mm:ss (Hours:Minutes:Seconds)
DATETIME: YYYY-MM-DD hh:mm:ss (Date and time separated by a space character)
TIMESTAMP: YYYYMMDDhhmmss
YEAR: YYYY (4 digit year)
0
 

Author Comment

by:lomidien
ID: 12445004
Sorry, forgot that tidbit of info...might have been helpful, huh?  :)

StartTime is a Date().
0
 
LVL 8

Expert Comment

by:kiranhk
ID: 12445020
sorry, i read it as MS SQL
0
 

Author Comment

by:lomidien
ID: 12445043
kiranhk,

Yeah, in fact I came across that page when I googled and had read it just before you posted.  Then I reread thinking I had missed something.  :)

I've always hated working with dates!!!!!

David
0
 
LVL 37

Assisted Solution

by:zzynx
zzynx earned 1000 total points
ID: 12445110
>> StartTime is a Date().
Well at http://www.webdevelopersnotes.com/tutorials/sql/mysql_development_tutorial_mysql_date_column_type_part_1.php3 I read:

Characteristics of Date
MySQL dates are ALWAYS represented with the year followed by the month and then the date.
Often you'll find dates written as YYYY-MM-DD, where YYYY is 4 digit year, MM is 2 digit month and DD, 2 digit date.

So...
0
 

Author Comment

by:lomidien
ID: 12445111
I tried the query below:

select * from dtevent where starttime > '2004-10-27 21:47:10'

using straight JDBC and this as my query string and it works fine, however using what we had posted above and with the correct date formatting from the JDBC sql line, it returns all records.  I would have thought that the HQL implementation for date queries would function almost identically, this must not be the case.

Still stuck.  :(

David
0
 

Author Comment

by:lomidien
ID: 12445122
I would use JDBC to query my results, but that breaks my JTable code which reads in objects mapped from Hibernate.
0
 
LVL 8

Expert Comment

by:kiranhk
ID: 12445147
can you post your hibernate mapping xml
0
 
LVL 8

Accepted Solution

by:
kiranhk earned 1000 total points
ID: 12445184
0
 

Author Comment

by:lomidien
ID: 12445254
If I used a NamedQuery posted within the mapping document, it will work just fine.  I'm sure it has to do with Hibernate's handling of converting java.util.Date and because using straight HQL, I was passing in a string, it was not quite jiving for whatever reason.  Who knows, but not that it's mapped in a file and not hard-coded, it works fine.

How about I split the points for you guys?

Thanks,
David
0
 
LVL 37

Expert Comment

by:zzynx
ID: 12445310
Thanks
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month4 days, 3 hours left to enroll

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question