Mybaits中Association元素使用

一、适用场景

Association元素适用于处理一对一类型关系，如下结构 OrderFlow和custInfo是一对一的，OrderFlow和OrderFlowInvoice也是一对一的

这种一对一的关系可以在一个表中也可以在多个表中

例子中的OrderFlow和custInfo在一个表中，OrderFlow和OrderFlowInvoice在两个表中

1、OrderFlow表的SQL如下：

CREATE TABLE `ck_order_flow_basic_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `order_id` bigint(20) NOT NULL COMMENT '包裹级别的订单号',
  `cart_id` bigint(20) DEFAULT NULL COMMENT '购物车ID',
  `cust_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id_inex` (`order_id`),
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8 COMMENT='订单基本信息表';

2、OrderFlowInvoice表的SQL如下：

CREATE TABLE `ck_order_flow_invoice` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `order_id` bigint(20) NOT NULL COMMENT '订单号',
  `invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
  `invoice_content` varchar(50) DEFAULT NULL COMMENT '发票内容',
  PRIMARY KEY (`id`),
  KEY `order_id_index` (`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COMMENT='订单发票';

3、OrderFlow对象的Java代码：

public class OrderFlow {

    private Long Id;
    private Long orderId;
	CustInfo custInfo;
	OrderFlowInvoice invoice;


	public Long getId() {
		return Id;
	}

	public void setId(Long orderFlowId) {
		this.Id = orderFlowId;
	}

	
	public CustInfo getCustInfo() {
		return custInfo;
	}

	public void setCustInfo(CustInfo custInfo) {
		this.custInfo = custInfo;
	}

	public Long getOrderId() {
		return orderId;
	}

	public void setOrderId(Long orderId) {
		this.orderId = orderId;
	}

	public OrderFlowInvoice getInvoice() {
		return invoice;
	}

	public void setInvoice(OrderFlowInvoice invoice) {
		this.invoice = invoice;
	}

}

4、CustInfo对象的Java代码：

public class CustInfo {
	private Long cartId;

	private Long custId;

	public Long getCartId() {
		return cartId;
	}

	public void setCartId(Long cartId) {
		this.cartId = cartId;
	}

	public Long getCustId() {
		return custId;
	}

	public void setCustId(Long custId) {
		this.custId = custId;
	}
}

5、OrderFlowInvoice对象的Java代码：

import java.util.Date;

public class OrderFlowInvoice {
	public OrderFlowInvoice() {

	}

	public OrderFlowInvoice(Integer id, Long orderid) {
		System.out.println(orderid);
	}

	private Long id;

	private Long orderId;

	private Short invoiceCategory;

	private String invoiceTitle;

	private String invoiceContent;

	private Date creationDate;

	private Date lastChangedDate;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Long getOrderId() {
		return orderId;
	}

	public void setOrderId(Long orderId) {
		this.orderId = orderId;
	}

	public String getInvoiceTitle() {
		return invoiceTitle;
	}

	public void setInvoiceTitle(String invoiceTitle) {
		this.invoiceTitle = invoiceTitle == null ? null : invoiceTitle.trim();
	}

	public String getInvoiceContent() {
		return invoiceContent;
	}

	public void setInvoiceContent(String invoiceContent) {
		this.invoiceContent = invoiceContent == null ? null : invoiceContent
				.trim();
	}

	public Date getCreationDate() {
		return creationDate;
	}

	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}

	public Date getLastChangedDate() {
		return lastChangedDate;
	}

	public void setLastChangedDate(Date lastChangedDate) {
		this.lastChangedDate = lastChangedDate;
	}
}

二、XML配置

1、MapperXML配置

<?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>
    <typeAliases> 
        <typeAlias alias="OrderFlow" type="webMyBatisModel.OrderFlow"/> 
    </typeAliases> 

    <environments default="development">
        <environment id="development">
        <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://10.255.254.22:3306/CK0" />
            <property name="username" value="writeuser"/>
            <property name="password" value="ddbackend"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="OrderFlow.xml"/>
    </mappers>
</configuration>

2、SQL映射XML文件

<association property="custInfo" resultMap="CustResultMap"></association>是

<?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="webMyBatisModel.OrderFlowMapper">
	<resultMap id="BaseResultMap" type="webMyBatisModel.OrderFlow">
		<id column="id" property="Id" jdbcType="BIGINT" />
		<result column="order_id" property="orderId" jdbcType="BIGINT" />
		<association property="custInfo" resultMap="CustResultMap">
		</association>
		<association property="invoice" resultMap="InvoiceResultMap">
		</association>
	</resultMap>
	<resultMap id="CustResultMap" type="webMyBatisModel.CustInfo">
		<result column="cart_id" property="cartId" jdbcType="BIGINT" />
		<result column="cust_id" property="custId" jdbcType="INTEGER" />
	</resultMap>
	<resultMap id="InvoiceResultMap" type="webMyBatisModel.OrderFlowInvoice">
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="order_id" property="orderId" jdbcType="BIGINT" />
		<result column="invoice_category" property="invoiceCategory"
			jdbcType="SMALLINT" />
		<result column="invoice_title" property="invoiceTitle"
			jdbcType="VARCHAR" />
		<result column="invoice_content" property="invoiceContent"
			jdbcType="VARCHAR" />
	</resultMap>
	<sql id="Base_Column_List">
		id, order_id,cart_id, cust_id
	</sql>

	<select id="selectByPrimaryKey" resultMap="BaseResultMap"
		parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from ck_order_flow_basic_info
		where id = #{id,jdbcType=BIGINT}
	</select>
	<select id="selectByID" resultMap="BaseResultMap" parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from ck_order_flow_basic_info
		where id = #{orderFlowId,jdbcType=BIGINT}
	</select>
	<select id="selectOrderByOrderID" resultMap="BaseResultMap"
		parameterType="java.lang.Long">
		select
		<include refid="Base_Column_List" />
		from ck_order_flow_basic_info
		where order_id = #{orderId,jdbcType=BIGINT}
	</select>
	<select id="selectOrderAndInvoiceByOrderID" resultMap="BaseResultMap"
		parameterType="java.lang.Long">
		select o.order_id,o.id,o.cart_id,o.cust_id,v.order_id,v.invoice_category,
		v.invoice_title,v.invoice_content
		from ck_order_flow_basic_info as o join ck_order_flow_invoice as v
		on o.order_id=v.order_id
		and o.order_id = #{orderId,jdbcType=BIGINT}
	</select>

三、mapper接口

import java.util.List;


public interface OrderFlowMapper {
	public List<OrderFlow> selectOrderByOrderID(long orderId);
	public OrderFlow selectByPrimaryKey(long id);
	public OrderFlow selectByID(long id);
	public OrderFlow selectOrderAndInvoiceByOrderID(long id);
}

四、测试代码

import java.io.Reader;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Test {
	private static SqlSessionFactory sqlSessionFactory;
	private static Reader reader;

	static {
		try {
			reader = Resources.getResourceAsReader("Configuration.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);

			// sqlSessionFactory.getConfiguration().addMapper(OrderFlowInvoiceMapper.class<OrderFlowInvoice>);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		SqlSession session = sqlSessionFactory.openSession();
		try {
			List<OrderFlow> orders = session
					.selectList(
							"webMyBatisModel.OrderFlowMapper.selectOrderAndInvoiceByOrderID",
							1410677970L);
			for (OrderFlow Flow : orders) {
				System.out.println(Flow.getCustInfo().getCustId());
				System.out.println(Flow.getInvoice().getInvoiceContent());
			}

		} finally {
			session.close();
		}
	}
}