Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

LocalDateTimeTypeHandler failing with TIMESTAMPTZ #1644

Closed
marcos-lg opened this issue Aug 23, 2019 · 5 comments
Closed

LocalDateTimeTypeHandler failing with TIMESTAMPTZ #1644

marcos-lg opened this issue Aug 23, 2019 · 5 comments

Comments

@marcos-lg
Copy link

BUG REPORT

MyBatis version

3.5.2 and 3.5.1

Database vendor and version

PostgreSQL 11.3

Test case or example project

DB table:

CREATE TABLE test(
 key serial NOT NULL PRIMARY KEY,
 created timestamp with time zone);
)

ResultMap in mapper:

  <resultMap id="TEST_MAP" type="Step" autoMapping="true">
    <id property="key" column="key"/>
    <result property="created" column="created" typeHandler="org.apache.ibatis.type.LocalDateTimeTypeHandler"/>
  </resultMap>

Java class where we are mapping the result:

public static class PipelinesStep implements Serializable {
    private long key;
    private LocalDateTime created;

  // ... getters and setters
}

Query to get the error:

  <select id="get" resultMap="TEST_MAP">
    SELECT *
    FROM test
  </select>

Steps to reproduce

Just run that query.

Expected result

The java object is populated without errors.

Actual result

Error example:

Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'started' from result set.  Cause: org.postgresql.util.PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type timestamp.

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:357)
	at com.sun.proxy.$Proxy48.selectOne(Unknown Source)
	at org.apache.ibatis.session.SqlSessionManager.selectOne(SqlSessionManager.java:166)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57)
	at com.sun.proxy.$Proxy49.get(Unknown Source)
	at org.gbif.crawler.status.service.persistence.PipelinesProcessStatusMapperTest.addStepTest(PipelinesProcessStatusMapperTest.java:90)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.rules.ExpectedException$ExpectedExceptionStatement.evaluate(ExpectedException.java:239)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.testcontainers.containers.FailureDetectingExternalResource$1.evaluate(FailureDetectingExternalResource.java:30)
	at org.junit.rules.RunRules.evaluate(RunRules.java:20)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'started' from result set.  Cause: org.postgresql.util.PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type timestamp.
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:83)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getPropertyMappingValue(DefaultResultSetHandler.java:472)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertyMappings(DefaultResultSetHandler.java:441)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:404)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.loader.ResultLoader.selectList(ResultLoader.java:81)
	at org.apache.ibatis.executor.loader.ResultLoader.loadResult(ResultLoader.java:70)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getNestedQueryMappingValue(DefaultResultSetHandler.java:760)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getPropertyMappingValue(DefaultResultSetHandler.java:465)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertyMappings(DefaultResultSetHandler.java:441)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:404)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:354)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:328)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:301)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:194)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 42 more
Caused by: org.postgresql.util.PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type timestamp.
	at org.postgresql.jdbc.PgResultSet.getLocalDateTime(PgResultSet.java:614)
	at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3353)
	at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3393)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ResultSetLogger.invoke(ResultSetLogger.java:69)
	at com.sun.proxy.$Proxy53.getObject(Unknown Source)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:38)
	at org.apache.ibatis.type.LocalDateTimeTypeHandler.getNullableResult(LocalDateTimeTypeHandler.java:28)
	at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:81)
	... 73 more

Comments

In mybatis 3.5.0 it works. The code of the LocalDateTimeTypeHandler has been changed after this version.
@harawata
Copy link
Member

Hi @asturcon ,
Thank you for the detailed report!

Yes, the implementation was changed and LocalDateTimeTypeHandler now calls java.sql.ResultSet#getObject() (which is the right method to retrieve JSR-310 classes) and it fully depends on the driver how to (or not to) convert/return the requested value.
Please see #1478 for the background of the change.
It may work once the conversion is supported by the driver.

A short-term solution is to copy the old LocalDateTimeTypeHandler to your project and register it in your config.
Please let me know if you need a help with the configuration.

@marcos-lg
Copy link
Author

Hi @harawata,

Ok, I can work it out by using the old handler or keep using mybatis 3.5.0 until the driver supports this conversion (I'm using the latest version of the driver already). It's not a problem for now.

Thank you!

@harawata
Copy link
Member

Thank you for the reply, @asturcon !
There are several ongoing discussions on pgjdbc's tracker.
Also, I should probably mention that the driver already supports conversion between LocalDateTime and TIMESTAMP.

@sultaale
Copy link

Hi @harawata,
Could you please provide an example how to register LocalDateTimeTypeHandler in config in springboot?
Thank you.

@harawata
Copy link
Member

harawata commented Oct 27, 2023 •
edited

@sultaale ,

Assuming that your type handler's fully-qualified-name is foo.bar.LocalDateTimeTypeHandler, you can register the type handler by adding the following line to your application.properties.

type-handlers-package=foo.bar

See the doc for the details.
https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/#configuration

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@harawata @kazuki43zoo @marcos-lg @sultaale