SQL Server type to generate Instant in Mybatis Generator

Question

I want to define columns in SQL Server in order for Mybatis Generator to generate java.time.Instant or java.time.OffsetDateTime. According to Mybatis Generator Core, columns with type TIMESTAMP_WITH_TIMEZONE are mapped to OffsetDateTime when useJSR310Types is true

I used type DATETIMEOFFSET in my DDL but my entities were translated as Object. Of course using DATETIME2 results in LocalDateTime, which is not what I want.

Example DDL

CREATE TABLE Users
(
    UserId        Int IDENTITY (1,1) NOT NULL,
    UserFirstName Nvarchar(50)       NOT NULL,
    UserLastName  Nvarchar(50)       NOT NULL,
    UserEmail     Nvarchar(100)      NOT NULL,
    Created DATETIME2 NOT NULL,
    Creator INT NOT NULL,
    Modified DATETIME2 NOT NULL,
    Modifier INT NOT NULL
);

Relevant fragments of generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "https://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>

    <contexttargetRuntime="MyBatis3DynamicSql" defaultModelType="hierarchical">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="true"/>
        </commentGenerator>

        <jdbcConnection driverClass="${jdbcDriverClass}"
                        connectionURL="${jdbcUrl}"
                        userId="${jdbcUsername}"
                        password="${jdbcPassword}"
        >
            <property name="nullCatalogMeansCurrent" value="true"/>
        </jdbcConnection>

        <javaTypeResolver type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">
            <property name="useJSR310Types" value="true"/>
        </javaTypeResolver>

        <javaModelGenerator targetPackage=""
                            targetProject="${workingDir}/src/main/java"/>

        <javaClientGenerator targetPackage=""
                             targetProject="${workingDir}/src/main/java" type="ANNOTATEDMAPPER"/>

        
        <table tableName="Users" domainObjectName="User" mapperName="UserBaseMapper">
            <property name="rootInterface" value=""/>
            <generatedKey column="UserId" identity="true" sqlStatement="JDBC"/>
        </table>

    </context>

</generatorConfiguration>

score 2 · Answer 1 · answered Jul 29 '21 at 11:05

MyBatis Generator is depending on the JDBC driver for type resolution. You can see how SQL Server does type mapping here: https://learn.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver15

According to that page, DATETIMEOFFSET is mapped to a non-standard JDBC type microsoft.sql.Types.DATETIMEOFFSET. The generator doesn't know anything about that type (and neither does MyBatis itself). This is why the generator resolves the column to Object.

If you want to use DATETIMEOFFSET columns in SQL Server then you can use an override in the generator to map the Java type to microsoft.sql.DateTimeOffset. You will also need to write a type handler so MyBatis can work with this type.

Unfortunately it looks like SQL Server does not have a type that maps directly to TIMESTAMP_WITH_TIMEZONE.

score 0 · Accepted Answer · answered Jul 29 '21 at 15:26

There was another way.

Suggest Mybatis to use Instant

     <table tableName="Users" domainObjectName="User" mapperName="UserBaseMapper">
        <property name="rootInterface" value=""/>
        <generatedKey column="UserId" identity="true" sqlStatement="JDBC"/>
        <columnOverride column="Created" jdbcType="TIMESTAMP_WITH_TIMEZONE" javaType="java.time.Instant"/>
        <columnOverride column="Modified" jdbcType="TIMESTAMP_WITH_TIMEZONE" javaType="java.time.Instant"/>
    </table>

Maybe (maybe) the JDBC type is redundant, but it damn worked both on SQL Server and H2 DB, where H2 uses TIMESTAMP WITH TIME ZONE as column type

Note that `java.time.OffsetDateTime` is supported by [many DB/drivers](https://github.com/mybatis/mybatis-3/pull/1368#issuecomment-428922657) while `java.time.Instant` is not. So, MyBatis' built-in `InstantTypeHandler` has to convert `Instant` from/to `Timestamp` which could generate unexpected result depending on the value and the default time zone. — ave, Jul 30 '21 at 07:45

SQL Server type to generate Instant in Mybatis Generator

2 Answers2