-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: trouble mapping java.sql.Timestamp to SQL TIMESTAMP
PostPosted: Mon Jun 26, 2006 1:31 pm 
Beginner
Beginner

Joined: Thu Apr 13, 2006 12:56 pm
Posts: 23
Hi,

I'm trying to map 'logoutTime' java.sql.Timestamp property to SQL TIMESTAMP, but SchemaExport keeps generating DATETIME instead. How can I generate TIMESTAMP?

thanks,
-nikita

Hibernate version:
3.1
Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="net.eviltwinstudios.common.beans.UserSession"
table="user_sessions"
>

<id
name="id"
column="id"
type="java.lang.String"
>
<generator class="assigned">
</generator>
</id>

<version
name="hibernateVersion"
column="hibernateVersion"
type="java.lang.Integer"
unsaved-value="null"
/>

<property
name="loginTime"
type="java.sql.Timestamp"
update="true"
insert="true"
column="loginTime"
/>

<property
name="logoutTime"
type="timestamp"
update="true"
insert="true"
column="logoutTime"
/>
</class>

</hibernate-mapping>

POJO Source :

Code:
    /**
     * @hibernate.property type="timestamp"
     */
    public Timestamp getLogoutTime() {
        return logoutTime;
    }

    /**
     * @hibernate.property
     */
    public Timestamp getLoginTime() {
        return loginTime;
    }

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Mysql 5.0 InnoDB
The generated SQL (show_sql=true):
DROP TABLE IF EXISTS `evilsite`.`user_sessions`;
CREATE TABLE `evilsite`.`user_sessions` (
`id` varchar(255) NOT NULL,
`hibernateVersion` int(11) NOT NULL,
`loginTime` datetime default NULL,
`logoutTime` datetime default NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 26, 2006 11:19 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Are you sure you don't want datetime? It's pretty rare to have both a version and a timestamp column, and I've never heard of a table with two timestamp columns like you've got. Hibernate only supports one timestamp per class. Look at the <timestamp> element, ref docs section 5.1.8.

Just so that you're clear: most DBMSs, including (afaik) MySQL, have broken from SQL standards and use datetime as the SQL type for recording dates and/or times. timestamp is used as the SQL type for timestamping a row's change: it's just a row-version type, usually a long integer value, much like java's Date type (a time since an epoch).

If you're confident that timestamp is the correct SQL type, then you can edit org/hibernate/dialect/MySQLDialect.java, and change the line
Code:
      registerColumnType( Types.TIMESTAMP, "datetime" );
Replace the datetime with timestamp. Then rebuild the jar. Do verify that the timestamp SQL type does what you expect it to do, first.

_________________
Code tags are your friend. Know them and use them.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jun 26, 2006 11:24 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Verified that timestamp is significantly different than datetime on MySQL, though it's not a simple int as it is on some DBMSs. From the MySQL manual:
  • DATETIME

    A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.
  • TIMESTAMP

    A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037.

    A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. By default, the first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value. Variations on automatic initialization and update properties are described in Section 11.3.1.1, “TIMESTAMP Properties as of MySQL 4.1”.


So that's the same as the <timestamp> element, but hibernate supports only one of those per mapping.

_________________
Code tags are your friend. Know them and use them.


Top
 Profile  
 
 Post subject: Hib 3.1.2: TIMESTAMP type mapped to DATETIME for mySQL
PostPosted: Fri Jan 12, 2007 5:01 am 
Newbie

Joined: Fri Jan 12, 2007 4:50 am
Posts: 6
Agreed that TIMESTAMP in mySQL behaves differently from DATETIME, that's why it is a different mySQL type. Apparently in JIRA there was the same issue in Oracle and it has been fixed. The Hibernate documentation states that TIMESTAMP maps to the SQL TIMESTAMP type. So is it something that should be fixed in Hibernate or in the documentation?

I have the following mapping:
<hibernate-mapping>
<class name="events.Event" table="EVENTS">
<id name="id" column="EVENT_ID">
<generator class="native"/>
</id>
<property name="date" type="timestamp" column="EVENT_DATE"/>
<property name="title"/>
</class>
</hibernate-mapping>

and here the DDL generated from the log:

[java] 17:09:25,843 INFO Environment:479 - Hibernate 3.1.2
...
[java] 17:09:27,626 DEBUG SchemaExport:296 - create table EVENTS (EVENT_ID bigint not null auto_increment, EVENT_DATE datetime, title v
archar(255), primary key (EVENT_ID)) type=InnoDB

This is not correct, the TIMESTAMP is the first one on the table and it still is not mapped to a TIMESTAMP type.

I am using Hibernate 3.1.2, not sure it has been fixed in the latest version, I can't upgrade right now.

Should this be a JIRA issue?

Thanks

Fred


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 12, 2007 5:08 am 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Hibernate type="timestamp" is MySQL DATETIME, nothing to fix. If you want type="mysql_timestamp", write a UserType.

_________________
JAVA PERSISTENCE WITH HIBERNATE
http://jpwh.org
Get the book, training, and consulting for your Hibernate team.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 12, 2007 5:27 am 
Newbie

Joined: Fri Jan 12, 2007 4:50 am
Posts: 6
Not sure I understand the meaning of the reply... Maybe you can expand a bit? Hibernate TIMESTAMP maps to my SQL-DATETIME because...

The Hibernate documentation states:

5.2.2. Basic value types
The built-in basic mapping types may be roughly categorized into
integer, long, short, float, double, character, byte, boolean, yes_no, true_false
Type mappings from Java primitives or wrapper classes to appropriate (vendor-specific) SQL column
types. boolean, yes_no and true_false are all alternative encodings for a Java boolean or
java.lang.Boolean.
string
A type mapping from java.lang.String to VARCHAR (or Oracle VARCHAR2).

date, time, timestamp
Type mappings from java.util.Date and its subclasses to SQL types DATE, TIME and TIMESTAMP
(or equivalent).

So how do we get the "TIMESTAMP" type in Hibernate to map to the "TIMESTAMP" in my SQL? Or should the documentation state that the Hibernate TIMESTAMP maps to DATETIME?

Thanks


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jan 12, 2007 6:54 am 
Newbie

Joined: Fri Jan 12, 2007 4:50 am
Posts: 6
Not sure I understand the meaning of the reply... Maybe you can expand a bit? Hibernate TIMESTAMP maps to my SQL-DATETIME because...

The Hibernate documentation states:

5.2.2. Basic value types
The built-in basic mapping types may be roughly categorized into
integer, long, short, float, double, character, byte, boolean, yes_no, true_false
Type mappings from Java primitives or wrapper classes to appropriate (vendor-specific) SQL column
types. boolean, yes_no and true_false are all alternative encodings for a Java boolean or
java.lang.Boolean.
string
A type mapping from java.lang.String to VARCHAR (or Oracle VARCHAR2).

date, time, timestamp
Type mappings from java.util.Date and its subclasses to SQL types DATE, TIME and TIMESTAMP
(or equivalent).

So how do we get the "TIMESTAMP" type in Hibernate to map to the "TIMESTAMP" in my SQL? Or should the documentation state that the Hibernate TIMESTAMP maps to DATETIME?

Thanks


Top
 Profile  
 
 Post subject: Re: trouble mapping java.sql.Timestamp to SQL TIMESTAMP
PostPosted: Thu Mar 11, 2010 12:46 pm 
Newbie

Joined: Mon Jul 20, 2009 5:09 pm
Posts: 16
Quote:
So how do we get the "TIMESTAMP" type in Hibernate to map to the "TIMESTAMP" in my SQL?


Why was this question never answered?

How can I tell Hibernate that it should map to MySQL TIMESTAMP instead of a DATETIME?


Thanks in anticipation.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.