订单商品数据模型
分析:
- 用户表(user): user_id, user_name 记录了购买商品的用户信息
- 订单表(orders): orders_id,number, user_id 记录了用户创建的订单(购买商品的订单)
- 订单明细表(orderdetail):detail_id,orders_id, items_id 记录了订单的详细信息(购买商品的信息)
- 商品表(items):items_id,items_name, items_price 记录了商品信息
User和orders:
User–》orders:一个用户可以创建多个订单,一对多
Orders–》user:一个订单只能由一个用户创建,一对一
Orders和orderdetail:
Orders–》orderdetail:一个订单包括多个订单明细表,一对多
Orderdetail–》orders:一个订单明细表对应一个订单,一对一
Orderdetail和items:
Orderdetail–》items:一个订单明细表对应一个商品,一对一
Items–》orderdetail:一个商品可以在多个订单明细表中存在,一对多
Orders和items:
Orders–》items:一个订单包含多个商品
Items–》orders:一个商品可以在多个订单中
一对一查询
1. 需求:
对订单表进行查询,查询订单表信息以及创建订单的用户信息
2. resultType
2.1 SQL:
SELECT orders.*,`user`.user_name,`user`.user_birth FROM orders,`user` WHERE orders.user_id = `user`.user_id
2.2 创建pojo
public class OrdersCustom extends Orders{
private String user_name;
private Date user_birth;
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public Date getUser_birth() {
return user_birth;
}
public void setUser_birth(Date user_birth) {
this.user_birth = user_birth;
}
@Override
public String toString() {
return "OrdersCustom [user_name=" + user_name + ", user_birth="
+ user_birth + ", toString()=" + super.toString() + "]";
}
}
2.3 创建mapper.java
public interface OrdersCustomMapper {
//查询订单关联查询用户信息
//<select id="selectOrders" resultType="orders">
public List<OrdersCustom> selectOrders() throws IOException;
}
2.4 创建mapper.xml
<select id="selectOrders" resultType="orderscustom">
SELECT orders.*,`user`.user_name,`user`.user_birth
FROM orders,`user`
WHERE orders.user_id = `user`.user_id
</select>
3 resultMap
3.1 添加User属性
private User user; //创建订单的用户
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
3.2 创建mapper.java
public interface OrdersMapper {
public List<Orders> selectOrders() throws IOException;
}
3.3 创建mapperxml
<!-- resultMap标签:定义订单查询关联用户的resultMap
type属性:查询结果对应的实体类
id属性:该resultMap的id
- 配置映射的订单信息
id标签:Orders的主键
column属性:数据表中名
property属性:实体类属性名
result标签:Orders的其他列
column属性:数据表中名
property属性:实体类属性名
- 配置映射的关联的用户信息
association标签:
property属性:关联的实体类在Orders中属性名
javaType属性:关联的实体类
-->
<resultMap type="com.wzw.mybatis.model.Orders" id="OrdersUserResultMap">
<id column="orders_id" property="orders_id"/>
<result column="number" property="number"/>
<association property="user" javaType="com.wzw.mybatis.model.User">
<id column="user_id" property="user_id"/>
<result column="user_name" property="user_name"/>
<result column="user_birth" property="user_birth"/>
</association>
</resultMap>
<select id="selectOrders" resultMap="OrdersUserResultMap">
SELECT orders.*,`user`.user_name,`user`.user_birth
FROM orders,`user`
WHERE orders.user_id = `user`.user_id
</select>
一对多查询
1. 需求:
对订单表及订单明细表查询,获取订单详细信息
2. SQL
SELECT orders.*,`user`.user_name,`user`.user_birth,
orderdetail.detail_id,orderdetail.num,orderdetail.items_id
FROM orders,`user`,orderdetail
WHERE orders.user_id = `user`.user_id
AND orderdetail.orders_id = orders.orders_id
3. 添加OrderDetail属性
private List<OrderDetail> orderDetails; //订单明细信息
public List<OrderDetail> getOrderDetails() {
return orderDetails;
}
public void setOrderDetails(List<OrderDetail> orderDetails) {
this.orderDetails = orderDetails;
}
4. 创建mapper.java
//查询订单及订单明细信息
public List<Orders> selectOrdersAndOrderDetail() throws IOException;
5. 创建mapper.xml
<!-- 查询订单及订单明细信息 -->
<resultMap type="com.wzw.mybatis.model.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
<!-- 配置订单信息 -->
<!-- 配置用户信息 -->
<!-- 配置订单明细信息 -->
<collection property="orderDetails" ofType="com.wzw.mybatis.model.OrderDetail">
<id column="detail_id" property="detail_id"/>
<result column="num" property="num"/>
<result column="orders_id" property="orders_id"/>
<result column="items_id" property="items_id"/>
</collection>
</resultMap>
<select id="selectOrdersAndOrderDetail" resultMap="OrdersAndOrderDetailResultMap">
SELECT orders.*,`user`.user_name,`user`.user_birth,
orderdetail.detail_id,orderdetail.num,orderdetail.items_id
FROM orders,`user`,orderdetail
WHERE orders.user_id = `user`.user_id
AND orderdetail.orders_id = orders.orders_id
</select>
多对多查询
1. 需求:
查询用户及用户购买商品的信息
2. SQL
SELECT `user`.*,orders.*,orderdetail.*,items.*
FROM orders,`user`,orderdetail,items
WHERE orders.user_id = `user`.user_id
AND orderdetail.orders_id = orders.orders_id
AND orderdetail.items_id = items.items_id
3. 添加Orders属性
private List<Orders> orderList;
public List<Orders> getOrderList() {
return orderList;
}
public void setOrderList(List<Orders> orderList) {
this.orderList = orderList;
}
4. mapper.java
//查询用户及用户购买的商品信息
public List<User> selectUserAndItems() throws IOException;
5. resultMap
<!-- 查询用户及购买商品的信息 -->
<resultMap type="com.wzw.mybatis.model.User" id="UserAndItemsResultMap">
<id column="user_id" property="user_id"/>
<result column="user_name" property="user_name"/>
<result column="user_birth" property="user_birth"/>
<!-- 配置订单表信息:一个用户创建多个订单 -->
<collection property="orderList" ofType="com.wzw.mybatis.model.Orders">
<id column="orders_id" property="orders_id"/>
<result column="number" property="number"/>
<!-- 配置订单明细表信息:一个订单存在多个订单明细 -->
<collection property="orderDetails" ofType="com.wzw.mybatis.model.OrderDetail">
<id column="detail_id" property="detail_id"/>
<result column="num" property="num"/>
<result column="orders_id" property="orders_id"/>
<result column="items_id" property="items_id"/>
<!-- 配置商品信息:一个订单明细对应一个商品 -->
<association property="items" javaType="com.wzw.mybatis.model.Items">
<id column="items_id" property="items_id"/>
<result column="items_name" property="items_name"/>
<result column="items_price" property="items_price"/>
</association>
</collection>
</collection>
</resultMap>
6. mapper.xml
<select id="selectUserAndItems" resultMap="UserAndItemsResultMap">
SELECT `user`.*,orders.*,orderdetail.*,items.*
FROM orders,`user`,orderdetail,items
WHERE orders.user_id = `user`.user_id
AND orderdetail.orders_id = orders.orders_id
AND orderdetail.items_id = items.items_id
</select>