MyBatis实现关系映射

关联关系

关系型数据库中的关联关系

在关系型数据库中，多表之间存在着三种关联关系，分别为一对一、一对多、多对多。

三种关联关系的具体说明如下：

• 一对一：在任意一方引入对方主键作为外键。
• 一对多：在多的一方，添加一的一方的主键作为外键。
• 多对多：产生中间关系表，引入两张表的主键作为外键，两个主键成为联合主键或者使用新的字段作为主键。

一对一

情景描述：一个用户对应一个身份证ID，一个身份证ID确定一个用户。

数据库设计

# 用户身份证表
create table tb_idcard(
	id int PRIMARY key auto_increment,
	code varchar(18)
)
insert into tb_idcard(code) values("152221198711020624");
insert into tb_idcard(code) values("152221198711020317");

# 客户表
create table tb_person(
	id int primary key auto_increment,
	name varchar(32),
	age int,
	sex varchar(8),
	card_id int unique
)
insert into tb_person(name,age,sex,card_id) values("Rose",29,'女',1);
insert into tb_person(name,age,sex,card_id) values("Tom",27,'男',2);

# 嵌套查询一对一
# 执行下列语句可以实现通过用户ID查询出用户信息及身份证信息
select p.*,card.code from tb_person p,tb_idcard card where p.card_id = card.id and p.id = 1

实体类

根据数据库表定义两个实体类：IdCard、Person。

IdCard

public class IdCard {
    private Integer id;
    private String code;

    // 省略setter、getter、toString
}

Person

public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    // 身份证对象
    private IdCard card;

    // 省略setter、getter、toString
}

由于我们需要查询出用户信息及身份证信息，所以需要在Person对象中放置IdCard对象。

Mapper接口类+映射文件实现一对一查询

省略同名接口类IdCardMapper.java、PersonMapper.java

IdCardMapper.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.example.dao.IdCardMapper">
    <select id="findCodeById" parameterType="Integer" resultType="IdCard">
        select * from tb_idcard where id = #{id}
    </select>
</mapper>

PersonMapper.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.example.dao.PersonMapper">

    <!-- 嵌套查询 -->
    <select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
        select * from tb_person where id = #{id}
    </select>
    <resultMap id="IdCardWithPersonResult" type="Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <!-- 将查询到的数据库字段card_id用于执行select中的方法，得到IdCard对象 -->
        <association property="card" column="card_id" javaType="IdCard"
                     select="com.example.dao.IdCardMapper.findCodeById"/>
    </resultMap>

    <!-- 嵌套结果 -->
    <select id="findPersonByIdResult" parameterType="Integer" resultMap="IdCardWithPersonResult2">
        select p.*,card.code from tb_person as p,tb_idcard as card where p.card_id = card.id and p.id = #{id}
    </select>
    <resultMap id="IdCardWithPersonResult2" type="Person">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <!-- 将查询到的数据库字段与对象属性一一对应，得到IdCard对象 -->
        <association property="card" javaType="IdCard">
            <id property="id" column="card_id"/>
            <result property="code" column="code"/>
        </association>
    </resultMap>
</mapper>

在PersonMapper.xml中，通过嵌套查询(或嵌套结果)，将查询到的card_id字段用于执行IdCardMapper.xml中的findCodeById()方法，得到IdCard对象，再通过ResultMap将查询到的字段注入Person对象中，最后得到注入后的Person对象。

Mapper接口类+注解实现一对一查询

PersonMapper.java

@Component
@Mapper
public interface PersonMapper {
    // 一对一注解实现 -> 嵌套查询
    @Select("select * from tb_person where id = #{id}")
    @Results(id="test1",value={
            @Result(property = "id",column = "id"),
            @Result(property = "name",column = "name"),
            @Result(property = "sex",column = "sex"),
            @Result(property = "age",column = "age"),
            @Result(property = "card",column = "card_id",one = @One(select = "com.example.dao.IdCardMapper.findCodeById"))
    })
    Person findPersonById(int id);

    // 一对一注解实现 -> 嵌套结果
    @Select("select p.*,card.code from tb_person as p,tb_idcard as card where p.card_id = card.id and p.id = #{id}")
    @Results(id = "test2", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "sex", column = "sex"),
            @Result(property = "age", column = "age"),
            // 嵌套结果
            @Result(property = "card.id",column = "card_id"),
            @Result(property = "card.code",column = "code")
    })
    Person findPersonById2(int id);
}

IdCardMapper.java

@Component
@Mapper
public interface IdCardMapper {
    @Select("select * from tb_idcard where id = #{id}")
    IdCard findCodeById(int id);
}

接口+注解方式通过@one注解实现嵌套查询（这里的select指向另一个需要引用的接口类方法的全路径），通过将查询结果字段与实体类属性映射的方式实现嵌套结果。

测试

@Autowired
PersonMapper mapper;

