MyBatis的关联映射

yandao

已于 2023-05-08 16:25:04 修改

阅读量1k

点赞数 2

分类专栏： mybatis 文章标签： mybatis java 数据库

于 2023-04-21 23:42:08 首次发布

本文链接：https://blog.csdn.net/yandao/article/details/130283162

版权

mybatis 专栏收录该内容

11 篇文章 3 订阅

订阅专栏

结果映射（resultMap）–主要使用这两个标签

association – 一个复杂类型的关联；许多结果将包装成这种类型
嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用
collection – 一个复杂类型的集合
嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用

多对一：查询的多个对象中有一个属性包含一个复杂对象，使用association标签嵌套
一对多：查询的单个对象中的一个属性包含多个其他对象，使用collection标签嵌套
javaType为实体类中属性的类型，为常用基本数据类型时可以省略
ofType则是用来指定到List或集合中的实体类类型，泛型中的约束类型

1.一对一查询

问题：根据用户Id查询Person的信息，包括IdCard的信息，用户和IdCard是一对一的关系

1.1 数据库准备

USE mybatis;
# 创建一个名称为tb_idcard的表
CREATE TABLE  tb_idcard( 
     id INT PRIMARY KEY AUTO_INCREMENT,
     CODE VARCHAR(18)
);
# 插入2条数据
INSERT INTO tb_idcard(CODE) VALUES('152221198711020624');
INSERT INTO tb_idcard(CODE) VALUES('152201199008150317');
# 创建一个名称为tb_person的表
CREATE TABLE  tb_person( 
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(32),
     age INT,
     sex VARCHAR(8),
     card_id INT UNIQUE,     
     FOREIGN KEY(card_id) REFERENCES tb_idcard(id)
);
# 插入2条数据
INSERT INTO tb_person(name,age,sex,card_id) VALUES('Rose',22,'女',1);
INSERT INTO tb_person(name,age,sex,card_id) VALUES('jack',23,'男',2);

1.2 实体类

1.2.1 IdCard.java

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class IdCard {
    private Integer id;
    private String code;
}

1.2.2 Person.java

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;
    private IdCard card;
}

1.3 用户配置接口

1.3.1 IdCardMapper.java

package com.biem.mapper;

public interface IdCardMapper{
}

1.3.2 Person.java

package com.biem.mapper;

public interface PersonMapper {
}

1.4 用户配置文件

1.4.1 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.biem.mapper.IdCardMapper">


</mapper>

1.4.2 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.biem.mapper.PersonMapper">


</mapper>

1.5 解决方法

1.5.1 嵌套查询方式

1.5.1.1 IdCardMapper.java

package com.biem.mapper;

import com.biem.pojo.IdCard;

public interface IdCardMapper {
    public IdCard findCodeById(Integer id);
}

1.5.1.2 PersonMapper.java

package com.biem.mapper;

import com.biem.pojo.Person;

public interface PersonMapper {
    public Person findPersonById(Integer id);
}

1.5.1.3 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.biem.mapper.IdCardMapper">

    <!--public IdCard findCodeById(Integer id);-->
    <select id="findCodeById" parameterType="Integer" resultType="IdCard">
        select * from tb_idcard where id=#{id}
    </select>
</mapper>

1.5.1.4 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.biem.mapper.PersonMapper">
    <!-- public Person findPersonById(Integer id); -->
    <resultMap id="IdCardWithPersonResult" type="Person">
        <id property="id" column="id"></id>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <!-- 一对一：association使用select 属性引入另外一条SQL语句 -->
        <association property="card" column="card_id" javaType="IdCard"
                     select="com.biem.mapper.IdCardMapper.findCodeById"/>
    </resultMap>

    <select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
        select * from tb_person where id = #{id}
    </select>
</mapper>

1.5.1.5 PersonTest.java

package com.biem.test;

import com.biem.mapper.PersonMapper;
import com.biem.pojo.Person;
import com.biem.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class PersonTest {

    @Test
    public void testFindPersonById(){
        SqlSession session = MyBatisUtil.openSession();
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        Person person = mapper.findPersonById(1);
        System.out.println(person);
        session.close();
    }
}

1.5.1.6 测试结果

