使用mybatis对查询数据进行分组

使用mybatis对查询数据进行分组

SELECT a.id AS a_id,a.name AS a_name,a.`desc` AS a_desc,a.op AS a_op,b.id AS b_id,b.priority AS b_priority,b.`content` AS b_content
FROM agent AS a 
LEFT JOIN (SELECT * FROM text_response WHERE intent_id IS NULL ) AS b 
ON a.id=b.`agent_id` WHERE a.id=39


a_id  a_name        a_desc    a_op    b_id  b_priority  b_content                                                              
39    股票应用      (NULL)    1       360    1          小库委屈，但小库不能说，上帝请让小库我更聪明吧  
39    股票应用      (NULL)    1       361    1          小库还要加强学习，主人加油训练小库                    
39    股票应用      (NULL)    1       362    1          小库还不能理解，但小库会努力学习新东西的           
39    股票应用      (NULL)    1       363    1          哎呀，小库不能理解，好好训练小库好不                 


public interface AgentDao {
    Agent selectAgentRelaById(Integer id);
    Agent selectById(Integer agent_id);
}



<?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.csf.ops.ai.dao.AgentDao">

    <resultMap type="com.csf.ops.ai.entity.Agent" id="agent">
        <id column="a_id" property="id" />
        <result column="a_name" property="name" />
        <result column="a_desc" property="desc" />
        <result column="a_op" property="op" />
        <!-- 描述集合属性 -->
        <collection property="responses" ofType="com.csf.ops.ai.entity.TextResponse">
            <id column="b_id" property="id" />
            <result column="b_priority" property="priority" />
            <result column="b_content" property="content" />
        </collection>
    </resultMap>
    
    <select id="selectAgentRelaById" parameterType="int" resultMap="agent">
        SELECT a.id AS a_id,a.name AS a_name,a.`desc` AS a_desc,a.op AS
        a_op,b.id AS b_id,b.priority AS b_priority,b.`content` AS b_content
        FROM agent AS a LEFT JOIN (SELECT * FROM text_response WHERE intent_id
        IS NULL ) AS b ON a.id=b.`agent_id` WHERE a.id=#{id}
    </select>

    <select id="selectById" parameterType="int" resultType="com.csf.ops.ai.entity.Agent">
        select * from agent where id=#{id}
    </select>
    
</mapper>


{
    "id": 39,
    "op": 1,
    "name": "股票应用",
    "responses": [
        {
            "id": 360,
            "priority": 1,
            "content": "小库委屈，但小库不能说，上帝请让小库我更聪明吧"
        },
        {
            "id": 361,
            "priority": 1,
            "content": "小库还要加强学习，主人加油训练小库"
        },
        {
            "id": 362,
            "priority": 1,
            "content": "小库还不能理解，但小库会努力学习新东西的"
        },
        {
            "id": 363,
            "priority": 1,
            "content": "哎呀，小库不能理解，好好训练小库好不"
        }
    ]
}

public class Agent implements Serializable{
    private Integer id;
    private Integer op;
    private String developer;
    private String name;
    private String desc;
    private List<TextResponse> responses;
}

public class TextResponse {
    private Integer id;
    private Integer priority;
    private String content;
    private Integer agent_id;
    private Integer intent_id;

}

 

转载于:https://www.cnblogs.com/xiaolei2017/p/7977102.html