tk-mybatis 通用mapper 使用xml扩展sql小坑

为了减少mybatis手写xml的代码量和出错率，我使用了tk-mybatis通用mapper。具体过程可以参考SpringBoot集成tk.mybatis

然鹅，通用mapper只是实现了常用SQL操作，对于一些复杂操作或者效率要求高的操作实现起来却很不方便，所以想着可以结合xml来扩展这些复杂SQL。

根据https://www.jianshu.com/p/50449dde7f2b配置好了扩展xml。但是我不想在xml中定义繁琐的resultMap来定义数据库字段和javabean字段的映射关系，这样不仅修改起来繁琐，而且很容易出错，我想使用JavaBean或者数据库实体类Entity来定义resultMap，再结合单元测试，完美。

如下是表sql

CREATE TABLE `subscribe` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) default NULL COMMENT '订阅者用户id',
  `type` INT default NULL COMMENT '订阅类型',
  `subordinate` varchar(64) default NULL COMMENT '所属',
  `trigger_type` INT DEFAULT NULL COMMENT '触发条件类型',
  `trigger_value` INT DEFAULT NULL COMMENT '触发值',
  `create_time` DATETIME default CURRENT_TIMESTAMP COMMENT '创建时间',
  `publish_time` DATETIME DEFAULT NULL COMMENT '发布时间',
  `last_publish_time` DATETIME DEFAULT NULL COMMENT '上一次发布时间',
  `publish_frequency` INT DEFAULT NULL COMMENT '发布频率',
  `delete_time` DATETIME default null COMMENT '删除时间',
  `is_deleted` BOOLEAN default false COMMENT '删除',

  PRIMARY KEY (`id`),
  INDEX subscribe_user_id(user_id),
  INDEX subscribe_publish_time(publish_time)
) ENGINE=InnoDB;

insert into subscribe (user_id, type, subordinate, trigger_type, trigger_value, publish_time, publish_frequency)
              values (1, 0, 110101, 0, 0, '2019-05-27 07:50:00', 0);
insert into subscribe (user_id, type, subordinate, trigger_type, trigger_value, publish_time, publish_frequency)
values (1, 0, 110101, 0, 1, '2019-05-27 07:50:00', 0);

如下是JavaBean

package com.eee.subscribe.domain;

import lombok.Builder;
import lombok.Data;
import tk.mybatis.mapper.annotation.KeySql;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.util.Date;

@Data
@Builder
@Table(name = "subscribe")
public class Subscribe implements Serializable {
    @Id
    @KeySql(useGeneratedKeys = true)
    private Long id;

    private Long userId;

    private SubscribeType type;

    private String subordinate;

    private TriggerType triggerType;

    private TriggerValue triggerValue;

    private Date createTime;

    private Date publishTime;

    private Date lastPublishTime;

    private PublishFrequency publishFrequency;

    private Date deleteTime;

    @Column(name = "is_deleted")
    private boolean deleted;
}

如下是mapper

@Mapper
@Component
interface SubscribeMapper extends BaseMapper<Subscribe, Long> {
    Subscribe selectById(Long id);
}

如下是mapper.xml

<?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.eee.subscribe.dao.SubscribeMapper">

    <select
            id="selectById"
            parameterType="java.lang.Long"
            resultType="com.eee.subscribe.domain.Subscribe">
        SELECT * FROM
            subscribe
        WHERE id = #{id};
    </select>
</mapper>

但是执行测试报错了：

org.springframework.dao.DataIntegrityViolationException: Error attempting to get column 'SUBORDINATE' from result set.  Cause: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "nqghMS" [22018-199]
; Data conversion error converting "nqghMS" [22018-199]; nested exception is org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "nqghMS" [22018-199]

后来我又打开了mapUnderscoreToCamelCase。但报错还是依旧。我甚至怀疑是mapUnderscoreToCamelCase没有生效，然鹅打开SpringBoot监控后发现此选项确实打开了。

mybatis.configuration.mapUnderscoreToCamelCase=true

最终发现是因为框架内映射代码选择字段和字段所属类时竟然有错误。如下图框架代码所示，取Subscribe表的type字段时，columnName是type，但parameterType并不是对应的SubscribeType类型，而是String类型，导致选择了错误的typeHandler，最后解析值错误了......

不知道这是为啥，但解决方法被我猜了了，就是实体映射JavaBean类的字段顺序要和建表的字段顺序相同，而且mapUnderscoreToCamelCase开不开启也没影响。

很奇怪的一个bug......