Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySql Date Query using Hibernate

Posted on 2004-10-29
15
Medium Priority
?
559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

636 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