mybatis关联查询映射

2019-11-08 03:11:10

字体：大中小

来源：转载

供稿：网友

xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.mybatis.mapper.OrdersMapperCustom">  <resultMap type="com.mybatis.po.Orders" id="OrdersUserResultMap">   <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/>   <association property="user" javaType="com.mybatis.po.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap>  <resultMap type="com.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">   <collection property="orderdetails" ofType="com.mybatis.po.Orderdetail">  <id column="orderdetail_id" property="id"/> <result column="items_id" property="itemsId"/> <result column="items_num" property="itemsNum"/> <result column="orders_id" property="ordersId"/> </collection> </resultMap>  <resultMap type="com.mybatis.po.User" id="UserAndItemsResultMap">  <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/>  <collection property="ordersList" ofType="com.mybatis.po.Orders"> <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/>  <collection property="orderdetails" ofType="com.mybatis.po.Orderdetail"> <id column="orderdetail_id" property="id"/> <result column="items_id" property="itemsId"/> <result column="items_num" property="itemsNum"/> <result column="orders_id" property="ordersId"/>  <association property="items" javaType="com.mybatis.po.Items"> <id column="items_id" property="id"/> <result column="items_name" property="name"/> <result column="items_detail" property="detail"/> <result column="items_price" property="price"/> </association> </collection> </collection> </resultMap>  <select id="findOrdersUser" resultType="com.mybatis.po.OrdersCustom"> SELECT orders.*, USER.username, USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id </select>  <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap"> SELECT orders.*, USER.username, USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id </select>  <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap"> SELECT orders.*, USER.username, USER.sex, USER.address, orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id FROM orders, USER, orderdetail WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id </select>  <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap"> SELECT orders.*, USER.username, USER.sex, USER.address, orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id, items.name items_name, items.detail items_detail, items.price items_price FROM orders, USER, orderdetail, items WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id </select>  <resultMap type="com.mybatis.po.Orders" id="OrdersUserLazyLoadingResultMap">  <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/>  <association property="user" javaType="com.mybatis.po.User" select="com.mybatis.mapper.UserMapper.findUserById" column="user_id">  </association> </resultMap>  <select id="findOrdersUserLazyLoading" resultMap="OrdersUserLazyLoadingResultMap"> SELECT * FROM orders </select></mapper>

mapper

OrdersMapperCustom.java

package com.mybatis.mapper;import java.util.List;import com.mybatis.po.Orders;import com.mybatis.po.OrdersCustom;import com.mybatis.po.User;public interface OrdersMapperCustom { //查询订单关联查询用户信息 public List<OrdersCustom> findOrdersUser()throws Exception; //查询订单关联查询用户使用resultMap public List<Orders> findOrdersUserResultMap()throws Exception; //查询订单(关联用户)及订单明细 public List<Orders> findOrdersAndOrderDetailResultMap()throws Exception; //查询用户购买商品信息 public List<User> findUserAndItemsResultMap()throws Exception; //查询订单关联查询用户，用户信息是延迟加载 public List<Orders> findOrdersUserLazyLoading()throws Exception;}

po

OrdersCustom.java

package com.mybatis.po;//通过此类映射订单和用户查询的结果，让此类继承包括字段较多的pojo类public class OrdersCustom extends Orders{ private String username; private String sex; private String address; 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 String getAddress() { return address; } public void setAddress(String address) { this.address = address; }}

Orders.java

package com.mybatis.po;import java.util.Date;import java.util.List;public class Orders { private Integer id; private Integer userId; private String number; private Date createtime; private String note; //用户信息 private User user; //订单明细 private List<Orderdetail> orderdetails; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number == null ? null : number.trim(); } public Date getCreatetime() { return createtime; } public void setCreatetime(Date createtime) { this.createtime = createtime; } public String getNote() { return note; } public void setNote(String note) { this.note = note == null ? null : note.trim(); } 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; }}

Orderdetail.java

package com.mybatis.po;public class Orderdetail { private Integer id; private Integer ordersId; private Integer itemsId; private Integer itemsNum; //明细对应的商品信息 private Items items; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getOrdersId() { return ordersId; } public void setOrdersId(Integer ordersId) { this.ordersId = ordersId; } public Integer getItemsId() { return itemsId; } public void setItemsId(Integer itemsId) { this.itemsId = itemsId; } public Integer getItemsNum() { return itemsNum; } public void setItemsNum(Integer itemsNum) { this.itemsNum = itemsNum; } public Items getItems() { return items; } public void setItems(Items items) { this.items = items; }}

Items.java

package com.mybatis.po;import java.util.Date;public class Items { private Integer id; private String name; private Float price; private String pic; private Date createtime; private String detail; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name == null ? null : name.trim(); } public Float getPrice() { return price; } public void setPrice(Float price) { this.price = price; } public String getPic() { return pic; } public void setPic(String pic) { this.pic = pic == null ? null : pic.trim(); } public Date getCreatetime() { return createtime; } public void setCreatetime(Date createtime) { this.createtime = createtime; } public String getDetail() { return detail; } public void setDetail(String detail) { this.detail = detail == null ? null : detail.trim(); }}

User.java

package com.mybatis.po;import java.io.Serializable;import java.util.Date;import java.util.List;public class User implements Serializable { //属性名和数据库表的字段对应 private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 //用户创建的订单列表 private List<Orders> ordersList; 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 List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; }}

上一篇：蓝桥杯——趣味逻辑推理二（2017.2.17）

下一篇：gdb 中layout的使用