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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 14 posts ] 
  Previous topic | Next topic 
Author Message
graderzhy
 Post subject: Timestamp type does not work proper in where clause
PostPosted: Tue Jan 17, 2006 6:58 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
========================================================

I have a problem, it is like following:
hbm.xml is as following:
------------------------
..
..
.
..
Code:
<composite-id name="id" class="com.....model.AlarmViolationLogId">
            <key-property name="alarmid" type="integer">
                <column name="ALARMID" />
            </key-property>
            <key-property name="readingdatetime" type="timestamp">
                <column name="READINGDATETIME" length="26" />
            </key-property>
        </composite-id>

.
.
.
-----------------------

pojo file is like following:

-----------------------------
Id pojo

Code:
public class AlarmViolationLogId  implements java.io.Serializable {


    // Fields   

     private Integer alarmid;
     private Date readingdatetime;
......
}


POJO:

Code:
public class AlarmViolationLog  implements java.io.Serializable {


    // Fields   

     private AlarmViolationLogId id;
     private double readingval;
     private double alarmval;
     private short violationcount;
     private double kwreadingval;
     private Date dateadded;
     private Integer gatewayid;
...
}


----------------------------

then the HQL is like following:
----------------------------
Code:
String query="select ....from ....where .....and alarmviolationlog.id.readingdatetime >= ? and alarmviolationlog.id.readingdatetime <= ? and site.siteid = ?";

arg0 is Object[3];

String dss="2005-08-23 22:55:00.000000000";
java.util.Date dates=new Date(Timestamp.valueOf(dss).getTime());
String dsg="2005-08-23 23:55:00.000000000";
java.util.Date dateg=new Date(Timestamp.valueOf(dsg).getTime());
arg0[0]=dates;
arg0[1]=dateg;
arg0[2]=new Integer(1580);

then

return getHibernateTemplate().find(query,arg0);

return an empty list,


If I remove the ---alarmviolationlog.id.readingdatetime <= ? and alarmviolationlog.id.readingdatetime <= ? --- from the where clause, it works fine, but this i s what I want, also it works fine if I use (less than compare operator <), I believe this is a bug of how to transfer the date to a string based on the DB dialect.

===========================

I am using Spring/Hibernate/DB2/Eclipse,

If I run the query directly on DB2, return 8 results, the only difference is that I am using '2005-08-23-22.55.00.000000000' and '2005-08-23-23.55.00.000000000', and it works fine. I use "'2005-08-23 22:55:00.000000000'", then the DB2 will complain.

{........s.siteid=1580 and readingdatetime < '2005-08-23-22:55:00' and readingdatetime < '2005-08-23-23:55:00'" | more
SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007
}


Also I noticed the log output from Eclipse is like :

DEBUG main org.hibernate.type.TimestampType - binding '2005-08-23 22:55:00' to parameter: 1
DEBUG main org.hibernate.type.TimestampType - binding '2005-08-23 23:55:00' to parameter: 2

======================================================

Really need help on how to compare the timestamp type in my situation?

Any suggestion will be appreciated?

Thanks
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Wed Jan 18, 2006 12:36 am 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Two points:

- It looks like you're using a timestamp as a datetime. That's quite wasteful: just use a datetime. That will save you a lot of DB space (there are no nanoseconds in datetimes), and it's less restrictive (you can only have one timestamp column per table, and it updates itself every time the row is modified). If you do only need a datetime, type="date" is what you want.

- SQL dialects vary. Find out exactly what format you DB server expects string representations of timestamps to look like (it will be in the server docs somewhere), and adhere to that. I think that most, or all, servers allow you to change the default format for all time types: if you don't want to modify your time strings, you can change the DB default format to conform to your requirements.
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Wed Jan 18, 2006 12:31 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
For your first point, this is not the case, we have to use timestamp, in my example, we just want to retrive the data for 1 hour, so the millseconds part is 000000. Also it is not true that one table can have only one timestamp column. Don't confuse with the timestamp of Hibernate and actual columns in Database.



For your second suggestion, we knew it is Ok to change the default timestamp format, but this will affect our existing application which already there, and we don't want to rewrite the existing application.

But Thanks anyway.

================================

Here what I want to find is that if there is any way that I can work around this issue. or if this is a bug of Hibernate.

Actually, if I change

Code:
String dss="2005-08-23 22:55:00.000000000";


to

Code:
String dss="2003-08-23 22:55:00.000000000";


it does return some results, but it is not what we want, and it is very wired result as following, there is no any result for 2004 and 2005 year, this is just one example from the unit testing log file:
Code:
DEBUG main org.hibernate.type.TimestampType - returning '2003-10-15 16:20:00' as column: col_2_0_


=======================

Any other suggestions?

Thanks in advance.
Top
 Profile  
 
CWitherow
 Post subject:
PostPosted: Wed Jan 18, 2006 1:35 pm 
Senior
Senior

Joined: Wed Aug 17, 2005 12:56 pm
Posts: 136
Location: Erie, PA (USA)
Try creating your parameters as instances of java.sql.Date rather than java.util.Date. The former handles milliseconds.

Curtis ...
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Wed Jan 18, 2006 1:43 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
I did that, does not work.

The issue is for DB2 , the default string representation for timestamp is yyyy-mm-dd-hh.mm.ss.nnnnnn.

But The ODBC string representation of a timestamp has the form yyyy-mm-dd hh:mm:ss.nnnnnn, that is why the comparation is very strange, my issue is that I don't want change my DB option since this will affect our existing application, I just want to find a way to change the string representation to match my DB2 represtation.

I am thinking maybe the last way is extending the Date class and overwrite the toString method, But I don't think it will work since the I believe the Hibernate will transfer the Date/Timestamp to some other format.

