###mybatis自定义类型转换器
在开发权限系统时,需要存储某个角色对应的权限信息,权限信息通过,分割,填入权限的键值。例如数据库存储如下:
类型转换器代码如下:
import com.google.common.base.Strings;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.stereotype.Component;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class ListIntegerTypeHandler extends BaseTypeHandler<List<Integer>> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, List<Integer> integers, JdbcType jdbcType) throws SQLException {
if (integers == null || integers.size() == 0) {
preparedStatement.setString(i, null);
return;
}
StringBuilder sb = new StringBuilder();
for (Integer s : integers) {
sb.append(s).append(",");
}
preparedStatement.setString(i, sb.toString().substring(0, sb.toString().length() - 1));
}
@Override
public List<Integer> getNullableResult(ResultSet resultSet, String s) throws SQLException {
if (Strings.isNullOrEmpty(resultSet.getString(s))) {
return null;
}
String[] arrays = resultSet.getString(s).split(",");
List<Integer> integers = new ArrayList<>();
for (String item : arrays) {
integers.add(Integer.valueOf(item));
}
return integers;
}
@Override
public List<Integer> getNullableResult(ResultSet resultSet, int i) throws SQLException {
if (Strings.isNullOrEmpty(resultSet.getString(i))) {
return null;
}
String[] arrays = resultSet.getString(i).split(",");
List<Integer> integers = new ArrayList<>();
for (String item : arrays) {
integers.add(Integer.valueOf(item));
}
return integers;
}
@Override
public List<Integer> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
if (Strings.isNullOrEmpty(callableStatement.getString(i))) {
return null;
}
String[] arrays = callableStatement.getString(i).split(",");
List<Integer> integers = new ArrayList<>();
for (String item : arrays) {
integers.add(Integer.valueOf(item));
}
return integers;
}
}
使用时,需注意以下信息:
###(1)在application.properties文件中,需要设置扫包的路径,否则mybatis无法识别该类型转换器。
指定mapper位置
mybatis.mapper-locations=classpath:/mapper/*.xml
指定需要扫描的类型转换器位置
mybatis.type-handlers-package=com.hero.server.support.mybatis.typehandler
###(2)查询时,自动将varchar类型转换成Integer列表。mapper文件中,结果映射时,添加对应的typehandler
<resultMap id="BaseResultMap" type="com.hero.server.entity.RolePermissions">
<!--@Table tb_role_permissions-->
<result property="roleId" column="role_id" jdbcType="INTEGER"/>
<result property="permissionIds" column="permission_ids" jdbcType="VARCHAR" typeHandler="com.hero.server.support.mybatis.typehandler.ListIntegerTypeHandler"/>
</resultMap>
###(3)插入或者更新操作时,同样需要对其进行设置,这里需要在插入语句出进行设置,否则mybatis报错。
insert into upgrade.tb_role_permissions(role_id, permission_ids)
values (#{roleId},
#{permissionIds, jdbcType=VARCHAR, typeHandler=com.hero.server.support.mybatis.typehandler.ListIntegerTypeHandler})
<!--通过主键修改数据-->
<update id="update">
update upgrade.tb_role_permissions
<set>
<if test="permissionIds != null and permissionIds != ''">
permission_ids = #{permissionIds, jdbcType=VARCHAR, typeHandler=com.hero.server.support.mybatis.typehandler.ListIntegerTypeHandler},
</if>
</set>
where role_id = #{roleId}
</update>