mybatis enum枚举 typeHandle

虽然mysql提供了enum类型，但有些情况下并不推荐直接使用。可以通过typeHandle将java中的枚举类型进行转换后存储到mysql

例如枚举 Delete

public enum Delete {

  NO(1, "没删除"),
  YES(2, "已删除");

  private Integer code;
  private String describe;

  public Integer getCode() {
    return code;
  }

  public String getDescribe() {
    return describe;
  }

  Delete(Integer code, String describe) {
    this.code = code;
    this.describe = describe;
  }

  public static Delete getDeleteFromCode(int code) {
    for (Delete delete : Delete.values()) {
      if (delete.getCode() == code) {
        return delete;
      }
    }
    return null;
  }

  public static Delete getDeleteFromDescribe(String describe) {
    for (Delete delete : Delete.values()) {
      if (delete.getDescribe() == describe) {
        return delete;
      }
    }
    return null;
  }
}

对于Delete这个枚举类型，在进行存储的时候可有四种选择

a.存name，此例中是 no yes

b.存序号，此例中是 0,1

c.存code，此例中是1,2

d.存describe,此例中是 没删除，已删除

mybatis对枚举类型有两种类型支持 EnumTypeHandler和EnumOrdinalTypeHandler。

其中EnumTypeHandler为默认的，存储枚举的name值(此例对应的是"NO,YES")，而EnumOrdinalTypeHandler存储的是枚举的序号 此处“NO”对应的序号是“0”，“YES”对应的序号是“1”;

ab两种情况mybatis已经做出了支持，而cd两种情况就需要我们自定义实现TypeHandler类。具体操作代码如下：

建表语句

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  `deleteStatusA` varchar(11) DEFAULT '0',
  `deleteStatusB` int(11) DEFAULT NULL,
  `deleteStatusC` int(11) DEFAULT NULL,
  `deleteStatusD` varchar(11) DEFAULT NULL,
  `createTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

实体类

import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Table;

@Table(name = "test_table")
public class TestTable {
    @Column(name = "id")
    private Integer id;

    private Delete delete;

    @Column(name = "createTime")
    private Date createtime;

    @Column(name = "updateTime")
    private Date updatetime;


    /**
     * @return ID
     */
    public Integer getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * @return delete
     */
    public Delete getDelete() {
        return delete;
    }

    /**
     * @param delete
     */
    public void setDelete(Delete delete) {
        this.delete = delete;
    }


    /**
     * @return createTime
     */
    public Date getCreatetime() {
        return createtime;
    }

    /**
     * @param createtime
     */
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    /**
     * @return updateTime
     */
    public Date getUpdatetime() {
        return updatetime;
    }

    /**
     * @param updatetime
     */
    public void setUpdatetime(Date updatetime) {
        this.updatetime = updatetime;
    }

}

mybatis配置文件

<?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.hstao.mapper.TestTableMapper">
  <resultMap id="BaseResultMap" type="com.hstao.model.domain.TestTable">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" property="id" jdbcType="INTEGER"/>
    <result column="deleteStatusA" property="delete"/>
    <result column="deleteStatusB" property="delete"/>
    <result column="deleteStatusC" property="delete"/>
    <result column="deleteStatusD" property="delete"/>
    <result column="createTime" property="createtime" jdbcType="TIMESTAMP"/>
    <result column="updateTime" property="updatetime" jdbcType="TIMESTAMP"/>
  </resultMap>

  <!--对应场景a-->
  <insert id="insertDataA" parameterType="TestTable">
   INSERT INTO test_table  ( id,deleteStatusA)
  values(#{id} ,#{delete})
  </insert>

  <!--对应场景b-->
  <insert id="insertDataB" parameterType="TestTable">
   INSERT INTO test_table  ( id,deleteStatusB)
 values(#{id} ,#{delete, typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler})
  </insert>

  <!--对应场景c-->
  <insert id="insertDataC" parameterType="TestTable">
   INSERT INTO test_table  ( id,deleteStatusC)
  values(#{id} ,#{delete, typeHandler=com.hstao.typeHandle.DeleteCodeTypeHandle})
  </insert>

  <!--对应场景d-->
  <insert id="insertDataD" parameterType="TestTable">
   INSERT INTO test_table  ( id,deleteStatusD)
  values(#{id} ,#{delete, typeHandler=com.hstao.typeHandle.DeleteDescribeTypeHandle})
  </insert>
</mapper>

自定义的DeleteCodeTypeHandle

/**
 * 描述: DeleteCodeTypeHandle
 *
 * @author hstao
 * @create 2019-05-07 10:05
 */
public class DeleteCodeTypeHandle implements TypeHandler<Delete> {

  @Override
  public void setParameter(PreparedStatement preparedStatement, int i, Delete delete, JdbcType jdbcType)
      throws SQLException {
    preparedStatement.setInt(i,delete.getCode());
  }

  @Override
  public Delete getResult(ResultSet resultSet, String s) throws SQLException {
    return Delete.getDeleteFromCode(resultSet.getInt(s));
  }

  @Override
  public Delete getResult(ResultSet resultSet, int i) throws SQLException {
    return Delete.getDeleteFromCode(resultSet.getInt(i));
  }

  @Override
  public Delete getResult(CallableStatement callableStatement, int i) throws SQLException {
    return Delete.getDeleteFromCode(callableStatement.getInt(i));
  }
}

自定义的DeleteDescribeTypeHandle

/**
 * 描述: DeleteDescribeTypeHandle
 *
 * @author hstao
 * @create 2019-05-07 10:15
 */
public class DeleteDescribeTypeHandle implements TypeHandler<Delete> {

  @Override
  public void setParameter(PreparedStatement preparedStatement, int i, Delete delete, JdbcType jdbcType)
      throws SQLException {
    preparedStatement.setString(i,delete.getDescribe());
  }

  @Override
  public Delete getResult(ResultSet resultSet, String s) throws SQLException {
    return Delete.getDeleteFromDescribe(resultSet.getString(s));
  }

  @Override
  public Delete getResult(ResultSet resultSet, int i) throws SQLException {
    return Delete.getDeleteFromDescribe(resultSet.getString(i));
  }

  @Override
  public Delete getResult(CallableStatement callableStatement, int i) throws SQLException {
    return Delete.getDeleteFromDescribe(callableStatement.getString(i));
  }
}

测试调用：

/**
 * 描述: TestTableManager
 *
 * @author hstao
 * @create 2019-05-06 16:36
 */
@Component
public class TestTableManager implements TestTableManagerI {
  @Resource
  private TestTableMapper testTableMapper;

  @Override
  public void insert(){
    TestTable testTable=new TestTable();
    testTable.setId(100);
    testTable.setDelete(Delete.YES);
    testTableMapper.insertDataA(testTable);
    testTableMapper.insertDataB(testTable);
    testTableMapper.insertDataC(testTable);
    testTableMapper.insertDataD(testTable);
  }
}

执行此处的insert方法，分别对应Delete的四种存储场景；

结果如下：

 

可以看到对于同一条数据，对于abcd四种不同场景，在mysql中最终存储的值不一样，根据业务需要，可以选择不同的类型，同时用java的enum来替代mysql的enum，代码可读性，封装也比较好