mybatis custom type controller (TypeHandler) processes strings as collections

1. Question:

Assume such a scenario

The value in localurl is roughly like this: dwad21.jpg, dwad22.jpg, dwad.23.jpg is a string

If I have a field (local_url) in the sql table that is the concatenation value of multiple url strings of local image resources. I want to get value plus value without extra conversion in java backend. What I need is the dwad21.jpg inside, it is best to do collection processing.

The best situation is to turn the string into a collection when using sql to map to an object dwad21.jpg, dwad22.jpg, dwad.23.jpg => {dwad21. jpg,dwad22.jpg,dwad.23.jpg}

Then automatically turn the collection into a string when using insert or update {dwad21.jpg, dwad22.jpg, dwad.23.jpg} =>dwad21.jpg,dwad22.jpg ,dwad.23.jpg

2. Solution:

2.1.Typehandler official website introduction

It mainly performs custom mapping processing on request data or received data.

I can only say that mybaits is very arbitrary and provides a custom type converter.

mybatis official website=>Configuration=>Type processor
“———————————————— ——————》
Function: When MyBatis sets parameters in a prepared statement (PreparedStatement) or retrieves a value from the result set, it will use a type processor to convert the obtained value into a Java type in an appropriate manner.


The following are the default type handlers for commonly used data types.

Type Handler Java Type=>JDBC Type
BooleanTypeHandler java.lang.Boolean,boolean =>BOOLEAN
ByteTypeHandler java.lang.Byte,byte => NUMERIC, BYTE
ShortTypeHandler java.lang.Short, short =>NUMERIC, SMALLINT
IntegerTypeHandler java.lang.Integer, int =>NUMERIC, INTEGER
LongTypeHandler java.lang.Long, long => NUMERIC, BIGINT
FloatTypeHandler java.lang.Float, float => NUMERIC, FLOAT
DoubleTypeHandler java.lang.Double, double=> NUMERIC ,DOUBLE
BigDecimalTypeHandler java.math.BigDecimal => NUMERIC, DECIMAL
StringTypeHandler java.lang.String => CHAR, VARCHAR

2.2. Create a custom handler

You can override existing type handlers or create your own to handle unsupported or nonstandard types. The specific steps are:

  1. Implement the org.apache.ibatis.type.TypeHandler interface,
  2. Inherits a convenience class org.apache.ibatis.type.BaseTypeHandler and can (optionally) map it to a JDBC type. for example:

In most usage scenarios, mybaitis’s default type converter can help you convert java objects to jdbc objects.

But my whole flower life is obviously not satisfying. It just so happens that mybatis allows us to customize class converters.

Here we use the implementation interface method to declare the type controller.

public class StringToListTypeHandler implements TypeHandler<List<String>> {<!-- -->

    @Override
    public void setParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType) throws SQLException {<!-- -->
        if (parameter != null) {<!-- -->
            ps.setString(i, String.join(",", parameter));
        } else {<!-- -->
            ps.setNull(i, jdbcType.TYPE_CODE);
        }
    }

    @Override
    public List<String> getResult(ResultSet rs, String columnName) throws SQLException {<!-- -->
        String columnValue = rs.getString(columnName);
        return convertStringToList(columnValue);
    }

    @Override
    public List<String> getResult(ResultSet rs, int columnIndex) throws SQLException {<!-- -->
        String columnValue = rs.getString(columnIndex);
        return convertStringToList(columnValue);
    }

    @Override
    public List<String> getResult(CallableStatement cs, int columnIndex) throws SQLException {<!-- -->
        String columnValue = cs.getString(columnIndex);
        return convertStringToList(columnValue);
    }

    private List<String> convertStringToList(String columnValue) {<!-- -->
        if (columnValue != null) {<!-- -->
            return Arrays.asList(columnValue.split(","));
        } else {<!-- -->
            return null;
        }
    }
}

  • setParameter method: This method is used to set the parameters of a Java object into a PreparedStatement, usually used to bind parameters to placeholders in SQL statements. In this example, it converts the List type parameter to a comma-separated string and sets it into a PreparedStatement. If the parameter is null, it will be set to the null value of the database type.

  • getResult(ResultSet rs, String columnName): Obtain the result from the ResultSet object and obtain the corresponding column value according to the column name columnName.

  • getResult(ResultSet rs, int columnIndex): Obtain the result from the ResultSet object and obtain the corresponding column value according to the column index columnIndex.

  • getResult(CallableStatement cs, int columnIndex): Obtain the result from the CallableStatement object and obtain the corresponding column value according to the column index columnIndex. Typically this scenario is used to get results from stored procedures.
    Generally only one method can be implemented

2.3. Register type converter

Note that MyBatis does not determine which type to use by detecting database metainformation, so you must specify that the field is of VARCHAR type in the parameter and result mapping

<typeHandlers>
    <typeHandler handler="com.chen.behindimagesmanage.handler.StringToListTypeHandler" javaType="java.util.List<java.lang.String>" jdbcType="VARCHAR"/>
</typeHandlers>

Of course, aliases can also be configured here. If the type is set here in the mapper, there is no need to set it.

2.4. mapper uses type converter

  1. mapper xml file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.chen.behindimagesmanage.dao.FileDao">

    <!-- Define a query statement -->
    <resultMap id="imageMetaDataResultMap" type="com.chen.behindimagesmanage.pojo.ImageMetaData">
        <id property="id" column="id" />
        <result property="md5" column="md5" />
        <result property="aliyunUrl" column="aliyun_url" />
        <result property="localUrl" column="local_url" typeHandler="com.chen.behindimagesmanage.handler.StringToListTypeHandler" />
        <result property="version" column="version" />
    </resultMap>
    <select id="getAllImg" resultType="com.chen.behindimagesmanage.pojo.ImageMetaData">
        SELECT * FROM image_metadata
    </select>



    <!-- Define an update statement -->
    <parameterMap id="imageMetaDataParamMap" type="com.chen.behindimagesmanage.pojo.ImageMetaData">
        <parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="md5" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="aliyunUrl" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="localUrl" typeHandler="com.chen.behindimagesmanage.handler.StringToListTypeHandler"/>
        <parameter property="version" jdbcType="INTEGER" javaType="int" mode="IN"/>
    </parameterMap>
    <update id="updateLocalUrl"
    >
        UPDATE image_metadata SET local_url= #{localUrl,typeHandler = com.chen.behindimagesmanage.handler.StringToListTypeHandler}, version = #{localUrl} + 1 WHERE version = #{vsersion}
    </update>

</mapper>
  1. mapper interface layer
@Mapper
public interface FileDao {<!-- -->
    /**
     * Get all img ownership information
     * @return all ownership information
     */
    List<ImageMetaData> getAllImg();

    /**
     * Update metadata belonging server
     * @param imageMetaData updated metadata column
     * @return affected row
     */
    int updateLocalUrl(ImageMetaData imageMetaData);
}