MyBatis typeHandler

MyBatis在设置参数或者从结果集中获取参数的时候，都会用到注册了的typeHandler进行处理。typeHandler的作用为将参数从javaType转为jdbcType，或者从数据库取出结果时把jdbcType转为javaType。

带的typeHandler

类型处理器	Java类型	JDBC类型
BooleanTypeHandler	java.lang.Boolean,boolean	数据库兼容的BOOLEAN
ByteTypeHandler	java.lang.Byte,byte	数据库兼容的NUMERIC或BYTE
ShortTypeHandler	java.lang.Short,short	数据库兼容的NUMERIC或SHORT INTEGER
IntegerTypeHandler	java.lang.Integer,int	数据库兼容的NUMERIC或INTEGER
LongTypeHandler	java.lang.Long,long	数据库兼容的NUMERIC或LONG INTEGER
FloatTypeHandler	java.lang.Float,float	数据库兼容的NUMERIC或FLOAT
DoubleTypeHandler	java.lang.Double,double	数据库兼容的NUMERIC或DOUBLE
BigDecimalTypeHandler	java.math.BigDecimal	数据库兼容的NUMERIC或DECIMAL
StringTypeHandler	java.lang.Stirng	CHAR,VARCHAR
ClobypeHandler	java.lang.String	CLOB,LONGVARCHAR
NStringTypeHanler	java.lang.String	NVARCHAR,NCHAR
NClobTypeHandler	java.lang.String	NNCLOB
ByteArrayTypeHandler	byte[]	数据库兼容的字节流类型
BlobTypeHandler	byte[]	BLOB,LONGVARBINARY
DateTypeHandler	java.util.Date	TIMESTAMP
DateOnlyTypeHandler	java.util.Date	DATE
TimeOnlyTypeHandler	java.util.Date	TIME
SqlTimestampTypeHandler	java.sql.Timestamp	TIMESTAMP
SqlDateTypeHandler	java.sql.Date	DATE
SqlTimeTypeHandler	java.sql.Time	TIME
ObjectTypeHandler	Any	OTHER或未指定类型
EnumTypeHandler	Enumeration Type	VARCHAR或任意兼容的字符串类型，存储枚举的名称
EnumOrdinalTypeHandler	Enumeration Type	任何兼容的NUMERIC或DOUBLE类型，存储枚举的索引

为了演示自带的typeHandler，新建一张表：

