7.6.2 MyBatis 一对多关联

PAX-K

已于 2022-01-19 12:25:05 修改

阅读量1k

点赞数 2

分类专栏： MyBatis 文章标签： java MyBatis 一对多双向关联

于 2022-01-18 17:56:45 首次发布

本文链接：https://blog.csdn.net/weixin_39340061/article/details/122565215

版权

MyBatis 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

实际生活中也有许多一对多级联关系，例如一个用户可以有多个订单，而一个订单只属于一个用户。同样，国家和城市也属于一对多级联关系。

在 MyBatis 中，通过 <resultMap> 元素的子元素 <collection> 处理一对多级联关系，collection 可以将关联查询的多条记录映射到一个 list 集合属性中。

java 持久对象

@Data
public class Order {
    private int id;
    private int orderNum;
    private int userId;
}

@Data
public class User {
    private int id;
    private String name, pwd;
    private List<Order> orderList = new ArrayList<>();
}

查询

单步查询

单步查询是通过一条 sql 语句将对象及其关联对象一起查出来。

映射文件
只需要在“一“方设置映射和查询语句。

<!--UserMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.UserMapper">
    
    <resultMap id="userAndOrder2" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="pwd" column="pwd"/>

        <collection property="orderList" column="userId" ofType="order">
            <id property="id" column="oid"/>
            <result property="orderNum" column="ordernum"/>
            <result property="userId" column="id"/>
        </collection>
    </resultMap>

    <select id="findUserOrderById2" resultMap="userAndOrder2">
        select u.*, o.id as oid, o.ordernum
        from user u, order1 o
        where u.id = #{id} and o.userId=u.id
    </select>

</mapper>

测试代码

@Test
public void testFindUserById2() {
    UserMapper userMapper     = sqlSession.getMapper(UserMapper.class);
    User       userOrderById1 = userMapper.findUserOrderById2(1);

    log.info("{}", userOrderById1);
}

输出结果

17:13:47.771 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById2 - ==> Preparing: select u.*, o.id as oid, o.ordernum from user u, order1 o where u.id = ? and o.userId=u.id
17:13:47.825 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById2 - > Parameters: 1(Integer)
17:13:47.914 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById2 - < Total: 2
17:13:47.915 [Test worker] INFO cn.dt.mybatis.mapper.UserMapperTest - User(id=1, name=编程帮, pwd=123, orderList=[Order(id=1, orderNum=20200107, userId=1), Order(id=4, orderNum=20200645, userId=1)])

分步查询

映射文件

<!--OrderMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.OrderMapper">
    <select id="findByUserId" resultType="Order">
        select * from  order1 where userId = #{userId}
    </select>
</mapper>

<!--UserMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.UserMapper">
    
    <resultMap id="userAndOrder1" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="pwd" column="pwd"/>

        <collection property="orderList"
                    ofType="order" column="id"
                    select="cn.dt.mybatis.mapper.OrderMapper.findByUserId"/>
    </resultMap>

    <select id="findUserOrderById1" resultMap="userAndOrder1">
        select *
        from user
        where id = #{id}
    </select>
</mapper>

测试代码

@Test
public void testFindUserById1() {
    UserMapper userMapper     = sqlSession.getMapper(UserMapper.class);
    User       userOrderById1 = userMapper.findUserOrderById1(1);

    log.info("{}", userOrderById1);
}

输出结果

17:16:51.629 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById1 - ==> Preparing: select * from user where id = ?
17:16:51.679 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById1 - ==> Parameters: 1(Integer)
17:16:51.765 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - ==> Preparing: select * from order1 where userId = ?
17:16:51.765 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - > Parameters: 1(Integer)
17:16:51.769 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - < Total: 2
17:16:51.770 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById1 - < Total: 1
17:16:51.771 [Test worker] INFO cn.dt.mybatis.mapper.UserMapperTest - User(id=1, name=编程帮, pwd=123, orderList=[Order(id=1, orderNum=20200107, userId=1), Order(id=4, orderNum=20200645, userId=1)])

双向关联查询

双向关联是通过 resultMap 在一方（User）使用 collection 标签、多方（Order）使用 association 标签来实现。同时通过 select 属性指向相应的查询。

映射文件

<!--OrderMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.OrderMapper">
    <resultMap id="order" type="Order">
        <id column="id" property="id" />
        <result column="ordernum" property="orderNum"/>
        <association column="userId"
                     property="user"
                     select="cn.dt.mybatis.mapper.UserMapper.findUserOrderById"
        />
    </resultMap>

    <select id="findById" resultMap="order">
        select * from order1 where id=#{id}
    </select>

</mapper>

<!--UserMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.UserMapper">
    <resultMap id="userAndOrder" type="User">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="pwd" column="pwd"/>

        <collection property="orderList"
                    ofType="Order"
                    column="id"
                    select="cn.dt.mybatis.mapper.OrderMapper.findByUserId"/>
    </resultMap>

    <select id="findById" resultMap="order">
        select * from order1 where id=#{id}
    </select>

    <select id="findByUserId"  resultMap="order">
        select * from order1 where userId=#{userId}
    </select>
