mysql -- collection一对多查询

数据库表

角色组表：

CREATE TABLE `sso_character_group` (
  `group_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色组ID',
  `group_name` varchar(255) DEFAULT NULL COMMENT '角色组名称',
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

角色表：

CREATE TABLE `sso_character` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色ID',
  `name` varchar(255) DEFAULT NULL COMMENT '角色名称',
  `group_id` int(11) DEFAULT NULL COMMENT '所属角色组ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

一个角色组包含多个角色

 

两个POJO

//CharacterGroup.java
package cn.com.hiveview.entity.module.portal;
import lombok.Data;

@Data
public class CharacterCondition {
    private Integer id;
    private String name;
}


//Character.java
package cn.com.hiveview.entity.module.portal;

import lombok.Data;
import java.util.List;

@Data
public class CharacterGroupCondition {
    private Integer groupId;
    private String groupName;
    private List<CharacterCondition> characterList;
}

controller--mapper层都是直接调用下一层，此处省略

直接上mapper.xml

    <resultMap id="characterGroup" type="cn.com.hiveview.entity.module.portal.CharacterGroupCondition">
        <id column="group_id" property="groupId"/>
        <result column="group_name" property="groupName"/>
        <collection property="characterList" column="{groupId=group_id}" select="getCharacterList"/>
    </resultMap>

    <select id="getList" resultMap="characterGroup" parameterType="cn.com.hiveview.entity.module.portal.CharacterGroupCondition">
        SELECT group_id,group_name FROM sso_character_group
    </select>

    <select id="getCharacterList" resultType="cn.com.hiveview.entity.module.portal.CharacterCondition">
        select id,name from sso_character where group_id=#{groupId}
    </select>

 

结果展示