Type Handling in MyBatis

jdbcType

The jdbcType is a property for mapping Java object field values to SQL column values.

You need to specify the jdbcType when passing null values for parameters. Some databases need to know the value’s type even if the value itself is NULL. The JDBC specification itself that requires the type to be specified.

• preparedStatement.setNull(int parameterIndex, int sqlType)

Most of the times you don’t need to specify the jdbcType as MyBatis is smart enough to figure out the type from the objects you are working with. But if you send your parameters to the MyBatis statement inside a HashMap, for example, and one of the parameters is null, MyBatis won’t be able to determine the type of the parameter by looking at the HashMap because the HashMap is just a generic container and null itself carries no type information. At that point it would be o good idea to provide the jdbcType so that switching the database implementation later on does not cause any issues with null values.

JDBC Types

• BIT, BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT
• FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL
• CHAR, VARCHAR, NVARCHAR, LONGVARCHAR, NCHAR
• BINARY, VARBINARY, LONGVARBINARY, BLOB, CLOB, NCLOB
• TIMESTAMP, DATE, TIME
• OTHER, UNDEFINED, NULL, CURSOR, ARRAY

MySQL and Java DB currently do not support the ARRAY SQL data type.

Update default mappings of Java values to column values

Convert java.util.Date objects to date strings

The java.util.Date objects will default convert to datetime strings (TIMESTAMP JDBC type) like 2022-08-18 11:12:05.545. If you want to convert a Date object value to a date string (DATE JDBC type) like 2022-08-18, you can specify the jdbcType to DATE.

javaType

The javaType is a property for mapping result set column values to Java field values.

MyBatis can usually figure out the type if you’re mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behavior.

typeHandler

typeHandler can convert column values to Java values and verse vice.

Convert between the Java Enum type value and the column value

<resultMap id="BaseResultMap" type="com.demo.Task">
    ...
    <result column="category" typeHandler="com.demo.typehandler.CategoryEnumTypeHandler"
            property="category"/>
</resultMap>

#{category,typeHandler=com.demo.typehandler.CategoryEnumTypeHandler}

public class TaskCategoryEnumTypeHandler extends BaseTypeHandler<TaskTypeCategory> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, TaskTypeCategory taskTypeCategory, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i, taskTypeCategory.getName());
    }

    @Override
    public TaskTypeCategory getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        return TaskTypeCategory.fromDatabaseValue(resultSet.getString(columnName));
    }

    @Override
    public TaskTypeCategory getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException {
        return TaskTypeCategory.fromDatabaseValue(resultSet.getString(columnIndex));
    }

    @Override
    public TaskTypeCategory getNullableResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
        return TaskTypeCategory.fromDatabaseValue(callableStatement.getString(columnIndex));
    }
}

public enum TaskTypeCategory {

    DEVELOPMENT("development", "研发"),
    OPERATION("operation", "运维"),
    ;

    private String value;

    private String name;

    TaskTypeCategory(String name, String value) {
        this.name = name;
        this.value = value;
    }

    public String getName() {
        return name;
    }

    /**
     * @JsonValue Deserializing JSON to Enum
     */
    @JsonValue
    public String getValue() {
        return value;
    }

    public static TaskTypeCategory fromColumnValue(String columnValue) {
        if (databaseValue == null) {
            return null;
        }
        TaskTypeCategory taskTypeCategory = Arrays.asList(TaskTypeCategory.values())
            .stream()
            .filter(item -> item.getName().equals(databaseValue))
            .findFirst()
            .get();
        return taskTypeCategory;
    }
}

public class Task {
    ...
    private TaskTypeCategory category;
    ...
}

References

[1] Mapper XML Files - MyBatis

[2] Is jdbcType necessary in a MyBatis mapper?