JavaEE_Mybatis_SpringMVC_Mybatis_lesson13_多对多关联映射（ResultMap）

最新推荐文章于 2024-04-02 13:39:07 发布

高达一号

最新推荐文章于 2024-04-02 13:39:07 发布

阅读量811

点赞数 2

分类专栏： JavaEE_Mybatis 文章标签： mybatis 关联映射

本文链接：https://blog.csdn.net/u010003835/article/details/50162009

版权

JavaEE_Mybatis 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

项目代码

http://pan.baidu.com/s/1c01BLvi

下面展示一个Mybatis Mapper代理开发方式的多对多映射的案例

映射方式采用ResultMap进行映射

由于关系比较复杂，

先展示数据库表结构的设计

业务：

查询一个人所购买的商品

需要用到4张关联表

user->orders->orderdetail->items

（单方向是一对多）

注意：分析业务的时候需要按照一个方向分析业务逻辑

SQL

		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
			user,
			orders,
			orderdetail,
			items
		WHERE
			orders.user_id = user.id
			AND orderdetail.orders_id = orders.id
			AND orderdetail.items_id = items.id

Pojo设计

按照

user.java

orders,java

orderdetail.java

items.java 给出

文档目录结构

user.java

package cn.itcast.mybatis.po;

import java.util.Date;
import java.util.List;

//hibernate字段名和属性名相对应
public class User {
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", birthday="
				+ birthday + ", sex=" + sex + ", address=" + address + "]";
	}

	private int id;
	private String username;
	private Date birthday;
	private String sex;
	private String address;

	// 用户的订单商品信息
	List<Orders> orderlist;

	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 Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	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;
	}

	// 用户的订单商品信息
	public List<Orders> getOrderlist() {
		return orderlist;
	}

	public void setOrderlist(List<Orders> orderlist) {
		this.orderlist = orderlist;
	}
}

orders.java

package cn.itcast.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;
	}

	@Override
	public String toString() {
		return "Orders [id=" + id + ", userId=" + userId + ", number=" + number
				+ ", createtime=" + createtime + ", note=" + note + ", user="
				+ user + ", orderdetails=" + orderdetails + "]";
	}

	// 订单明细
	public List<Orderdetail> getOrderdetails() {
		return orderdetails;
	}

	public void setOrderdetails(List<Orderdetail> orderdetails) {
		this.orderdetails = orderdetails;
	}

}

orderdetail.java

package cn.itcast.mybatis.po;

public class Orderdetail {
	private Integer id;

	private Integer ordersId;

	private Integer itemsId;

	private Integer itemsNum;

	// 商品的Items信息
	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;
	}

	@Override
	public String toString() {
		return "Orderdetail [id=" + id + ", ordersId=" + ordersId
				+ ", itemsId=" + itemsId + ", itemsNum=" + itemsNum + "]";
	}

	// 商品Items的信息
	public Items getItems() {
		return items;
	}

	public void setItems(Items items) {
		this.items = items;
	}

}

items.java

package cn.itcast.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();
    }

	@Override
	public String toString() {
		return "Items [id=" + id + ", name=" + name + ", price=" + price
				+ ", pic=" + pic + ", createtime=" + createtime + ", detail="
				+ detail + "]";
	}
    
}

Mapper.xml

ResultMap定义

	<!-- 多对多查询用户购买的商品信息 -->
	<resultMap type="cn.itcast.mybatis.po.User" id="UsersAndItemsResultMap">
		<!-- collection: 将关联查询到多条记录映射到集合对象中 -->
		<!-- property: 将关联查询到多条记录映射到cn.itcast.mybatis.po.Orders那个属性 -->
		<!-- ofType 指定映射到list集合属性中pojo类型 -->
		<id column="user_id" property="id"/>
		<result column="username" property="username"/> 
		<result column="sex" property="sex"/> 
		<result column="address" property="address"/>
		
		<collection property="orderlist" ofType="cn.itcast.mybatis.po.Orders">
		<!--
			private Integer id;
			private Integer userId;
			private String number;
			private Date createtime;
			private String note; 
		 -->
		 	<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="cn.itcast.mybatis.po.Orderdetail">
		 	<!--
		 		private Integer id;
				private Integer ordersId;
				private Integer itemsId;
				private Integer itemsNum;
				// 商品的Items信息
				private Items items; 
		 	 -->
		 	 	<id column="orderdetail_id" property="id"/>
		 	 	<result column="items_id" property="itemsId"/>
		 	 	<result column="items_num" property="itemsNum"/>
		 	 	
		 	 	<association property="items" javaType="cn.itcast.mybatis.po.Items">
		 	 	<!-- 
		 	 	  private Integer id;
    			  private String name;
    			  private Float price;
    			  private String pic;
    			  private Date createtime;
     			  private String detail;
		 	 	 -->
		 	 	 <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>

Statement定义 SqlStatement

	<select id="findUsersAndItemsResultMap" resultMap="UsersAndItemsResultMap">
		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
			user,
			orders,
			orderdetail,
			items
		WHERE
			orders.user_id = user.id
			AND orderdetail.orders_id = orders.id
			AND orderdetail.items_id = items.id
	</select>

Mapper.java

package cn.itcast.mybatis.mapper;

import java.util.List;

import cn.itcast.mybatis.po.Orders;
import cn.itcast.mybatis.po.OrdersCustom;
import cn.itcast.mybatis.po.User;

public interface UserCustomMapper {
	// 根据订单查询用户信息
	// 主表：Orders
	// 从表：User
	List<OrdersCustom> findOrderUser() throws Exception;

	// 利用ResultMap进行关联映射
	List<Orders> findOrderUserResultMap() throws Exception;

	// 利用ResultMap进行关联映射
	List<Orders> findOrdersAndOrderDetailMap() throws Exception;

	List<User> findUsersAndItemsResultMap() throws Exception;
}

JUnit Test

	@Test
	public void testFindUsersAndItemsResultMap() {
		SqlSession sqlSession = null;
		try {
			sqlSession = sqlSessionFactory.openSession();
			UserCustomMapper userCustomMapper = sqlSession
					.getMapper(UserCustomMapper.class);
			List<User> list = userCustomMapper.findUsersAndItemsResultMap();
			// user 对 orders 为一对多映射
			for (User tmp : list) {
				System.out.println(tmp);
				// orderlist oders对orderdetail为一对多映射
				List<Orders> list2 = tmp.getOrderlist();
				for (Orders tmp2 : list2) {
					System.out.println(tmp2);
					List<Orderdetail> list3 = tmp2.getOrderdetails();
					// orderdetails ordertail对Items为一对一映射
					for (Orderdetail tmp3 : list3) {
						System.out.println(tmp3);
						// Items
						System.out.println(tmp3.getItems());
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlSession.close();
		}
	}

测试结果