MyBatis-自定义TypeHandler实战

从 Java 类型到 JDBC 类型

setNonNullParameter ：设置非空参数

从 JDBC 类型到 Java 类型

getNullableResult ：获取空结果集（根据列名），一般都是调用这个

getNullableResult ：获取空结果集（根据下标值）

getNullableResult ：存储过程用的

1.自定义TypeHandler-RoleParamMappingTypeHandler

public class RoleParamMappingTypeHandler extends BaseTypeHandler<String> {
    private static final Map<String,String> roleMappingInput=new HashMap<>(4);
    private static final Map<String,String> roleMappingOutput=new HashMap<>(4);
    static {
        for (UserRoleEnum userRoleEnum : UserRoleEnum.values()) {
            roleMappingInput.put(userRoleEnum.getRoleCode(),userRoleEnum.getPsRoleCode());
            roleMappingOutput.put(userRoleEnum.getPsRoleCode(),userRoleEnum.getRoleCode());
        }

    }
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType)
            throws SQLException {
        log.info("员工角色:{}---------------setNonNullParameter转化后：{}",parameter,roleMappingInput.get(parameter));
        ps.setString(i, roleMappingInput.get(parameter));
    }

    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {

        log.info("员工角色:{}---------------getNullableResult：{}",rs.getString(columnName),roleMappingOutput.get(rs.getString(columnName)));
        return roleMappingOutput.get(rs.getString(columnName));
    }

    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        // 根据下标获取 String 类型的参数的时候调用
        log.info("员工角色:{}------------------------------getNullableResult2："+columnIndex);
        return  roleMappingOutput.get(rs.getString(columnIndex));
    }

    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        log.info("员工角色:{}------------------------------getNullableResult3：");
        return  roleMappingOutput.get(cs.getString(columnIndex));
    }

或者

public class RoleParamMappingTypeHandler implements TypeHandler<String> {
    private static final Map<String,String> roleMappingInput=new HashMap<>(4);
    private static final Map<String,String> roleMappingOutput=new HashMap<>(4);
    static {
        for (UserRoleEnum userRoleEnum : UserRoleEnum.values()) {
            roleMappingInput.put(userRoleEnum.getRoleCode(),userRoleEnum.getPsRoleCode());
            roleMappingOutput.put(userRoleEnum.getPsRoleCode(),userRoleEnum.getRoleCode());
        }

    }
      @Override
        public void setParameter(PreparedStatement preparedStatement, int i, String s, JdbcType jdbcType)
            throws SQLException {
            log.info("员工角色:{}---------------setNonNullParameter转化后：{}", s,roleMappingInput.get(s));
            preparedStatement.setString(i, roleMappingInput.get(s));
        }

        @Override
        public String getResult(ResultSet resultSet, String s) throws SQLException {
            log.info("员工角色:{}---------------getNullableResult：{}",resultSet.getString(s),roleMappingOutput.get(resultSet.getString(s)));
            return roleMappingOutput.get(resultSet.getString(s));
        }

        @Override
        public String getResult(ResultSet resultSet, int i) throws SQLException {
            log.info("员工角色:{}------------------------------getNullableResult2："+i);
            return  roleMappingOutput.get(resultSet.getString(i));
        }

        @Override
        public String getResult(CallableStatement callableStatement, int i) throws SQLException {
            log.info("员工角色:{}------------------------------getNullableResult3：");
            return  roleMappingOutput.get(callableStatement.getString(i));
        }
}

2.添加mybatis-config

<typeHandlers>
        <typeHandler handler="com.X.server.mybatis.type.handler.RoleParamMappingTypeHandle"></typeHandler>
    </typeHandlers>

或者通过配置类

 @Primary
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(final DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        .....
         //指定处理类加载
        //sessionFactory.setTypeHandlers(new TypeHandler[] { new RoleParamMappingTypeHandler()});
// 或者 包路径加载
        sessionFactory.setTypeHandlersPackage("com.X.server.mybatis.type.handler");

        return sessionFactory.getObject();
    }

3.使用

返回值转化映射

  <resultMap id="PsroleuserBaseResultMap" type="*.PsroleuserResponseVO" >
    <result column="ROLENAME" property="rolename"  
              typeHandler="com.X.server.mybatis.type.handler.RoleParamMappingTypeHandle"/>
  </resultMap>

输入参数转化映射

AND R.ROLENAME = #{rolename,jdbcType=VARCHAR,typeHandler=com.X.server.mybatis.type.handler.RoleParamMappingTypeHandler}

