【Mybatis】级联查询之一对多级联查询

引言

与前面介绍的一对一相比 Mybatis】级联查询之一对一级联查询，在实际开发中接触最多的还是一对多的关联关系。
应用场景举例：用户与订单之间的关系为一对多，即一个用户可以有多个订单，而一个订单只属于一个用户。

创建工程和数据库表

创建Spring整合Mybatis工程 【Spring】Spring整合Mybatis案例

创建orders表和user表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `userid` int(11) DEFAULT NULL,
  `orderinfo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userorder` (`userid`),
  CONSTRAINT `userorder` FOREIGN KEY (`userid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

项目结构如下：

创建实体类和数据库操作接口

Order 类

public class Order {
    private int id;
    private int userid;
    private String orderinfo;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    public String getOrderinfo() {
        return orderinfo;
    }

    public void setOrderinfo(String orderinfo) {
        this.orderinfo = orderinfo;
    }

    @Override
    public String   toString() {
        return "Order{" +
                "id=" + id +
                ", userid=" + userid +
                ", orderinfo='" + orderinfo + '\'' +
                '}';
    }
}

User类

public class User {
    private int id;
    private String name;
    private int age;
    private List<Order> orderList;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", orderList=" + orderList +
                '}';
    }
}

UserMapper

@Repository("userMapper")
@Mapper
public interface UserMapper {
    List<User> findUserOrdersById();
}

OrderMapper

@Repository("orderMapper")
@Mapper
public interface OrderMapper {
    List<Order> findOrdersByUserId(int id);
}

创建配置文件

mybatis-config.xml

<?xml version = "1.0" encoding = "UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0 //EN"
        "http//mybatis.org/dtd/mybatis-3-config.dtd" >
<configuration>
    <settings>
        <!--在使用MyBatis嵌套查询方式进行关联查询时，使用MyBatis的延迟加载可以在一定程度上提高查询效率-->
        <!--打开延迟加载的开关-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--将积极加载改为按需加载-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <mappers>
        <mapper resource="UserMapper.xml"/>
        <mapper resource="OrderMapper.xml"/>
    </mappers>

</configuration>

OrderMapper.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.lucas.mybatis.mapper.OrderMapper">
    <!--根据用户id查询订单信息-->
    <select id="findOrdersByUserId" parameterType="integer" resultType="com.lucas.mybatis.model.Order">
        select * from orders where userid=#{id}
    </select>

</mapper>

UserMapper.xml

方法1

<?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.lucas.mybatis.mapper.UserMapper">
    <resultMap id="userAndOrders" type="com.lucas.mybatis.model.User">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <!--ofType表示集合的元素类型，将id传递给Dao接口中的findOrdersByUserId方法-->
        <collection property="orderList" ofType="com.lucas.mybatis.model.Order" column="id" select="com.lucas.mybatis.mapper.OrderMapper.findOrdersByUserId"></collection>
    </resultMap>
    <select id="findUserOrdersById"   resultMap="userAndOrders">
        select * from user
    </select>
</mapper>

方法2

<?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.lucas.mybatis.mapper.UserMapper">
    <resultMap id="userAndOrders" type="com.lucas.mybatis.model.User">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <!--ofType表示集合的元素类型-->
        <collection property="orderList" ofType="com.lucas.mybatis.model.Order"  >
            <id property="id" column="id"></id>
            <result property="orderinfo" column="orderinfo"></result>
        </collection>
    </resultMap>
    <select id="findUserOrdersById"   resultMap="userAndOrders">
       select user.*,orders.id,orders.orderinfo from user ,orders where `user`.id = orders.userid
    </select>
</mapper>

添加测试代码

public class App {
    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserMapper userMapper = (UserMapper) applicationContext.getBean("userMapper");
        List<User> users = userMapper.findUserOrdersById();
        for (User user : users) {
            System.out.println(user);
        }
    }
}