DEBUG 04-21 17:35:29,353 ==>  Preparing: select * from tb_person where id = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:35:29,412 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:35:29,458 ====>  Preparing: select * from tb_idcard where id=?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:35:29,458 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:35:29,462 <====      Total: 1 (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:35:29,465 <==      Total: 1 (BaseJdbcLogger.java:159) 
Person(id=1, name=Rose, age=22, sex=女, card=IdCard(id=1, code=152221198711020624))

1.5.2 嵌套结果方式

1.5.2.1 PersonMapper.java 添加

public Person findPersonByIdWithNestedResults(Integer id);

1.5.2.2 PersonMapper.xml添加

<!--public Person findPersonByIdWithNestedResults(Integer id);-->
    <resultMap id="IdCardWithPersonResult2" type="Person">
        <id property="id" column="id"></id>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>

        <association property="card" javaType="IdCard">
            <id property="id" column="card_id"></id>
            <result property="code" column="code"></result>
        </association>
    </resultMap>
    <select id="findPersonByIdWithNestedResults" parameterType="Integer" resultMap="IdCardWithPersonResult">
        select p.*, idcard.code
        from tb_person p, tb_idcard idcard
        where p.card_id = idcard.id and p.id = #{id}
    </select>

1.5.2.3 PersonTest.java添加

    @Test
    public void testFindPersonByIdWithNestedResults(){
        SqlSession session = MyBatisUtil.openSession();
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        Person person = mapper.findPersonByIdWithNestedResults(1);
        System.out.println(person);
        session.close();
    }

1.5.2.4 测试结果

DEBUG 04-21 17:31:48,431 ==>  Preparing: select p.*, idcard.code from tb_person p, tb_idcard idcard where p.card_id = idcard.id and p.id = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:31:48,479 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:31:48,529 ====>  Preparing: select * from tb_idcard where id=?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:31:48,530 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:31:48,533 <====      Total: 1 (BaseJdbcLogger.java:159) 
DEBUG 04-21 17:31:48,536 <==      Total: 1 (BaseJdbcLogger.java:159) 
Person(id=1, name=Rose, age=22, sex=女, card=IdCard(id=1, code=152221198711020624))

2.一对多查询

问题：查询用户以及关联的订单，一个用户有多个订单

2.1 数据库准备

USE mybatis;
# 创建一个名称为tb_user的表
CREATE TABLE tb_user (
  id int(32) PRIMARY KEY AUTO_INCREMENT,
  username varchar(32),
  address varchar(256)
 );
# 插入3条数据
INSERT INTO tb_user VALUES ('1', '小明', '北京');
INSERT INTO tb_user VALUES ('2', '李华', '上海');
INSERT INTO tb_user VALUES ('3', '李刚', '上海');
# 创建一个名称为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)
);
# 插入3条数据
INSERT INTO tb_orders VALUES ('1', '1000011', '1');
INSERT INTO tb_orders VALUES ('2', '1000012', '1');
INSERT INTO tb_orders VALUES ('3', '1000013', '2');

2.2 实体类

2.2.1 Orders.java

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private String number;
    private Integer userId;
}

2.2.2 Users.java

package com.biem.pojo;

import lombok.*;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
    private Integer id;
    private String username;
    private String address;
    private List<Orders> ordersList;
}

2.3 用户配置接口

2.3.1 OrdersMapper.java

package com.biem.mapper;

public interface OrdersMapper {
}

2.3.2 UsersMapper.java

package com.biem.mapper;

public interface UsersMapper {
}

2.4 用户配置文件

2.4.1 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.biem.mapper.OrdersMapper">
</mapper>

2.4.2 UsersMapper.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.biem.mapper.UsersMapper">
</mapper>

2.5 解决办法

2.5.1 嵌套查询方式

2.5.1.1 OrdersMapper.java

package com.biem.mapper;

public interface OrdersMapper {
	public List<Orders> selectOrders(Integer uid);
}

2.5.1.2 UsersMapper.java

package com.biem.mapper;

import com.biem.pojo.User;

public interface UserMapper {
    public User findUserById(int id);
}