CREATE TABLE `t_role` (
    `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `role_name` varchar(60) NOT NULL COMMENT '角色名称',
    `note` varchar(1024) DEFAULT NULL COMMENT '备注',
    `is_girl` varchar(20) DEFAULT NULL COMMENT '是否为女孩',
    PRIMARY KEY (`id`)
)

库表对应的实体类Role略。

接口RoleMapper中定义一个createRole()抽象方法：

1	public int createRole(Role role);

映射文件：

<insert id="createRole" parameterType="role">
    <![CDATA[
        insert into t_role(role_name,note,is_girl) values (#{roleName},#{note},
        #{isGirl})
    ]]>
</insert>

测试插入方法：

......
sqlSession = SqlSessionFactoryUtil.openSqlSession();
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
Role role = new Role();
role.setRoleName("雏田");
role.setNote("日向雏田");
role.setIsGirl(Boolean.TRUE);
roleMapper.createRole(role);
sqlSession.commit();
......

查询数据库：

mysql> select * from t_role;
+----+-----------+----------+---------+
| id | role_name | note     | is_girl |
+----+-----------+----------+---------+
| 1  |  雏田      | 日向雏田 |    1    |
+----+-----------+----------+---------+
1 row in set (0.00 sec)

从结果中可以看出，默认的BooleantypeHandler将true转换为了1。

如果想把true转换为Y,false转换为N,我们可以自定义BooleantypeHandler。

自定义typeHandler

自定义typeHandler可以通过继承BasetypeHandler或者实现typeHandler接口来实现，现自定义一个BooleanTypeHandler：

package mrbird.leanote.typehandler;
 
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
 
public class BooleanTypeHandler implements TypeHandler<Boolean>{
 
    @Override
    public Boolean getResult(ResultSet arg0, String arg1) throws SQLException {
        String str = arg0.getString(arg1);
        Boolean flag = Boolean.FALSE;
        if(str.equalsIgnoreCase("Y")){
            flag = Boolean.TRUE;
        }
        return flag;
    }
    
    @Override
    public Boolean getResult(ResultSet arg0, int arg1) throws SQLException {
        String str = arg0.getString(arg1);
        Boolean flag = Boolean.FALSE;
        if(str.equalsIgnoreCase("Y")){
            flag = Boolean.TRUE;
        }
        return flag;
    }
    
    @Override
    public Boolean getResult(CallableStatement arg0, int arg1)
            throws SQLException {
        String str = arg0.getString(arg1);
        Boolean flag = Boolean.FALSE;
        if(str.equalsIgnoreCase("Y")){
            flag = Boolean.TRUE;
        }
        return flag;  
    }
    
    @Override
    public void setParameter(PreparedStatement arg0, int arg1, Boolean arg2,
    		JdbcType arg3) throws SQLException {
        Boolean flag = (Boolean) arg2;    
        String value = flag == true ? "Y" : "N";    
        arg0.setString(arg1, value); 	
    }
}

在mybatis-config.xml文件中配置该typeHandler：

<typeHandlers>
    <typeHandler javaType="Boolean" jdbcType="VARCHAR" 
        handler="mrbird.leanote.typehandler.BooleanTypeHandler" />
</typeHandlers>

然后在映射文件中对需要转换的字段标注javaType和jdbcType，或者无需在mybatis-config.xml中注册直接在映射文件中指明typeHandler的路径即可。

指明javaType和jdbcType，与注册中的一致即可找到相对应的typeHandler：

<insert id="createRole" parameterType="role">
    <![CDATA[
        insert into t_role(role_name,note,is_girl) values (#{roleName},#{note},
        #{isGirl,javaType=Boolean,jdbcType=VARCHAR})
    ]]>
</insert>

或指明typeHandler路径：

<insert id="createRole" parameterType="role">
    <![CDATA[
        insert into t_role(role_name,note,is_girl) values (#{roleName},#{note},
        #{isGirl,typeHandler=mrbird.leanote.typehandler.BooleanTypeHandler})
    ]]>
</insert>

测试：

......
sqlSession = SqlSessionFactoryUtil.openSqlSession();
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
Role role = new Role();
role.setRoleName("鸣人");
role.setNote("旋涡鸣人");
role.setIsGirl(Boolean.FALSE);
roleMapper.createRole(role);
sqlSession.commit();
......

查询数据库：

mysql> select * from t_role;
+----+-----------+----------+---------+
| id | role_name | note     | is_girl |
+----+-----------+----------+---------+
|  1 | 雏田      | 日向雏田  | 1       |
|  2 | 鸣人      | 旋涡鸣人  | N       |
+----+-----------+----------+---------+
2 rows in set (0.00 sec)

可发现，false已经转换为N了。

枚举类型typeHandler

MyBatis自带两种枚举类型处理器：

1.org.apache.ibatis.type.EnumOrdinalTypeHandler

2.org.apache.ibatis.type.EnumTypeHandler

EnumOrdinalTypeHandler使用整数下标传递，EnumTypeHandler使用枚举字符串传递。

创建一个枚举类型Sex：

package mrbird.leanote.enums;
 
public enum Sex {
    MALE(1,"男"),FEMALE(2,"女");
    private int id;
    private String name;
    private Sex(int id, String name) {
        this.id = id;
        this.name = name;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

EnumOrdinalTypeHandler

创建一张表来演示EnumOrdinalTypeHandler：

CREATE TABLE `t_student` (
    `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
    `cnname` varchar(60) NOT NULL COMMENT '学生姓名',
    `sex` tinyint(4) NOT NULL COMMENT '性别',
    `selfcard_no` int(20) NOT NULL COMMENT '学生证号',
    `note` varchar(1024) DEFAULT NULL COMMENT '备注',
    PRIMARY KEY (`id`)
)

