Mybatis学习（5）高级映射

需求：

一、一对一查询

查询订单信息，关联查询创建订单的用户信息；

orders--->user：一个订单只由一个用户创建，一对一

orders表和 user表：

1）使用resultType方法：

思路: 查询订单的同时，关联查询用户信息；创建Order的扩展类，包含订单信息、用户信息；

pojo -- Orders：

 1 package com.cy.po;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class Orders {
 7     private Integer id;
 8 
 9     private Integer userId;
10 
11     private String number;
12 
13     private Date createtime;
14 
15     private String note;
16 
17     public Integer getId() {
18         return id;
19     }
20 
21     public void setId(Integer id) {
22         this.id = id;
23     }
24 
25     public Integer getUserId() {
26         return userId;
27     }
28 
29     public void setUserId(Integer userId) {
30         this.userId = userId;
31     }
32 
33     public String getNumber() {
34         return number;
35     }
36 
37     public void setNumber(String number) {
38         this.number = number == null ? null : number.trim();
39     }
40 
41     public Date getCreatetime() {
42         return createtime;
43     }
44 
45     public void setCreatetime(Date createtime) {
46         this.createtime = createtime;
47     }
48 
49     public String getNote() {
50         return note;
51     }
52 
53     public void setNote(String note) {
54         this.note = note == null ? null : note.trim();
55     }
56 }

Orders

pojo-- User：

 1 package com.cy.po;
 2 
 3 import java.util.Date;
 4 
 5 public class User {
 6     //属性名和数据库表的字段对应
 7         private int id;
 8         private String username;// 用户姓名
 9         private String sex;// 性别
10         private Date birthday;// 生日
11         private String address;// 地址
12         public int getId() {
13             return id;
14         }
15         public void setId(int id) {
16             this.id = id;
17         }
18         public String getUsername() {
19             return username;
20         }
21         public void setUsername(String username) {
22             this.username = username;
23         }
24         public String getSex() {
25             return sex;
26         }
27         public void setSex(String sex) {
28             this.sex = sex;
29         }
30         public Date getBirthday() {
31             return birthday;
32         }
33         public void setBirthday(Date birthday) {
34             this.birthday = birthday;
35         }
36         public String getAddress() {
37             return address;
38         }
39         public void setAddress(String address) {
40             this.address = address;
41         }
42         @Override
43         public String toString() {
44             return "------->> User [id=" + id + ", username=" + username + ", sex=" + sex
45                     + ", birthday=" + birthday + ", address=" + address + "]";
46         }
47 }

User

pojo -- Order扩展类 OrdersCustom：

 1 package com.cy.po;
 2 
 3 /**
 4  * 
 5  * <p>Description: 订单的扩展类</p>
 6  */
 7 //通过此类映射订单和用户查询的结果，让此类继承包括 字段较多的pojo类
 8 public class OrdersCustom extends Orders{
 9     
10     //添加用户属性
11     /*USER.username,
12       USER.sex,
13       USER.address */
14     private String username;
15     private String sex;
16     private String address;
17     public String getUsername() {
18         return username;
19     }
20     public void setUsername(String username) {
21         this.username = username;
22     }
23     public String getSex() {
24         return sex;
25     }
26     public void setSex(String sex) {
27         this.sex = sex;
28     }
29     public String getAddress() {
30         return address;
31     }
32     public void setAddress(String address) {
33         this.address = address;
34     }
35     
36 }

OrdersCustom

Mapper.xml:

<mapper namespace="com.cy.mapper.OrdersMapperCustom">

    <!-- 查询订单关联查询用户信息 -->
    <select id="findOrdersUser" resultType="com.cy.po.OrdersCustom">
        SELECT orders.*,USER.username,USER.sex,USER.address
        FROM orders,USER
        WHERE orders.user_id = user.id
    </select>
    
</mapper>

Mapper接口：

/**
 * 
 * <p>Title: OrdersMapperCustom</p>
 * <p>Description: 订单mapper</p>
 */
public interface OrdersMapperCustom {
    
    //查询订单关联查询用户信息
    public List<OrdersCustom> findOrdersUser()throws Exception;
    
}

