Which Date class to use with mysql datetime

vator · **Joined:** Sat Dec 01, 2007 4:34 pm **Posts:** 20

My app is connected to a mysql database. I want to register some info about a product in a table including the date the row is added.

This is my table in mysql
Product:

Code:

ProductId primarykey int(4)
Comment varchar(100)
logDate    dateTime

Hibernate mappping class
Product.hbm.xml

Code:

<hibernate-mapping>
  <class name="db.Product" table="Product">
    <id name="ProductID"  type="long" column="ProductID">
      <generator class="native"/>
    </id>
    <property name="comment"    column="Comment"    type="string"/>
    <property name="logDate"     column="logDate"/>
  </class>
</hibernate-mapping>

1. question: what type should I use for the property logDate?

Code:

Hibernate entity class
Product.java

Code:

public class Product {
  private Long ProductID;
  private String comment;
  [b]private java.util.Date logDate;[/b]

 Setter and getters...

2. question: What kind of dateclass should I use?
Now I'm using java.util.Date, but nearly the whole class is deprecated!!!!
There is no point in using java.sql.Date either, because you got the same problem there....

In some parts of my code I want to seperate the logTime from the date. Should I use GregorianCalendar?

For me working with dates in java seems unnesessarily complicated.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

You should use java.util.Date on the Java side.
Try mapping it as either "date" or "timestamp".

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

gonzao_diaz wrote:

You should use java.util.Date on the Java side.
Try mapping it as either "date" or "timestamp".

This is exactly how I mapped but somehow I loose the time value when saved into the database.

It is defined as a java.util.Date in my class
and mapped as

Code:

and while saving as:

Code:

obj.setDateTimeField(new Date());
session.save(obj);

Only the date is stored and the time value in MySql gets stored as 00:00:00.

I also tried changing the mapping as a sql type as "datetime" which is what it is in the database, but to no avail. Am I missing something in my mapping?

Code:

<property name="dateTimeField" >
     <column name="dateTimeColumn" sql-type="datetime" />
</property>

ivan2010 · **Joined:** Fri Feb 08, 2008 2:41 pm **Posts:** 18

What if you try:

Code:

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

no change :-?

ivan2010 · **Joined:** Fri Feb 08, 2008 2:41 pm **Posts:** 18

Instead of type="date" or sql-type="datetime" try type="timestamp"

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

ivan2010 wrote:

Instead of type="date" or sql-type="datetime" try type="timestamp"

Yeah that is exactly what I was referring to, it does not work.

Interestingly when I do a cast using the java.sql.TimeStamp wrapper on my date field before setting it to my object using its corresponding setter and save WITHOUT ANY CHANGES (no type or sql-type defined) in the mapping, it seems to work

Code:

java.sql.Timestamp ts = new java.sqlTimestamp(new Date());
obj.setDate(ts);

But, I am interested in a mapping solution, where in it saves the time information as well and while retrieving gets me a java Date.

simit · **Joined:** Thu May 14, 2009 10:34 am **Posts:** 11

I am sorry, I might be asking very obvious thing, but Why don't you use, java.sql.Date for your logDate, instead of java.util.Date???

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

simit wrote:

I am sorry, I might be asking very obvious thing, but Why don't you use, java.sql.Date for your logDate, instead of java.util.Date???

Sorry for the late reply, we are already into production and I believe its too late for such changes, although I have not yet implemente your idea of using java.sql.Date instead of java.util.Date. I am trying to see if there are any other options.

Also, I was going through articles and found that there will be issues when when you compare this java.sql.Date with a java.util.Date?

This must have been handled by many already and I was wondering to find the best or correct approach!

sidni · **Joined:** Fri May 29, 2009 2:21 am **Posts:** 12

Going thru the post, Primarily there r 2 concerns:

1) HB Mapping to fulfill storage of date and time value.

Java Side:

Code:

private java.util.Date dateCreated

HB Mapping:

Code:

<property name="dateCreated" type="java.util.Date">
      <column name="dateCreated" sql-type="datetime" />
   </property>

DB side:

Code:

dateCreated datetime

This works smoothly for me (proper values for time on DB side). I wld suggest u re-try it as above.
If this does not work go thru below link:

http://www.theresearchkitchen.com/blog/archives/58

2) Retrieving datetime value from DB.

Code:

Parent parent1 = new Parent();
      parent1 = (Parent) session.load(Parent.class, new Long(1));
Calendar ca = Calendar.getInstance();
      ca.setTime(parent1.getDateCreated());
System.out.println("Cal" + ca.HOUR);

As we have retrieved Hour value, likewise we can have any other related values.

Hope this helps.

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

Thanks sidni,

I will give it one more try again!

Every thing you said was already tried as discussed in one of my responses.

For some reason, as I said, I had to always cast using the java.sql.TimeStamp in order to store the actual time in the database.

Code:

obj.setDate((java.sql.TimeStamp)(new Date())
session.save(obj);

The article that you referenced discusses an option where you want to persist the
"day-level-precision Date field" into a MySql dateTime or timeStamp column to avoid failing comparison issues. My problem lies with persisting even the time value of the java.util.Date into the MySQL DateTime column.

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

Code:

<property name="recordAddedDateTime" type="java.util.Date">
      <column name="record_added_date_time" sql-type="datetime">
      </column>
</property>

did not help guys. Any other solutions?

I reconfirmed that my recordAddedDateTime field/property is of type java.util.Date
and record_added_date_time column is of type java.sql.datetime

pvradhakrishna · **Joined:** Tue Feb 19, 2008 6:05 pm **Posts:** 82

Reallly sorry guys for wasting your time and effort.

We have a specific formatted Date class that returns the application specific formatted date.

I have been testing two scenarios where in one case I take a MM/dd/yyyy format date from user and save it into the database and in the other case I save a formatted date both actually truncating the time value of the date before persisting it to the database.

<property name="recordAddedDateTime" column="record_added_date_time" />

This alone was good enough to map a java.util.Date to java.sql.DateTime to save the timevalue of the date field.

Solved!