Problem while getting the Current Timestamp from DB

Milagro_09 · **Joined:** Fri Dec 07, 2007 4:25 pm **Posts:** 6

HI all,

I am new to hibernate.
I am trying to get a system date from database (Oracle 10g) in the timestamp format.

so in my hbm file i have used the query

<sql-query name="getTime">
select SYSDATE from DUAL
</sql-query>

while in my DAO i m using the method to get the time

Code:

    public long getTime() {
        Timestamp dbTime = (Timestamp) getHibernateTemplate().findByNamedQuery("getTime").get(0);
        //java.sql.Date dbTime = (java.sql.Date) getHibernateTemplate().findByNamedQuery("getTime").get(0);
        long timeInMilis = dbTime.getTime();
        LOG.debug("Got systime from DB : " + timeInMilis);
        return timeInMilis;
    }

but it is giving me an error as below.......

Code:

java.lang.ClassCastException
   at com.toro.service.rebate.rebatepayment.dao.RebatePaymentDaoImpl.getTime(RebatePaymentDaoImpl.java:124)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:177)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy4.getTime(Unknown Source)
   at com.toro.service.rebate.rebatepayment.service.RebatePaymentEngineImpl.getPaymentSchema(RebatePaymentEngineImpl.java:155)
   at com.toro.service.rebate.rebatepayment.service.RebatePaymentEngineImpl.createVitriaRequestXML(RebatePaymentEngineImpl.java:135)
   at com.toro.service.rebate.rebatepayment.service.RebatePaymentEngineImpl.payRebates(RebatePaymentEngineImpl.java:95)
   at com.toro.service.rebate.rebatepayment.RebatePaymentServiceImpl.payRebatesForDate(RebatePaymentServiceImpl.java:98)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.apache.axis2.receivers.RawXMLINOnlyMessageReceiver.invokeBusinessLogic(RawXMLINOnlyMessageReceiver.java:79)
   at org.apache.axis2.receivers.AbstractInMessageReceiver.receive(AbstractInMessageReceiver.java:33)
   at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:144)
   at org.apache.axis2.transport.http.util.RESTUtil.invokeAxisEngine(RESTUtil.java:122)
   at org.apache.axis2.transport.http.util.RESTUtil.processXMLRequest(RESTUtil.java:75)
   at org.apache.axis2.transport.http.AxisServlet$ProcessRESTRequest.processXMLRequest(AxisServlet.java:763)
   at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:190)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
   at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
   at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
   at java.lang.Thread.run(Thread.java:534)

I came to know that sysdate retruns me java.sql.date object and not timestamp so i changed my query in the hbm as

Code:

   <sql-query name="getTime">
      SELECT CURRENT_TIMESTAMP FROM dual
   </sql-query>

but now i am getting another exception

Code:

org.springframework.orm.hibernate3.HibernateSystemException: No Dialect mapping for JDBC type: -101; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: -101
Caused by: 
org.hibernate.MappingException: No Dialect mapping for JDBC type: -101
   at org.hibernate.dialect.TypeNames.get(TypeNames.java:56)
   at org.hibernate.dialect.TypeNames.get(TypeNames.java:81)
   at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:368)
   at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:559)
   at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:485)
   at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:501)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2211)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
   at org.hibernate.loader.Loader.list(Loader.java:2090)
   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:150)
   at org.springframework.orm.hibernate3.HibernateTemplate$32.doInHibernate(HibernateTemplate.java:916)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
   at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:907)
   at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedQuery(HibernateTemplate.java:899)
   at com.toro.service.rebate.rebatepayment.dao.RebatePaymentDaoImpl.getTime(RebatePaymentDaoImpl.java:124)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:296)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:177)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:166)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy4.getTime(Unknown Source)
   at com.toro.service.rebate.rebatepayment.service.RebatePaymentEngineImpl.getPaymentSchema(RebatePaymentEngineImpl.java:155)
   at com.toro.service.rebate.rebatepayment.service.RebatePaymentEngineImpl.createVitriaRequestXML(RebatePaymentEngineImpl.java:135)
   at com.toro.service.rebate.rebatepayment.service.RebatePaymentEngineImpl.payRebates(RebatePaymentEngineImpl.java:95)
   at com.toro.service.rebate.rebatepayment.RebatePaymentServiceImpl.payRebatesForDate(RebatePaymentServiceImpl.java:98)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:324)
   at org.apache.axis2.receivers.RawXMLINOnlyMessageReceiver.invokeBusinessLogic(RawXMLINOnlyMessageReceiver.java:79)
   at org.apache.axis2.receivers.AbstractInMessageReceiver.receive(AbstractInMessageReceiver.java:33)
   at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:144)
   at org.apache.axis2.transport.http.util.RESTUtil.invokeAxisEngine(RESTUtil.java:122)
   at org.apache.axis2.transport.http.util.RESTUtil.processXMLRequest(RESTUtil.java:75)
   at org.apache.axis2.transport.http.AxisServlet$ProcessRESTRequest.processXMLRequest(AxisServlet.java:763)
   at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:190)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
   at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
   at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
   at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
   at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
   at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
   at java.lang.Thread.run(Thread.java:534)

i am using the ORacler 10g Db and the dielect used in the Aplication context.xml is
rg.hibernate.dialect.Oracle9Dialect
i tried searching for this error on web and was not very lucky to get a satisfying answer..
can sombody help me?

regards,

Zombie[/quote]

farzad · **Posted:** Fri Dec 07, 2007 5:01 pm

one thing you could do is to create a view that has this timestamp value and map it to a class, which has a timestamp field which is mapped to the timestamp column of the view with a timestamp data type [I just had to repeat myself a thousand times]. Something like:

Code:

<hibernate-mapping>
    <class name="MyClass" table="MyView" mutable="false">
        <id name="id" type="int">
            <column name="ID"/>
            <generator class="assigned"/>
        </id>

        <property name="date" type="timestamp">
            <column name="DATE"/>
        </property>
    </class>
</hibernate-mapping>

Farzad-

bellerophon · **Joined:** Sat Dec 15, 2007 3:34 am **Posts:** 3

Hi Milagro_09,

Try using a query like this :

Timestamp timestamp = (Timestamp) session.createSQLQuery("select systime param from dual").addScalar("param", Hibernate.TIMESTAMP).uniqueResult();

Do let me know if it helps.

nestorjb · **Joined:** Sun Oct 26, 2003 9:02 pm **Posts:** 90

I'm trying to do the same thing with Java Persistence API. The problem is that I cannot define the resul type so Hibernate always asumes is a Date with no time information.

Has anyone resolved this issue?

nestorjb · **Joined:** Sun Oct 26, 2003 9:02 pm **Posts:** 90

The only way I got it to work was to create a POJO CurrentTimestampTo and map it using a subselect. This was the only way to map the property with type timestamp. You can't do this with Native Queries.

artconnoisseur · **Posted:** Thu Oct 23, 2008 5:05 am

bellerophon wrote:

Hi Milagro_09,

Try using a query like this :

Timestamp timestamp = (Timestamp) session.createSQLQuery("select systime param from dual").addScalar("param", Hibernate.TIMESTAMP).uniqueResult();

Do let me know if it helps.

I encountered a similar problem when implementing an SQL query returning a scalar value of Oracle type TIMESTAMP WITH TIME ZONE (Side note: didn't have a problem with Oracle type TIMESTAMP). Using the technique above did resolve the issue.