2.5.1.3 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.biem.mapper.OrdersMapper">
    <!-- public List<Orders> selectOrders(Integer user_id); -->
    <select id="selectOrders" parameterType="Integer" resultType="Orders">
        select * from tb_orders where user_id=#{userId}
    </select>
</mapper>

2.5.1.4 UsersMapper.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.biem.mapper.UsersMapper">
    <resultMap id="usersWithOrders1" type="Users">
        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <!--由于查询老师中有一个属性为所有学生对象集合，所一使用collection标签进行嵌套，
		方法的返回值是List，所以javaType使用ArrayList来接收，而list中泛型约束类型放的
		是orders对象所以使用ofType为orderst来进行映射，使用select进行子查询，并用column
		将查询出的id字段传给子查询需要的id-->
        <!--此处javaType可以省略-->
        <collection property="ordersList" javaType="ArrayList" ofType="orders" select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
    </resultMap>

    <!-- public Users findUserById(Integer id); -->
    <select id="findUserById" parameterType="Integer" resultMap="usersWithOrders1">
        select * from tb_user where id = #{id}
    </select>
</mapper>

2.5.1.5 UsersTest.java

import org.junit.Test;

public class UsersTest {

    @Test
    public void testFindUserById(){
        SqlSession session = MyBatisUtil.openSession();
        UsersMapper mapper = session.getMapper(UsersMapper.class);
        Users user = mapper.findUserById(1);
        System.out.println("user = " + user);
        session.close();
    }
}

2.5.1.6 测试结果

DEBUG 04-21 20:39:59,758 ==>  Preparing: select * from tb_user where id = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 20:39:59,897 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 20:39:59,962 ====>  Preparing: select * from tb_orders where user_id=?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 20:39:59,963 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 20:39:59,967 <====      Total: 2 (BaseJdbcLogger.java:159) 
DEBUG 04-21 20:39:59,971 <==      Total: 1 (BaseJdbcLogger.java:159) 
user = Users(id=1, username=小明, address=北京, ordersList=[Orders(id=1, number=1000011, userId=1), Orders(id=2, number=1000012, userId=1)])

2.5.2 嵌套结果方式

2.5.2.1 UsersMapper.java添加

public Users findUserByIdWithNestedResults(Integer id);

2.5.2.2 UsersMapper.xml添加

    <!-- public Users findUserByIdWithNestedResults(Integer id); -->
    <!-- 一对多：查看某一个用户及关联的订单信息，
        注意：当关联查询出来的列名相同时，则需要使用别名区分  -->
    <select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >
        select u.*, o.id as orders_id , o.number, o.user_id
        from tb_user u, tb_orders o
        where u.id = o.user_id and u.id=#{id}
    </select>
    <resultMap id="UsersWithOrdersResult" type="Users">
        <id property="id" column="id"></id>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        <collection property="ordersList" ofType="Orders">
            <id property="id" column="orders_id"></id>
            <result property="number" column="number"/>
            <result property="userId" column="user_id"/>
        </collection>
    </resultMap>

2.5.2.3 UsersTest.java添加

    @Test
    public void testFindUserByIdWithNestedResults(){
        SqlSession session = MyBatisUtil.openSession();
        UsersMapper mapper = session.getMapper(UsersMapper.class);
        Users user = mapper.findUserByIdWithNestedResults(1);
        System.out.println("user = " + user);
        session.close();
    }

2.5.2.4 测试结果

DEBUG 04-21 21:13:51,214 ==>  Preparing: select u.*, o.id as orders_id , o.number, o.user_id from tb_user u, tb_orders o where u.id = o.user_id and u.id=?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 21:13:51,289 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 21:13:51,380 <==      Total: 2 (BaseJdbcLogger.java:159) 
user = Users(id=1, username=小明, address=北京, ordersList=[Orders(id=1, number=1000011, userId=1), Orders(id=2, number=1000012, userId=1)])

3.多对一查询

解决user_Id为某个值的所有orders的信息

3.1 环境配置

2.1-2.4配置
对于Users–>Orders 1对多
对于Orders->Users 多对1

Orders.java添加属性User

package com.biem.pojo;

import lombok.*;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private String number;
    private Integer userId;
    private Users users;
}

3.2 解决方案

3.2.1 嵌套查询方式

3.2.1.1 UsersMapper.java 添加

