mybatis学习之高级映射中的一对多查询

最新推荐文章于 2023-05-12 09:16:57 发布

Alex_Ricardo

最新推荐文章于 2023-05-12 09:16:57 发布

阅读量988

点赞数

分类专栏： mybatis 文章标签： mybatis web框架 Java EE 数据库

本文链接：https://blog.csdn.net/fjnmbb12/article/details/73655519

版权

mybatis 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

一对多查询

mybatis使用resultMap的collection对关联查询的多条记录映射到一个list集合属性中

使用resultType实现：

将订单明细映射到order中的orderdetails中，需要自行处理（双重循环遍历），将订单明细存储在orderdetails的集合中。

在此处使用resultMap实现

1、需求

查询订单及订单明细的信息

2 、sql语句

确定主查询表：订单表 order

确定关联查询表：订单明细表 orderdetail

在一对一查询的基础上，添加订单明细表的关联即可

SELECT orders.*,user.`username`,user.`sex`,user.`address` , orderdetail.`id` orderdetail_id,orderdetail.`items_id`, orderdetail.`items_num`,orderdetail.`orders_id` FROM orders,USER,orderdetail WHERE orders.`user_id` = user.`id` AND orderdetail.`orders_id` = orders.`id`

`3、分析`

若使用resultType将上面的查询结果集映射到pojo中时，订单信息的pojo中会重复

要求：

对于order映射不能出现重复记录

设想：

在order,java类中添加List<OrderDetail>属性

最终会将订单信息映射到order中，订单所对应的订单明细映射到order中的list中去

结果：

映射成的order记录数为两条（order信息不重复）

每一个order中的orderdetail属性存储了该订单所对应的订单明细

`4、在orders类中添加list订单明细属性`

    private Integer id;
    private Integer userId;
    private String number;
    private Date  createtime;
    private String note;
    //用户信息
    private User user;

    //订单详情List
    private List<OrderDetail> OrderDetails;

5、resultMap的定义

    <!--
    订单及订单明细的resultMap
     使用继承extends，不用在此配置订单信息和用户信息的配置
     -->
    <resultMap id="OrdersAndOrderdetailResultMap" type="pojo.Order" extends="OrderUserResultMap">
        <!-- 订单信息 -->
        <!-- 用户信息 -->
        
        <!--
        订单明细信息
        一个订单包含多条明细
        要使用collection进行映射
        collection:对关联的查询的多条记录映射到集合中去
        property:将关联的多条记录映射到order中的哪条属性离去
        ofType:指定要映射的集合属性中的pojo的类型,即orderdetail
        -->
        <collection property="orderDetails" ofType="pojo.OrderDetail">
            <!-- 订单明细的唯一标识 -->
            <id column="orderdetail_id" property="id" />
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum" />
            <result column="orders_id" property="ordersId" />
            <result />
        </collection>
    </resultMap>

6、mapper.xml

    <!-- 查询订单 关联查询用户 及 订单明细  使用resultMap -->
    <select id="findOrderAndOrderDetailResultMap" resultMap="OrdersAndOrderdetailResultMap">
        SELECT orders.*,user.`username`,user.`sex`,user.`address` , orderdetail.`id` orderdetail_id,orderdetail.`items_id`, orderdetail.`items_num`,orderdetail.`orders_id` FROM orders,USER,orderdetail WHERE orders.`user_id` = user.`id` AND orderdetail.`orders_id` = orders.`id`
    </select>

7、mapper.java

    //查询订单关联查询用户和订单详情
    public List<Order> findOrderAndOrderDetailResultMap() throws Exception;

8、测试代码

    @Test
    public void testFindOrderAndOrderDetailResultMap() throws Exception{
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrderMapperCustom orderMapperCustom = sqlSession.getMapper(OrderMapperCustom.class);
        List<Order> list = orderMapperCustom.findOrderAndOrderDetailResultMap();
        System.out.println(list);
    }

9、log4j输出结果

DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - Class not found: org.jboss.vfs.VFS
DEBUG [main] - JBoss 6 VFS API is not available in this environment.
DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo
DEBUG [main] - Reader entry: Items.class
DEBUG [main] - Reader entry: Order.class
DEBUG [main] - Reader entry: OrderCustom.class
DEBUG [main] - Reader entry: OrderDetail.class
DEBUG [main] - Reader entry: User.class
DEBUG [main] - Reader entry: UserCustom.class
DEBUG [main] - Reader entry: UserQueryVo.class
DEBUG [main] - Listing file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/Items.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/Items.class
DEBUG [main] - Reader entry: ����   4 X
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/Order.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/Order.class
DEBUG [main] - Reader entry: ����   4 f
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/OrderCustom.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/OrderCustom.class
DEBUG [main] - Reader entry: ����   4 "
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/OrderDetail.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/OrderDetail.class
DEBUG [main] - Reader entry: ����   4 @
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/User.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/User.class
DEBUG [main] - Reader entry: ����   4 L
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/UserCustom.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/UserCustom.class
DEBUG [main] - Reader entry: ����   4 
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/UserQueryVo.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/pojo/UserQueryVo.class
DEBUG [main] - Reader entry: ����   4 %
DEBUG [main] - Checking to see if class pojo.Items matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class pojo.Order matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class pojo.OrderCustom matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class pojo.OrderDetail matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class pojo.User matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class pojo.UserCustom matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class pojo.UserQueryVo matches criteria [is assignable to Object]
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper
DEBUG [main] - Reader entry: OrderMapperCustom.class
DEBUG [main] - Reader entry: OrderMapperCustom.xml
DEBUG [main] - Reader entry: UserMapper.class
DEBUG [main] - Reader entry: UserMapper.xml
DEBUG [main] - Listing file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/OrderMapperCustom.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/OrderMapperCustom.class
DEBUG [main] - Reader entry: ����   4    
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/OrderMapperCustom.xml
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/OrderMapperCustom.xml
DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/UserMapper.class
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/UserMapper.class
DEBUG [main] - Reader entry: ����   4    findUserByIdResultMap (I)Lpojo/User; 
DEBUG [main] - Find JAR URL: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/UserMapper.xml
DEBUG [main] - Not a JAR: file:/E:/%e5%a4%a7%e5%ad%a6%e9%a1%b9%e7%9b%ae/IDEA%20Project/mybatis/out/production/mybatis/mapper/UserMapper.xml
DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
DEBUG [main] - Checking to see if class mapper.OrderMapperCustom matches criteria [is assignable to Object]
DEBUG [main] - Checking to see if class mapper.UserMapper matches criteria [is assignable to Object]
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 428910174.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
DEBUG [main] - ==>  Preparing: select COUNT(*) from user WHERE user.sex = ? and user.username LIKE '%测试%' 
DEBUG [main] - ==> Parameters: 1(String)
DEBUG [main] - <==      Total: 1
6

Process finished with exit code 0