db.properties
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://localhost:3306/order 3 jdbc.username=root 4 jdbc.password=root
SqlMapConfig.xml
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>
OrderMapper.xml
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>
OrderMapper.java
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 }
Items.java
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 }
Orderdetail.java
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 }
Orders.java
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 }
OrdersCustom.java
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 }
OrderUserCustom.java
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 }
User.java
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 }
github地址：https://github.com/leechenxiang/mybatis003-order-reference-query