public Users getUsersById(Integer id);

3.2.1.2 OrdersMapper.java 添加

public List<Orders> getOrders1(Integer userId);

3.2.1.3 UsersMappers.xml

    <select id="getUsersById" resultType="users">
        select * from tb_user where `id` = #{id}
    </select>

3.2.1.4 OrdersMapper.xml

    <select id="getOrders1" resultMap="ordersResult1">
        select * from tb_orders where user_id = #{userId}
    </select>
    <resultMap id="ordersResult1" type="orders">
        <result column="id" property="id"/>
        <result column="number" property="number"/>
        <!--复杂的属性，我们需要单独处理
            对象：使用association
            集合：collection-->
        <association column="id" property="users" javaType="users" select="com.biem.mapper.UsersMapper.getUsersById"/>
    </resultMap>

3.2.1.5 OrdersTest.java

package com.biem.test;

import com.biem.mapper.OrdersMapper;
import com.biem.pojo.Orders;
import com.biem.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;


public class OrdersTest {

    @Test
    public void testGetOrders1(){
        SqlSession session = MyBatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.getOrders1(1);
        System.out.println("ordersList = " + ordersList);
    }
}

3.2.1.6 测试结果

DEBUG 04-21 22:11:09,143 ==>  Preparing: select * from tb_orders where user_id = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,203 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,251 ====>  Preparing: select * from tb_user where `id` = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,252 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,254 <====      Total: 1 (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,258 ====>  Preparing: select * from tb_user where `id` = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,259 ====> Parameters: 2(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,262 <====      Total: 1 (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:11:09,262 <==      Total: 2 (BaseJdbcLogger.java:159) 
ordersList = [Orders(id=1, number=1000011, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null)), Orders(id=2, number=1000012, userId=1, users=Users(id=2, username=李华, address=上海, ordersList=null))]

3.2.2 嵌套结果方式

3.2.2.1 OrderMapper.java 添加

public List<Orders> getOrders1ByNestedResult(Integer userId);

3.2.2.1 OrderMapper.xml添加

<!-- public List<Orders> getOrders1ByNestedResult(Integer userId); -->
    <select id="getOrders1ByNestedResult" resultMap="ordersResult2">
        <!-- id为接口中实现的方法名，resultMap为返回值类型，由于返回值包含一个Teacher对象，所以不能用resultType-->
        select o.id oid, o.number number, o.user_id uid , u.username username, u.address address
        from tb_orders o,tb_user u
        where o.user_id=u.id and o.user_id = #{userId}
    </select>
    <resultMap id="ordersResult2" type="orders">
        <result column="oid" property="id"/>
        <result column="number" property="number"/>
        <result column="uid" property="userId"/>
        <association property="users" javaType="users">
            <!--property是实体类中的字段属性，复杂的属性，使用了javaType描述的是属性的类型，它是一个Teacher对象-->
            <result property="id" column="uid"/>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
        </association>
    </resultMap>

3.2.2.3 OrderTest.java 添加

    @Test
    public void testGetOrders2(){
        SqlSession session = MyBatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.getOrders1ByNestedResult(1);
        System.out.println("ordersList = " + ordersList);
    }

3.2.2.4 测试结果

DEBUG 04-21 22:36:45,304 ==>  Preparing: select o.id oid, o.number number, o.user_id uid , u.username username, u.address address from tb_orders o,tb_user u where o.user_id=u.id and o.user_id = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:36:45,394 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 22:36:45,438 <==      Total: 2 (BaseJdbcLogger.java:159) 
ordersList = [Orders(id=1, number=1000011, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null)), Orders(id=2, number=1000012, userId=1, users=Users(id=1, username=小明, address=北京, ordersList=null))]

4 多对多查询

4.1 数据库准备

USE mybatis;
# 创建一个名称为tb_product的表
CREATE TABLE tb_product (
  id INT(32) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(32),
  price DOUBLE 
 );
# 插入3条数据
INSERT INTO tb_product VALUES ('1', 'Java基础入门', '44.5');
INSERT INTO tb_product VALUES ('2', 'Java Web程序开发入门', '38.5');
INSERT INTO tb_product VALUES ('3', 'SSM框架整合实战', '50');
# 创建一个名称为tb_ordersitem 的中间表
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)
);
# 插入3条数据
INSERT INTO tb_ordersitem  VALUES ('1', '1', '1');
INSERT INTO tb_ordersitem  VALUES ('2', '1', '3');
INSERT INTO tb_ordersitem  VALUES ('3', '3', '3');
# 订单表tb_orders由于在上面已经创建,所以这里不用再次创建