so here what I want to know is how to get that done to let the string representation to match my DB2 representation.

Any other suggestion?

Thanks
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Wed Jan 18, 2006 4:44 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Use a UserType. You'll be able to cut'n'paste most of the code from TimestampType, and put the special parsing code into nullSafeGet() and nullSafeSet().

BTW, Hibernate's Timestamp type is designed to handle SQL's Timestamp type. It can handle other types, but it is specifically designed to handle the one-column-only updated-by-DB SQL Timestamp type. So I'll stand by my first statement. And further, I'll add that if I were to try all of this here in my company, our DBA would bite my head off :)
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Wed Jan 18, 2006 5:05 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
This is what I am doing now, I am just trying to override the TIMESTAMP_FORMAT to "yyyy-MM-dd-HH.mm.ss" from "yyyy-MM-dd HH:mm:ss",

let me finish this.

Thanks
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Wed Jan 18, 2006 5:43 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
why hibernate can not know my defined type, and give me following exception:

Code:
[org.hibernate.mapping.Column(READINGDATETIME)]
org.hibernate.MappingException: Could not determine type for:  com.stonewatercontrols.service.SWTimestampTypeExtend, for columns: [org.hibernate.mapping.Column(READINGDATETIME)]
   at org.hibernate.mapping.SimpleValue.getType(SimpleValue.java:265)
   at org.hibernate.mapping.Property.getType(Property.java:47)
   at org.hibernate.mapping.Component.getType(Component.java:149)
   at org.hibernate.mapping.SimpleValue.isValid(SimpleValue.java:252)
   at org.hibernate.mapping.RootClass.validate(RootClass.java:189)
   at org.hibernate.cfg.Configuration.validate(Configuration.java:839)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1000)



And my type defination is as following:

Code:
public class SWTimestampTypeExtend extends TimestampType {

   //private static final String TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss";
   private static final String MY_TIMESTAMP_FORMAT = "yyyy-MM-dd-HH.mm.ss";

   

   public String toString(Object val) {
      return new SimpleDateFormat(MY_TIMESTAMP_FORMAT).format( (java.util.Date) val );
   }

   


   public String objectToSQLString(Object value, Dialect dialect) throws Exception {
      //return '\'' + value.toString() + '\'';
      return '\'' + toString(value) + '\'';
   }

   public Object fromStringValue(String xml) throws HibernateException {
      try {
         return new Timestamp( new SimpleDateFormat(MY_TIMESTAMP_FORMAT).parse(xml).getTime() );
      }
      catch (ParseException pe) {
         throw new HibernateException("could not parse XML", pe);
      }
   }

   
   public String objectToSQLString(Object arg0) throws Exception {
      // TODO Auto-generated method stub
      //return null;
      
      //return '\'' + arg0.toString() + '\'';
      return '\'' + toString(arg0) + '\'';
   }

}




Should I implements the UserType interface? if so, is there any example which I can follow?

In following:
http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html

there is an example org.hibernate.test.DoubleStringType, but I can not find this guy.

Any help?

Thanks
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Wed Jan 18, 2006 5:55 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Unless you modify your dialect, you do have to use a UserType. There are several examples on the wiki. The first one is at http://www.hibernate.org/169.html, browse the site for lots more examples.
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Wed Jan 18, 2006 5:58 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
I found an example, I am working on it.

Thanks
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Wed Jan 18, 2006 8:13 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
Ok, finally to make it clear, my question is :

How can I get Hibernate to format the Timestamp parameter in the format that DB2 expects?

DB2 expect

Code:
yyyy-MM-dd-HH.mm.ss.000000


But Hibernate always give me this format:

Code:
yyyy-MM-dd HH:mm:ss.000000
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Wed Jan 18, 2006 8:43 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Change your timestamp mapping to be a string, but leave the column as timestamp. You can rely on DB2 to convert from string to timestamp for you. Create a usertype class (implements org.hibernate.usertype.UserType). The two important methods will be:

Code:
/**
   * @param rs The result set to get the timestamp from
   * @param names The columns that the timestamp is stored in
   * @param owner The object containing the timestamp.
   * @return A java.util.Date
   * @throws java.sql.SQLException if something goes wrong when using java.sql
   */
  public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
    throws SQLException
  {
    String sTime = rs.getString(names[0]);
    return rs.wasNull() ? null : new SimpleDateFormat(MY_TIMESTAMP_FORMAT).parse(sTime, 0);
  }

  /**
   * @param st The statement to put value into
   * @param value The value to put into st
   * @param index The position in st to put value
   * @throws java.sql.SQLException if anything goes wrong when using SQL
   */
  public void nullSafeSet(PreparedStatement st, Object value, int index)
    throws SQLException
  {
    if (value == null)
    {
      st.setNull(index, Types.TIMESTAMP);
    }
    else
    {
      st.setString(index, new SimpleDateFormat(TIMESTAMP_FORMAT).format((Date) value);
    }
  }


Probably there's a better way.
Top
 Profile  
 
graderzhy
 Post subject:
PostPosted: Thu Jan 19, 2006 12:27 pm 
Newbie

Joined: Fri Nov 18, 2005 1:06 pm
Posts: 15
Thanks tenwit.

I have fixed my issue by another way.

Thanks every one
Top
 Profile  
 
agadkari
 Post subject:
PostPosted: Thu Jun 28, 2007 11:55 am 
Newbie

Joined: Mon Jan 09, 2006 11:12 am
Posts: 6
graderzhy wrote:
Thanks tenwit.

I have fixed my issue by another way.

Thanks every one


I am facing similar issue -- could you please explain what exactly you did to fix this issue?

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

Board index » Hibernate & Java Persistence » Hibernate Users

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.