1. 关系模型分析
1:1 | 订单->用户;订单明细->商品;订单明细->订单 |
---|---|
1:n | 用户->订单;订单->订单明细; |
n:m | 订单<->商品;用户<->商品 |
2. 一对一
2.1 需求
- 根据订单Id获取订单信息,包括订单所属的用户信息
2.2 嵌套 pojo 方式
-
实体类
Orders类属于Mybatis框架中对应数据表Orders的一个基础类,通常由逆向工程生成,每次逆向工程都会覆盖原来的Orders类,为了避免Orders类中的自定义内容被逆向工程覆盖,因此推荐做法:定义Orders的扩展类OrdersExt1public class User { private Integer id; private String username; private Date birthday; private String sex; private String address; } public class Order { private int id; private int user_id; private String number; private Date createtime; private String note; } public class OrderExt extends Order { //多对一 private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } }
-
OrdersMapper接口
/** * 根据编号查询订订单 * @param id * @return */ public OrderExt findOrderExtById(Integer id);
-
OrdersMapper.xml
<resultMap type="orderExt" id="orderExtMap"> <!-- order属性 , 基础属性--> <id column="id" property="id"/> <result column="user_id" property="user_id"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- 一方 , 多对一 --> <association property="user" javaType="user"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> <select id="findOrderExtById" parameterType="int" resultMap="orderExtMap"> SELECT o.id, o.user_id, o.number, o.createtime, o.note, u.id uid, u.username, u.birthday, u.sex, u.address FROM `order` o , `user` u WHERE o.user_id = u.id and o.id = #{id} </select>
-
测试
@Before public void before() throws IOException { // MyBatis 配置文件路径 String resource = "mybatis-config.xml"; // 通过Resource 读取配置文件输入流 InputStream inputStream = Resources.getResourceAsStream(resource); // 创建SQL会话工厂 sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void method01() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); OrderExt orderExt = orderMapper.findOrderExtById(1); System.out.println(orderExt); sqlSession.close(); }
2.3 平铺 pojo 方式
-
实体类
public class Order { private int id; private int user_id; private String number; private Date createtime; private String note; } public class OrderExt2 extends Order { private Integer uid; private String username; private Date birthday; private String sex; private String address; }
-
接口方法
/** * 根据编号查询订订单 * @param id * @return */ public OrderExt2 findOrderExtById(Integer id);
-
映射sql
<select id="findOrderExtById" parameterType="int" resultType="OrderExt2"> SELECT o.id, o.user_id, o.number, o.createtime, o.note, u.id uid, u.username, u.birthday, u.sex, u.address FROM `order` o , `user` u WHERE o.user_id = u.id and o.id = #{id} </select>
-
测试
@Test public void method01() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); OrderExt2 orderExt = orderMapper.findOrderExtById(1); System.out.println(orderExt); sqlSession.close(); }
2.4 两种实现方式比较:
- 平铺式实现方式简单,但是结构不清晰
- 嵌套是实现方式相对复杂(手工映射结果集),结构清晰
- 一般对于结构简单的1:1关系,推荐使用平铺式;对于结果相对负责的1:1关系,推荐使用嵌套式
3. 一对多
3.1 需求
- 根据订单Id查找订单信息(包含用户信息、订单明细)
3.2 开发步骤
- 步骤1:创建PO类 Items、OrderDetail、OrderDetailExt
-
Items类
public class Item { private Integer id; private String name; private Double price; private String detail; private String pic; private Date createtime; }
-
OrderDetail类
public class OrderDetail { private Integer id; private Integer orders_id; private Integer items_id; private Integer items_num; }
-
OrderDetailExt类
public class OrderDetailExt extends OrderDetail { private Item item; public Item getItem() { return item; } public void setItem(Item item) { this.item = item; } }
-
步骤2:修改OrdersExt1类关联订单明细信息
public class OrderExt extends Order { // 多对一 private User user; // 多对一 private List<OrderDetailExt> orderDetails; public User getUser() { return user; } public void setUser(User user) { this.user = user; } public List<OrderDetailExt> getOrderDetails() { return orderDetails; } public void setOrderDetails(List<OrderDetailExt> orderDetails) { this.orderDetails = orderDetails; } }
-
步骤3:修改OrdersMapper接口
/** * 根据编号查询订单和详单信息 * @param id * @return */ public OrderExt findOrderWithDetail(Integer id);
-
步骤4:添加sql映射
<!-- 订单和详单的 mapping --> <resultMap type="orderExt" id="orderWithDetailMap"> <id column="id" property="id" /> <result column="user_id" property="user_id" /> <result column="number" property="number" /> <result column="createtime" property="createtime" /> <result column="note" property="note" /> <!-- 一方 --> <association property="user" javaType="user"> <id column="uid" property="id" /> <result column="username" property="username" /> <result column="birthday" property="birthday" /> <result column="sex" property="sex" /> <result column="address" property="address" /> </association> <!-- 一对多 --> <!-- ofType 是多的一方中的每一个元素的类型,也就是 List 集合中的泛型类型 。 --> <collection property="orderDetails" ofType="orderDetail"> <id column="orderdetail_id" property="id" /> <result column="orders_id" property="orders_id" /> <result column="items_id" property="items_id" /> <result column="items_num" property="items_num" /> </collection> </resultMap>
<select id="findOrderWithDetail" parameterType="int" resultMap="orderWithDetailMap"> SELECT o.id, o.user_id, o.number, o.createtime, o.note, od.id orderdetail_id, od.orders_id, od.items_id, od.items_num, u.id uid, u.username, u.birthday, u.sex, u.address FROM `order` o ,`orderdetail` od, `user` u WHERE o.id = od.orders_id AND o.user_id = u.id AND o.id = #{id} </select>
-
步骤5:测试
@Test public void method02() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); OrderExt orderExt = orderMapper.findOrderWithDetail(2); System.out.println(orderExt); sqlSession.close(); }
4. 多对多
4.1 需求
- 获取所有订单(含有所属用户、订单明细、商品)
4.2 开发步骤
-
步骤1:添加接口方法
/** * 根据编号查询订单、详单、商品的信息 * @param id * @return */ public OrderExt findOrderWithDetailAndItem(Integer id);
-
步骤2:添加sql映射
<!-- 订单、详单、商品的 mapping --> <resultMap type="orderExt" id="orderWithDetailAndItemMap"> <id column="id" property="id" /> <result column="user_id" property="user_id" /> <result column="number" property="number" /> <result column="createtime" property="createtime" /> <result column="note" property="note" /> <!-- 一方 --> <association property="user" javaType="user"> <id column="uid" property="id" /> <result column="username" property="username" /> <result column="birthday" property="birthday" /> <result column="sex" property="sex" /> <result column="address" property="address" /> </association> <!-- 一对多 --> <!-- ofType 是多的一方中的每一个元素的类型 --> <collection property="orderDetails" ofType="orderDetailExt"> <id column="orderdetail_id" property="id" /> <result column="orders_id" property="orders_id" /> <result column="items_id" property="items_id" /> <result column="items_num" property="items_num" /> <!-- 关联 Item --> <association property="item" javaType="item"> <id column="item_id" property="id"/> <result column="name" property="name"/> <result column="price" property="price"/> <result column="detail" property="detail"/> <result column="pic" property="pic"/> <result column="createtime" property="createtime"/> </association> </collection> </resultMap>
<select id="findOrderWithDetailAndItem" parameterType="int" resultMap="orderWithDetailAndItemMap"> SELECT o.id, o.user_id, o.number, o.createtime, o.note, od.id orderdetail_id, od.orders_id, od.items_id, od.items_num, u.id uid, u.username, u.birthday, u.sex, u.address, i.id item_id, i.name, i.price, i.detail, i.pic, i.createtime FROM `order` o ,`orderdetail` od, `user` u , `items` i WHERE o.id = od.orders_id AND o.user_id = u.id AND o.id = #{id} </select>
-
步骤3:测试
@Test public void method03() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); OrderExt orderExt = orderMapper.findOrderWithDetailAndItem(2); System.out.println(orderExt); sqlSession.close(); }