如何查询订单关联用户信息

如何查询订单关联用户信息？

方案一：

1.一对一的关联查询如何操作：
a.因为数据来自多张表，所以需要定义resultMap—orderWithCustomerMap告诉mybatis如何映射
b.一对一关联使用association标签，属性customer是一的对象，所属类型javaType是Customer
对其映射时，
查询结果集中的c.cid,c.cname,c.c_phone,c.c_address都要在其中进行映射
cid是id映射
c.cname,c.c_phone,c.c_address是result映射
c.在sql语句中引用这个resultMap
d.测试
OrderMapper.xml配置文件

<?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">
	<mapper namespace="com.abc.mybatis.mapper.OrderMapper">
	
	<!-- public List<Order> queryAllOrderWithCustomer(); -->
	<select id="queryAllOrderWithCustomer" resultMap="orderWithCustomerMap">
		select o.order_id,o.order_date,c.cid,c.cname,c.c_phone,c.c_address
		from orderes o,customer c
		where o.cid=c.cid
	</select>
	
	<resultMap type="Order" id="orderWithCustomerMap">
		<id column="order_id" property="orderId"></id>
		<result column="order_date" property="orderDate"/>
		<!-- 一对一关联使用，属性customer是一的对象，所属类型javaType是Customer
		对其映射时，
		查询结果集中的c.cid,c.cname,c.c_phone,c.c_address都要在其中进行映射
		cid是id映射
		c.cname,c.c_phone,c.c_address是result映射
		 -->
		<association property="customer" javaType="Customer">
			<id column="cid" property="cId"></id>
			<result column="cname" property="cName"/>
			<result column="c_phone" property="cPhone"/>
			<result column="c_address" property="cAddress"/>
		</association>
	</resultMap>
		
		
	</mapper>

mybatis-config.xml主配置文件

<?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>


<!-- – configuration 配置
• properties 属性
• settings 设置
• typeAliases 类型命名
• typeHandlers 类型处理器
• objectFactory 对象工厂
• plugins 插件
• Environments 环境
• mappers 映射器 -->

	<!-- 加载属性文件 -->
	<properties resource="jdbc_oracle.properties"></properties>
	
	<!-- 打开日志开关，由log4j进行日志输出 -->
	<settings>
		<setting name="logImpl" value="LOG4J"/>
	</settings>

	<!-- 配置别名 -->
	<typeAliases>
		<package name="com.abc.mybatis.domain"/>
	</typeAliases>

	<!-- 对数据库做个映射 -->
	<environments default="armbank">
		<environment id="armbank">
			<transactionManager type="JDBC"></transactionManager>
			<dataSource type="pooled">
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.username}"/>
				<property name="driver" value="${jdbc.driver}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
	<!-- 映射文件写好之后一定要告诉主配置文件，否则报错 -->
	<!-- 包扫描效率高 -->
		<package name="com.abc.mybatis.mapper"/>
	
	</mappers>

</configuration>

测试类

public class Test {
	public static void main(String[] args) throws Exception {
		//初始化sqlSessionFactory
		InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(is);
		//初始化SqlSession
		SqlSession sqlSession=sessionFactory.openSession();
		//获取mapper
		OrderMapper mapper=sqlSession.getMapper(com.abc.mybatis.mapper.OrderMapper.class);
		
		List<Order> orders=mapper.queryAllOrderWithCustomer();
		
		System.out.println(orders.toString());
	
	}
}

查询所有订单接口

package com.abc.mybatis.mapper;

import java.util.List;

import com.abc.mybatis.domain.Order;

public interface OrderMapper {
	
	public List<Order> queryAllOrderWithCustomer();

}

Customer用户实体类

package com.abc.mybatis.domain;

public class Customer {
	
	private Integer cId;
	private String cName;
	private String cPhone;
	private String cAddress;
	public Integer getcId() {
		return cId;
	}
	public void setcId(Integer cId) {
		this.cId = cId;
	}
	public String getcName() {
		return cName;
	}
	public void setcName(String cName) {
		this.cName = cName;
	}
	public String getcPhone() {
		return cPhone;
	}
	public void setcPhone(String cPhone) {
		this.cPhone = cPhone;
	}
	public String getcAddress() {
		return cAddress;
	}
	public void setcAddress(String cAddress) {
		this.cAddress = cAddress;
	}
	@Override
	public String toString() {
		return "Customer [cId=" + cId + ", cName=" + cName + ", cPhone=" + cPhone + ", cAddress=" + cAddress + "]\n";
	}

}

Product商品实体类

package com.abc.mybatis.domain;

public class Product {
	
	private Integer pId;
	private String pName;
	public Integer getpId() {
		return pId;
	}
	public void setpId(Integer pId) {
		this.pId = pId;
	}
	public String getpName() {
		return pName;
	}
	public void setpName(String pName) {
		this.pName = pName;
	}
	
}

Order订单实体类

package com.abc.mybatis.domain;

import java.util.Date;

public class Order {
	
	private Integer orderId;
	private Customer customer;
	private Date orderDate;
	
	public Integer getOrderId() {
		return orderId;
	}
	public void setOrderId(Integer orderId) {
		this.orderId = orderId;
	}
	public Customer getCustomer() {
		return customer;
	}
	public void setCustomer(Customer customer) {
		this.customer = customer;
	}
	public Date getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}
	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", customer=" + customer + ", orderDate=" + orderDate + "]\n";
	}

}

