Mybatis实践教程（八）-一对多的关联关系

创建一对多关系的数据表，我们在这里用用户和订单表来表示一对多关系，一个用户有多个订单，一个订单只对应一个用户。

创建t_user,t_order表

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `address` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址',
  `mobile_phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '联系电话',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_name` varchar(30) DEFAULT NULL,
  `user_id` bigint(15) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下面我们通过实例来表示Mybatis用户和订单的一对多关联关系。

需求：查询用户的所有订单信息。

创建实体对象

User

package com.example.springbootmybatis.domain;

import java.util.List;

public class User {
    private Long id;
    private String userName;
    private Integer age;
    private String mobilePhone;
    private String address;

    private List<Order> order;

    public String getMobilePhone() {
        return mobilePhone;
    }

    public Long getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getAge() {
        return age;
    }


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


    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", age=" + age +
                ", mobilePhone='" + mobilePhone + '\'' +
                ", address='" + address + '\'' +
                ", order=" + order +
                '}';
    }

    public void setMobilePhone(String mobilePhone) {
        this.mobilePhone = mobilePhone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

Order

package com.example.springbootmybatis.domain;

public class Order {

    private Long orderId;
    private String orderName;
    private Long userId;

    public Long getOrderId() {
        return orderId;
    }

    public void setOrderId(Long orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", userId=" + userId +
                '}';
    }
}

UserService实现查询订单信息的接口

package com.example.springbootmybatis.service;

import com.example.springbootmybatis.domain.User;

import java.util.List;
import java.util.Map;

public interface UserService {

    List<User> getAllUsers();

    User selectUserById(Integer id);

    List<User> getAllUsersByCond(Map<String,Object> map);

    int insertUser(User user);

    List<User> selectUserListBytDynamic(User user);
    
    int updateByDynamic(User user);

    List<User> getUserOrderList();
}

UserServiceImpl实现类

package com.example.springbootmybatis.service.impl;

import com.example.springbootmybatis.domain.User;
import com.example.springbootmybatis.mapper.UserMapper;
import com.example.springbootmybatis.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;


@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    @Override
    public List<User> getAllUsers() {
        return userMapper.selectUserList();
    }

    @Override
    public User selectUserById(Integer id) {
        return userMapper.selectUserById(id);
    }

    @Override
    public List<User> getAllUsersByCond(Map<String, Object> map) {
        return userMapper.getAllUsersByCond(map);
    }

    @Override
    public int insertUser(User user) {
        return userMapper.insertUser(user);
    }

    @Override
    public List<User> selectUserListBytDynamic(User user) {
        return userMapper.selectUserListBytDynamic(user);
    }

    @Override
    public int updateByDynamic(User user) {
        return userMapper.updateByDynamic(user);
    }

    @Override
    public List<User> getUserOrderList() {
        return userMapper.getUserOrderList();
    }
}

UserMapper接口类

package com.example.springbootmybatis.mapper;

import com.example.springbootmybatis.domain.User;

import java.util.List;
import java.util.Map;

public interface UserMapper {

    List<User> selectUserList ();

    User selectUserById(Integer id);

    List<User> getAllUsersByCond(Map<String,Object> map);

    int insertUser(User user);

    List<User> selectUserListBytDynamic(User user);

    int updateByDynamic(User user);

    List<User> getUserOrderList();

}

UserMapper.xml

collection标签设置对象中的集合对象，用于获取集合对象的数据信息。

