You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by Thorsten Elfert <th...@db.com> on 2007/11/07 16:58:47 UTC

Oracle date type mapping issue

Hi,

I use Oracle 9i, JDK 5 and the latest iBatis Release. Whenever I read a
date object out of oracle and map it into a java.util.Date attribute the
time gets truncated. I tried already to set explicitly the javaType as
java.util.Date or java.sql.Timestamp and the jdbcType as DATE in the
sql-map but it does not solve the problem. What is the correct way to map
a java.util.Date to the Oracle Date Object ? (In the database the date and
time gets stored correctly)

Thanks in advance!!

Informationen (einschließlich Pflichtangaben) zu einzelnen, innerhalb der EU tätigen Gesellschaften und Zweigniederlassungen des Konzerns Deutsche Bank finden Sie unter http://www.db.com/de/content/pflichtangaben.htm. Diese E-Mail enthält vertrauliche und/ oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

Please refer to http://www.db.com/en/content/eu_disclosures.htm for information (including mandatory corporate particulars) on selected Deutsche Bank branches and group companies registered or incorporated in the European Union. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

Re: Oracle date type mapping issue

Posted by Thorsten Elfert <th...@db.com>.

I found the issue:

The time gets already truncated during the insert. 

After adding the jdbcType as TIMESTAMP in the corresponding insert 
statement it worked. 

Now, it looks like:

