MyBatis(五)——关联映射

1.一对一

在任意一方引入对方主键作为外键

例如有IdCard(id,code)和Person(id,name,id_card)，IdCard.id和Person.id_card关联

通过PersonMapper查询Person，其中又涉及到实体类IdCard，这就是所谓的一对一关系映射。

package com.mybatis.bean;

public class IdCard {

    private Integer id;
    private String code;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    @Override
    public String toString() {
        return "IdCard [id=" + id + ", code=" + code + "]";
    }
}

/*
create table tb_idcard(
    id int primary key auto_increment,
    code varchar(18)
);

insert into tb_idcard(code) values('44422211111102623X');
insert into tb_idcard(code) values('444222111111023141');





*/

package com.mybatis.bean;

public class Person {

    private Integer id;
    private String name;
    private IdCard card;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public IdCard getCard() {
        return card;
    }
    public void setCard(IdCard card) {
        this.card = card;
    }
    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", card=" + card + "]";
    }
    

}
/*
create table tb_person(
id int primary key auto_increment,
name varchar(32),
card_id int unique,
foreign key(card_id) references tb_idcard(id)
);

insert into tb_person(id,name,card_id) values(null,'守林鸟',2);
insert into tb_person values(null,'灰太狼',1); 

*/

(1)嵌套查询，IdCardMapper.xml和PersonMapper.xml的配置如下：

    <!-- public IdCard findCodeById(Map<String, Object> map); -->
    <select id="findCodeById" resultType="com.mybatis.bean.IdCard">
        select * from tb_idcard where id=#{id}
    </select>

    <!-- public Person findPersonById(Map<String, Object> map); -->
    <select id="findPersonById" parameterType="Integer" resultType="IdCardWithPersonResult">
        select * from tb_person where id=#{id}
    </select>
    
    <resultMap type="com.mybatis.bean.Person" id="IdCardWithPersonResult">
        <id property="id" column="id"></id><!-- 主键 -->
        <result property="name" column="name"/>
        <association property="card" column="card_id" javaType="com.mybatis.bean.IdCard"
        select="com.mybatis.mapper.IdCardMapper.findCodeById"/>
    </resultMap>

Person封装了IdCard，通过<resultMap>的子元素<association>封装另一条语句的查询语句，其中用select属性连接到另一条SQL语句，即运行了多条SQL语句。

(2)嵌套结果

只执行一条SQL语句，就是传统的MySQL中的查询语句，用<association>将一些属性弄成映射关系。

    <select id="findPersonById" resultMap="IdCardResult">
        <!-- select * from tb_person where id=#{id}  -->
        select p.*,idcard.code 
        from tb_person p,tb_idcard idcard 
        where p.card_id=idcard.id and p.id=#{id} 
    </select>

    <resultMap type="com.mybatis.bean.Person" id="IdCardResult">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="card" javaType="com.mybatis.bean.IdCard">
            <id property="id" column="card_id"></id>
            <result property="code" column="code"/>
        </association>
    </resultMap>

这一条语句[select p.*,idcard.code from tb_person p,tb_idcard idcard where p.card_id=idcard.id and p.id=1]和[select p.id,p.name,p.card_id,i.code from tb_person p left join tb_idcard i on p.card_id=i.id where p.id=1]效果一样。

显然，对于大型数据集合，嵌套查询会导致成百上千关联的SQL语句被执行，从而极大消耗了数据库性能并且降低查询效率，显然嵌套结果更加主流，不难看出对SQL语句的掌握要求比较高。

 

2.一对多

例如一个用户有多个订单，查用户的时候顺便把订单查出来，用<resultMap>的子元素<collection>实现，其中有一个ofType属性，这个与一对一查询的javaType属性对应。

package com.mybatis.bean;

import java.util.List;

public class User {

    private Integer id;
    private String username;
    private List<Order> orderList;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public List<Order> getOrderList() {
        return orderList;
    }
    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", orderList=" + orderList + "]";
    }
}
/*
create table tb_user(
id int primary key auto_increment,
username varchar(32)
);

insert into tb_user values('1','灰太狼');
insert into tb_user values('2','守林鸟');
insert into tb_user values(null,'霸王别鸡');

*/

package com.mybatis.bean;

public class Order {

    private Integer id;
    private String number;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
    @Override
    public String toString() {
        return "Order [id=" + id + ", number=" + number + "]";
    }

}

/*
create table tb_order(
id int primary key auto_increment,
number varchar(32) not null,
user_id int not null,
foreign key(user_id) references tb_user(id)
);
insert into tb_order values('1','10086','1');
insert into tb_order values(null,'10087',3);
insert into tb_order values(null,'10088',2);

*/

    <!-- public User findUserWithOrders(HashMap<String, Object> map); -->
    <select id="findUserWithOrders" resultMap="UserWithOrderResult">
        select u.*,o.id as order_id,o.number from tb_user u,tb_order o
        where u.id=o.user_id and u.id=#{id}
    </select>
    
    <resultMap type="com.mybatis.bean.User" id="UserWithOrderResult">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <!-- 一对多关系映射 -->
        <collection property="orderList" ofType="com.mybatis.bean.Order">
            <id property="id" column="order_id"></id>
            <result property="number" column="number"/>
        </collection>
    </resultMap>

 

3.多对多

通常用一个中间表来维护，例如有订单表tb_order(id,number,user_id),中间表tb_orderitem(id,order_id,product_id),商品表tb_product(id,name),中间表的order_id参照订单表的id，product_id参照商品表的id。

在上面的表的基础上再创建tb_product和tb_orderitem。

create table tb_product(
id int primary key auto_increment,
name varchar(32)
);

insert into tb_product values('1','Java从入门到入土');
insert into tb_product values(null,'C++从入门到放弃');
insert into tb_product values(null,'MySQL从删库到跑路');
insert into tb_product values(null,'ACM竞赛入门指导');



create table tb_orderitem(
id int primary key auto_increment,
order_id int,
product_id int,
foreign key(order_id) references tb_order(id),
foreign key(product_id) references tb_product(id)
);

insert into tb_orderitem values('1','1','1');
insert into tb_orderitem values('2','1','3');
insert into tb_orderitem values('3','3','3');

配置多对多的关联映射，也是用<collection>，这里用的是嵌套结果的方式。

<mapper namespace="com.mybatis.mapper.OrderMapper">
    <select id="findOrdersWithProduct" resultMap="OrderWithProductResult">
        select o.*,p.id as pid,p.name from tb_order o,tb_product p,tb_orderitem oi
        where oi.order_id=o.id and oi.product_id=p.id and o.id=#{id}
    </select>
    
    <resultMap type="Order" id="OrderWithProductResult">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
        <!-- 多对多关联映射，嵌套结果，collection -->
        <collection property="productList" ofType="Product">
            <id property="id" column="pid"/>
            <result property="name" column="name"/>
        </collection>
    </resultMap>
</mapper>

 

 

---

 参考&引用

《JavaEE企业级应用开发教程》——黑马程序员