@Test
public void findPersonByIdTest(){
    System.out.println(mapper.findPersonById(1));
    // 执行结果
    // Person{id=1, name='Tom', age=27, sex='男', card=IdCard{id=2, code='152221198711020317'}}
}

一对多

情景描述：一个用户可以有多个订单，同时多个订单归一个用户所有。

数据库设计

# 用户表tb_user
create table tb_user(
	id int(32) primary key auto_increment,
	username varchar(32),
	address varchar(256)
)
insert into tb_user(username,address) values("詹姆斯","克利夫兰");
insert into tb_user(username,address) values("科比","洛杉矶");
insert into tb_user(username,address) values("保罗","洛杉矶");
select * from tb_user;

# 订单表tb_orders
create table tb_orders(
	id int(32) primary key auto_increment,
	number varchar(32) not null,
	user_id int(32) not null,
	foreign key(user_id) references tb_user(id)
)
insert into tb_orders(number,user_id) values("10001","1");
insert into tb_orders(number,user_id) values("10002","1");
insert into tb_orders(number,user_id) values("10003","2");
select * from tb_orders;

# 嵌套查询一对多
select u.*,o.id as orders_id,o.number from tb_user u,tb_orders o where o.user_id = u.id and u.id = 1;

实体类

根据数据库表定义两个实体类：Orders、User。

Orders

private Integer id;// 订单ID
private String number;// 订单编号

// 省略setter、getter、toString

User

private Integer id;// 用户id
private String username;// 用户姓名
private String address;// 用户地址
private List<Orders> ordersList;// 用户订单

// 省略setter、getter、toString

实体类定义中，我们需要在一的一方添加多的一方作为属性。

Mapper接口+映射文件实现一对多查询

省略同名接口类UserMapper.java、OrdersMapper.java

UserMapper.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.example.dao.UserMapper">
    <!-- 嵌套查询 -->
    <select id="findUserWithOrders" parameterType="Integer" resultMap="UserWithOrdersResult1">
        select * from tb_user where id = #{id}
    </select>
    <resultMap id="UserWithOrdersResult1" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="ordersList" column="id" ofType="Orders" select="com.example.dao.OrdersMapper.queryUserOrders"></collection>
    </resultMap>

    <!-- 嵌套结果 -->
    <select id="findUserWithOrders" parameterType="Integer" resultMap="UserWithOrdersResult">
        select u.*,o.id as orders_id,o.number from tb_user u,tb_orders o where u.id = o.user_id and u.id = #{id}
    </select>
    <resultMap id="UserWithOrdersResult" type="User">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="ordersList" ofType="Orders">
            <id property="id" column="orders_id"/>
            <result property="number" column="number"/>
        </collection>
    </resultMap>
</mapper>

OrdersMapper.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.example.dao.OrdersMapper">
    <select id="queryUserOrders" parameterType="Integer" resultType="Orders">
        select * from tb_orders where user_id = #{id}
    </select>
</mapper>

注意，这里需要实现的OrdersMapper接口类返回类型为List<Product>，因为查询出了多个对象。

在UserMapper.xml中，通过嵌套查询，将查询到id字段继续嵌套执行queryUserOrders方法，得到List对象，最后将查询的字段注入User对象中。

通过嵌套结果，将查询到的字段注入User对象中，得到注入后的User对象。

Mapper接口+注解实现一对多查询

@Component
@Mapper
public interface UserMapper {
    // 根据用户id查询订单
    // 注解形式只能实现嵌套查询，无法实现嵌套结果
    @Select("select * from tb_user where id = #{id}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "address",column = "address"),
            @Result(property = "ordersList",column = "id",many = @Many(select = "com.example.dao.UserMapper.queryOrders"))
    })
    User findUserWithOrders(int id);

    @Select("select * from tb_orders where user_id = #{id}")
    List<Orders> queryOrders(int id);
}

测试

@Autowired
UserMapper userMapper;

@Test
public void queryManytoMany(){
    System.out.println(userMapper.findUserWithOrders(1));
    // 执行结果
    // User{id=1, username='詹姆斯', address='克利夫兰', ordersList=[Orders{id=1, number='10001'}, Orders{id=4, number='10002'}]}
}

多对多

情景描述：一个订单对应多个商品，一个商品对应多个订单。

多对多的实现方法与一对多类似，只是应用场景不同，因为涉及到多个实体的所属关系，基于第二范式的要求，需要将订单及商品表中的主键抽离出来作为外键形成一个中间表。

数据库设计

# 订单表tb_orders
create table tb_orders(
	id int(32) primary key auto_increment,
	number varchar(32) not null
)
insert into tb_orders(number) values("10001");
insert into tb_orders(number) values("10002");
insert into tb_orders(number) values("10003");

select * from tb_orders;

# 商品表
create table tb_product(
	id int(32) primary key auto_increment,
	name varchar(32),
	price double
)

