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

た入场券 2022-01-05 04:23 272阅读 0赞

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

创建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 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 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>
    
        
        <select id="selectUserById" resultMap="userResult">
            select * from t_user
            <where>
                id =#{id}
            </where>
        </select>
    
        
        <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>
    
        
        <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>
    
       
        <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}]}