Mybatis 一对多查询 collection中包含下划线的字段为null的解决办法

背景情况

SpringBoot框架
resultMap 中其他包含下划线字段取值正常，仅仅collection嵌套查询出现问题

<resultMap id="postInfoResultMap" type="com.app.pojo.Post">
        <id column="post_id" property="postId"/>
        <result column="user_id" property="userId"/>
        <result column="content" property="content"/>
        <result column="posting_time" property="postingTime"/>
        <result column="like_number" property="likeNumber"/>
        <result column="comment_number" property="commentNumber"/>
        <result column="view_number" property="viewNumber"/>
        <collection property="postPictureList" ofType="com.app.pojo.PostPicture"
                    column="post_id"
                    select="listPostPictureByPostId">
            <id column="picture_id" property="pictureId"/>
            <result column="address" property="address"/>
            <result column="post_id" property="postId"/>
        </collection>
</resultMap>

使用到该映射的语句

<select id="getPostInfoByPostId" resultMap="postInfoResultMap">
	SELECT
        post_id,
        user_id,
        content,
        posting_time,
        like_number,
        comment_number,
        view_number
    FROM
        post
    WHERE
        post_id = #{postId};
</select>
<select id="listPostPictureByPostId" resultType="com.app.pojo.PostPicture">
    SELECT
        picture_id,
        address,
        post_id
    FROM
        post_picture
    WHERE
        post_id = #{postId}
    ORDER BY
        picture_id;
</select>

Post(
	postId=111, 
	userId=120170, 
	content=99新Y7000 卡, 
	postingTime=2020-02-21 17:00:58, 
	likeNumber=1, 
	commentNumber=6, 
	viewNumber=118, 
	postPictureList=[
		PostPicture(pictureId=null, address=https://此处是地址0, postId=null), 
		PostPicture(pictureId=null, address=https://此处是地址1, postId=null),
		PostPicture(pictureId=null, address=https://此处是地址2, postId=null)
	]
)

尝试解决

反复测试发现一些规律：
仅仅collection 中的有下划线的字段值为 null

• 将column换成不带下划线的名字（另起一个），sql语句里也添加对应相同的别名❌
• 将column换成实体类中的字段名，不给sql语句别名❌
• sql查询语句起个和实体类的字段相似的别名（即不强调大小写），column改不改都行 ✅

<select id="listPostPictureByPostId" resultType="com.app.pojo.PostPicture">
    SELECT
        picture_id as pictureid,
        address,
        post_id as postId
    FROM
        post_picture
    WHERE
        post_id = #{postId}
    ORDER BY
        picture_id;
</select>

就可以成功得到数据

Post(
	postId=111, 
	userId=120170, 
	content=新7000 卡, 
	postingTime=2020-02-21 17:00:58, 
	likeNumber=1, 
	commentNumber=6, 
	viewNumber=118, 
	postPictureList=[
		PostPicture(pictureId=12, address=https://此处是地址0, postId=111), 
		PostPicture(pictureId=13, address=https://此处是地址1, postId=111),
		PostPicture(pictureId=14, address=https://此处是地址2, postId=111)
	]
)

为什么会这样，还没完全搞懂。先发个解决方案记录一下。

解决方案总结

1. 配置驼峰命名和数据库带下划线字段的自动转换（sql无别名）

• application.yml

  mybatis:
    configuration:
      map-underscore-to-camel-case: true
  

• application.properties

  mybatis.configuration.map-underscore-to-camel-case=true
  

2. 给 sql 语句中加上别名

上文的尝试解决操作