设有这样一个
需求:
有一个布尔型的字段需要保存到数据库中,但是数据库不支持布尔类型,因此采用一位字符(char(1))来存储这个布尔值,javabean中仍然采用boolean类型。
我们采用mybatis作为持久层框架,但是就有一个问题,数据库中使char型,而程序中是boolean型,如何实现数据类型自动转换?
解决办法:
mybatis提供了对自定义的类型转换器(typeHandler)的支持,因此我们可以自己编写类型转换器来实现这一自动转换的功能。
实现步骤:
第一步:编写自定义类型转换器
<strong><font class="Apple-style-span" color="#696969">package com.hotent.base.db.mybatis.handler;
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;
/**
* @author
* java中的boolean和jdbc中的char之间转换;true-Y;false-N
*/
public class BooleanTypeHandler implements TypeHandler<Object>{
/* (non-Javadoc)
* @see org.apache.ibatis.type.TypeHandler#getResult(java.sql.ResultSet, java.lang.String)
*/
@Override
public Object getResult(ResultSet arg0, String arg1) throws SQLException {
String str = arg0.getString(arg1);
Boolean rt = Boolean.FALSE;
if (str.equalsIgnoreCase("Y")){
rt = Boolean.TRUE;
}
return rt;
}
@Override
public Object getResult(ResultSet arg0, int arg1) throws SQLException {
// TODO Auto-generated method stub
Boolean b = arg0.getBoolean(arg1);
return b == true ? "Y" : "N";
}
/* (non-Javadoc)
* @see org.apache.ibatis.type.TypeHandler#getResult(java.sql.CallableStatement, int)
*/
@Override
public Object getResult(CallableStatement arg0, int arg1)
throws SQLException {
Boolean b = arg0.getBoolean(arg1);
return b == true ? "Y" : "N";
}
/* (non-Javadoc)
* @see org.apache.ibatis.type.TypeHandler#setParameter(java.sql.PreparedStatement, int, java.lang.Object, org.apache.ibatis.type.JdbcType)
*/
@Override
public void setParameter(PreparedStatement arg0, int arg1, Object arg2,
JdbcType arg3) throws SQLException {
Boolean b = (Boolean) arg2;
String value = (Boolean) b == true ? "Y" : "N";
arg0.setString(arg1, value);
}
}</font></strong>
第二步:注册类型转换器
在mybatis的配置文件中添加typeHandlers节点,在配置该节点时最好按照官方文档提供的节点顺序添加,否则可能会出现以下错误信息:
The content of element type "configuration" must match "(properties?,settings?,typeAliases?,typeHandlers?,objectFactory?,objectWrapperFactory?,plugins?,environments?,mappers?)".
官方文档的配置文件节点顺序为:properties,setting,typeAliases,typeHandles,objectFactory,plugins,environments,mappins
以下为xml代码:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
<properties resource="conf/x5-base-db.properties"/>
<settings >
<setting name="cacheEnabled" value="false"/>
</settings>
<typeHandlers>
<typeHandler javaType="Boolean" jdbcType="CHAR" handler="com.hotent.base.db.mybatis.handler.BooleanTypeHandler" />
</typeHandlers>
<plugins>
<plugin interceptor="com.hotent.base.db.mybatis.OffsetLimitInterceptor">
<property name="dbType" value="${jdbc.dbType}"/>
<property name="Dialect.oracle" value="com.hotent.base.db.mybatis.dialect.OracleDialect"/>
<property name="Dialect.mssql" value="com.hotent.base.db.mybatis.dialect.SQLServer2005Dialect"/>
<property name="Dialect.mysql" value="com.hotent.base.db.mybatis.dialect.MySQLDialect"/>
<property name="Dialect.db2" value="com.hotent.base.db.mybatis.dialect.DB2Dialect"/>
<property name="Dialect.h2" value="com.hotent.base.db.mybatis.dialect.H2Dialect"/>
</plugin>
</plugins>
</configuration>
第三步: 指定类型转换以isFolder字段为例,下面贴出其mapping代码
<?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.hotent.platform.system.model.Resources">
<resultMap id="Resources" type="com.hotent.platform.system.model.Resources">
<id property="id" column="ID_" jdbcType="VARCHAR"/>
<result property="name" column="NAME_" jdbcType="VARCHAR"/>
<result property="alias" column="ALIAS" jdbcType="VARCHAR"/>
<result property="sn" column="SN_" jdbcType="VARCHAR"/>
<result property="icon" column="ICON_" jdbcType="VARCHAR"/>
<result property="parentId" column="PARENT_ID_" jdbcType="VARCHAR"/>
<result property="defaultUrl" column="DEFAULT_URL_" jdbcType="VARCHAR"/>
<result property="isFolder" column="IS_FOLDER_" jdbcType="CHAR"/>
<result property="displayInMenu" column="DISPLAY_IN_MENU_" jdbcType="VARCHAR"/>
<result property="isOpen" column="IS_OPEN_" jdbcType="VARCHAR"/>
<result property="systemId" column="SYSTEM_ID_" jdbcType="VARCHAR"/>
<result property="path" column="PATH_" jdbcType="VARCHAR"/>
</resultMap>
<insert id="create" parameterType="com.hotent.platform.system.model.Resources">
INSERT INTO sys_res_resources
(ID_,NAME_,ALIAS,SN_,ICON_,PARENT_ID_,DEFAULT_URL_,IS_FOLDER_,DISPLAY_IN_MENU_,IS_OPEN_,SYSTEM_ID_,PATH_)
VALUES
(#{id,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{alias,jdbcType=VARCHAR}, #{sn,jdbcType=VARCHAR}, #{icon,jdbcType=VARCHAR}, #{parentId,jdbcType=VARCHAR}, #{defaultUrl,jdbcType=VARCHAR}, #{isFolder,jdbcType=CHAR,javaType=Boolean}, #{displayInMenu,jdbcType=VARCHAR}, #{isOpen,jdbcType=VARCHAR}, #{systemId,jdbcType=VARCHAR}, #{path,jdbcType=VARCHAR})
</insert>
<update id="update" parameterType="com.hotent.platform.system.model.Resources">
UPDATE sys_res_resources SET
NAME_=#{name,jdbcType=VARCHAR},
ALIAS=#{alias,jdbcType=VARCHAR},
SN_=#{sn,jdbcType=VARCHAR},
ICON_=#{icon,jdbcType=VARCHAR},
PARENT_ID_=#{parentId,jdbcType=VARCHAR},
DEFAULT_URL_=#{defaultUrl,jdbcType=VARCHAR},
IS_FOLDER_=#{isFolder, jdbcType=CHAR,javaType=Boolean},
DISPLAY_IN_MENU_=#{displayInMenu,jdbcType=VARCHAR},
IS_OPEN_=#{isOpen,jdbcType=VARCHAR},
SYSTEM_ID_=#{systemId,jdbcType=VARCHAR},
PATH_=#{path,jdbcType=VARCHAR}
WHERE
ID_=#{id}
</update>
</mapper>
这样mybatis就能将数据库中的类型与java中的类型自动做转换了。其他类型转方法同上