测试代码：

 1 public class OrdersMapperCustomTest {
 2     private SqlSessionFactory sqlSessionFactory;
 3     
 4     @Before
 5     public void setUp() throws Exception {
 6         String resource = "SqlMapConfig.xml";
 7         InputStream inputStream = Resources.getResourceAsStream(resource);
 8         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 9     }
10 
11     @Test
12     public void testFindOrdersUser() throws Exception {
13         SqlSession sqlSession = sqlSessionFactory.openSession();
14         OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
15         List<OrdersCustom> list = ordersMapperCustom.findOrdersUser();
16         System.out.println(list.toString());
17         sqlSession.close();
18     }
19 
20 }

View Code

2）使用resultMap方法：

思路：使用resultMap将查询结果中的订单信息映射到Orders对象中，在orders类中添加User属性，将关联查询出来的用户信息映射到orders对象中的user属性中。

Orders中保存user：

 1 package com.cy.po;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class Orders {
 7     private Integer id;
 8 
 9     private Integer userId;
10 
11     private String number;
12 
13     private Date createtime;
14 
15     private String note;
16     
17     private User user;
18 
19     public User getUser() {
20         return user;
21     }
22 
23     public void setUser(User user) {
24         this.user = user;
25     }
26 
27     public Integer getId() {
28         return id;
29     }
30 
31     public void setId(Integer id) {
32         this.id = id;
33     }
34 
35     public Integer getUserId() {
36         return userId;
37     }
38 
39     public void setUserId(Integer userId) {
40         this.userId = userId;
41     }
42 
43     public String getNumber() {
44         return number;
45     }
46 
47     public void setNumber(String number) {
48         this.number = number == null ? null : number.trim();
49     }
50 
51     public Date getCreatetime() {
52         return createtime;
53     }
54 
55     public void setCreatetime(Date createtime) {
56         this.createtime = createtime;
57     }
58 
59     public String getNote() {
60         return note;
61     }
62 
63     public void setNote(String note) {
64         this.note = note == null ? null : note.trim();
65     }
66 }

包含user的Orders

Mapper.xml:

<mapper namespace="com.cy.mapper.OrdersMapperCustom">
    <!-- 订单查询关联用户的resultMap 将整个查询的结果映射到com.cy.po.Orders中-->
    <resultMap id="OrdersUserResultMap" type="com.cy.po.Orders">
        <!-- 配置映射的订单信息 -->
        <!-- id：指定查询列中的唯 一标识，订单信息的中的唯 一标识，如果有多个列组成唯一标识，配置多个id
            column：订单信息的唯一标识列
            property：订单信息的唯一标识 列所映射到Orders中哪个属性
          -->
        <id column="id" property="id"></id>
        <result column="user_id" property="userId"></result>
        <result column="number" property="number"></result>
        <result column="createtime" property="createtime"></result>
        <result column="note" property="note"></result>
        <!-- 配置映射的关联的用户信息 -->
        <!-- association：用于映射关联查询单个对象的信息  property：要将关联查询的用户信息映射到Orders中哪个属性 -->
        <association property="user" javaType="com.cy.po.User">
            <!-- id：关联查询用户的唯 一标识
            column：指定唯一标识用户信息的列  property：映射到user的哪个属性 --> 
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association>
    </resultMap>
    
    <!-- 查询订单关联查询用户信息，使用resultmap -->
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
        SELECT
        orders.*,
        USER.username,
        USER.sex,
        USER.address
        FROM
        orders,
        USER
        WHERE orders.user_id = user.id
    </select>
    
</mapper>

mapper接口：

//查询订单关联查询用户使用resultMap
public List<Orders> findOrdersUserResultMap()throws Exception;

测试代码：

@Test
    public void testFindOrdersUserResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        
        List<Orders> orders = ordersMapperCustom.findOrdersUserResultMap();
        System.out.println(orders);
        sqlSession.close();
    }

使用resultType和resultMap实现一对一查询的总结：

resultType：使用resultType实现较为简单，如果pojo中没有包括查询出来的列名，需要增加列名对应的属性，即可完成映射。

resultMap：需要单独定义resultMap，如果对查询结果有特殊的要求，使用resultMap可以完成将关联查询映射pojo的属性中。

resultMap可以实现延迟加载，resultType无法实现延迟加载。

二、一对多查询

需求：查询订单及订单明细的信息。

在orders.java类中添加List<orderDetail> orderDetails属性：

 1 package com.cy.po;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class Orders {
 7     private Integer id;
 8 
 9     private Integer userId;
10 
11     private String number;
12 
13     private Date createtime;
14 
15     private String note;
16     
17     //用户信息
18     private User user;
19     
20     private List<Orderdetail> orderdetails;
21     
22     public List<Orderdetail> getOrderdetails() {
23         return orderdetails;
24     }
25 
26     public void setOrderdetails(List<Orderdetail> orderdetails) {
27         this.orderdetails = orderdetails;
28     }
29 
30     public User getUser() {
31         return user;
32     }
33 
34     public void setUser(User user) {
35         this.user = user;
36     }
37 
38     public Integer getId() {
39         return id;
40     }
41 
42     public void setId(Integer id) {
43         this.id = id;
44     }
45 
46     public Integer getUserId() {
47         return userId;
48     }
49 
50     public void setUserId(Integer userId) {
51         this.userId = userId;
52     }
53 
54     public String getNumber() {
55         return number;
56     }
57 
58     public void setNumber(String number) {
59         this.number = number == null ? null : number.trim();
60     }
61 
62     public Date getCreatetime() {
63         return createtime;
64     }
65 
66     public void setCreatetime(Date createtime) {
67         this.createtime = createtime;
68     }
69 
70     public String getNote() {
71         return note;
72     }
73 
74     public void setNote(String note) {
75         this.note = note == null ? null : note.trim();
76     }
77 }

Orders中包含Orderdetail的集合属性

 1 package com.cy.po;
 2 
 3 public class Orderdetail {
 4     private Integer id;
 5 
 6     private Integer ordersId;
 7 
 8     private Integer itemsId;
 9 
10     private Integer itemsNum;
11     
12     //明细对应的商品信息
13     private Items items;
14 
15     public Integer getId() {
16         return id;
17     }
18 
19     public void setId(Integer id) {
20         this.id = id;
21     }
22 
23     public Integer getOrdersId() {
24         return ordersId;
25     }
26 
27     public void setOrdersId(Integer ordersId) {
28         this.ordersId = ordersId;
29     }
30 
31     public Integer getItemsId() {
32         return itemsId;
33     }
34 
35     public void setItemsId(Integer itemsId) {
36         this.itemsId = itemsId;
37     }
38 
39     public Integer getItemsNum() {
40         return itemsNum;
41     }
42 
43     public void setItemsNum(Integer itemsNum) {
44         this.itemsNum = itemsNum;
45     }
46 
47     public Items getItems() {
48         return items;
49     }
50 
51     public void setItems(Items items) {
52         this.items = items;
53     }
54 
55     @Override
56     public String toString() {
57         return "Orderdetail [id=" + id + ", ordersId=" + ordersId
58                 + ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
59     }
60     
61 }

订单明细类Orderdetail

mapper.xml:

 1 <mapper namespace="com.cy.mapper.OrdersMapperCustom">
 2     <!-- 订单及订单明细的resultMap 使用extends继承，不用在中配置订单信息和用户信息的映射 -->
 3     <resultMap id="OrdersAndOrderDetailResultMap" type="com.cy.po.Orders" extends="OrdersUserResultMap" >
 4         <!-- 订单信息 --><!-- 用户信息 --><!-- 使用extends继承，不用在中配置订单信息和用户信息的映射 -->
 5         
 6         <!-- 订单明细信息 一个订单关联查询出了多条明细，要使用collection进行映射
 7         collection：对关联查询到多条记录映射到集合对象中
 8         property：将关联查询到多条记录映射到com.cy.po.Orders哪个属性
 9         ofType：指定映射到list集合属性中pojo的类型
10          -->
11         <collection property="orderdetails" ofType="com.cy.po.Orderdetail">
12             <!-- id：订单明细唯一标识 property:要将订单明细的唯 一标识映射到com.cy.po.Orderdetail的哪个属性 -->
13             <id column="orderdetail_id" property="id"></id>
14             <result column="items_id" property="itemsId"/>
15             <result column="items_num" property="itemsNum"/>
16             <result column="orders_id" property="ordersId"/>
17         </collection>
18     </resultMap>
19     <!-- 查询订单关联查询用户及订单明细，使用resultmap -->
20     <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
21         SELECT 
22           orders.*,
23           USER.username,
24           USER.sex,
25           USER.address,
26           orderdetail.id orderdetail_id,
27           orderdetail.items_id,
28           orderdetail.items_num,
29           orderdetail.orders_id
30         FROM
31           orders,
32           USER,
33           orderdetail
34         WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
35     </select>
36     
37 </mapper>

mapper接口：

//查询订单(关联用户)及订单明细
public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;

测试代码：

1 @Test
2     public void FindOrdersAndOrderDetailResultMap() throws Exception {
3         SqlSession sqlSession = sqlSessionFactory.openSession();
4         OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
5         
6         List<Orders> orders = ordersMapperCustom.findOrdersAndOrderDetailResultMap();
7         System.out.println(orders);
8         sqlSession.close();
9     }

View Code

三、多对多查询

需求：查询用户及用户购买商品信息

思路：

将用户信息映射到user中。

在user类中添加订单列表属性List<Orders> orderslist，将用户创建的订单映射到orderslist

在Orders中添加订单明细列表属性List<OrderDetail>orderdetials，将订单的明细映射到orderdetials

在OrderDetail中添加Items属性，将订单明细所对应的商品映射到Items

在上面代码基础上，User类中添加List<Orders> orderslist:

 1 package com.cy.po;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class User {
 7     //属性名和数据库表的字段对应
 8         private int id;
 9         private String username;// 用户姓名
10         private String sex;// 性别
11         private Date birthday;// 生日
12         private String address;// 地址
13         
14         private List<Orders> orderslist;
15         
16         public List<Orders> getOrderslist() {
17             return orderslist;
18         }
19         public void setOrderslist(List<Orders> orderslist) {
20             this.orderslist = orderslist;
21         }
22         public int getId() {
23             return id;
24         }
25         public void setId(int id) {
26             this.id = id;
27         }
28         public String getUsername() {
29             return username;
30         }
31         public void setUsername(String username) {
32             this.username = username;
33         }
34         public String getSex() {
35             return sex;
36         }
37         public void setSex(String sex) {
38             this.sex = sex;
39         }
40         public Date getBirthday() {
41             return birthday;
42         }
43         public void setBirthday(Date birthday) {
44             this.birthday = birthday;
45         }
46         public String getAddress() {
47             return address;
48         }
49         public void setAddress(String address) {
50             this.address = address;
51         }
52         @Override
53         public String toString() {
54             return "------->> User [id=" + id + ", username=" + username + ", sex=" + sex
55                     + ", birthday=" + birthday + ", address=" + address + "]";
56         }
57 }

User中保存List

mapper.xml:

<mapper namespace="com.cy.mapper.OrdersMapperCustom">
    <!-- 查询用户及购买的商品 -->
    <resultMap type="com.cy.po.User" id="UserAndItemsResultMap">
        <!-- 用户信息 -->
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        
        <!-- 订单信息 一个用户对应多个订单，使用collection映射-->
        <collection property="orderslist" ofType="com.cy.po.Orders">
            <id column="id" property="id"></id>
            <result column="user_id" property="userId"></result>
            <result column="number" property="number"></result>
            <result column="createtime" property="createtime"></result>
            <result column="note" property="note"></result>
            <!-- 订单明细 一个订单包括 多个明细-->
            <collection property="orderdetails" ofType="com.cy.po.Orderdetail">
                <id column="orderdetail_id" property="id"></id>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>
                <!-- 商品信息一个订单明细对应一个商品-->
                <association property="items" javaType="com.cy.po.Items">
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_detail" property="detail"/>
                    <result column="items_price" property="price"/>
                </association>
            </collection>
        </collection>
    </resultMap>
    <!-- 查询用户及购买的商品信息，使用resultmap -->
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
        SELECT 
          orders.*,
          USER.username,
          USER.sex,
          USER.address,
          orderdetail.id orderdetail_id,
          orderdetail.items_id,
          orderdetail.items_num,
          orderdetail.orders_id,
          items.name items_name,
          items.detail items_detail,
          items.price items_price
        FROM
          orders,
          USER,
          orderdetail,
          items
        WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id
    </select>
    
</mapper>

上面，其实思路就是用户中保存List<Orders>,然后Order中有多个订单明细List<Orderdetail>;一个订单明细对应一个商品，private Item;

顺着写来来就是了，不难。

mapper接口：

//查询用户购买商品信息
public List<User> findUserAndItemsResultMap() throws Exception;

测试代码：

@Test
    public void FindUserAndItemsResultMap() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        
        List<User> users = ordersMapperCustom.findUserAndItemsResultMap();
        System.out.println(users);
        sqlSession.close();
    }

View Code

多对多查询总结：

将查询用户购买的商品信息明细清单，（用户名、用户地址、购买商品名称、购买商品时间、购买商品数量）

针对上边的需求就使用resultType将查询到的记录映射到一个扩展的pojo中，很简单实现明细清单的功能。

posted on 2017-01-15 01:53 有点懒惰的大青年阅读(256) 评论(0) 编辑收藏举报