db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/order
jdbc.username=root
jdbc.password=root
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 加载数据库连接参数配置文件 -->
<properties resource="db.properties" /> <!--
全局配置参数
比如 二级缓存 延迟加载...等
此全局参数会影响mybatis运行的性能，要谨慎配置
-->
<!-- <settings> -->
<!-- <setting name="" value=""/> -->
<!-- </settings> --> <!-- 定义别名 -->
<typeAliases>
<!-- 单个别名定义
type：pojo的路径
alias：别名的名称
-->
<!-- <typeAlias type="cn.itcast.mybatis.po.User" alias="user"/> -->
<!-- 批量别名定义
name：指定包名，将包下边的所有pojo定义别名 ，别名为类名（首字母大写或小写都行）
-->
<package name="com.mybatis.bean"/>
</typeAliases> <!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments> <!-- 配置mapper映射文件 -->
<mappers>
<!-- resource方式
在UserMapper.xml，定义namespace为mapper接口的地址，映射文件通过namespace找到对应的mapper接口文件
-->
<!-- <mapper resource="sqlmap/UserMapper.xml" /> -->
<!-- class方式
class：指定 mapper接口的地址
遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
-->
<!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> --> <!--
批量mapper扫描
遵循规则：将mapper.xml和mapper.java文件放在一个目录 且文件名相同
主要以这样的方式为主来加载mapper
-->
<package name="com.mybatis.mapper"/> </mappers>
</configuration>
OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mybatis.mapper.OrderMapper" > <!--
********
1 对 1 查询建议使用 resultType
********
--> <!-- type 为主类，即A类包含了B类作为属性，则type就写A类 -->
<resultMap type="Orders" id="orderUserResultMap">
<!--
id 和 result 这两个标签是映射到 Orders 中去的
如果有多个字段决定id唯一，则写多个id标签
-->
<id column="id" property="id"/>
<result column="order_number" property="order_number"/>
<result column="user_id" property="user_id"/> <!--
用于映射单个关联对象
property: 将关联信息映射到哪个属性
javaType: 属性映射的类型
-->
<association property="user" javaType="com.mybatis.bean.User">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
</association>
</resultMap> <!--
extends： 继承上一个resultMap, 公用相同属性
-->
<resultMap type="Orders" id="orderUserDetailResultMap" extends="orderUserResultMap"> <!--
collection： 用于映射集合对象
property: 将集合信息映射到哪个属性
ofType: 这个集合对象的类型，也就是<>泛型
-->
<collection property="orderdetails" ofType="com.mybatis.bean.Orderdetail">
<id column="order_detail_id" property="id"/>
<result column="item_id" property="item_id"/>
<result column="item_num" property="item_num"/>
<result column="item_price" property="item_price"/>
</collection> </resultMap> <!--
1 对 1 查询
-->
<select id="findOrderUserList" resultType="OrderUserCustom">
select
o.id,
o.order_number,
o.user_id,
u.username,
u.birthday
from
orders o
inner join
user u
on
o.user_id = u.id
</select> <select id="findOrderUserListByResultMap" resultMap="orderUserResultMap">
select
o.id,
o.order_number,
o.user_id,
u.username,
u.birthday
from
orders o
inner join
user u
on
o.user_id = u.id
</select> <!--
1 对 多 查询
-->
<select id="findOrderItemsList" resultMap="orderUserDetailResultMap">
select
o.id,
o.order_number,
o.user_id,
u.username,
u.birthday,
od.item_id,
od.item_num,
od.item_price,
od.id as order_detail_id
from
orders o
inner join
user u
on
o.user_id = u.id
left join
orderdetail od
on
o.id = od.orders_id
</select>
</mapper>
OrderMapper.java
package com.mybatis.mapper; import java.util.List; import com.mybatis.bean.OrderUserCustom;
import com.mybatis.bean.Orders; public interface OrderMapper { public List<OrderUserCustom> findOrderUserList() throws Exception; public List<Orders> findOrderUserListByResultMap() throws Exception; public List<Orders> findOrderItemsList() throws Exception; }
Items.java
package com.mybatis.bean; /**
* 商品信息
*
* @author Thinkpad
*
*/
public class Items {
private int id;// 商品id
private String item_name;// 商品名称
private Float item_price;// 商品价格
private String item_detail;// 商品明细 public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getItem_name() {
return item_name;
} public void setItem_name(String item_name) {
this.item_name = item_name;
} public Float getItem_price() {
return item_price;
} public void setItem_price(Float item_price) {
this.item_price = item_price;
} public String getItem_detail() {
return item_detail;
} public void setItem_detail(String item_detail) {
this.item_detail = item_detail;
} }
Orderdetail.java
package com.mybatis.bean; /**
* 订单明细
*
* @author Thinkpad
*
*/
public class Orderdetail {
private int id;// 主键
private int orders_id;// 订单id
private int item_id;// 商品id
private int item_num;// 商品数量
private Float item_price;// 商品价格 // 商品信息
private Items items;// 明细对应的商品信息 public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public int getOrders_id() {
return orders_id;
} public void setOrders_id(int orders_id) {
this.orders_id = orders_id;
} public int getItem_id() {
return item_id;
} public void setItem_id(int item_id) {
this.item_id = item_id;
} public int getItem_num() {
return item_num;
} public void setItem_num(int item_num) {
this.item_num = item_num;
} public Float getItem_price() {
return item_price;
} public void setItem_price(Float item_price) {
this.item_price = item_price;
} public Items getItems() {
return items;
} public void setItems(Items items) {
this.items = items;
} }
Orders.java
package com.mybatis.bean; import java.util.List; /**
* 订单信息
*
* @author Thinkpad
*
*/
public class Orders {
private int id;// 订单id
private int user_id;// 用户id
private String order_number;// 订单号 private User user;// 用户信息 private List<Orderdetail> orderdetails;// 订单明细信息 public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public int getUser_id() {
return user_id;
} public void setUser_id(int user_id) {
this.user_id = user_id;
} public String getOrder_number() {
return order_number;
} public void setOrder_number(String order_number) {
this.order_number = order_number;
} public User getUser() {
return user;
} public void setUser(User user) {
this.user = user;
} public List<Orderdetail> getOrderdetails() {
return orderdetails;
} public void setOrderdetails(List<Orderdetail> orderdetails) {
this.orderdetails = orderdetails;
} }
OrdersCustom.java
package com.mybatis.bean;
public class OrdersCustom extends User {
private String user_id;// 用户id
private String order_number;// 订单号
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getOrder_number() {
return order_number;
}
public void setOrder_number(String order_number) {
this.order_number = order_number;
}
}
OrderUserCustom.java
package com.mybatis.bean;
import java.util.Date;
public class OrderUserCustom extends Orders {
private String username;
private Date birthday;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
User.java
package com.mybatis.bean; import java.util.Date;
import java.util.List; public class User implements java.io.Serializable {
private int id;
private int[] ids;// 存储多个id
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 出生日期
private String address;// 地址
private String detail;// 详细信息
private Float score;// 成绩 // 订单信息
private List<Orders> orders; public int getId() {
return id;
} public void setId(int id) {
this.id = id;
} public String getUsername() {
return username;
} public void setUsername(String username) {
this.username = username;
} public String getSex() {
return sex;
} public void setSex(String 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 String getDetail() {
return detail;
} public void setDetail(String detail) {
this.detail = detail;
} public Float getScore() {
return score;
} public void setScore(Float score) {
this.score = score;
} @Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address
+ ", detail=" + detail + ", score=" + score + "]";
} public List<Orders> getOrders() {
return orders;
} public void setOrders(List<Orders> orders) {
this.orders = orders;
} public int[] getIds() {
return ids;
} public void setIds(int[] ids) {
this.ids = ids;
} }
github地址：https://github.com/leechenxiang/mybatis003-order-reference-query