-->
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
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
abruegl
 Post subject: Converting Dates in the mapping
PostPosted: Thu Sep 20, 2007 11:47 am 
Newbie

Joined: Tue Sep 18, 2007 3:09 pm
Posts: 3
Hi everyone, I'm trying out hibernate and have to support a legacy database that has a Table with Date columns. These Date columns are just Dates without time zones and are in the Sydney time zone, not UTC. I want to create a mapping that converts these dates to UTC when I get the object, and convert the dates back to Sydney time when they are inserted or updated.

I made a solution below, but I get errors when running, so if anyone could give me a better suggestion how to accomplish this, or a suggestion to what I am doing wrong, I would be greatful. Thanks very much!


Hibernate version:
3.2.4

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.nighthawk.soa.dao.entity.AR2.Turnaroundtime"
table="TURNAROUNDTIME"
>

<id
name="studyid"
column="STUDYID"
type="java.lang.String"
length="32"
>
<generator class="assigned">

</generator>
</id>

<property
name="timeqc"
type="java.util.Date"
update="true"
insert="true"
column="TIMEQC"
/>

<property
name="timein"
type="java.util.Date"
update="true"
insert="true"
column="TIMEIN"
/>

<property
name="timereportstarted"
type="java.util.Date"
update="true"
insert="true"
column="TIMEREPORTSTARTED"
/>

<property
name="timereportcompleted"
type="java.util.Date"
update="true"
insert="true"
column="TIMEREPORTCOMPLETED"
/>

<property
name="timereviewed"
type="java.util.Date"
update="true"
insert="true"
column="TIMEREVIEWED"
/>

<property
name="timefaxed"
type="java.util.Date"
update="true"
insert="true"
column="TIMEFAXED"
/>

<property
name="timefaxconfirmed"
type="java.util.Date"
update="true"
insert="true"
column="TIMEFAXCONFIRMED"
/>

<property
name="timescheduled"
type="java.util.Date"
update="true"
insert="true"
column="TIMESCHEDULED"
/>
<loader query-ref="turnaroundtime"/>
<sql-insert>
INSERT INTO TURNAROUNDTIME(TIMEQC, TIMEIN, TIMEREPORTSTARTED, TIMEREPORTCOMPLETED, TIMEREVIEWED, TIMEFAXED, TIMEFAXCONFIRMED, TIMESCHEDULED, STUDYID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
</sql-insert>
<sql-update>
update TURNAROUNDTIME set TIMEQC=?, TIMEIN=?, TIMEREPORTSTARTED=?, TIMEREPORTCOMPLETED=?, TIMEREVIEWED=?, TIMEFAXED=?, TIMEFAXCONFIRMED=?, TIMESCHEDULED=? where STUDYID=?
</sql-update>

</class>
<sql-query name="turnaroundtime">
<return alias="tat" class="net.nighthawk.soa.dao.entity.AR2.Turnaroundtime"/>
SELECT TIMEQC AS {tat.timeqc}, TIMEIN AS {tat.timein}, TIMEREPORTSTARTED AS {tat.timereportstarted}, TIMEREPORTCOMPLETED AS {tat.timereportcompleted}, TIMEREVIEWED AS {tat.timereviewed}, TIMEFAXED AS CONVERT_TIME({tat.timefaxed}, 'Australia/Sydney', 'UTC'), TIMEFAXCONFIRMED AS {tat.timefaxconfirmed}, TIMESCHEDULED AS {tat.timescheduled}, STUDYID AS {tat.studyid} FROM TURNAROUNDTIME WHERE STUDYID=?
</sql-query>
<database-object>
<create>
create or replace
function CONVERT_TIME ( datetime in timestamp, tz1 in varchar2, tz2 in varchar2 )
return timestamp with time zone
as
retval timestamp with time zone;
begin

retval := from_tz(datetime, tz1) at time zone tz2;
return retval;
end;
</create>
<drop>
drop function CONVERT_TIME
</drop>
</database-object>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Turnaroundtime t = (Turnaroundtime) session.load(Turnaroundtime.class, "123456");

Full stack trace of any exception that occurs:
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:2223)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)

