Mybatis学习（二）——动态代理、高级查询结果映射

<?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.rykj.mapper.OrderMapper">
	<resultMap type="com.rykj.dto.CusVO" id="basemap2">

		<id column="cusId" property="id" />
		<result column="cusName" property="cusName" />
		<result column="sex" property="sex" />
	</resultMap>
	<!-- 一对一 -->
	<resultMap type="com.rykj.dto.OrderVO" id="map1">

		<!-- 映射主键 -->
		<id column="id" property="id" />

		<result column="order_no" property="orderNo" />
		<result column="cusid" property="cusId" />
		<result column="orderDate" property="cusDate" />

		<!-- association property:一对一的属性名 javaType:指定一对一属性的类型 -->
		<association property="cus" javaType="Customer">

			<!-- 一对一的id：可以不去映射，但是建议映射：可以提高结果映射效率 -->
			<id column="cusid" property="id" />

			<result column="cusName" property="cusName" />
			<result column="sex" property="sex" />
		</association>
	</resultMap>


	<select id="selectOrderAndCus" resultMap="map1">
		select a.*,cusName,sex
		FROM
		b_ordermain a LEFT JOIN s_cus b on a.cusid=b.id
	</select>

	<!-- 一对多结果映射 -->

	<resultMap type="com.rykj.dto.CusVO" id="map2">

		<!-- 映射主键 -->
		<id column="cus_id" property="id" />
		<result column="cusName" property="cusName" />
		<result column="sex" property="sex" />
		<!-- 将同一用户的两个订单信息映射到一个集合中： collection： property：扩展类中集合的属性名 ofType:集合属性的泛型 -->
		<collection property="orders" ofType="OrderMain">

			<!-- 一对一的id：可以不去映射，但是建议映射：可以提高结果映射效率 -->
			<id column="cusid" property="id" />
			<result column="order_no" property="orderNo" />
			<result column="cusid" property="cusId" />
			<result column="orderDate" property="cusDate" />

		</collection>
	</resultMap>


	<select id="selectCusAndOrder" resultMap="map2">
		SELECT b.id as
		cus_id,cusName,sex,a.*
		from s_cus b left join b_ordermain a on
		a.cusid=b.id
	</select>


	<!-- 多对多映射关系 -->
	<resultMap type="com.rykj.dto.CusVO" id="map3" extends="basemap2">

		<!-- customer和ordermain是一对多的关系，使用collection -->
		<collection property="orders" ofType="OrderMain">
			<id column="orderId" property="id" />
			<result column="cusId" property="cusId" />
			<!-- ordermain和orderdetails是一对多的关系，使用collection -->
			<collection property="details" ofType="orderdetail">
				<id column="id" property="detailId" />
				<!-- orderdetails和goods是一对一的关系，使用association -->
				<association property="goods" javaType="goods">
					<result column="goodsName" property="goodsname" />
					<result column="price" property="price" />
					<result column="gg" property="gg" />
				</association>
			</collection>
		</collection>
	</resultMap>

	<select id="selectUserGoods" resultMap="map3">
		select a.id
		cusId,a.cusName,a.sex,d.*,b.id orderId,c.id detailId from s_cus
		a left
		join b_ordermain b on a.id = b.cusid
		left join b_orderdetail c on b.id
		= c.orderid
		left join b_goods d on c.goodsId = d.id
	</select>

	<resultMap type="com.rykj.dto.CusVO" id="map4" extends="basemap2">
		<!-- select:mapperStatementId,查询的mapperStatementId column:上面mapperStatementId查询需要的参数，如果需要传入的参数是多个，column="{column1=value1,column2=value2}" -->
		<collection property="orders" ofType="ordermain" select="selectMainByCusId"
			column="id"></collection>
	</resultMap>
	
	
	<select id="selectCus" resultMap="map4">
		select * from s_cus
	</select>
	<select id="selectMainByCusId" resultType="ordermain"
		parameterType="int">
		select * from b_ordermain where cusId=#{id}
	</select>



</mapper>

配置文件配置好之后

同一包下写接口，接口名与ID一致

接口类：

package com.rykj.mapper;

import java.util.List;

import com.rykj.dto.CusVO;
import com.rykj.dto.OrderVO;

public interface OrderMapper {
	List<OrderVO > selectOrderAndCus();
	List<CusVO> selectCusAndOrder();
	List<CusVO> selectUserGoods();
	List<CusVO> selectCus();
	List<CusVO> selectMainByCusId();
	List<OrderVO> selectOrders();
	List<OrderVO> selectCusById();
	

}

实现类：

package com.rykj.test;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.rykj.dto.CusVO;
import com.rykj.dto.OrderVO;
import com.rykj.mapper.OrderMapper;
import com.rykj.pojo.OrderMain;
import com.rykj.util.SqlSessionFactory;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		selectOrderAndCusTest();
		//selectCusAndOrderTest();
		//selectUserGoodsTest();
		//selectCusByIdTest();
		//selectMainByCusIdTest();
		//selectOrders();
		//selectCusById();
		

	}
	
	private static void selectCusById() {
		SqlSession sqlSession=SqlSessionFactory.createSqlSession();
		OrderMapper mapper =sqlSession.getMapper(OrderMapper.class);
		List<OrderVO> orders=mapper.selectOrderAndCus();
		for (OrderVO orderVO : orders) {
			System.out.println("id:"+orderVO.getId());
			System.out.println("cusid:"+orderVO.getCusId());
			System.out.println("OrderNo:"+orderVO.getOrderNo());
			System.out.println("OrderDate:"+orderVO.getCusDate());	
		}
		sqlSession.commit();
		sqlSession.close();
	}

	private static void selectOrders() {
		SqlSession sqlSession=SqlSessionFactory.createSqlSession();
		OrderMapper mapper =sqlSession.getMapper(OrderMapper.class);
		List<OrderVO> orders=mapper.selectOrderAndCus();
		for (OrderVO orderVO : orders) {
			System.out.println("id:"+orderVO.getId());
			System.out.println("cusid:"+orderVO.getCusId());
			System.out.println("OrderNo:"+orderVO.getOrderNo());
			System.out.println("OrderDate:"+orderVO.getCusDate());	
		}
		sqlSession.commit();
		sqlSession.close();
		
	}
	
}