ZoneDateTime as parameter in Select

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

ZoneDateTime as parameter in Select

alexander.koch
Hello,

I am using MyBatis and java.time.ZonedDateTime. Until recently I was using mybatis 3.4.6 and mybatis-typehandlers-jsr310 1.0.2. Now I switched to mybatis 3.5.0 and removed the dependency to mybatis-typehandlers-jsr310.

I have select statements, that use ZonedDateTime as a parameter. The column in the database is of type DATETIME. When I go to the new version (3.5.0) I get an exception:



### SQL: SELECT * FROM table WHERE id= ? and startDate = ? ORDER BY startDate DESC
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='arg1', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: Der in SQL für eine Instanz von java.time.ZonedDateTime zu verwendende Datentyp kann nicht abgeleitet werden. Benutzen Sie 'setObject()' mit einem expliziten Typ, um ihn festzulegen.

I tried setting the type with jdbctype="DATE" or "TIMESTAMP

@Select("SELECT * FROM TABLE WHERE id= #{arg0} and startDate = #{arg1,jdbcType=DATE} ORDER BY startDate DESC")
@ConstructorArgs({
@Arg(column = "Id", javaType = Integer.class),
@Arg(column = "StartDate", javaType = Date.class),
@Arg(column = "EndDate", javaType = Date.class),
})
SomeThing getEntry(Integer id, ZonedDateTime startDate, TenantId tenantId);


But this does not solve my problem. 

Any idea what has changed?


Alex 

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: ZoneDateTime as parameter in Select

Iwao AVE!

Hi Alex,

PreparedStatement#setTimestamp() was used in 3.4.6, whereas PreparedStatement#setObject() is used in 3.5.0.
This change was necessary to fix a problem (i.e. time zone information is lost) when storing/retrieving ZonedDateTime to/from the database.
https://github.com/mybatis/mybatis-3/issues/1081

Unfortunately, pgjdbc does not support ZonedDateTime as the parameter to the setObject() yet [1].

If the old implementation [2] worked perfectly for you, you can copy it as a custom type handler and register it globally in the config [3].
Let me know if you need a help with this.

[1] There are several ongoing discussions. https://github.com/pgjdbc/pgjdbc/search?q=zoneddatetime&type=Issues
[2] https://github.com/harawata/mybatis-3/blob/a2ef920a5fc58256d57968c69423c033bf6779d3/src/main/java/org/apache/ibatis/type/ZonedDateTimeTypeHandler.java
[3] http://www.mybatis.org/mybatis-3/configuration.html#typeHandlers

Regards,
Iwao


On Wed, Feb 20, 2019 at 12:18 AM <[hidden email]> wrote:
Hello,

I am using MyBatis and java.time.ZonedDateTime. Until recently I was using mybatis 3.4.6 and mybatis-typehandlers-jsr310 1.0.2. Now I switched to mybatis 3.5.0 and removed the dependency to mybatis-typehandlers-jsr310.

I have select statements, that use ZonedDateTime as a parameter. The column in the database is of type DATETIME. When I go to the new version (3.5.0) I get an exception:



### SQL: SELECT * FROM table WHERE id= ? and startDate = ? ORDER BY startDate DESC
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='arg1', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: Der in SQL für eine Instanz von java.time.ZonedDateTime zu verwendende Datentyp kann nicht abgeleitet werden. Benutzen Sie 'setObject()' mit einem expliziten Typ, um ihn festzulegen.

I tried setting the type with jdbctype="DATE" or "TIMESTAMP

@Select("SELECT * FROM TABLE WHERE id= #{arg0} and startDate = #{arg1,jdbcType=DATE} ORDER BY startDate DESC")
@ConstructorArgs({
@Arg(column = "Id", javaType = Integer.class),
@Arg(column = "StartDate", javaType = Date.class),
@Arg(column = "EndDate", javaType = Date.class),
})
SomeThing getEntry(Integer id, ZonedDateTime startDate, TenantId tenantId);


But this does not solve my problem. 

Any idea what has changed?


Alex 

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

AW: ZoneDateTime as parameter in Select

alexander.koch

Hi Iwao,

 

oh ok. I understand. I will have a look at the discussions. Interesting topic for sure. I have restored to old behaviour and all my regression tests are running green again J

 

Thank you fort he quick help!

 

Regards

Alex

 

Von: [hidden email] [mailto:[hidden email]] Im Auftrag von Iwao AVE!
Gesendet: Dienstag, 19. Februar 2019 17:09
An: mybatis-user
Betreff: Re: ZoneDateTime as parameter in Select

 

Hi Alex,

PreparedStatement#setTimestamp() was used in 3.4.6, whereas PreparedStatement#setObject() is used in 3.5.0.
This change was necessary to fix a problem (i.e. time zone information is lost) when storing/retrieving ZonedDateTime to/from the database.
https://github.com/mybatis/mybatis-3/issues/1081

Unfortunately, pgjdbc does not support ZonedDateTime as the parameter to the setObject() yet [1].

If the old implementation [2] worked perfectly for you, you can copy it as a custom type handler and register it globally in the config [3].
Let me know if you need a help with this.

[1] There are several ongoing discussions. https://github.com/pgjdbc/pgjdbc/search?q=zoneddatetime&type=Issues
[2] https://github.com/harawata/mybatis-3/blob/a2ef920a5fc58256d57968c69423c033bf6779d3/src/main/java/org/apache/ibatis/type/ZonedDateTimeTypeHandler.java
[3] http://www.mybatis.org/mybatis-3/configuration.html#typeHandlers

Regards,
Iwao

 

On Wed, Feb 20, 2019 at 12:18 AM <[hidden email]> wrote:

Hello,

 

I am using MyBatis and java.time.ZonedDateTime. Until recently I was using mybatis 3.4.6 and mybatis-typehandlers-jsr310 1.0.2. Now I switched to mybatis 3.5.0 and removed the dependency to mybatis-typehandlers-jsr310.

 

I have select statements, that use ZonedDateTime as a parameter. The column in the database is of type DATETIME. When I go to the new version (3.5.0) I get an exception:

 

 

 

### SQL: SELECT * FROM table WHERE id= ? and startDate = ? ORDER BY startDate DESC

### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='arg1', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: Der in SQL für eine Instanz von java.time.ZonedDateTime zu verwendende Datentyp kann nicht abgeleitet werden. Benutzen Sie 'setObject()' mit einem expliziten Typ, um ihn festzulegen.

 

I tried setting the type with jdbctype="DATE" or "TIMESTAMP

 

@Select("SELECT * FROM TABLE WHERE id= #{arg0} and startDate = #{arg1,jdbcType=DATE} ORDER BY startDate DESC")
@ConstructorArgs({
       
@Arg(column = "Id", javaType = Integer.class),
       
@Arg(column = "StartDate", javaType = Date.class),
       
@Arg(column = "EndDate", javaType = Date.class),
})
SomeThing getEntry(Integer id, ZonedDateTime startDate, TenantId tenantId);

 

 

But this does not solve my problem. 

 

Any idea what has changed?

 

 

Alex 

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.