书接上文，当多表查询，为了一个查询出全部，使用了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 orgNameFROM group g where 1=1<if test="searchWords != null and searchWords != ''">AND g.group_name LIKE CONCAT('%', #{searchWords}, '%')</if>GROUP BY g.group_idorder by g.group_id asc limit #{page},#{size}</select>

重点：

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

@OneToMany
@JoinColumn(name = "group_id")

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