【mybatis】一对多查询

原创已于 2025-01-23 14:48:35 修改 · 306 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mybatis

于 2025-01-23 14:46:58 首次发布

书接上文，当多表查询，为了一个查询出全部，使用了GROUP_CONCAT和json_object函数，但是当数据量过大时，需要更改mysql的数据库配置，调大group_concat_max_len配置参数。

但是项目中可能存在线上数据库不让重启，或者是线上数据库参数不让动的情况

使用以下方法实现，一次查询，一对多

类结构：类Group和类GroupUser

表：group、group_user_ref、user

Group.java

@Entity
@Table(name = "group")
@Data
public class Group {

    @Id
    @Column(name = "group_id")
    private int groupId;

    ……其他字段

    @OneToMany
    @JoinColumn(name = "group_id")
    private List<GroupUser> userInfo;


}

GroupUser .java

@Entity
@Data
public class GroupUser {
    @Id
    @Column(name = "user_id")
    private String userId;
    @Column(name = "LOGIN_ID")
    private String loginId;
    @Column(name = "USER_NAME")
    private String userName;
    @Column(name = "MOBILE")
    private String mobile;
    @Column(name = "ORG_IDS")
    private String orgIds;
}

GroupMapper.java

    List<Group>  getAllGroups(@Param("page")int page,@Param("size")int size,@Param("searchWords")String searchWords);

GroupMapper.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.mapper.GroupMapper">
  <resultMap id="BaseResultMap" type="com.bean.Group">
        <id property="groupId" column="group_id"/>
        <--其他字段-->
        <collection property="userInfo" column="group_id"
                    select="com.mapper.GroupMapper.selectUserByGroupId">
        </collection>
    </resultMap>

 <select id="selectUserByGroupId" parameterType="java.lang.Integer" resultType="com.bean.GroupUser">
    select u.user_id,u.LOGIN_ID, u.USER_NAME, u.MOBILE, u.ORG_IDS 
    FROM group_user_ref r 
    left join user u on r.user_id = u.user_id 
    WHERE group_id = #{groupId}
</select>

 <select id="getAllGroups" resultMap="BaseResultMap">
        SELECT g.*,org.ORG_NAME as orgName
        FROM group g 
        where 1=1
        <if test="searchWords != null and searchWords != ''">
            AND g.group_name LIKE CONCAT('%', #{searchWords}, '%')
        </if>
        GROUP BY g.group_id
        order by g.group_id asc limit #{page},#{size}
    </select>

重点：

1.Group类中，一对多字段注解

@OneToMany
@JoinColumn(name = "group_id")

2.Mapper中使用collection标签，使用select查询，通过group_id关联，一次查询出关联表对应user和user信息