DB2 in AS400 date format configuration using spring

1.1k views Asked by At

I'm trying to get a date after 12-31-2039 in DB2 but it always returns null. My project uses spring JDBC configured with jt400.jar JDBC driver. How can I resolve this problem? Follows my config at spring:

<bean id="dataSource" class="br.com.mycompany.persistence.driver.SecuredAS400JDBC">
    <property name="serverName"     value="${br.com.mycompany.dao.jdbc.server}" />
    <property name="databaseName"   value="${br.com.mycompany.dao.jdbc.database}" />
    <property name="libraries"      value="${br.com.mycompany.dao.jdbc.database}" />
    <property name="user"           value="${br.com.mycompany.dao.jdbc.username}" />
    <property name="password"       value="${br.com.mycompany.dao.jdbc.password}" />
    <property name="dataTruncation" value="false" />
    <property name="naming" value="sql" />
    <property name="errors" value="full" />
    <property name="trace" value="false" />
    <property name="prompt" value="false" />
</bean>

And here is my resultset operation:

private static final class rowMapperDTO implements RowMapper<AcionamentoFaixa> {

    public AcionamentoFaixa mapRow(ResultSet rs, int rowNum) throws SQLException {

        AcionamentoFaixa obj = new  AcionamentoFaixa();         
        obj.setDe(rs.getDate(4));
        obj.setAte(rs.getDate(5));

        return obj;
    }
}
1

There are 1 answers

0
Felipe Gabriel Caparelli On

I found the answer at IBM Toolbox Java official documentation: http://www-03.ibm.com/systems/power/software/i/toolbox/faq/jdbc.html#faqB5

"The Toolbox JDBC driver uses the date format that is set up as the default on the IBM i system. This default is usually set to "mdy" which only supports dates between 1940 and 2039. You can override the date format by specifying the "date format" property when opening the JDBC connection. The best choice is "iso", which supports a full four-digit date."

So, I tried to config the bean with the property dateFormat to iso and it works perfectly!

<bean id="dataSource" class="br.com.mycompany.persistence.driver.SecuredAS400JDBC">
    <property name="serverName"     value="${br.com.mycompany.dao.jdbc.server}" />
    <property name="databaseName"   value="${br.com.mycompany.dao.jdbc.database}" />
    <property name="libraries"      value="${br.com.mycompany.dao.jdbc.database}" />
    <property name="user"           value="${br.com.mycompany.dao.jdbc.username}" />
    <property name="password"       value="${br.com.mycompany.dao.jdbc.password}" />
    <property name="dataTruncation" value="false" />
    <property name="naming" value="sql" />
    <property name="errors" value="full" />
    <property name="trace" value="false" />
    <property name="prompt" value="false" />
    <property name="dateFormat" value="iso" />
</bean>