OrderDetail订单详情实体类

package com.abc.mybatis.domain;

public class OrderDetail {
	
	private Integer orderDetailId;
	private Product product;
	private Integer pNumber;
	private Double pPrice;
	private Order order;
	public Integer getOrderDetailId() {
		return orderDetailId;
	}
	public void setOrderDetailId(Integer orderDetailId) {
		this.orderDetailId = orderDetailId;
	}
	public Product getProduct() {
		return product;
	}
	public void setProduct(Product product) {
		this.product = product;
	}
	public Integer getpNumber() {
		return pNumber;
	}
	public void setpNumber(Integer pNumber) {
		this.pNumber = pNumber;
	}
	public Double getpPrice() {
		return pPrice;
	}
	public void setpPrice(Double pPrice) {
		this.pPrice = pPrice;
	}
	public Order getOrder() {
		return order;
	}
	public void setOrder(Order order) {
		this.order = order;
	}

}

===============================

方案二：

2.一对一的关联查询如何操作：简单粗暴，就是针对查询结果集再定义一个实体类与这些字段做映射，这个很容易理解，不用考虑关系

只定义一个实体类，需要什么就定义什么属性

package com.neuedu.mybatis.bo;

import java.util.Date;

import com.neuedu.abc.domain.Customer;

/**
 * 即封装订单数据又封装客户信息
 * @author CZW
 *
 */

public class OrderCustomer {
	
	private Integer orderId;
	private Date orderDate;
	private Integer cId;
	private String cName;
	private String cPhone;
	private String cAddress;
	public Integer getOrderId() {
		return orderId;
	}
	public void setOrderId(Integer orderId) {
		this.orderId = orderId;
	}
	public Date getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}
	public Integer getcId() {
		return cId;
	}
	public void setcId(Integer cId) {
		this.cId = cId;
	}
	public String getcName() {
		return cName;
	}
	public void setcName(String cName) {
		this.cName = cName;
	}
	public String getcPhone() {
		return cPhone;
	}
	public void setcPhone(String cPhone) {
		this.cPhone = cPhone;
	}
	public String getcAddress() {
		return cAddress;
	}
	public void setcAddress(String cAddress) {
		this.cAddress = cAddress;
	}
	@Override
	public String toString() {
		return "OrderCustomer [orderId=" + orderId + ", orderDate=" + orderDate + ", cId=" + cId + ", cName=" + cName
				+ ", cPhone=" + cPhone + ", cAddress=" + cAddress + "]\n";
	}
}

OrderMapper.xml配置映射

<!-- public List<Order> queryAllOrderWithCustomer2(); -->
	<select id="queryAllOrderWithCustomer2" resultMap="orderWithCustomerMap2">
		select o.order_id,o.order_date,c.cid,c.cname,c.c_phone,c.c_address
		from orderes o,customer c
		where o.cid=c.cid
	</select>
	
	<resultMap type="orderCustomer" id="orderWithCustomerMap2">
	<!-- 由于查询结果集中的字段名和映射的实体类中的属性名不一样，所以要映射 -->
		<id column="order_id" property="orderId"></id>
		<result column="order_date" property="orderDate"/>
		<result column="cid" property="cId"/>
		<result column="cname" property="cName"/>
		<result column="c_phone" property="cPhone"/>
		<result column="c_address" property="cAddress"/>
	</resultMap>

sql脚本

第一步：	建表
订单表	    id	客户编号	日期		
订单明细表	订单明细编号	商品编号	商品数量	单价	订单编号（外键
客户表	        客户编号	客户其他信息
商品表            商品编号  商品名称 。。。			

create table customer(
  cid number(3) primary key,
  cname varchar2(40) not null,
  c_phone varchar2(20) not null,
  c_address varchar2(50) not null
);

create table product(
  p_id number(6) primary key,
  p_name varchar2(60) not null
);

create table orderes(
  order_id number(4) primary key,
  cid number(3),
  order_date date not null
);

alter table orderes add constraint f_orderes_cid  foreign key(cid)  references customer(cid);

create table order_detail(
  order_detail_id number(5) primary key,
  p_id number(6) not null,---商品数量  单价  订单编号（外键
  P_number number(7) not null,
  p_price number(7,2) not null,
  order_id number(4) 
);
alter table order_detail add constraint f_orderdetail_pid foreign key(p_id) references product(p_id);
alter table order_detail add constraint f_orderdetail_orderid foreign key(order_id) references orderes(order_id);
---生成id的序列
create sequence order_detail_id;
create sequence order_id;

第二步：	创建实体类  注意外键应该定义成对象类型
第三步：	写dao

----查询查询所有用户信息，关联查询订单及订单明细信息，订单明细信息中关联查询商品信息的sql语句----------------------------------------
select 
  c.cid,c.cname,c.c_phone,c.c_address,
  o.order_id,o.order_date,
  od.order_detail_id,od.p_number,od.p_price,
  p.p_id,p.p_name
from customer c,orderes o,order_detail od,product p
    where c.cid=o.cid and o.order_id=od.order_id and od.p_id=p.p_id