Announcement Announcement Module
Collapse
No announcement yet.
Query doesn't return the Timestamp column data Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query doesn't return the Timestamp column data

    Hello All,

    I have a column in my table of type TIMESTAMP. I use iBatis to read the database, I just need to read, no need to write. But unfortunately, for now I am getting this column value as null, the value is in the following format:
    14-03-06 05:50:34.000000000
    I am not getting any error, just the value is null. I ran the same query in SQLDeveloper and it returns the data properly, so the query is good. Its just the mapping in iBatis that's causing this problem, I believe.

    1. CustomerInfoMapping.xml:
    Code:
    <result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" />
    
    <select id="getCustomerInfo" parameterType="ParamBean" resultMap="CustomerInfoBean">
    
    SELECT X, Y, Z, MAX(CREATE_TIME) FROM XXXX
    WHERE XXXXXXXXXXXXX;
    
    </select>
    2. CustomerInfo.java (Pojo)
    Code:
    private java.sql.Timestamp createTime;
    Getters and Setters
    3. Rest of the info.
    DB Version: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
    Spring jar: Couple of Spring jars version 3.0.3
    Ojdbc jar: ojdbc6.jar
    ibatis jar: ibatis-core-3.0.jar
    4. What I tried so far ?

    1). In the Mapping xml, I tried using jdbcType as java.util.Date.
    2). In the Mapping xml, I tried with appending javaType=java.sql.Timestamp
    3). In the Mapping xml, I tried with appending javaType=oracle.sql.TIMESTAMP
    4). In the Mapping xml, I tried with appending javaType=java.sql.Date
    5). In the Mapping xml, I tried with appending javaType=java.util.Date
    6). Like this I tried with few combinations of jdbcType and javaType;
    7). I thought of using CallBack Handlers, however, they are meant to convert the type of response. In this case I am not getting the value itself, so thought there is no point in doing this.


    None of them worked. Please help.


    Thanks.
    Last edited by samjna; Mar 10th, 2014, 03:03 AM.

  • #2
    Ok we found the root cause. I just had MAX(CREATE_TIME) in my query, that was the problem. Check out the following link for more info.

    https://community.oracle.com/thread/3529017


    Solution:

    1. Change MAX(CREATE_TIME) to MAX(CREATE_TIME) AS CREATE_TIME

    Code:
    SELECT CUSTOMER_LASTNAME, CUSTOMER_FIRSTNAME, MAX(CREATE_TIME) AS CREATE_TIME FROM DummySchema.CustomerInfo custInfo WHERE custInfo.CUSTOMER_ID = '7880343'

    2. Change jdbcType to TIMESTAMP.
    Code:
    <result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP"/>
    3. Change Pojo Type to java.sql.Timestamp.
    Code:
    private java.sql.Timestamp createTime;

    Comment

    Working...
    X