Mybatis resultMap（使用子查询）

最新推荐文章于 2025-04-11 21:25:51 发布

杨丑丑

最新推荐文章于 2025-04-11 21:25:51 发布

阅读量2.2k

点赞数

分类专栏： Mybatis MySQL 文章标签： java mysql

本文链接：https://blog.youkuaiyun.com/weixin_43883033/article/details/108201588

版权

Mybatis 同时被 2 个专栏收录

4 篇文章

订阅专栏

MySQL

3 篇文章

订阅专栏

本文探讨了Mybatis中resultMap如何处理子查询的情况，特别指出子查询返回的集合字段属性必须直接对应数据库字段，别名可能导致错误。同时，展示了实现数据库子查询子集的XML配置示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mybatis resultMap（使用子查询）

mybatis返回查询返回字段问题（子查询返回的集合字段属性必须是数据库的字段，别名会报错）

  <resultMap id="NoteDetailInfoMap" type="com.huanshuo.wisdomportal.vo.NoteDetailInfo">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="user_id" property="userId" jdbcType="INTEGER" />
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
        <result column="publish_time" property="publishTime" jdbcType="TIMESTAMP" />
        <result column="user_space_baseinfo_id" property="userSpaceBaseinfoId" jdbcType="INTEGER" />
        <result column="content" property="content" jdbcType="LONGVARCHAR" />
        <result column="is_delete" property="isDelete" jdbcType="BIT" />
        <result column="tenant_id" property="tenantId" jdbcType="VARCHAR" />
        <result column="title" property="title" jdbcType="VARCHAR" />
        <result column="is_top" property="isTop" jdbcType="BIT" />
        <result column="public_area" property="publicArea" jdbcType="BIT" />
        <result column="status" property="status" jdbcType="BIT" />
        <result column="visitors" property="visitors" jdbcType="INTEGER" />
        <result column="praises" property="praises" jdbcType="INTEGER" />
        <result column="comments" property="comments" jdbcType="INTEGER" />
        <result column="classification" property="classification" jdbcType="VARCHAR" />
        <result column="collectionCount" property="collectionCount" jdbcType="INTEGER" />
        <result column="user_space_note_id" property="userSpaceNoteId" jdbcType="INTEGER" />
        <collection property="noteFileEntities" ofType="com.huanshuo.wisdomportal.entity.UserSpaceNoteFileEntity">
            <result column="name" property="name" jdbcType="VARCHAR" />
            <result column="link" property="link" jdbcType="VARCHAR" />
            <result column="size" property="size" jdbcType="INTEGER" />
            <result column="suffix" property="suffix" jdbcType="VARCHAR" />
            <result column="img" property="img" jdbcType="VARCHAR" />
            <result column="file_id" property="fileId" jdbcType="VARCHAR" />
            <result column="createtime" property="createtime" jdbcType="TIMESTAMP" />
        </collection>
    </resultMap>

<select id="noteCollectionList" resultMap="NoteDetailInfoMap">
        SELECT
            z.id ,
            z.create_time ,
            z.is_delete ,
            z.classification,
            z.collectionCount,
            z.comments,
            z.praises,
            z.publish_time,
            z.visitors
        FROM(
            SELECT
                    **zusn. id,
                    zc. user_id,
                    zc.user_space_note_id,
                    zc. is_delete,
                    zusn.create_time,
                    zusn.is_top**
                FROM
                  z_user_sapace_note_collection zc
                LEFT JOIN z_user_space_note zusn ON zusn.id= zc.user_space_note_id
                WHERE zc.tenant_id=#{tenantId}
                AND zusn.tenant_id=#{tenantId}
                AND zusn.is_delete=0
                AND zc.is_delete=0
                AND zc.user_id=#{userId}
                <if test="startIndex!= null and startSize!=null">
                    limit #{startIndex}, #{startSize}
                </if>
            ) z
        LEFT JOIN z_user_space_note_file zusnf
        ON z.user_space_note_id = zusnf.user_space_note_id
     ORDER BY z.create_time DESC;
    </select>

2.返回数据库子查询的子集


{
    place: '动物园',
    allCounts: 300,
    perDay : [
        {
            date: '2018-11-11',
            counts: 100
        },
        {
            date: '2018-11-12',
            counts: 200
        }
    ]
},
{
    place: '植物园',
    allCounts: 100,
    perDay : [
        {
            date: '2018-11-11',
            counts: 100
        }
    ]
},

xml文件如下：

<select id="pageResultMapSelect" parameterType="java.util.Map" resultMap="pageResultMap">
    select date,counts from t_广州 where place=#{place} group by date
</select>
<resultMap id="pageResultMap" resultType="java.util.Map">
    <result column="place" property="place" />
    <result column="allCounts" property="allCounts" />
    <collection property="perDay" javaType="java.util.List" ofType="java.util.Map" column="(place=place)" select="pageResultMapSelect"></collection>
</resultMap>
<select id="page" parameterType="java.util.Map" resultMap="pageResultMap">
    select place,sum(counts) as allCounts from t_广州 group by place
</select>