</mapper>

测试代码


// OrderMapperTest.java

@Test
public void testFindByUserId() {
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    mapper.findByUserId(1).forEach(this::print);
}
@Test
public void testFindById(){
    OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
    Order       byId   = mapper.findById(1);
    print(byId);
}

输出结果

11:42:07.592 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - ==> Preparing: select * from order1 where userId=?
11:42:07.639 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - ==> Parameters: 1(Integer)
11:42:07.717 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - ==> Preparing: select * from user where id=?
11:42:07.718 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - > Parameters: 1(Integer)
11:42:07.721 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - < Total: 1
11:42:07.722 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - < Total: 2
11:42:07.723 [Test worker] INFO cn.dt.mybatis.mapper.OrderMapperTest - Order{id=1, orderNum=20200107, user=(1, 编程帮)}
11:42:07.725 [Test worker] INFO cn.dt.mybatis.mapper.OrderMapperTest - Order{id=4, orderNum=20200645, user=(1, 编程帮)}
…
11:42:07.746 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findById - ==> Preparing: select * from order1 where id=?
11:42:07.746 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findById - ==> Parameters: 1(Integer)
11:42:07.749 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - ====> Preparing: select * from user where id=?
11:42:07.750 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - ====> Parameters: 1(Integer)
11:42:07.753 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - > Preparing: select * from order1 where userId=?
11:42:07.753 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - > Parameters: 1(Integer)
11:42:07.756 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - < Total: 2
11:42:07.757 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - < Total: 1
11:42:07.757 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findById - < Total: 1
11:42:07.757 [Test worker] INFO cn.dt.mybatis.mapper.OrderMapperTest - Order{id=1, orderNum=20200107, user=(1, 编程帮)}

测试代码


// UserMapperTest.java
@Test
public void testFindUserById1() {
    UserMapper userMapper     = sqlSession.getMapper(UserMapper.class);
    User       userOrderById1 = userMapper.findUserOrderById(1);
    log.info("{}", userOrderById1);
}

输出结果

11:43:09.202 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - ==> Preparing: select * from user where id=?
11:43:09.256 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - ==> Parameters: 1(Integer)
11:43:09.335 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - ==> Preparing: select * from order1 where userId=?
11:43:09.336 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - > Parameters: 1(Integer)
11:43:09.340 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.findByUserId - < Total: 2
11:43:09.341 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.findUserOrderById - < Total: 1
11:43:09.342 [Test worker] INFO cn.dt.mybatis.mapper.UserMapperTest - User(id=1, name=编程帮, pwd=123, orderList=[Order{id=1, orderNum=20200107, user=(1, 编程帮)}, Order{id=4, orderNum=20200645, user=(1, 编程帮)}])

插入

映射文件

<!--OrderMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.OrderMapper">
    <insert id="addOrders" parameterType="java.util.List">
        insert into order1 (ordernum, userId) values
        <foreach collection="list" item="it" separator=",">
            (#{it.orderNum}, #{it.userId})
        </foreach>
    </insert>
</mapper>

<!--UserMapper.xml-->
<mapper namespace="cn.dt.mybatis.mapper.UserMapper">
    <insert id="addUser" keyProperty="id" useGeneratedKeys="true" parameterType="User">
        insert into User(name, pwd)
        values (#{name}, #{pwd})
    </insert>
</mapper>

测试代码

 @Test
public void testAddUserOrders() {

    try {
        User user = new User();
        user.setName("dt");
        user.setPwd("123");

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.addUser(user);

        List<Order> orders = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            Order o = new Order();
            o.setOrderNum(i);
            o.setUserId(user.getId());
            orders.add(o);
        }
        user.setOrderList(orders);

        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
        orderMapper.addOrders(orders);
    } catch (Exception e) {
        log.error("错误", e);
        sqlSession.rollback();
    }
}

输出结果

17:38:43.613 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.addUser - ==> Preparing: insert into User(name, pwd) values (?, ?)
17:38:43.664 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.addUser - > Parameters: dt(String), 123(String)
17:38:43.669 [Test worker] DEBUG cn.dt.mybatis.mapper.UserMapper.addUser - < Updates: 1
17:38:43.750 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.addOrders - ==> Preparing: insert into order1 (ordernum, userId) values (?, ?) , (?, ?) , (?, ?) , (?, ?) , (?, ?)
17:38:43.752 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.addOrders - > Parameters: 0(Integer), 14(Integer), 1(Integer), 14(Integer), 2(Integer), 14(Integer), 3(Integer), 14(Integer), 4(Integer), 14(Integer)
17:38:43.758 [Test worker] DEBUG cn.dt.mybatis.mapper.OrderMapper.addOrders - < Updates: 5