Mybatis 3.5.3 must explicite set jdbctype for JDK time api classes

nino martinez wael

unread,

Feb 28, 2020, 8:58:54 AM2/28/20

to mybatis-user

Are it correct that I must explicit set jdbc types for java 8 date/time types?

#{schedule.startDate, jdbcType=DATE},

#{schedule.endTime, jdbcType=TIME},

If we do not set date or time jdbcType, we get an conversion error if the java side object are null. We are using mssql-jdbc 7.4.1.jre11 and we are using Java 11

-Nino

Iwao AVE!

unread,

Feb 28, 2020, 9:23:25 AM2/28/20

to mybatis-user

Hello Nino,

I don't think that is necessary.

Please post the full stack trace of the 'conversion error'.

And which Java 8 types do you use? LocalDate and LocalTime?

Regards,

Iwao

--
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 mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/db46c44d-a880-4736-99cf-f2a015686fe5%40googlegroups.com.

nino martinez wael

unread,

Feb 28, 2020, 9:47:41 AM2/28/20

to mybatis-user

Yes localtime and localdate:)

create sql:

CREATE TABLE CCAdminNextGen_Trunk.dbo.Schedule (
 id bigint NOT NULL,
 FK_Calendar bigint NOT NULL,
 dayOfWeek tinyint NULL,
 startDate date NULL,
 endDate date NULL,
 recurring bit NULL,
 startTime time NULL,
 endTime time NULL,
 description nvarchar(50) COLLATE Latin1_General_100_CI_AS NULL,
 CONSTRAINT PK_schedule PRIMARY KEY (id)
) GO

JAVA:

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class Schedule {
    private static final Long serialVersionUID = 1L;
    private Long id;
    @Getter(onMethod = @__(@JsonIgnore))
    private Long FK_Calendar;
    @Range(min = 1L, max = 7L)
    private Integer dayOfWeek;

    private LocalDate startDate;
    private LocalDate endDate;
    private Boolean recurring;
    private LocalTime startTime;
    private LocalTime endTime;
    private String description;

stacktrace:
### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

### The error may exist in dk/netdesign/ccadmin/orion/schedule/mapper/ScheduleMapper.xml

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: DECLARE @applicationId BIGINT; DECLARE @scheduleId BIGINT; DECLARE @actionId BIGINT; SET @applicationId=?; SET @scheduleId=?; SET @actionId=NULL; SET @actionId=?; BEGIN UPDATE Schedule SET dayOfWeek=?, startTime=?,startDate=?,endTime=?,endDate=?, description=? WHERE id=@scheduleId END IF (@actionId is null) BEGIN SELECT @actionId=FK_Action FROM ApplicationToAction WHERE FK_Schedule=@scheduleId AND FK_Application=@applicationId; DELETE FROM ApplicationToAction WHERE FK_Schedule=@scheduleId AND FK_Application=@applicationId ; DELETE FROM Action WHERE id = @actionId; SET @actionId=null; END BEGIN DECLARE @newActionId BIGINT; MERGE Action AS T USING (VALUES (?,?,?,?,?,?,?))as S(id,FK_ActionType, value, FK_Script, FK_Menu, FK_Application, useDefault) ON T.id = s.id WHEN MATCHED THEN UPDATE SET FK_ActionType=S.FK_ActionType, value=S.value, FK_Script=S.FK_Script, FK_Menu=S.FK_Menu, FK_Application=S.FK_Application, useDefault=S.useDefault WHEN NOT MATCHED THEN INSERT (FK_ActionType, value, FK_Script, FK_Menu, FK_Application, useDefault) VALUES(S.FK_ActionType, S.value, S.FK_Script, S.FK_Menu, S.FK_Application, S.useDefault); SET @newActionId = SCOPE_IDENTITY(); IF(@newActionId is not null) BEGIN INSERT INTO ApplicationToAction (FK_Application, FK_Action, FK_Schedule) VALUES(@applicationId, @newActionId, @scheduleId); SET @actionId=@newActionId; END END

### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

; uncategorized SQLException; SQL state [S0003]; error code [257]; Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.] with root cause

com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:505) ~[mssql-jdbc-7.4.1.jre11.jar:?]

at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.4.2.jar:?]

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:?]

at jdk.internal.reflect.GeneratedMethodAccessor144.invoke(Unknown Source) ~[?:?]

at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]

at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]

at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.3.jar:3.5.3]

at com.sun.proxy.$Proxy150.execute(Unknown Source) ~[?:?]

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.3.jar:3.5.3]

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.3.jar:3.5.3]

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.3.jar:3.5.3]

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.3.jar:3.5.3]

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.3.jar:3.5.3]

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.3.jar:3.5.3]

at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]

at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]

at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]

at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.3.jar:2.0.3]

at com.sun.proxy.$Proxy117.update(Unknown Source) ~[?:?]

at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287) ~[mybatis-spring-2.0.3.jar:2.0.3]

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67) ~[mybatis-3.5.3.jar:3.5.3]

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93) ~[mybatis-3.5.3.jar:3.5.3]

at com.sun.proxy.$Proxy125.updateScheduleOrActionForApplication(Unknown Source) ~[?:?]

at dk.netdesign.ccadmin.orion.application.service.ApplicationServiceImpl.updateScheduleForApplication(ApplicationServiceImpl.java:69) ~[classes/:?]

at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]

at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]

at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]

at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:888) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at org.springframework.web.servlet.FrameworkServlet.doPut(FrameworkServlet.java:920) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:663) ~[tomcat-embed-core-9.0.30.jar:9.0.30]

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE]

at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.30.jar:9.0.30]

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.30.jar:9.0.30]