<insert id="insertPrice" parameterClass="com.db.rocs.bo.price.Price" >
        <![CDATA[
                insert into TEST_TABLE (..., CREATED_AT, ...)
                        values(..., #createdAt:TIMESTAMP#, ...)
                ]]>
        </insert>

Thanks all for your support!
________________________________________

"Larry Meadors" <lm...@apache.org> 
Sent by: larry.meadors@gmail.com
08.11.2007 13:23
Please respond to
user-java@ibatis.apache.org

To
user-java@ibatis.apache.org
cc

Subject
Re: Oracle date type mapping issue

Was the time truncated on the insert/update?

select to_char(validfrom,'DD/MM/YYYY HH24:MI:SS') from {your table here}

Larry

On Nov 8, 2007 1:04 AM, Thorsten Elfert <th...@db.com> wrote:
>
> well, this does not work as well. I tried:
>
> <result property="validFrom" column="VALID_FROM" jdbcType="TIMESTAMP"/>
>
> and I tried:
>
> <result property="validFrom" column="VALID_FROM" jdbcType="TIMESTAMP"
> javaType="java.sql.Timestamp"/>
>
> the time gets truncated all the time.
>

-- 

Informationen (einschließlich Pflichtangaben) zu einzelnen, innerhalb der EU tätigen Gesellschaften und Zweigniederlassungen des Konzerns Deutsche Bank finden Sie unter http://www.db.com/de/content/pflichtangaben.htm. Diese E-Mail enthält vertrauliche und/ oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet.

Please refer to http://www.db.com/en/content/eu_disclosures.htm for information (including mandatory corporate particulars) on selected Deutsche Bank branches and group companies registered or incorporated in the European Union. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

Re: Oracle date type mapping issue

Posted by Larry Meadors <lm...@apache.org>.

Was the time truncated on the insert/update?

select to_char(validfrom,'DD/MM/YYYY HH24:MI:SS') from {your table here}

Larry


On Nov 8, 2007 1:04 AM, Thorsten Elfert <th...@db.com> wrote:
>
> well, this does not work as well. I tried:
>
> <result property="validFrom" column="VALID_FROM" jdbcType="TIMESTAMP"/>
>
> and I tried:
>
> <result property="validFrom" column="VALID_FROM" jdbcType="TIMESTAMP"
> javaType="java.sql.Timestamp"/>
>
> the time gets truncated all the time.
>

Re: Oracle date type mapping issue

Posted by Gwyn Evans <gw...@gmail.com>.

Hi Thorsten,

Hmm - the first is what I use, i.e.

	CREATE TABLE XX_LOG
	(
	    ID        INTEGER  NOT NULL,
	    T         DATE DEFAULT SYSDATE NOT NULL,
	    ...
	) ;
and
        <resultMap id="LogResult" class="c.z.t.w.m.model.Log">
            <result column="ID" property="id" jdbcType="INTEGER" />
            <result column="T" property="timestamp" jdbcType="TIMESTAMP" />
            ...
        </resultMap>

and 
    <select id="selectByExample" resultMap="LogResult" ...>
            select L.ID, L.T, ... from XX_LOG L WHERE ...
    </select>
and
    public class Log implements Serializable {
	private Long id;
	private Date timestamp;
       public Long getId() { return id; }
       public void setId(Long id) { this.id = id; }
       public Date getTimestamp() { return timestamp; }
       public void setTimestamp(Date timestamp) { this.timestamp = timestamp; }
 	...
    }

/Gwyn

On 08 November 2007, 8:04:16 AM, Thorsten Elfert wrote:
>

well, this does not work as well. I tried: 

<result property="validFrom" column="VALID_FROM" jdbcType="TIMESTAMP"/> 

and I tried: 

<result property="validFrom" column="VALID_FROM" jdbcType="TIMESTAMP" javaType="java.sql.Timestamp"/> 

the time gets truncated all the time. 

Thorsten 
________________________________________ 

Hi Thorsten, 

On 07 November 2007, 3:58:47 PM, Thorsten Elfert wrote: 

> 

Hi, 

I use Oracle 9i, JDK 5 and the latest iBatis Release. Whenever I read a date object out of oracle and map it into a java.util.Date attribute the time gets truncated. I tried already to set explicitly the javaType as java.util.Date or java.sql.Timestamp and the jdbcType as DATE in the sql-map but it does not solve the problem. What is the correct way to map a java.util.Date to the Oracle Date Object ? (In the database the date and time gets stored correctly) 

Try using the jdbcType as TIMESTAMP as the SQL DATE type only contains a date! 
See "Dates and Times" here - http://www.oreilly.com/catalog/jentnut2/chapter/ch02.html. 

-- 
/Gwyn .

Re: Oracle date type mapping issue

Posted by Thorsten Elfert <th...@db.com>.

well, this does not work as well. I tried:

and I tried:

the time gets truncated all the time.

Thorsten
________________________________________

Gwyn Evans <gw...@gmail.com>
07.11.2007 17:34
Please respond to
user-java@ibatis.apache.org

To
Thorsten Elfert/OuB/Zentrale/DeuBa@dbcom
cc
user-java@ibatis.apache.org
Subject
Re: Oracle date type mapping issue

Hi Thorsten,

On 07 November 2007, 3:58:47 PM, Thorsten Elfert wrote:

Hi,

Try using the jdbcType as TIMESTAMP as the SQL DATE type only contains a
date!
See "Dates and Times" here -
http://www.oreilly.com/catalog/jentnut2/chapter/ch02.html.

--
/Gwyn

Re: Oracle date type mapping issue

Posted by Gwyn Evans <gw...@gmail.com>.

Hi Thorsten,

On 07 November 2007, 3:58:47 PM, Thorsten Elfert wrote:
>

Hi, 

I use Oracle 9i, JDK 5 and the latest iBatis Release. Whenever I read a date object out of oracle and map it into a java.util.Date attribute the time gets truncated. I tried already to set explicitly the javaType as java.util.Date or java.sql.Timestamp and the jdbcType as DATE in the sql-map but it does not solve the problem. What is the correct way to map a java.util.Date to the Oracle Date Object ? (In the database the date and time gets stored correctly) 


Try using the jdbcType as TIMESTAMP as the SQL DATE type only contains a date!
See "Dates and Times" here - http://www.oreilly.com/catalog/jentnut2/chapter/ch02.html.

-- 
/Gwyn

Re: Oracle date type mapping issue

Posted by Nathan Maves <na...@gmail.com>.

type to set your jdbc type to TIMESTAMP

On Nov 7, 2007 8:58 AM, Thorsten Elfert <th...@db.com> wrote:

>
> Hi,
>
> I use Oracle 9i, JDK 5 and the latest iBatis Release. Whenever I read a
> date object out of oracle and map it into a java.util.Date attribute the
> time gets truncated. I tried already to set explicitly the javaType as
> java.util.Date or java.sql.Timestamp and the jdbcType as DATE in the
> sql-map but it does not solve the problem. What is the correct way to map a
> java.util.Date to the Oracle Date Object ? (In the database the date and
> time gets stored correctly)
>
> Thanks in advance!!
>
> --
>
> Informationen (einschließlich Pflichtangaben) zu einzelnen, innerhalb der
> EU tätigen Gesellschaften und Zweigniederlassungen des Konzerns Deutsche
> Bank finden Sie unter *http://www.db.com/de/content/pflichtangaben.htm*.
> Diese E-Mail enthält vertrauliche und/ oder rechtlich geschützte
> Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail
> irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und
> vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte
> Weitergabe dieser E-Mail ist nicht gestattet.
>
> Please refer to *http://www.db.com/en/content/eu_disclosures.htm* for
> information (including mandatory corporate particulars) on selected Deutsche
> Bank branches and group companies registered or incorporated in the European
> Union. This e-mail may contain confidential and/or privileged information.
> If you are not the intended recipient (or have received this e-mail in
> error) please notify the sender immediately and delete this e-mail. Any
> unauthorized copying, disclosure or distribution of the material in this
> e-mail is strictly forbidden.