<?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.springbootmybatis.mapper.UserMapper">
    <!-- 该resultMap用于实体对象User与表映射-->
    <resultMap id="userResult" type="com.example.springbootmybatis.domain.User">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="age" column="age"/>
        <result property="address" column="address"/>
        <result property="mobilePhone" column="mobile_phone"/>
    <!--    collection标签设置对象中的集合对象，用于获取集合对象的数据信息。-->
        <collection property="order" column="t_order" ofType="com.example.springbootmybatis.domain.Order">
            <id property="orderId"  column="order_id" />
            <result property="orderName" column="order_name"/>
            <result property="userId" column="user_id"/>
        </collection>
    </resultMap>

    <sql id="common">
        id,user_name,age,mobile_phone,address
    </sql>
    <!--查询所有用户信息-->
    <select id="selectUserList" resultMap="userResult">
        select <include refid="common"></include> from t_user
    </select>

    <!--   根据id查询单条用户信息-->
    <select id="selectUserById" resultMap="userResult">
        select * from t_user
        <where>
            id =#{id}
        </where>
    </select>

    <!-- 模糊查询 like查询-->
    <select id="getAllUsersByCond" parameterType="map" resultMap="userResult">
        select * from t_user
        <where>
            user_name like concat('%', #{userName}, '%')
        </where>
    </select>


    <!--添加一条用户信息-->
    <insert id="insertUser" parameterType="User">
           insert into t_user(user_name, age, address, mobile_phone)
               values(#{userName}, #{age}, #{address}, #{mobilePhone})
   </insert>

    <!--动态查询sql-->
    <select id="selectUserListBytDynamic" parameterType="User"
            resultMap="userResult">
        select <include refid="common"></include> from t_user
        <where>
            <if test="userName!=null">
                user_name like concat('%', #{userName}, '%')
            </if>
            <if test="age!=null and age!=0">
                and age =#{age}
            </if>
            <if test="address!=null and address !=''">
                and address like concat ('%',#{address},'%')
            </if>
            <if test="mobilePhone!=null and mobilePhone !=''">
                and mobile_phone like concat ('%',#{mobilePhone},'%')
            </if>
        </where>

    </select>

   <!-- 动态修改sql-->
    <update id="updateByDynamic" parameterType="User">
        update t_user
        <set>
            <if test="userName!=null">
                user_name =#{userName},
            </if>
            <if test="age!=null and age!=0">
                age =#{age},
            </if>
            <if test="address!=null and address !=''">
                address =#{address},
            </if>
            <if test="mobilePhone!=null and mobilePhone !=''">
               mobile_phone =#{mobilePhoe}
            </if>
        </set>
        where id = #{id}
    </update>

    <!--查询用户的所有订单信息 一对多关联关系-->
    <select id="getUserOrderList"  resultMap="userResult">
        SELECT * FROM t_user u inner
        join t_order o on u.id = o.user_id
    </select>
</mapper>

测试类 在MybatisTest类中添加方法

package com.example.springbootmybatis;

import com.example.springbootmybatis.domain.Husband;
import com.example.springbootmybatis.domain.User;
import com.example.springbootmybatis.service.HusBandService;
import com.example.springbootmybatis.service.UserService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisTest {
    Logger logger = LoggerFactory.getLogger(MybatisTest.class);
    @Resource
    private UserService userService;
    @Resource
    private HusBandService husBandService;

    /**
     * 查询所有用户信息
     */
    @Test
    public void getAllUsers() {
        List<User> userList = userService.getAllUsers();
        for (User user : userList) {
            System.out.println(user);
        }
    }

    /**
     * 根据id查询单条用户信息
     */
    @Test
    public void selectUserById() {
        User user = userService.selectUserById(1);
        System.out.println(user);
    }

    /**
     * 模糊查询
     */
    @Test
    public void getAllUsersByCond() {
        Map<String, Object> map = new HashMap<>();
        map.put("userName", "a");
        List<User> userList = userService.getAllUsersByCond(map);
        for (User user : userList) {
            System.out.println(user);
        }
    }

    /**
     * 添加数据
     */
    @Test
    public void insertUserInfo() {
        User user = new User();
        user.setAge(20);
        user.setMobilePhone("18599208271");
        user.setUserName("hello test");
        user.setAddress("test address");
        userService.insertUser(user);

    }

    /**
     * 动态查询sql
     */
    @Test
    public void selectUserListBytDynamic() {
        User user = new User();
        user.setUserName("hello");
        user.setAddress("address");
        List<User> userList = userService.selectUserListBytDynamic(user);
        for (User userResult : userList) {
            System.out.println(userResult.toString());
        }
    }

    /**
     * 动态修改sql
     */
    @Test
    public void updateByDynamic(){
        User user = new User();
        user.setId(2L);
        user.setUserName("update user");
        user.setAddress("update address");
        userService.updateByDynamic(user) ;
    }

    /**
     * 一对一关联关系查询
     */
    @Test
    public void one2oneMapping(){
        List<Husband> list = husBandService.one2oneMapping();
        for (Husband husbandRes:list) {
            System.out.println(husbandRes.toString());
        }
    }


    /**
     * 一对多关联关系查询
     */
    @Test
    public void getUserOrderList(){
        List<User> list = userService.getUserOrderList();
        for (User user:list) {
            System.out.println(user.toString());
        }
    }
}

执行测试类结果

User{id=1, userName='admin', age=25, mobilePhone='13001092823', address='beijing', order=[Order{orderId=1, orderName='商品1', userId=1}, Order{orderId=2, orderName='商品2', userId=1}, Order{orderId=3, orderName='商品3', userId=1}]}
User{id=2, userName='update user', age=34, mobilePhone='18888888888', address='update address', order=[Order{orderId=4, orderName='商品4', userId=2}]}