The version upgrade of mybatis caused an abnormal problem in the input parameter resolution of offsetdatetime. Redo

background

Recently, a data statistics service needs to upgrade the version of springboot from 1.5 x. Release upgrade directly to 2.3 0.release. Considering that the built-in spi of springboot is not used, the upgrade process is smooth. However, due to code cleanliness and version cleanliness, I see that the version of mybatis that the project relies on is 3.4 5, compared with the latest version at that time, 3.5 5 is far behind, so I upgraded it to 3.5 5。 After the upgrade, execute all existing integration tests and find some exceptions in the query methods of offsetdatetime type input parameters, so debug the source code level to find the final problem and solve it.

Problem recurrence

A query method in the project is similar to the following demonstration example:

public interface OrderMapper {

    List<Order> selectByCreateTime(@Param("startCreateTime") OffsetDateTime startCreateTime,@Param("endCreateTime") OffsetDateTime endCreateTime);
}

The SQL code snippet in the corresponding XML file is as follows:

<select id="selectByCreateTime" resultMap="BaseResultMap">
    SELECT *
    FROM t_order
    WHERE deleted = 0 
        AND create_time <![CDATA[>=]]> #{startCreateTime}
        AND create_time <![CDATA[<=]]> #{e ndCreateTime}
</select>

The above ordermapper#selectbycreatetime() method is 3.4 in mybatis version 5, when mybatis version is upgraded to 3.5 Execute again after 5. On the premise that the SQL execution log output is correct, an empty set is returned. The specific contents are as follows:

查询订单列表：[]

Although God's perspective confirmed that there was a problem with the input parameter resolution, based on the log where the exception occurred for the first time, the specific location of the problem could not be located. At that time, the conditional reflection thought that such exceptions would occur in several places (SQL is relatively simple, which can eliminate the case of human writing wrong SQL placeholders):

At that time, the MySQL connector java version used in the project was 8.0 18, not upgraded to the latest version 8.0 21, so it was also suspected that the lower version of MySQL driver package was not compatible with resolving parameters of offsetdatetime type.

A brief analysis of the execution process of mybatis

The source code of mybatis is not complex. If you omit the analysis of its configuration and mapping file parsing module, the execution process of a query SQL (selectlist) is roughly as follows:

Of course, because the problem occurs in the parameter parsing part, you only need to pay attention to the processing logic of statementhandler. In the parent BaseStatementHandler constructor of StatementHandler, ParameterHandler and ResultSetHandler instances are initialized and submitted to doQuery () method in SimpleExecutor. Queries using placeholder parameters are then called PreparedStatementHandler#parameterize () through prepareStatement () method in doQuery () method. Finally, delegate to the defaultparameterhandler #setparameters () method to set parameters. This setparameters () method will use parametermapping and typehandler.

If a built-in typehandler or a custom typehandler implementation is used and a parameter resolution exception occurs at the same time, it is very likely that the exception occurs from the defaultparameterhandler #setparameters() method, so that the typehandler with the exception can be found.

Root cause of parameter parsing exception

As for the exception of resolving offsetdatetime type mentioned earlier in this article, the code will actually enter the offsetdatetimetypehandler when executing the query. Here is a comparison with 3.4 5 and 3.5 Implementation of offsetdatetimetypehandler corresponding to mybatis in version 5:

The main differences are found as follows:

Preparedstatement #settimestamp () is a very early product. There is no problem with this method. 3.4 In version 5, mybatis compatible the offsetdatetime type with the timestamp type. Then it can be basically determined that the problem occurs in the preparedstatement#setobject () method. For mysql8 Driven by X, the implementation class selected for Preparedstatement is com MysqL. cj. jdbc. Clientpreparedstatement, through layers of debug, finally reaches the abstractquerybindings#setobject() method:

Since there are no fragments in the driver that resolve the offsetdatetime type, Therefore, the abstractquerybindings#setserializableobject() method (i.e. the code of else branch) will be used to find out the truth and directly convert it into a byte [] and transmit it to the MySQL server. The problem is here. Serializing the offsetdatetime type directly is suspected that the expected parameters obtained at the MySQL service end are not the expected parameters, resulting in the invalidation of the query conditions (I didn't take the time to read the MySQL protocol here, nor did I spend a lot of time capturing packages, so I'm just guessing here). However, this problem still hasn't been solved in the latest release of MySQL: MySQL connector Java: 8.0.21 on July 12, 2020. However, I see another doubt here. The developers of mybatis should not be able to solve this key and uncomplicated problem Mistakes, so take the time to look at the code submission record here:

