Can't seem to load datetime column

nbc · **Posted:** Mon Jun 09, 2008 9:35 am

I have a mysql database with a couple of columns of type 'DATETIME'. My Hibernate config file looks like:

.... other column definitions....
<property column="hostList" name="hostList"/>
<property column="startTime" name="startTime"/> *** Last 2 are datetime
<property column="stopTime" name="stopTime"/>

Tried changing this to

<property column="startTime" name="startTime" type="calendar"/>
<property column="stopTime" name="stopTime" type="calendar"/>

Regardless of which I use, when I try loading the table, I get this in the tomcat log:

Hibernate: select imtest0_.id as id7_, imtest0_.userId as userId7_, imtest0_.supportGroup as supportG3_7_, imtest0_.maintName as maintName7_, imtest0_.hostList as hostList7_, imtest0_.startTime as startTime7_, imtest0_.stopTime as stopTime7_ from imtest imtest0_
Cannot convert value '0000-00-00 00:00:00' from column 7 to TIMESTAMP.
Failed to load IMTest: Exception org.hibernate.exception.GenericJDBCException: could not execute query

The table definition is:
mysql> describe imtest;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| userId | varchar(64) | NO | | | |
| supportGroup | varchar(64) | NO | | | |
| maintName | varchar(64) | NO | | | |
| hostList | varchar(1024) | NO | | | |
| startTime | datetime | NO | | | |
| stopTime | datetime | NO | | | |
+--------------+---------------+------+-----+---------+----------------+

The data looks like this if I select it from mysql:
mysql> select * from imtest;
+----+---------+--------------+---------------+--------------------+---------------------+---------------------+
| id | userId | supportGroup | maintName | hostList | startTime | stopTime |
+----+---------+--------------+---------------+--------------------+---------------------+---------------------+
| 1 | nbc | GEO | nbcMaint1 | GEO_abc, GEO_def | 2008-06-09 12:34:00 | 2008-06-10 12:34:00 |

Can someone tell me what I'm doing wrong? I'm not having any trouble with columns of ints or strings, but I'm missing something in handling dates...

thanks,

nbc

Cameron McKenzie · **Posted:** Mon Jun 09, 2008 10:31 am

There are three TemporalTypes you can work with, DATE, TIME and TIMESTAMP.

Quote:

When storing time based information in a database, it can be stored in one of three ways: as a time, as a date, or as a time-date combination known as a TIMESTAMP. If the Hibernate framework sees a time based property, it will be treated as a TIMESTAMP unless you tell Hibernate otherwise. For the instances of the Calendar and Date fields in our User class, we will decorate the getters for our fields with a @Temporal annotation, and use the TemporalType constants to clarify the property as being of type TIME, DATE or TIMESTAMP.

Using JPA annotations, they'd look like this. Not exactly a solution to your mapping file, but it might point you in the right direction:

Code:

@Temporal(TemporalType.TIMESTAMP)
public java.util.Date getLastAccessTime() {
  return lastAccessTime;
}

Code:

@Temporal(TemporalType.DATE)
public java.util.Calendar getRegistrationDate() {
  return registrationDate;
}

I pulled this info from a tutorial on column mappings, from my website. You might find it useful:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=06hibernatetableandcolumnmappingwithjpa

Good luck!

nbc · **Posted:** Mon Jun 09, 2008 10:43 am

Thanks - I'm think I'm going to spend some time at your website.... :)

A couple of followup questions...

1) Does this mean that I'm restricted to using TIMESTAMP data? Which means dates from 1/1/1970 -> x/y/2038? That isn't a problem for this application - just want to make sure...

2) I'm using Netbeans to build my web app, and I have not used any annotations myself - I've created the xyz.hbm.xml files to map the columns to class member names, but that is all. Can I add an annotation to one set of routines in the POJO or do I have to annotate everything in the class? I have not looked at that part of the hibernate manual yet....

thanks,

nbc