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
官网itzixi.com
微信公众号:BeJavaGod
新浪微博
知乎
简书
cnblogs
今日头条
豆瓣
--> 同步更新