插入时候使用

<insert id="insertPerson" parameterType="person">
        INSERT INTO person (id,name,sex,hobbys,data_time) values(#{id},#{name},#{sex},#{hobbys,typeHandler=com.sankuai.lkl.typeHandler.ListTypeHandler},#{date})
</insert>
或者 注意注解会在查询和插入的时候都会生效
@ColumnType(typeHandler = IntegerArrayHandler.class, jdbcType = JdbcType.ARRAY, column = "role_id")

4.注意typeHandler不生效的情况

如果集成为extends BaseTypeHandler<String>，由于typeHandler设置值的时候 ，调用的是setNonNullParameter()，当参数为null的时候，无法触发此方法， 所以返回值为空，也无法触发getResult(),所以表现出来的是不生效。

解决办法，1 查看输入参数，保证参数不为空，2  实现 TypeHandler<String>接口，可以处理空参数。

5.typeHandler实例Map，List转数据库String

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import lombok.extern.slf4j.Slf4j;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Slf4j
public class JsonTypeHandler<T extends Object> implements TypeHandler<T> {

    private Class<T> clazz;
    private static final SerializerFeature[] serializerFeatures;
    static {
        serializerFeatures = new SerializerFeature[]{
            SerializerFeature.WriteMapNullValue,
            SerializerFeature.WriteNullListAsEmpty
        };
    }
    public  JsonTypeHandler() {}
    public  JsonTypeHandler(Class<T> clazz) {
        if (clazz == null) {
            throw new IllegalArgumentException("Type argument cannot be null");
        }
        this.clazz = clazz;
    }
    private String toJson(T object){
        try {
            return JSONObject.toJSONString(object,serializerFeatures);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
    private T toObject(String content, Class<?> clazz) {
        if (content != null && !content.isEmpty()) {
            try {
                return (T) JSONObject.parseObject(content, clazz);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, T object, JdbcType jdbcType)
        throws SQLException {
        preparedStatement.setString(i,this.toJson(object));
    }

    @Override
    public T getResult(ResultSet resultSet, String s) throws SQLException {
        return this.toObject(resultSet.getString(s),this.clazz);
    }

    @Override
    public T getResult(ResultSet resultSet, int i) throws SQLException {
        return this.toObject(resultSet.getString(i),this.clazz);
    }

    @Override
    public T getResult(CallableStatement callableStatement, int i) throws SQLException {
        return this.toObject(callableStatement.getString(i),this.clazz);
    }
}




import java.util.List;

public class ListMap2JsonTypeHandler extends JsonTypeHandler<List> {
    public ListMap2JsonTypeHandler() {
        super(List.class);
    }
}


import java.util.Map;

public class Map2JsonTypeHandler extends JsonTypeHandler<Map> {
    public Map2JsonTypeHandler() {
        super(Map.class);
    }
}

6.角色自动转化数据库数值

import com.*.enums.UserRoleEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.*;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @date 2021/9/10 10:24
 * @version 1.0
 */
@Slf4j
public class RoleParamMappingTypeHandler extends BaseTypeHandler<String> {
    private static final Map<String,String> roleMappingInput=new HashMap<>(4);
    private static final Map<String,String> roleMappingOutput=new HashMap<>(4);
    static {
        for (UserRoleEnum userRoleEnum : UserRoleEnum.values()) {
            roleMappingInput.put(userRoleEnum.getRoleCode(),userRoleEnum.getPsRoleCode());


            roleMappingOutput.put(userRoleEnum.getPsRoleCode(),userRoleEnum.getRoleCode());
        }

    }
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType)
        throws SQLException {
        log.info("员工角色:{}---------------setNonNullParameter转化后：{}",parameter,roleMappingInput.get(parameter));
        ps.setString(i, roleMappingInput.get(parameter));
    }

    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {

        log.info("员工角色:{}---------------getNullableResult：{}",rs.getString(columnName),roleMappingOutput.get(rs.getString(columnName)));
        return roleMappingOutput.get(rs.getString(columnName));
    }

    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        // 根据下标获取 String 类型的参数的时候调用
        log.info("员工角色:{}------------------------------getNullableResult2："+columnIndex);
        return  roleMappingOutput.get(rs.getString(columnIndex));
    }

    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        log.info("员工角色:{}------------------------------getNullableResult3：");
        return  roleMappingOutput.get(cs.getString(columnIndex));
    }

}