MyBatis学习笔记9 联合查询（一）

不念不忘少年蓝@ 2024-03-27 09:24 63阅读 0赞

##  ##

#### 1、一对一关联 ####

> 以客户表和客户详情表为例，客户表里存放基本信息，客户详情表存放详细信息。

1.1、提出需求

> 根据客户id，查询客户信息并查到详情表对应的信息。

*  表字段以及对应关系

![36c8febb329e70133477593d9e07c6cf.png][]

1.2、创建实体类

*  首先定义客户实体类

> client表中有一个client\_info\_id字段，所以在Client类中定义一个clientInfo属性，用于维护Client和ClientInfo之间的一对一关系，将联合查询的结果中的客户详情信息，映射的该属性当中。

public class Client {
        private int id;
        private String client_name;
        private String client_age;
        private String client_company;
        //客户详情
        private ClientInfo clientInfo;
        //getter and setter...
    }

*  定义客户详情实体类

public class ClientInfo {
        private int id;
        private String client_hobby;
        private String client_address;
        //getter and setter...
    }

*  定义Dao层接口

public interface ClientDao {
        Client queryClientAndInfo(int id);
    }

*  mapper映射文件

<mapper namespace="com.rg.dao.ClientDao">
        
        <select id="queryClientAndInfo" resultMap="client_info_map">
            SELECT c.client_name, c.client_age, c.client_company, c.client_info_id,ci.id, ci.client_hobby, client_address
            FROM client as c
                     INNER JOIN client_info as ci on c.client_info_id = ci.id
            where c.id = #{id}
        </select>
        <resultMap id="client_info_map" type="com.rg.bean.Client">
            <id property="id" column="id"/>
            <result property="client_name" column="client_name"/>
            <result property="client_age" column="client_age"/>
            <result property="client_company" column="client_company"/>
            
            <association property="clientInfo" javaType="com.rg.bean.ClientInfo">
                <id property="id" column="id"/>
                <result property="client_address" column="client_address"/>
                <result property="client_hobby" column="client_hobby"/>
            </association>
        </resultMap>
        
        <select id="queryClientAndInfo" resultMap="client_Info_map">
            select * from client where id = #{id}
        </select>
        
        <resultMap id="client_Info_map" type="com.rg.bean.Client">
            
            <id property="id" column="id"/>
            
            <result property="client_name" column="client_name"/>
            <result property="client_age" column="client_age"/>
            <result property="client_company" column="client_company"/>
            
            <association property="clientInfo" column="client_info_id" javaType="com.rg.bean.ClientInfo" select="selectClientInfo"/>
        </resultMap>
        <select id="selectClientInfo" resultType="com.rg.bean.ClientInfo">
            select * from client_info where id = #{id}
        </select>
    </mapper>

*  测试

@Test
    public void test01() {
        Client client = clientDao.queryClientAndInfo(1);
        System.out.println(client);
    }

*  结果

Client{id=1, client_name='王经理', client_age='18', client_company='阿里巴巴', clientInfo=ClientInfo{id=1, client_hobby='打篮球', client_address='上海'}}

> 个人感觉第二种方式更简洁，首先它不需要写很长的联合语句，其次结构更加简洁明了。

[36c8febb329e70133477593d9e07c6cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/3d49c9a26a4d43cbb47b2b4a9c2ce255.png