4.2 实体类

4.2.1 Orders.java

已经创建所以不用再次创建
添加一个属性

private List<Product> productList;

变成：

package com.biem.pojo;

import lombok.*;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private String number;
    private Integer userId;
    private Users users;
    private List<Product> productList;
}

4.2.2 Product.java

package com.biem.pojo;

import lombok.*;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product {
    private Integer id;
    private String name;
    private Double price;
    private List<Orders> ordersList;
}

4.3 用户配置接口

4.3.1 OrdersMapper.java

已经创建

4.3.2 ProductMapper.java

package com.biem.mapper;

public interface ProductMapper {
}

4.4 用户配置文件

4.4.1 OrdersMapper.xml

已经创建

4.4.2 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.biem.mapper.ProductMapper">

</mapper>

4.5 解决方案

4.5.1 嵌套查询方式

4.5.1.1 OrdersMapper.java 添加

public List<Orders> findOrdersWithProduct(Integer id);

4.5.1.2 ProductMapper.java 添加

public List<Product> findProductById(Integer id);

4.5.1.3 OrdersMapper.xml 添加

    <select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">
        select * from tb_orders where id = #{id}
    </select>
    <resultMap id="OrdersWithProductResult" type="Orders">
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <collection property="productList" column="id" ofType="Product" select="com.biem.mapper.ProductMapper.findProductById">
        </collection>
    </resultMap>

4.5.1.4 ProductMapper.xml 添加

    <!--public List<Product> findProductById(Integer id);-->
    <select id="findProductById" parameterType="Integer" resultType="Product">
        select * from tb_product where id in(
            select product_id from tb_ordersitem where orders_id = #{id}
            )
    </select>

4.5.1.5 OrderTest.java添加

    @Test
    public void testFindOrdersWithProduct(){
        SqlSession session = MyBatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.findOrdersWithProduct(1);
        System.out.println("ordersList = " + ordersList);
    }

4.5.1.6 测试结果

DEBUG 04-21 23:21:46,851 ==>  Preparing: select * from tb_orders where id = ?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:21:46,897 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:21:46,959 ====>  Preparing: select * from tb_product where id in( select product_id from tb_ordersitem where orders_id = ? )  (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:21:46,960 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:21:46,966 <====      Total: 2 (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:21:46,970 <==      Total: 1 (BaseJdbcLogger.java:159) 
ordersList = [Orders(id=1, number=1000011, userId=1, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]

4.5.2 嵌套结果查询

4.5.2.1 OrderMapper.java 添加

public List<Orders> findOrdersWithProductByNestedResult(Integer id);

4.5.2.2 OrdersMapper.xml 添加

    <!-- public List<Orders> findOrdersWithProductByNestedResult(Integer id); -->
    <select id="findOrdersWithProductByNestedResult" parameterType="Integer" resultMap="OrdersWithProductResult2">
        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="OrdersWithProductResult2" type="Orders">
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <collection property="productList" ofType="product">
            <id property="id" column="pid"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>

4.5.2.3 OrderTest.java 添加

	@Test
    public void findOrdersWithProductByNestedResult(){
        SqlSession session = MyBatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.findOrdersWithProductByNestedResult(1);
        System.out.println("ordersList = " + ordersList);
    }

4.5.2.4 测试结果

DEBUG 04-21 23:38:47,365 ==>  Preparing: 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=?  (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:38:47,438 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 04-21 23:38:47,485 <==      Total: 2 (BaseJdbcLogger.java:159) 
ordersList = [Orders(id=1, number=1000011, userId=null, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]

yandao

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MyBatis的关联映射

结果映射（resultMap）–主要使用这两个标签association – 一个复杂类型的关联；许多结果将包装成这种类型嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用collection – 一个复杂类型的集合嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用。
复制链接

扫一扫

专栏目录