Mybatis 映射问题及解决方法

问题描述

在SpringBoot项目中,集成的MybatisPlus配置了驼峰转换

@Bean("sqlSessionFactory")
public SqlSessionFactory sqlSessionFactory() throws Exception {
    MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
    sqlSessionFactory.setDataSource(multipleDataSource(db1(), db2()));
    MybatisConfiguration configuration = new MybatisConfiguration();
    configuration.setJdbcTypeForNull(JdbcType.NULL);
    configuration.setMapUnderscoreToCamelCase(true);
    configuration.setCacheEnabled(false);
    //扫描 mapper 路径 配置在yml文件中的不生效
    ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    Resource[] resource = resolver.getResources("classpath:mapper/**/*.xml");
    sqlSessionFactory.setMapperLocations(resource);
    sqlSessionFactory.setConfiguration(configuration);
    //PerformanceInterceptor(),OptimisticLockerInterceptor()
    //添加分页功能
    sqlSessionFactory.setPlugins(new Interceptor[]{
        paginationInterceptor()
        });
    sqlSessionFactory.setGlobalConfig(globalConfiguration());
    return sqlSessionFactory.getObject();
}

但是在POJO实体中,存在带_的属性

@Data
public class SPDMessage {

    /** 出入库单号 */
    @JSONField(name = "INOUTBILLNO")
    private String inoutbillno;

    /** 年月 */
    @JSONField(name = "YEAR_MONTH")
    private String year_month;

}

在mybatis中的xml中执行查询时,查询结果就会映射不到实体上去

解决方法

取消Type返回类型,利用resultMap进行字段和属性的映射关系配置

<?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.viewhigh.oes.mapper.oracle.MateWhrMainMapper">

    <resultMap id="BaseResultMap" type="com.viewhigh.oes.pojo.SPDMessage">
        <result column="INOUTBILLNO" jdbcType="VARCHAR" property="inoutbillno" />
        <result column="YEAR_MONTH" jdbcType="VARCHAR" property="year_month" />
        <result column="MAKE_DATE" jdbcType="VARCHAR" property="make_date" />
        <result column="IN_DEPT" jdbcType="VARCHAR" property="in_dept" />
        <result column="STORE_CODE" jdbcType="VARCHAR" property="store_code" />
        <result column="VEN_CODE" jdbcType="VARCHAR" property="ven_code" />
        <result column="VEN_NAME" jdbcType="VARCHAR" property="ven_name" />
        <result column="INV_CODE" jdbcType="VARCHAR" property="inv_code" />
        <result column="INV_AMOUNT" jdbcType="VARCHAR" property="inv_amount" />
        <result column="INV_PRICE" jdbcType="VARCHAR" property="inv_price" />
        <result column="INV_MONEY" jdbcType="VARCHAR" property="inv_money" />
        <result column="BUYERCODE" jdbcType="VARCHAR" property="buyercode" />
        <result column="APPLYCODE" jdbcType="VARCHAR" property="applycode" />
        <result column="SETTLESDNO" jdbcType="VARCHAR" property="settlesdno" />
        <result column="OWNERID" jdbcType="VARCHAR" property="ownerid" />

    </resultMap>

    <select id="queryWhrMainList" resultMap="BaseResultMap">
        SELECT b.DEPOT_CODE INOUTBILLNO,b.ACCT_YEAR || b.ACCT_MONTH YEAR_MONTH,to_char(b.IN_OUT_DATE,'yyyy-mm-dd hh24:mi:ss') MAKE_DATE,d.DEPT_CODE IN_DEPT,e.STORE_CODE,
               c.VEN_CODE,c.VEN_NAME,f.INV_CODE INV_CODE,a.AMOUNT INV_AMOUNT,a.PRICE INV_PRICE,a.AMOUNT_MONEY INV_MONEY,g.EMP_CODE BUYERCODE,h.EMP_CODE APPLYCODE,
               k.PAY_BILL_NO SETTLESDNO,b.COMP_CODE OWNERID
        FROM MATE_WHR_DETAIL a
            LEFT JOIN MATE_WHR_MAIN b on a.DEPOT_ID = b.DEPOT_ID
            LEFT JOIN SYS_VENDOR c on c.VEN_ID = a.VEN_ID
            LEFT JOIN SYS_DEPT d on d.DEPT_ID = a.DEPT_ID
            LEFT JOIN SYS_STORE_DICT e on e.STORE_ID = b.IN_STORE_ID
            LEFT JOIN SYS_MATE_DICT f on f.MATE_ID = a.INV_ID
            LEFT JOIN SYS_EMP g on g.EMP_ID = b.EMP_ID
            LEFT JOIN SYS_EMP h on TO_CHAR(h.EMP_ID) = b.EMP_CODE
            LEFT JOIN PURC_BILL_DETAIL i on i.IOW_DETAILID = a.IOW_B_ID
            LEFT JOIN PURC_PAY_DETAIL j on j.BILL_DETAIL_ID = i.BILL_DETAIL_ID
            LEFT JOIN PURC_PAY_MAIN k on k.PAY_BILL_ID = j.PAY_BILL_ID
        WHERE b.IS_IN_OUT = 1 and k.PAY_BILL_NO = #{settlesdno}
    </select>
</mapper>