resultMap映射输出多对多

 

package cn.itcast.mybatis.mapper;

import java.util.List;

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

public interface OrdersMapperCustomer {
	
	//创建操作数据库的接口方法,用于被mybatis增强	
	public List<User> findUserAndItemsResultMap()throws Exception;	
}

代理Mapper接口类

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- namespace命名空间，作用就是对sql进行分类化管理，理解sql隔离 注意：使用mapper代理方法开发，namespace有特殊重要的作用，namespace等于mapper接口地址 -->
<mapper namespace="cn.itcast.mybatis.mapper.OrdersMapperCustom">

	<!-- 订单查询关联用户的resultMap
	将整个查询的结果映射到cn.itcast.mybatis.po.Orders中
	 -->
	
	<!-- 查询用户及购买的商品 -->
	<resultMap type="cn.itcast.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映射
		 -->
		 <collection property="ordersList" ofType="cn.itcast.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="cn.itcast.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="cn.itcast.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>	
	
	<!-- 查询订单关联查询用户及订单明细，使用resultmap -->
	<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>
	
	<!-- 查询用户及购买的商品信息，使用resultmap -->
	<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>
	
	<!-- 查询订单关联查询用户，用户信息需要延迟加载 -->
	<select id="findOrdersUserLazyLoading" resultMap="OrdersUserLazyLoadingResultMap">
		SELECT * FROM orders
	</select>	
</mapper>

Mapper接口类的配置

 

package cn.itcast.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;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}
	public List<Orders> getOrdersList() {
		return ordersList;
	}
	public void setOrdersList(List<Orders> ordersList) {
		this.ordersList = ordersList;
	}
}

Use用户类,包含Order订单表,一对多关系

 

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;

	// 类中含有pojo类,用户信息
	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;
	}
}

Orders订单类,包含订单明细OrderDetail类,一对多关系

 

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

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

OrderDetail订单明细表,包含商品信息Items类,一对一关系

 

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 + "]";
	}    
}

商品信息表

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<!-- 加载属性文件 -->
	<properties resource="db.properties">
		<!--properties中还可以配置一些属性名和属性值  -->
		<!-- <property name="jdbc.driver" value=""/> -->
	</properties>
	<!-- 全局配置参数，需要时再设置 -->
	<!-- <settings>
	
	</settings> -->
	
	<!-- 别名定义 -->
	<!--<typeAliases>
		
		针对单个别名定义
		type：类型的路径
		alias：别名
		
		<typeAlias type="cn.itcast.mybatis.po.User" alias="user"/>
		批量别名定义 
		指定包名，mybatis自动扫描包中的po类，自动定义别名，别名就是类名（首字母大写或小写都可以）
		
		<package name="cn.itcast.mybatis.po"/>
		
	</typeAliases> -->
	
	<!-- 和spring整合后 environments配置将废除-->
	<environments default="development">
		<environment id="development">
		<!-- 使用jdbc事务管理，事务控制由mybatis-->
			<transactionManager type="JDBC" />
		<!-- 数据库连接池，由mybatis管理-->
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.username}" />
				<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- 加载 映射文件 -->
	<mappers>
		<!-- <mapper resource="mapper/OrdersMapperCustomer.xml"/> -->
		
		<!--通过resource方法一次加载一个映射文件 -->
		<!-- <mapper resource="mapper/UserMapper.xml"/> -->
		
		<!-- 通过mapper接口加载单个 映射文件
		遵循一些规范：需要将mapper接口类名和mapper.xml映射文件名称保持一致，且在一个目录 中
		上边规范的前提是：使用的是mapper代理方法
		 -->
		<!-- <mapper class="cn.itcast.mybatis.mapper.UserMapper"/> -->
		
		<!-- 批量加载mapper
		指定mapper接口的包名，mybatis自动扫描包下边所有mapper接口进行加载
		遵循一些规范：需要将mapper接口类名和mapper.xml映射文件名称保持一致，且在一个目录 中
		上边规范的前提是：使用的是mapper代理方法
		 -->
		<package name="cn.itcast.mybatis.mapper"/>

	</mappers>
	
</configuration>

SqlMapConfig配置