Mybatis一对多关联映射实例

女爷i 2022-06-15 06:30 202阅读 0赞

# Mybatis一对多关联映射实例 #

##### **需求：查询购买了商品的用户的信息和用户的订单信息以及订单的明细。      这句话有点绕，稍微捋一捋，假设天明在京东商城有过一次购物经历      这一次他买了三个旺仔小馒头和4瓶旺仔牛奶      现在的需求就是需要      （1）、查询出天明的信息      （2）、天明下的那个订单的信息      （3）以及订单里面购物清单的详细信息。**       #####

### 定义实体类       ###

#### 用户实体 ####

package com.hl.myabtis.first.beas;
    
    import java.util.Date;
    
    public class User { 
    
        private int id;
        private String username;
        private char sex;
        private Date birthday;
        private String address;
        public String getUsername() {
            return username;
        }
        public void setUsername(String username) {
            this.username = username;
        }
        public char getSex() {
            return sex;
        }
        public void setSex(char sex) {
            this.sex = sex;
        }
        public Date getBirthday() {
            return birthday;
        }
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
        public String getAddress() {
            return address;
        }
        public void setAddress(String address) {
            this.address = address;
        }
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        @Override
        public String toString() {
            return "User [id=" + id + ", username=" + username + ", sex=" + sex
                    + ", birthday=" + birthday + ", address=" + address + "]";
        }
    
    }

#### 订单实体 ####

package com.hl.myabtis.first.beas;
    
    import java.util.Date;
    import java.util.List;
    
    public class Orders { 
    
        private Integer id;
    
        private Integer user_id;
    
        private String number;
    
        private Date createtime;
    
        private String note;
    
        //用户信息
        private User user;
    
        //订单明细
        private List<Orderdetail> orderdetails;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getUserId() {
            return user_id;
        }
    
        public void setUserId(Integer userId) {
            this.user_id = userId;
        }
    
        public String getNumber() {
            return number;
        }
    
        public void setNumber(String number) {
            this.number = number;
        }
    
        public Date getCreatetime() {
            return createtime;
        }
    
        public void setCreatetime(Date createtime) {
            this.createtime = createtime;
        }
    
        public String getNote() {
            return note;
        }
    
        public void setNote(String note) {
            this.note = note;
        }
        public User getUser() {
            return user;
        }
    
        public void setUser(User user) {
            this.user = user;
        }
    
        public List<Orderdetail> getOrderdetailList() {
            return orderdetails;
        }
    
        public void setOrderdetailList(List<Orderdetail> orderdetails) {
            this.orderdetails = orderdetails;
        }
    
        @Override
        public String toString() {
            return "Orders [id=" + id + ", user_id=" + user_id + ", number="
                    + number + ", createtime=" + createtime + ", note=" + note
                    + ", user=" + user + ", orderdetails=" + orderdetails
                    + "]";
        }
    
    }

#### 订单明细实体 ####

package com.hl.myabtis.first.beas;
    /** * 订单明细 * @author 浪丶荡 * */
    public class Orderdetail { 
    
        private Integer id;
    
        private Integer orders_id;
    
        private Integer items_id;
    
        private Integer items_number;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getOrders_id() {
            return orders_id;
        }
    
        public void setOrders_id(Integer orders_id) {
            this.orders_id = orders_id;
        }
    
        public Integer getItems_id() {
            return items_id;
        }
    
        public void setItems_id(Integer items_id) {
            this.items_id = items_id;
        }
    
        public Integer getItems_number() {
            return items_number;
        }
    
        public void setItems_number(Integer items_number) {
            this.items_number = items_number;
        }
    
        @Override
        public String toString() {
            return "Orderdetail [id=" + id + ", orders_id=" + orders_id
                    + ", items_id=" + items_id + ", items_number=" + items_number
                    + "]";
        }
    
    }

### mapper映射文件 ###

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.hl.mybatis.first.mapper.OrdersCustomMapper">
    
        
        <resultMap type="user" id="userResultMap">
            
            <id column="user_id" javaType="java.lang.Integer"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </resultMap>
        
        <resultMap type="Orders" id="ordersResultMap">
            <id column="orderId" property="id"/>
            <result column="user_id" property="user_id"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
        </resultMap>
    
        
        <resultMap type="com.hl.myabtis.first.beas.Orderdetail" id="ordersDetailResultMap">
            
            <id column="orderdetail_id" property="id"/>
            <result column="itemsId" property="items_id"/>
            <result column="itemsNum" property="items_number"/>
            <result column="ordersID" property="orders_id"/>
        </resultMap>
    
        
        <resultMap type="com.hl.myabtis.first.beas.Orders" id="ordersUserResultMap">
            
            <id column="orderId" property="id"/>
            <result column="user_id" property="user_id"/>
            <result column="number" property="number"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
    
            <association property="user" resultMap="userResultMap" />
        </resultMap>
    
        
        <resultMap type="com.hl.myabtis.first.beas.Orders" id="ordersAndOrderdetailResultMap" extends="ordersUserResultMap">
            
            
            <collection property="orderdetails" resultMap="ordersDetailResultMap" />
        </resultMap>
    
        
        <select id="findOrdersAndOrderdetailResultMap" resultMap="ordersAndOrderdetailResultMap">
            SELECT
                o.id as orderId,
                o.note,
                o.createtime,
                o.number,
                o.user_id,
                u.address,
                u.sex,
                u.username,
                od.id AS orderdetail_id,
                od.items_id AS itemsId,
                od.items_num AS itemsNum,
                od.orders_id AS ordersID
            FROM
                USER u,
                orders o,
                orderdetail od
            WHERE
                o.user_id = u.id and o.id = od.orders_id
        </select>   
    </mapper>

### mapper接口 ###

public interface OrdersCustomMapper {
        //查询订单关联 查询用户信息以及订单明细 ResultMap映射
        public List<Orders> findOrdersAndOrderdetailResultMap() throws Exception;
    
    }

### 测试用例 ###

package com.hl.mybatis.first.test;
    
    import java.io.InputStream;
    import java.util.List;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.Before;
    import org.junit.Test;
    
    import com.hl.myabtis.first.beas.Orders;
    import com.hl.myabtis.first.beas.OrdersCustom;
    import com.hl.mybatis.first.mapper.OrdersCustomMapper;
    
    public class OrdersMapperTest {
    
        private SqlSessionFactory sqlSessionFactory;
    
        @Before
        public void testBefore() throws Exception{
    
            String resource = "SqlMapConfig.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource );
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }
        @Test
        public void testFindOrdersAndOrderdetailResultMap() throws Exception{
            SqlSession sqlSession = sqlSessionFactory.openSession();
            OrdersCustomMapper ordersMapper = sqlSession.getMapper(OrdersCustomMapper.class);
            List<Orders> list = ordersMapper.findOrdersAndOrderdetailResultMap();
            for (Orders order : list) {
                System.out.println(order);
            }
            sqlSession.close();
        }
    }

### 附上源码：[http://download.csdn.net/detail/leisure\_life/9852850][http_download.csdn.net_detail_leisure_life_9852850] ###

[http_download.csdn.net_detail_leisure_life_9852850]: http://download.csdn.net/detail/leisure_life/9852850