at org.hibernate.loader.Loader.list(Loader.java:2099)

at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)

at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)

at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)

at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)

at org.hibernate.persister.entity.NamedQueryLoader.load(NamedQueryLoader.java:57)

at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3044)

at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:395)

at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:375)

at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:139)

at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:98)

at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)

at org.hibernate.impl.SessionImpl.immediateLoad(SessionImpl.java:836)

at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:66)

at org.hibernate.proxy.AbstractLazyInitializer.getImplementation(AbstractLazyInitializer.java:111)

at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:150)

at net.nighthawk.soa.dao.entity.AR2.Turnaroundtime$$EnhancerByCGLIB$$e87aa595.getTimefaxed(<generated>)

at net.nighthawk.soa.utils.study.TalonAR2Transporter.main(TalonAR2Transporter.java:1526)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected



at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)

at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490)

at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)

at org.hibernate.loader.Loader.doQuery(Loader.java:674)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)

at org.hibernate.loader.Loader.doList(Loader.java:2220)

... 19 more

Name and version of the database you are using:
Oracle 10g

The generated SQL (show_sql=true):
SELECT TIMEQC AS TIMEQC16_0_, TIMEIN AS TIMEIN16_0_, TIMEREPORTSTARTED AS TIMEREPO4_16_0_, TIMEREPORTCOMPLETED AS TIMEREPO5_16_0_, TIMEREVIEWED AS TIMEREVI6_16_0_, TIMEFAXED AS CONVERT_TIME(TIMEFAXED16_0_, 'Australia/Sydney', 'UTC'), TIMEFAXCONFIRMED AS TIMEFAXC8_16_0_, TIMESCHEDULED AS TIMESCHE9_16_0_, STUDYID AS STUDYID16_0_ FROM TURNAROUNDTIME WHERE STUDYID=?

Debug level Hibernate log excerpt:
2007-09-20 10:26:20,635: - DEBUG - (NamedQueryLoader.java:36) [main] - loading entity: net.nighthawk.soa.dao.entity.AR2.Turnaroundtime using named query: turnaroundtime

2007-09-20 10:26:20,649: - DEBUG - (AbstractBatcher.java:401) [main] - SELECT TIMEQC AS TIMEQC16_0_, TIMEIN AS TIMEIN16_0_, TIMEREPORTSTARTED AS TIMEREPO4_16_0_, TIMEREPORTCOMPLETED AS TIMEREPO5_16_0_, TIMEREVIEWED AS TIMEREVI6_16_0_, TIMEFAXED AS CONVERT_TIME(TIMEFAXED16_0_, 'Australia/Sydney', 'UTC'), TIMEFAXCONFIRMED AS TIMEFAXC8_16_0_, TIMESCHEDULED AS TIMESCHE9_16_0_, STUDYID AS STUDYID16_0_ FROM TURNAROUNDTIME WHERE STUDYID=?

2007-09-20 10:26:20,770: - TRACE - (NullableType.java:133) [main] - binding '123456' to parameter: 1

2007-09-20 10:26:21,480: - WARN - (JDBCExceptionReporter.java:77) [main] - SQL Error: 923, SQLState: 42000

2007-09-20 10:26:21,481: - ERROR - (JDBCExceptionReporter.java:78) [main] - ORA-00923: FROM keyword not found where expected



2007-09-20 10:26:21,707: - INFO - (DefaultLoadEventListener.java:111) [main] - Error performing load command

org.hibernate.exception.SQLGrammarException: could not execute query
Top
 Profile  
 
abruegl
 Post subject:
PostPosted: Thu Sep 20, 2007 4:26 pm 
Newbie

Joined: Tue Sep 18, 2007 3:09 pm
Posts: 3
My syntax was bad for:

Code:
TIMEFAXED AS CONVERT_TIME({tat.timefaxed}, 'Australia/Sydney', 'UTC')



Changed to:
Code:
CONVERT_TIME(TIMEFAXED, 'Australia/Sydney', 'UTC') AS {tat.timefaxed}


Works now, thanks![/code]
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
  [ 2 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.