Mybatis custom typehandler to solve the problem of special type conversion

We know that the data types in Java and MySQL are different. In Java, in addition to the basic data types, there are objects.

Sometimes when you use Mysql to store data or read data from mysql, you will have some special needs, such as:

It's too hard, Ba!

There are two solutions:

The first method will not be repeated here, which is not smart enough. Here we mainly talk about how to customize the handler to solve the special type conversion problem of Java data - > MySQL data

This handler not only facilitates our database operation, but also facilitates code reuse.

Here, take the varchar string of integer [] array as an example.

Problem example

We define a role class corresponding to the role table of the database:

public class Role {
  private Integer id;
  private String name;
  private Integer[] accessIds;
  private Date createTime;
  // ... ignore get and set methods
}

Notice that there is an accessids field, whose type is integer []

Database design:

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
 `id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,`access_ids` varchar(255) DEFAULT NULL,`create_time` datetime NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1','测试角色',','2019-11-14 13:43:14');

Custom handler class

By inheriting basetypehandler class and overriding its method, define a handler class that automatically converts integer [] to database varchar type:

/**
 * Java Int数组与MysqL String转换器
 * 比如[1,3] --> ","
 */
public class StringToIntArrayHandler extends BaseTypeHandler<Integer[]> {

  private static final String splitCharset = ",";

  @Override
  public void setNonNullParameter(PreparedStatement ps,int i,Integer[] objects,JdbcType jdbcType) throws sqlException {
    String str = arrayToString(objects);
    ps.setString(i,str);
  }

  @Override
  public Integer[] getNullableResult(ResultSet rs,String columnName) throws sqlException {
    String str = rs.getString(columnName);
    return stringToArray(str);
  }

  @Override
  public Integer[] getNullableResult(ResultSet rs,int columnIndex) throws sqlException {
    String str = rs.getString(columnIndex);
    return stringToArray(str);
  }

  @Override
  public Integer[] getNullableResult(CallableStatement cs,int columnIndex) throws sqlException {
    String str = cs.getString(columnIndex);
    return stringToArray(str);
  }

  // --- private methods ---

  /**
   * Integer数组转String
   * 注：使用提前设定好的分隔符分割数组的每一项
   */
  private static String arrayToString(Integer[] array) {
    StringBuilder res = new StringBuilder();
    if (array != null && array.length > 0) {
      for (Object o : array) {
        res.append(splitCharset).append(o.toString());
      }
      res.append(splitCharset);
    }
    return res.length() > 0 ? res.toString() : null;
  }

    /**
   * 从String转Integer数组
   * 注：String是用分隔符分割的，使用String.split方法可以分解为数组
   */
  private static Integer[] stringToArray(String str) {
    List<Integer> list = new ArrayList<>();
    if (str != null) {
      String[] array = str.split(splitCharset);
      if (array.length > 0) {
        for (String o : array) {
          if (o != null && o.length() > 0) {
            list.add(Integer.parseInt(o));
          }
        }
      }
    }
    return list.toArray(new Integer[0]);
  }
}

What is the specific role of this class?

Let's demonstrate how to use smile

Apply custom handler in mybatis

Mybatis XML file for storing SQL statements:

<?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.example.model.dao.RoleDAO">

  <resultMap id="roleMap" type="com.example.model.bean.Role">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="accessIds" column="access_ids"
        typeHandler="ccom.example.model.dao.handler.StringToIntArrayHandler"/>
    <result property="createTime" column="create_time"/>
  </resultMap>

  <select id="findById" parameterType="map" resultMap="roleMap">
    SELECT id,name,access_ids,create_time
    FROM role
    WHERE id = #{id}
  </select>

  <insert id="insert" parameterType="com.example.model.bean.Role">
    <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
      SELECT LAST_INSERT_ID()
    </selectKey>

    INSERT INTO role
    (name,create_time,access_ids)
    VALUES
    (#{name},#{createTime},#{accessIds,jdbcType=VARCHAR,typeHandler=com.example.model.dao.handler.StringToIntArrayHandler})
  </insert>

</mapper>

The above XML demonstrates how to apply typehandler in the case of select and insert.