• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

MySql Date Query using Hibernate

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
lomidien
Asked:
lomidien
  • 6
  • 5
  • 4
2 Solutions
 
kiranhkCommented:
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
 
zzynxSoftware engineerCommented:
Try

from com.PackageNamesHere as code where code.StartTime > '2004-10-27'
0
 
lomidienAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
zzynxSoftware engineerCommented:
forgot the time part:
     from com.PackageNamesHere as code where code.StartTime > '2004-10-27 21:47:10'
0
 
zzynxSoftware engineerCommented:
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
 
lomidienAuthor Commented:
Sorry, forgot that tidbit of info...might have been helpful, huh?  :)

StartTime is a Date().
0
 
kiranhkCommented:
sorry, i read it as MS SQL
0
 
lomidienAuthor Commented:
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
 
zzynxSoftware engineerCommented:
>> 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
 
lomidienAuthor Commented:
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
 
lomidienAuthor Commented:
I would use JDBC to query my results, but that breaks my JTable code which reads in objects mapped from Hibernate.
0
 
kiranhkCommented:
can you post your hibernate mapping xml
0
 
lomidienAuthor Commented:
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
 
zzynxSoftware engineerCommented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now