如何用mybatisMapjson\u arrayagg结果

xa9qqrwz  于 2021-06-27  发布在  Java
关注(0)|答案(2)|浏览(790)

我无法绘制 JSON_ARRAYAGG function .
mybatisMap器:

<mapper namespace="com.test.mapper.UserEntityMapper">
    <resultMap id="userMap" type="UserVO">
        <id property="id" column="id" />
        <result property="username" column="username"/>
        <collection property="pictures" ofType="PictureVO">
            <id property="id" column="id" />
            <result property="location" column="location" />
        </collection>
    </resultMap>
    <select id="findUserById" parameterType="map" resultMap="userMap">
        SELECT
        users.id,
        users.username,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                "id",
                pic.id,
                "location",
                pic.location
            ) 
        ) AS pictures
        FROM
            users
            LEFT JOIN pictures pic ON users.id = pic.user_id 
        WHERE
            users.id = 1
    </select>
</mapper>

用户VO模型:

@Getter
@Setter
@NoArgsConstructor
public class UserVO {
    private Long id;
    private String username;
    private List<PictureVO> pictures;
}

picturevo型号:

@Getter
@Setter
@NoArgsConstructor
public class PictureVO {
    private Long id;
    private String location;
}

调用api的结果:

{
    "id": 1,
    "username": "connor",
    "pictures": [
        {
            "id": 1,
            "location": null
        }
    ]
}

如你所见 pictures 只有一个 id 可以正确Map。假设有两个结果 pictures ,如果我直接执行sql,这里只返回1。

lnxxn5zx

lnxxn5zx1#

您应该设置方法的(“finduserbyid”)返回类型列表,mybatis将自动完成。尝试在mapper接口中写入:

List<UserVo> findUserById();
bogh5gae

bogh5gae2#

mybatis返回java对象而不是json,因此可能是您的“api”将java对象转换为json。
如果我是对的,你只需要做正常的mybatis图。
像这样:

<resultMap id="userMap" type="UserVO">
  <id property="id" column="id" />
  <result property="username" column="username"/>
  <collection property="pictures" ofType="PictureVO">
    <id property="id" column="picture_id" />
    <result property="location" column="location" />
  </collection>
</resultMap>

<select id="findUserById" parameterType="map" resultMap="userMap">
  SELECT
    users.id,
    users.username,
    pic.id picture_id,
    pic.location
  FROM
    users
    LEFT JOIN pictures pic ON users.id = pic.user_id
  WHERE
    users.id = 1
</select>

请注意 pic.id 需要别名来区分列 users.id 在结果集中。

相关问题