1、背景
使用Mybatis存储枚举类型,数据库中以int类型存储,需要进行类型转换(handler适配)
2、代码实现
枚举类定义:
public interface IEnum
{
int getValue();
}
public enum EnumWhatIfChangeType implements IEnum
{
/**
* 不变
*/
UnChanged(0),
/**
* 变化
*/
Changed(1);
private int value;
private EnumWhatIfChangeType(int value)
{
this.value = value;
}
@Override
public int getValue()
{
return this.value;
}
}
Handler适配:
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
public class EnumValueTypeHandler extends BaseTypeHandler<IEnum>
{
private Class<IEnum> type;
/**
* 构造函数<br>
*/
public EnumValueTypeHandler(Class<IEnum> type)
{
this.type = type;
}
@Override
public IEnum getNullableResult(ResultSet rs, String columnName)
throws SQLException
{
int value = rs.getInt(columnName);
if (rs.wasNull())
{
return null;
}
else
{
return convertToEnum(value);
}
}
@Override
public IEnum getNullableResult(ResultSet rs, int columnIndex)
throws SQLException
{
int value = rs.getInt(columnIndex);
if (rs.wasNull())
{
return null;
}
else
{
return convertToEnum(value);
}
}
@Override
public IEnum getNullableResult(CallableStatement cs, int columnIndex)
throws SQLException
{
// 数据库中存储int类型
int value = cs.getInt(columnIndex);
if (cs.wasNull())
{
return null;
}
else
{
return convertToEnum(value);
}
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, IEnum param,
JdbcType jdbcType) throws SQLException
{
ps.setInt(i, param.getValue());
}
private IEnum convertToEnum(int value)
{
IEnum[] iEnums = type.getEnumConstants();
for (IEnum iEnum : iEnums)
{
if (iEnum.getValue() == value)
{
return iEnum;
}
}
return null;
}
}
Mapper.xml中
1、查询转换 -- resultMap标签添加转换
2、插入数据转换 -- insert标签中添加handler
<resultMap type="com.xxx.xxPM" id="xxPM">
<result property="changeType" column="CHANGETYPE" javaType="com.xxx.EnumChangeType" typeHandler="com.xxx.handler.EnumValueTypeHandler" />
</resultMap>
<select id="select" parameterType="map" resultMap="xxPM">
SELECT a.CHANGETYPE as changeType
FROM TBL_xxx
</select>
<!--插入数据 -->
<insert id="insertList" parameterType="list" useGeneratedKeys="true" keyProperty="id">
INSERT INTO TBL_xxx
(xxx,CHANGETYPE) VALUES
<foreach collection="list" item="item" separator=",">
(#{xxx}, #{item.changeType, typeHandler=com.xxx.EnumValueTypeHandler})
</foreach>
</insert>
3、条件查询中添加typeHandler无效,接收Map<String,Object>为参数进行条件查询,报错String不能转换为IEnum,解决办法,Map<String,Object>查询条件处进行转换,设置枚举转换后的value为查询条件,如:changeType->1
<if test="changeType != null">
AND CHANGETYPE = #{changeType}
</if>
附:
1、Mybatis返回基本类型为NULL是sql写法:(使用IFNULL)
<select id="selectMax" resultType="java.lang.Long">
SELECT IFNULL(MAX(TIME), 0)
FROM TBL_TIME
</select>
2、模糊查询数值类型(使用CONCAT(xxx,'')转化为字符串后模糊匹配)
<select id="pagingSelect" parameterType="map" resultMap="xxxPM">
SELECT a.xxx, a.INFOPMID as infoPMId, i.PROID as proId, i.SCENID as scenId
FROM TBL_TIME a , TBL_INFO i
WHERE i.PROID = #{proId} AND i.SCENID = #{scenId} AND i.ID = a.INFOPMID>
<if test="count != null">
AND CONCAT(COUNTBEFORE,"->",COUNTAFTER) LIKE CONCAT("%", #{count}, "%")
</if>
LIMIT #{offset},#{limit}
</select>
3、数值型进行模糊匹配,使用ROUND函数不能满足精度要求,可使用CAST转化0为0.00之后进行匹配
如:
查询score1分数是score2两倍的数据库记录,比率统一保留为两位小数。查询比例为2.00的数据库记录
select * from student WHERE CAST(score1/sroce2 as DECIMAL(18,2)) LIKE '%2.00%'
参考博客: