mybatis 一二事（3） - 多表关联查询

1 jdbc.driver=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/order
3 jdbc.username=root
4 jdbc.password=root

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4 "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <!-- 加载数据库连接参数配置文件 -->
 7     <properties resource="db.properties" />
 8     
 9     <!-- 
10         全局配置参数
11         比如 二级缓存 延迟加载...等
12         此全局参数会影响mybatis运行的性能，要谨慎配置    
13      -->
14 <!--     <settings> -->
15 <!--     <setting name="" value=""/> -->
16 <!--     </settings> -->
17     
18     <!-- 定义别名 -->
19     <typeAliases>
20         <!-- 单个别名定义
21         type：pojo的路径 
22         alias：别名的名称
23         -->
24         <!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->
25         <!-- 批量别名定义
26         name：指定包名，将包下边的所有pojo定义别名 ，别名为类名（首字母大写或小写都行）
27          -->
28         <package name="com.mybatis.bean"/>
29     </typeAliases>
30     
31     <!-- 和spring整合后 environments配置将废除 -->
32     <environments default="development">
33         <environment id="development">
34             <transactionManager type="JDBC" />
35             <dataSource type="POOLED">
36                 <property name="driver" value="${jdbc.driver}"/>
37                 <property name="url" value="${jdbc.url}"/>
38                 <property name="username" value="${jdbc.username}"/>
39                 <property name="password" value="${jdbc.password}"/>
40             </dataSource>
41         </environment>
42     </environments>
43 
44     <!-- 配置mapper映射文件 -->
45     <mappers>
46         <!-- resource方式
47         在UserMapper.xml，定义namespace为mapper接口的地址，映射文件通过namespace找到对应的mapper接口文件
48          -->
49         <!-- <mapper resource="sqlmap/UserMapper.xml" /> -->
50         <!-- class方式
51         class：指定 mapper接口的地址
52         遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
53          -->
54         <!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> -->
55         
56         <!--
57             批量mapper扫描
58             遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
59             主要以这样的方式为主来加载mapper
60           -->
61         <package name="com.mybatis.mapper"/>
62         
63         
64     </mappers>
65 </configuration>

  1 <?xml version="1.0" encoding="UTF-8" ?>
  2 <!DOCTYPE mapper
  3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5 
  6 <mapper namespace="com.mybatis.mapper.OrderMapper" >
  7     
  8     <!-- 
  9         ********
 10         1 对 1 查询建议使用 resultType
 11         ********
 12      -->
 13     
 14     <!-- type 为主类，即A类包含了B类作为属性，则type就写A类 -->
 15     <resultMap type="Orders" id="orderUserResultMap">
 16         <!-- 
 17             id 和 result 这两个标签是映射到 Orders 中去的 
 18             如果有多个字段决定id唯一，则写多个id标签
 19         -->
 20         <id column="id" property="id"/>
 21         <result column="order_number" property="order_number"/>
 22         <result column="user_id" property="user_id"/>
 23 
 24         <!-- 
 25             用于映射单个关联对象
 26             property: 将关联信息映射到哪个属性
 27             javaType: 属性映射的类型
 28          -->
 29         <association property="user" javaType="com.mybatis.bean.User">
 30             <id column="user_id" property="id"/>
 31             <result column="username" property="username"/>
 32             <result column="birthday" property="birthday"/>
 33         </association>
 34     </resultMap>    
 35 
 36 
 37     <!-- 
 38         extends： 继承上一个resultMap, 公用相同属性
 39      -->
 40     <resultMap type="Orders" id="orderUserDetailResultMap" extends="orderUserResultMap">
 41 
 42         <!-- 
 43             collection： 用于映射集合对象
 44             property: 将集合信息映射到哪个属性
 45             ofType: 这个集合对象的类型，也就是<>泛型
 46          -->
 47         <collection property="orderdetails" ofType="com.mybatis.bean.Orderdetail">
 48             <id column="order_detail_id" property="id"/>
 49             <result column="item_id" property="item_id"/>
 50             <result column="item_num" property="item_num"/>
 51             <result column="item_price" property="item_price"/>
 52         </collection>
 53 
 54     </resultMap>    
 55 
 56     <!-- 
 57         1 对 1 查询
 58      -->
 59     <select id="findOrderUserList" resultType="OrderUserCustom">
 60         select 
 61             o.id,
 62             o.order_number,
 63             o.user_id,
 64             u.username,
 65             u.birthday
 66         from 
 67             orders o 
 68         inner join 
 69             user u 
 70         on 
 71             o.user_id = u.id
 72     </select>
 73 
 74     <select id="findOrderUserListByResultMap" resultMap="orderUserResultMap">
 75         select 
 76             o.id,
 77             o.order_number,
 78             o.user_id,
 79             u.username,
 80             u.birthday
 81         from 
 82             orders o 
 83         inner join 
 84             user u 
 85         on 
 86             o.user_id = u.id
 87     </select>
 88     
 89     <!-- 
 90         1 对 多 查询
 91      -->
 92      <select id="findOrderItemsList" resultMap="orderUserDetailResultMap">
 93          select 
 94                 o.id,
 95                 o.order_number,
 96                 o.user_id,
 97                 u.username,
 98                 u.birthday,
 99                 od.item_id,
100                 od.item_num,
101                 od.item_price,
102                 od.id as order_detail_id
103             from 
104                 orders o 
105             inner join 
106                 user u 
107             on 
108                 o.user_id = u.id 
109             left join 
110                 orderdetail od 
111             on 
112                 o.id = od.orders_id
113     </select>
114 </mapper>

 1 package com.mybatis.mapper;
 2 
 3 import java.util.List;
 4 
 5 import com.mybatis.bean.OrderUserCustom;
 6 import com.mybatis.bean.Orders;
 7 
 8 public interface OrderMapper {
 9     
10     public List<OrderUserCustom> findOrderUserList() throws Exception;
11     
12     public List<Orders> findOrderUserListByResultMap() throws Exception;
13     
14     public List<Orders> findOrderItemsList() throws Exception;
15     
16 }

 1 package com.mybatis.bean;
 2 
 3 /**
 4  * 商品信息
 5  * 
 6  * @author Thinkpad
 7  *
 8  */
 9 public class Items {
10     private int id;// 商品id
11     private String item_name;// 商品名称
12     private Float item_price;// 商品价格
13     private String item_detail;// 商品明细
14 
15     public int getId() {
16         return id;
17     }
18 
19     public void setId(int id) {
20         this.id = id;
21     }
22 
23     public String getItem_name() {
24         return item_name;
25     }
26 
27     public void setItem_name(String item_name) {
28         this.item_name = item_name;
29     }
30 
31     public Float getItem_price() {
32         return item_price;
33     }
34 
35     public void setItem_price(Float item_price) {
36         this.item_price = item_price;
37     }
38 
39     public String getItem_detail() {
40         return item_detail;
41     }
42 
43     public void setItem_detail(String item_detail) {
44         this.item_detail = item_detail;
45     }
46 
47 }

 1 package com.mybatis.bean;
 2 
 3 /**
 4  * 订单明细
 5  * 
 6  * @author Thinkpad
 7  *
 8  */
 9 public class Orderdetail {
10     private int id;// 主键
11     private int orders_id;// 订单id
12     private int item_id;// 商品id
13     private int item_num;// 商品数量
14     private Float item_price;// 商品价格
15 
16     // 商品信息
17     private Items items;// 明细对应的商品信息
18 
19     public int getId() {
20         return id;
21     }
22 
23     public void setId(int id) {
24         this.id = id;
25     }
26 
27     public int getOrders_id() {
28         return orders_id;
29     }
30 
31     public void setOrders_id(int orders_id) {
32         this.orders_id = orders_id;
33     }
34 
35     public int getItem_id() {
36         return item_id;
37     }
38 
39     public void setItem_id(int item_id) {
40         this.item_id = item_id;
41     }
42 
43     public int getItem_num() {
44         return item_num;
45     }
46 
47     public void setItem_num(int item_num) {
48         this.item_num = item_num;
49     }
50 
51     public Float getItem_price() {
52         return item_price;
53     }
54 
55     public void setItem_price(Float item_price) {
56         this.item_price = item_price;
57     }
58 
59     public Items getItems() {
60         return items;
61     }
62 
63     public void setItems(Items items) {
64         this.items = items;
65     }
66 
67 }

 1 package com.mybatis.bean;
 2 
 3 import java.util.List;
 4 
 5 /**
 6  * 订单信息
 7  * 
 8  * @author Thinkpad
 9  *
10  */
11 public class Orders {
12     private int id;// 订单id
13     private int user_id;// 用户id
14     private String order_number;// 订单号
15 
16     private User user;// 用户信息
17 
18     private List<Orderdetail> orderdetails;// 订单明细信息
19 
20     public int getId() {
21         return id;
22     }
23 
24     public void setId(int id) {
25         this.id = id;
26     }
27 
28     public int getUser_id() {
29         return user_id;
30     }
31 
32     public void setUser_id(int user_id) {
33         this.user_id = user_id;
34     }
35 
36     public String getOrder_number() {
37         return order_number;
38     }
39 
40     public void setOrder_number(String order_number) {
41         this.order_number = order_number;
42     }
43 
44     public User getUser() {
45         return user;
46     }
47 
48     public void setUser(User user) {
49         this.user = user;
50     }
51 
52     public List<Orderdetail> getOrderdetails() {
53         return orderdetails;
54     }
55 
56     public void setOrderdetails(List<Orderdetail> orderdetails) {
57         this.orderdetails = orderdetails;
58     }
59 
60 }

 1 package com.mybatis.bean;
 2 
 3 public class OrdersCustom extends User {
 4 
 5     private String user_id;// 用户id
 6     private String order_number;// 订单号
 7 
 8     public String getUser_id() {
 9         return user_id;
10     }
11 
12     public void setUser_id(String user_id) {
13         this.user_id = user_id;
14     }
15 
16     public String getOrder_number() {
17         return order_number;
18     }
19 
20     public void setOrder_number(String order_number) {
21         this.order_number = order_number;
22     }
23 
24 }

 1 package com.mybatis.bean;
 2 
 3 import java.util.Date;
 4 
 5 public class OrderUserCustom extends Orders {
 6 
 7     private String username;
 8     private Date birthday;
 9     public String getUsername() {
10         return username;
11     }
12     public void setUsername(String username) {
13         this.username = username;
14     }
15     public Date getBirthday() {
16         return birthday;
17     }
18     public void setBirthday(Date birthday) {
19         this.birthday = birthday;
20     }
21 
22 }

 1 package com.mybatis.bean;
 2 
 3 import java.util.Date;
 4 import java.util.List;
 5 
 6 public class User implements java.io.Serializable {
 7     private int id;
 8     private int[] ids;// 存储多个id
 9     private String username;// 用户姓名
10     private String sex;// 性别
11     private Date birthday;// 出生日期
12     private String address;// 地址
13     private String detail;// 详细信息
14     private Float score;// 成绩
15 
16     // 订单信息
17     private List<Orders> orders;
18 
19     public int getId() {
20         return id;
21     }
22 
23     public void setId(int id) {
24         this.id = id;
25     }
26 
27     public String getUsername() {
28         return username;
29     }
30 
31     public void setUsername(String username) {
32         this.username = username;
33     }
34 
35     public String getSex() {
36         return sex;
37     }
38 
39     public void setSex(String sex) {
40         this.sex = sex;
41     }
42 
43     public Date getBirthday() {
44         return birthday;
45     }
46 
47     public void setBirthday(Date birthday) {
48         this.birthday = birthday;
49     }
50 
51     public String getAddress() {
52         return address;
53     }
54 
55     public void setAddress(String address) {
56         this.address = address;
57     }
58 
59     public String getDetail() {
60         return detail;
61     }
62 
63     public void setDetail(String detail) {
64         this.detail = detail;
65     }
66 
67     public Float getScore() {
68         return score;
69     }
70 
71     public void setScore(Float score) {
72         this.score = score;
73     }
74 
75     @Override
76     public String toString() {
77         return "User [id=" + id + ", username=" + username + ", sex=" + sex
78                 + ", birthday=" + birthday + ", address=" + address
79                 + ", detail=" + detail + ", score=" + score + "]";
80     }
81 
82     public List<Orders> getOrders() {
83         return orders;
84     }
85 
86     public void setOrders(List<Orders> orders) {
87         this.orders = orders;
88     }
89 
90     public int[] getIds() {
91         return ids;
92     }
93 
94     public void setIds(int[] ids) {
95         this.ids = ids;
96     }
97 
98 }