<?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();
}
}