准备如下用户表,订单表,订单明细表,商品表
用户表:user_id,user_name
订单表:order_id,order_no,user_id
订单明细表:orderdetail_id,order_id,product_id
商品表:product_id,product_name
实体类
package com.zns.domain; import java.util.List; public class User { private int userid; private String username; private List<Order> orders; public int getUserid() { return userid; } public void setUserid(int userid) { this.userid = userid; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public List<Order> getOrders() { return orders; } public void setOrders(List<Order> orders) { this.orders = orders; } @Override public String toString() { return "User [userid=" + userid + ", username=" + username + ", orders=" + orders + "]"; } }
package com.zns.domain; import java.util.List; public class Order { private int orderid; private String orderno; private int userid; private User user; private List<OrderDetail> orderDetails; public int getOrderid() { return orderid; } public void setOrderid(int orderid) { this.orderid = orderid; } public String getOrderno() { return orderno; } public void setOrderno(String orderno) { this.orderno = orderno; } public int getUserid() { return userid; } public void setUserid(int userid) { this.userid = userid; } 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; } @Override public String toString() { return "Order [orderid=" + orderid + ", orderno=" + orderno + ", userid=" + userid + ", user=" + user + ", orderDetails=" + orderDetails + "]"; } }
package com.zns.domain; public class OrderDetail { private int orderdetailid; private int orderid; private int productid; private Product product; public int getOrderdetailid() { return orderdetailid; } public void setOrderdetailid(int orderdetailid) { this.orderdetailid = orderdetailid; } public int getOrderid() { return orderid; } public void setOrderid(int orderid) { this.orderid = orderid; } public int getProductid() { return productid; } public void setProductid(int productid) { this.productid = productid; } public Product getProduct() { return product; } public void setProduct(Product product) { this.product = product; } @Override public String toString() { return "OrderDetail [orderdetailid=" + orderdetailid + ", orderid=" + orderid + ", productid=" + productid + ", product=" + product + "]"; } }
package com.zns.domain; public class Product { private int productid; private String productname; public int getProductid() { return productid; } public void setProductid(int productid) { this.productid = productid; } public String getProductname() { return productname; } public void setProductname(String productname) { this.productname = productname; } @Override public String toString() { return "Product [productid=" + productid + ", productname=" + productname + "]"; } }
Mybatis映射配置如下
<!-- 查询订单的信息和关联的用户信息 (一对一) --> <select id="select1" resultMap="resultMap1"> SELECT a.order_id, a.order_no, b.user_name FROM tb_order AS a, tb_user AS b WHERE a.user_id = b.user_id </select> <resultMap type="com.zns.domain.Order" id="resultMap1"> <id column="order_id" property="orderid" /> <result column="order_no" property="orderno" /> <result column="user_id" property="userid" /> <association property="user" javaType="com.zns.domain.User"> <id column="user_id" property="userid" /> <result column="user_name" property="username" /> </association> </resultMap> <!-- 查询订单的信息和关联的用户信息和关联的订单明细 (一对多) --> <select id="select2" resultMap="resultMap2"> SELECT a.order_id, a.order_no, b.user_name, c.orderdetail_id, c.product_id FROM tb_order AS a, tb_user AS b, tb_order_detail AS c WHERE a.user_id = b.user_id AND a.order_id = c.order_id </select> <resultMap type="com.zns.domain.Order" id="resultMap2"> <id column="order_id" property="orderid" /> <result column="order_no" property="orderno" /> <result column="user_id" property="userid" /> <association property="user" javaType="com.zns.domain.User"> <id column="user_id" property="userid" /> <result column="user_name" property="username" /> </association> <collection property="orderDetails" ofType="com.zns.domain.OrderDetail"> <id column="orderdetail_id" property="orderdetailid" /> <result column="order_id" property="orderid" /> <result column="product_id" property="productid" /> </collection> </resultMap> <!-- 查询用户和用户购买的商品信息 (多对多) --> <select id="select3" resultMap="resultMap3"> SELECT a.user_id, a.user_name, b.order_id, b.order_no, c.orderdetail_id, c.product_id, d.product_name FROM tb_user AS a, tb_order AS b, tb_order_detail AS c, tb_product AS d WHERE a.user_id = b.user_id AND b.order_id = c.order_id AND c.product_id = d.product_id </select> <resultMap type="com.zns.domain.User" id="resultMap3"> <id column="user_id" property="userid" /> <result column="user_name" property="username" /> <!-- 一个用户对应多个订单 --> <collection property="orders" ofType="com.zns.domain.Order"> <id column="order_id" property="orderid" /> <result column="order_no" property="orderno" /> <!-- 一个订单对应多个订单明细 --> <collection property="orderDetails" ofType="com.zns.domain.OrderDetail"> <id column="orderdetail_id" property="orderdetailid" /> <result column="order_id" property="orderid" /> <result column="product_id" property="productid" /> <!-- 一个订单明细对应一个商品 --> <association property="product" javaType="com.zns.domain.Product"> <id column="product_id" property="productid" /> <result column="product_name" property="productname" /> </association> </collection> </collection> </resultMap>