这里sex字段类型为tinyint类型。

Student实体类略。

定义一个interface：

package mrbird.leanote.mapper;
 
import java.util.List;
import mrbird.leanote.pojo.Student;
 
public interface StudentMapper {
    public List<Student> getAllStudent();
    public int createStudent(Student stu);
}

对应的映射文件StudentMapper.xml：

<?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="mrbird.leanote.mapper.StudentMapper">  
    <resultMap type="student" id="studentList">
        <id column="id" property="id"/>
        <result column="cnname" property="cnName"/>
        <result column="sex" property="sex"
            typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
        <result column="selfcard_no" property="selfCardNo"/>
        <result column="note" property="note"/>
    </resultMap>
    <select id="getAllStudent" resultMap="studentList">
        <![CDATA[select * from t_student]]>
    </select>
    <insert id="createStudent" parameterType="student">
        <![CDATA[
            insert into t_student(cnname,sex,selfcard_no,note) values (
            #{cnName},
            #{sex,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler},
            #{selfCardNo},#{note})
        ]]>
    </insert>
</mapper>

测试createStudent方法：

...
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setCnName("鸣人");
stu.setSex(Sex.MALE);
stu.setSelfCardNo(1L);
stu.setNote("漩涡鸣人");
studentMapper.createStudent(stu);
sqlSession.commit();
...

查询数据库：

mysql> select * from t_student;
+----+--------+-----+-------------+----------+
| id | cnname | sex | selfcard_no | note     |
+----+--------+-----+-------------+----------+
|  1 | 鸣人   |  0  |          1  | 漩涡鸣人  |
+----+--------+-----+-------------+----------+
1 row in set (0.00 sec)

可见EnumOrdinalTypeHandler已经将MALE转换为了MALE的下标了。

测试getAllStudent方法：

...
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.getAllStudent();
for(Student stu : students){
	System.out.println(stu.getCnName()+"性别："+stu.getSex());
}
...

控制台输出：

鸣人性别：MALE

EnumTypeHandler

为了演示EnumTypeHandler，我们需要把sex字段类型改为VARCHAR：

1	alter table t_student modify column sex VARCHAR(20);

修改映射文件中的typeHandler：

<?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="mrbird.leanote.mapper.StudentMapper">  
    <resultMap type="student" id="studentList">
        <id column="id" property="id"/>
        <result column="cnname" property="cnName"/>
        <result column="sex" property="sex"
            typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>
        <result column="selfcard_no" property="selfCardNo"/>
        <result column="note" property="note"/>
    </resultMap>
    <select id="getAllStudent" resultMap="studentList">
        <![CDATA[select * from t_student]]>
    </select>
    <insert id="createStudent" parameterType="student">
        <![CDATA[
            insert into t_student(cnname,sex,selfcard_no,note) values (
            #{cnName},
            #{sex,typeHandler=org.apache.ibatis.type.EnumTypeHandler},
            #{selfCardNo},#{note})
        ]]>
    </insert>
</mapper>

测试createStudent方法：

...
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student stu = new Student();
stu.setCnName("雏田");
stu.setSex(Sex.FEMALE);
stu.setSelfCardNo(2L);
stu.setNote("日向雏田");
studentMapper.createStudent(stu);
sqlSession.commit();
...

查询数据库：

mysql> select * from t_student where selfcard_no = '2';
+----+--------+--------+-------------+----------+
| id | cnname | sex    | selfcard_no | note     |
+----+--------+--------+-------------+----------+
|  2 | 雏田   | FEMALE |           2 | 日向雏田  |
+----+--------+--------+-------------+----------+
1 row in set (0.00 sec)

可见EnumTypeHandler保存的是枚举字符串。

《深入浅出MyBatis技术原理与实战》读书笔记