This is a submission by raupach on August 22, 2017. The message submitted is: test the offsetdatetimehandler to retain the UTC offset. The unit test class offsetdatetimetypehandlertest only verified the correctness of the parameter passing of typehandler #setparameter() and Preparedstatement #setobject(), and did not do integration test to track the parameter passing of all types of databases. It is estimated that this step was neglected, but this should not belong to mybatis. After all, it is only the encapsulation of database driver package. Among them, the integration test timestampwithtimezonetypehandlertest uses an in memory database. It can be guessed that the HSQLDB driver improves the parameter resolution of date and time.

The same problem will not appear in H2 database, so I debug the source code of H2 database driver for parameter setting slightly, and finally locate org h2. value. Line 1333 of datatype (the version of the driver package is com. H2database: H2: 1.4.200) has the parsing logic corresponding to jsr310.offset_date_time, so the H2 database driver can support the parameter value setting of all parameter types introduced by jsr310. The following screenshot is the parsing implementation of preparedstatement#setobject() in the H2 database driver (see the source code of org.h2.jdbc.jdbcpreparedstatement and datatype#converttovalue()):

It can be seen here that the H2 driver really parses all the date and time types added to jdk8 +:

Solutions to problems

If MySQL is selected, the problem of this parameter parsing exception is up to MySQL: MySQL connector Java: 8.0 There is only one solution: set the offsetdatetime type compatible with the timestamp type for parameter setting. In fact, all non localxx date and time types need to be compatible. The compatibility table is as follows:

Take offsetdatetime as an example, you only need to refer to or directly use 3.4 Offsetdatetimetypehandler of mybatis in version 5, and then directly override the built-in implementation through configuration.

// 假设全类名为club.throwable.OffsetDateTimeTypeHandler
public class OffsetDateTimeTypeHandler extends BaseTypeHandler<OffsetDateTime> {

  @Override
  public void setNonNullParameter(PreparedStatement ps,int i,OffsetDateTime parameter,JdbcType jdbcType)
          throws sqlException {
    ps.setTimestamp(i,Timestamp.from(parameter.toInstant()));
  }

  @Override
  public OffsetDateTime getNullableResult(ResultSet rs,String columnName) throws sqlException {
    Timestamp timestamp = rs.getTimestamp(columnName);
    return getOffsetDateTime(timestamp);
  }

  @Override
  public OffsetDateTime getNullableResult(ResultSet rs,int columnIndex) throws sqlException {
    Timestamp timestamp = rs.getTimestamp(columnIndex);
    return getOffsetDateTime(timestamp);
  }

  @Override
  public OffsetDateTime getNullableResult(CallableStatement cs,int columnIndex) throws sqlException {
    Timestamp timestamp = cs.getTimestamp(columnIndex);
    return getOffsetDateTime(timestamp);
  }

  private static OffsetDateTime getOffsetDateTime(Timestamp timestamp) {
    if (timestamp != null) {
      // 这里可以考虑自定义系统的时区，例如ZoneId.of("Asia/Shanghai")
      return OffsetDateTime.ofInstant(timestamp.toInstant(),ZoneId.systemDefault());
    }
    return null;
  }
}

Overwrite the typehandler configuration in the configuration file. The following is the configuration file mybatis config. In the classpath Example of XML:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!--下划线转驼峰-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--未知列映射忽略-->
        <setting name="autoMappingUnkNownColumnBehavior" value="NONE"/>
    </settings>
    <typeHandlers>
        <!--覆盖内置OffsetDateTimeTypeHandler-->
        <typeHandler handler="throwable.club.OffsetDateTimeTypeHandler"/>
    </typeHandlers>
</configuration>

Other types of parsing exceptions can be compatible with this idea.

Summary

Care should be taken to upgrade the basic framework version. In addition, the solution mentioned in this paper is only a relatively reasonable solution obtained by the author through problem analysis and positioning, and there may be a better solution.

The demo project warehouse of this article:

(the end of this article is c-2-d e-a-20200802. Some time ago, there was a problem with the moving bandwidth, which was broken for nearly a week.)