insert into tb_product(name,price) values("Java基础入门","30.0");
insert into tb_product(name,price) values("Web程序开发入门","38.5");
insert into tb_product(name,price) values("SSM框架整合实战","50.0");

# 中间表
create table tb_ordersitem(
	id int(32) primary key auto_increment,
	orders_id int(32),
	product_id int(32),
	foreign key(orders_id) references tb_orders(id),
	foreign key(product_id) references tb_product(id)
)

# 一个订单有多个商品 一个商品属于多个订单
# 1号订单下有1、2两个商品
# 2号商品对应1、4两个订单
insert into tb_ordersitem(orders_id,product_id) values(1,1);
insert into tb_ordersitem(orders_id,product_id) values(1,2);
insert into tb_ordersitem(orders_id,product_id) values(4,2);

select o.*,p.id as pid,p.name,p.price from tb_orders o,tb_product p,tb_ordersitem oi where oi.orders_id = o.id and oi.product_id = p.id and o.id = 1; 

实体类

根据数据库表定义两个实体类：Orders、Product。

Orders

private Integer id;// 订单ID
private String number;// 订单编号
List<Product> productList;// 订单所属商品

// 省略setter、getter、toString

Product

private Integer id;
private String name;
private Double price;
private List<Orders> ordersList;// 商品所属订单

// 省略setter、getter、toString

由于涉及一个订单会有多个商品，一个商品对应多个订单，所以这里Orders、Product类中放置集合。

Mapper接口类+映射文件实现多对多查询

省略同名接口类OrdersMapper.java、ProductMapper.java

OrdersMapper.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.example.dao.OrdersMapper">
    <!-- 嵌套查询 -->
    <select id="findProductsWithOrder1" parameterType="Integer" resultMap="ProductWithOrderResult1">
        select * from tb_orders where id = #{id}
    </select>
    <resultMap id="ProductWithOrderResult1" type="Orders">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
        <collection property="productList" column="id" ofType="Product" select="com.example.dao.ProductMapper.queryProduct"></collection>
    </resultMap>
    <!-- 嵌套结果 -->
    <select id="findProductsWithOrder2" parameterType="Integer" resultMap="ProductWithOrderResult2">
        select o.*,p.id as pid,p.name,p.price from tb_orders o,tb_product p,tb_ordersitem oi where oi.orders_id = o.id and oi.product_id = p.id and o.id = #{id}
    </select>
    <resultMap id="ProductWithOrderResult2" type="Orders">
        <id property="id" column="id"/>
        <result property="number" column="number"/>
        <collection property="productList" ofType="Product">
            <id property="id" column="pid"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>
</mapper>

ProductMapper.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.example.dao.ProductMapper">
    <select id="queryProduct" parameterType="Integer" resultType="Product">
        select * from tb_product where id in (select product_id from tb_ordersitem where orders_id = #{id})
    </select>
</mapper>

注意，这里需要实现的ProductMapper接口类返回类型为List<Product>，因为查询出了多个对象。

嵌套查询，将查询到id字段继续嵌套执行queryProduct方法，得到List集合对象，最后将查询的字段注入Orders对象中。
嵌套结果，将查询出的字段依次注入Orders对象中。

Mapper接口类+注解实现多对多查询

// 查询订单号
@Select("select * from tb_orders where id = #{id}")
@Results({
        @Result(property = "id",column = "ld"),
        @Result(property = "number",column = "number"),
        @Result(property = "productList",column = "id",many = @Many(select = "com.example.dao.UserMapper.queryProducts"))
})
Orders queryOrder(int id);

// 查询商品信息
@Select("select * from tb_product where id in (select product_id from tb_ordersitem where orders_id = #{id})")
List<Product> queryProducts(int id);

这里订单号查询出订单信息，再通过订单id字段嵌套执行queryProducts方法，该方法通过订单号在中间表中查询出商品id，再通过商品id查询出商品表中的商品。

测试

这里我们测试注解实现的方法

@Autowired
UserMapper userMapper;

@Test
public void queryOrders(){
    System.out.println(userMapper.queryOrder(1));
    // 执行结果
    //Orders{id=1, number='10001', productList=[Product{id=1, name='Java基础入门', price=30.0, ordersList=null}, Product{id=2, name='SSM框架整合实战', price=50.0, ordersList=null}]}
}

常见错误解决方法

映射文件与接口文件同一目录无法识别的问题

如果是用Maven打包的项目，默认src下的xml文件是不会包含到打包中，所以Mybatis如果配置成接口和xml配置文件都放到src同级目录会在运行/测试时报错，报错如下：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)

解决办法

在pom.xml文件找到<build>标签，在里面加入如下代码：

<resources>
    <resource>
        <directory>src/main/java</directory>
        <includes>
            <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
    </resource>
</resources>