Ibatis 关联查询

目前公司的查询结果是关联的数据结构,

 

酒店列表的酒店对象 有个字段是 价格计划列表 .

 

对于这种关联的一对多数据结构, 我们可以在SQLMAP中完成, 而不必对查询结果进行遍历, 还可以配置延迟加载, 提高性能

 

SQLMAP:

 

<resultMap class="com.woyo.travel.hotel.dto.HotelSRVDto" id="hotelSearchDto">
    <result property="hotelId" column="hotel_id" />
	<result property="code" column="code" />
	<result property="pricePlanList"  column="{hotelId=hotel_id,beginDate=begin_date,endDate=end_date}" select="com.woyo.travel.hotel.dto.SearchSRVDto.searchPlanList" />	
  </resultMap>
<!--对象com.woyo.travel.hotel.dto.HotelSRVDto中必须存在 hotelId , beginDate , endDate 属性, 否则会报SQLMAP属性错误-->

  <select id="searchPlanList" resultMap="pricePlanSearchDto">
			SELECT pp.id id, hr.id room_id, hr.broadband broad_band, pp.pay_way pay_way_name, "cu xiao" promotion_info
			FROM t_htl_price_plan pp
			LEFT JOIN t_htl_hotel_room hr ON pp.room_id = hr.id
			LEFT JOIN t_htl_add_value_strategy avs ON pp.id = avs.price_plan_id
			WHERE hr.hotel_id=#hotelId#  AND pp.begin_date &gt;= #beginDate# AND pp.end_date &lt;= #endDate#  
  </select>
 

 

 最近 ， 公司的数据结构也要使用这种的关联加载 。 结构 ：

 

使用以上的方式加载， 属于  N+1 的加载方式， 比较消耗查询效率 。 。

 

这里碰到使用 SQLMAp 必须要注意的几个 地方：　

 

内部类不能被　ＳＱＬＭＡＰ　识别到：

 

比如　：　TheoryDesView　是　类　SuitAdminView　的内部类，　以下的写法，　Ｉｂａｔｉｓ　就不能识别。

 

如果有看到文章的牛人找到可以识别的方法请指教。　我就另外开了一个单独的类。

 

	<resultMap class="com.cares.flyingnet.crs.simulator.dto.SuitAdminView.TheoryDesView" id="TheoryDesView" >
		<result property="id" column="TRAINING_THEORY_ID" />
		<result property="t_date" column="t_date" />
		<result property="t_content" column="t_content" />
		<result property="ORDER_NUM" column="ORDER_NUM" />
	</resultMap>
	

 

 

于是采用了 另外的一种  Result Map 的加载方式 。

 

这种方式用　Ｌｅｆｔ　Ｊｏｉｎ　一次关联查询出所有的数据。在ＳＱＬＭＡＰ里面使用　ＧＲＯＵＰＢＹ　属性分组。　

 

	<typeAlias alias="SuitAdminView"
		type="com.cares.flyingnet.crs.simulator.dto.SuitAdminView." />
		
	<resultMap class="com.cares.flyingnet.crs.simulator.dto.SuitAdminView" id="SuitAdminViewRstMap" groupBy="suiteId">
		<result property="isScheduled" column="isScheduled" />
		<!-- <result property="changed" column="changed" /> -->
		<result property="suiteId" column="suiteId" />
		<result property="sign_property" column="sign_property" />
		<result property="batch_no" column="batch_no" />
		<result property="studentLock" column="studentLock" />
		<result property="bench_Month" column="bench_Month" />
		<result property="checkin_date" column="checkin_date" />
		<result property="suite_no" column="suite_no" />
		<result property="ac_type" column="ac_type" />
		<result property="train_com" column="train_com" />
		<result property="releaseStatus" column="releaseStatus" />
		<result property="theoryDesViewListMap" resultMap="SuitAdminView.TheoryDesView" />
		<result property="training_simDesViewListMap" resultMap="SuitAdminView.TrainingSimDesView" />
 		<result property="studentListMap" resultMap="SuitAdminView.PilotInfForVoteDto" />
 		<result property="simTeacherList" resultMap="SuitAdminView.SimTeacherView" />
	</resultMap>
	
	<resultMap class="com.cares.flyingnet.crs.simulator.dto.TheoryDesViewMap" id="TheoryDesView" >
		<result property="id" column="TRAINING_THEORY_ID" />
		<result property="t_date" column="t_date" />
		<result property="t_content" column="t_content" />
		<result property="ORDER_NUM" column="ORDER_NUM" />
	</resultMap>
	
	<resultMap class="com.cares.flyingnet.crs.simulator.dto.Training_simDesViewMap" id="TrainingSimDesView" >
		<result property="id" column="TRAINING_SIM_ID" />
		<result property="s_date" column="s_date" />
		<result property="s_time" column="s_time" />
		<result property="s_no" column="s_no" />
		<result property="S_ORDER" column="S_ORDER" />
		<result property="s_type" column="s_type" />
		<result property="teacherName" column="simTeacherName" />
		<result property="unitName" column="simUnitName" />
		<result property="changed" column="simChanged" />
		<result property="SIM_TEACHER_ID" column="SIM_TEACHER_ID" />
	</resultMap>
	
	<resultMap class="com.cares.flyingnet.crs.simulator.dto.PilotInfForVoteDto" id="PilotInfForVoteDto" >
		<result property="pilotName" column="pilotName" />
		<result property="unitName" column="unitName" />
		<result property="unitCode" column="unitCode" />
		<result property="month1" column="month1" />
		<result property="month2" column="month2" />
		<result property="valid_end_date" column="valid_end_date" />
		<result property="technicalStandard" column="technicalStandard" />
		<result property="changed" column="changed" />
		<result property="position" column="position" />
	</resultMap>
	
	<resultMap class="com.cares.flyingnet.crs.simulator.dto.SimTeacherDto" id="SimTeacherView" >
		<result property="simTeacherId" column="simTeacherId" />
		<result property="teacherNo" column="teacherNo" />
		<result property="TType" column="TType" />
		<result property="TUnit" column="TUnit" />
		<result property="bigSuiteNo" column="tBigSuiteNo" />
		<result property="pilotId" column="tPilotId" />
		<result property="pilotName" column="tPilotName" />
		<result property="totalDay" column="tTotalDay" />
		<result property="startDate" column="tStartDate" />
		<result property="endDate" column="tEndDate" />
		<result property="needType" column="tNeedType" />
		<result property="changed" column="tChanged" />
		<result property="teacherLock" column="teacherLock" />
		<result property="TUnitName" column="TUnitName" />
	</resultMap>
	
	<!-- 查询总排班表 wuao 2011-10-14 -->
	<select id="suitAdminViewList" parameterClass="java.util.Map" resultMap="SuitAdminViewRstMap">
		SELECT s.IS_SCHEDULED isScheduled,
		<!-- 'true' changed, -->
       s.SUITE_ID suiteId,
       s.SIGN_PROPERTY sign_property,
       s.BATCH_NO batch_no,
       s.STUDENT_LOCK studentLock,
       s.BENCH_MONTH bench_Month,
       s.CHECKIN_DATE checkin_date,
       s.SUITE_NO suite_no,
       s.AC_TYPE_CODE ac_type,
	   s.TRAIN_COM train_com,
	   s.RELEASE_STATUS releaseStatus,
	   
	   h.TRAINING_THEORY_ID,
	   h.T_DATE t_date, 
		h.T_CONTENT t_content,
		h.ORDER_NUM ORDER_NUM,
		
		m.TRAINING_SIM_ID,
		m.S_DATE s_date, 
		m.S_TIME s_time,
		m.S_NO s_no,
		m.S_ORDER S_ORDER,
		m.S_TYPE s_type,
		m.SIM_TEACHER_ID,
		'' simTeacherName,
		'' simUnitName,
		'' simChanged,
		
		stubasicinfo.pilotName,
		stubasicinfo.unitName,
		stubasicinfo.unitCode,
		stubasicinfo.month1,
		stubasicinfo.month2,
		stubasicinfo.valid_end_date,
		stubasicinfo.technicalStandard,
		stubasicinfo.changed,
		stubasicinfo.position,
		
		teabasicinfo.simTeacherId as simTeacherId,
		teabasicinfo.teacherNo as teacherNo,
		 teabasicinfo.TType as TType,
		 teabasicinfo.TUnit as TUnit,
		 teabasicinfo.bigSuiteNo as tBigSuiteNo,
		 teabasicinfo.pilotId as tPilotId,
		 teabasicinfo.pilotName as tPilotName,
		 teabasicinfo.totalDay as tTotalDay,
		teabasicinfo.startDate as tStartDate,
		teabasicinfo.endDate as tEndDate,
		teabasicinfo.needType as tNeedType,
		teabasicinfo.changed as tChanged,
		 teabasicinfo.teacherLock as teacherLock,
		 teabasicinfo.TUnitName as TUnitName
		
		
	  FROM SIM_SUITE s 
	  left join SIM_TRAINING_THEORY h on s.SUITE_ID = h.SUITE_ID
	  left join SIM_TRAINING_SIM m on s.SUITE_ID = m.SUITE_ID
	  left join
	  (
			SELECT distinct  P.PILOT_NAME pilotName, 
			simS.SUITE_ID SUITE_ID,
		    U.UNIT_NAME unitName,
		    U.UNIT_CODE unitCode,
		    S.MONTH1 month1,
		    S.MONTH2 month2,
		    S.valid_end_date as valid_end_date,
		    CASE
		    WHEN SUBSTR(NVL(S.TECHNICAL_STANDARD, '21'), 0, 1) = '1' THEN
		    '机长'
		    ELSE
		    '副驾驶'
		    END technicalStandard,
		    simS.CHANGED changed,
		    simS.position position 
		    FROM flight.PILOT_BASIC_INFO P, flight.PILOT_TECHNICAL_STANDARD S, flight.BD_AERO_UNIT
		    U,sim_student_assign simS
		    WHERE P.BASIC_INFO_ID = S.PILOT_INFO_ID
		    AND U.UNIT_CODE = SUBSTR(P.AERO_UNIT_CODE, 0, 4)
		    AND (S.MAIN_AC_TYPE_CODE = '11' OR S.MAIN_AC_TYPE_CODE = '12' OR
		    S.MAIN_AC_TYPE_CODE = '14')
		    AND P.WORK_TYPE = '5201'
				AND S.MONTH1 IS NOT NULL
				AND S.MONTH2 IS NOT NULL
				AND P.VALIDITY='VALID'
				and p.basic_info_id=simS.Pilot_Id
    
     ) stubasicinfo on s.SUITE_ID = stubasicinfo.SUITE_ID
     
     left join 
	 (
	 
		 select 
		t.SIM_TEACHER_ID as simTeacherId,
		 t.TEACHER_NO as teacherNo,
		 t.T_TYPE as TType,
		 t.T_UNIT as TUnit,
		 t.BIG_SUITE_NO as bigSuiteNo,
		 t.PILOT_ID as pilotId,
		 p.pilot_name as pilotName,
		 t.TOTAL_DAY as totalDay,
		 t.START_DATE as startDate,
		 t.END_DATE as endDate,
		 t.NEED_TYPE as needType,
		 t.CHANGED as changed,
		 t.TEACHER_LOCK as teacherLock,
		 d.unit_name as TUnitName
		  from SIM_TEACHER  t left join 
		  flight.bd_aero_unit d on substr(t.t_unit,1,4)=d.unit_code
		  left join 
		  flight.pilot_basic_info p on p.basic_info_id = t.PILOT_ID
  
	 )   teabasicinfo on m.SIM_TEACHER_ID = teabasicinfo.simTeacherId
	  
     
	  WHERE s.FIXED_YEAR=#fixedYear#
	   AND  s.AC_TYPE_CODE=#acType#
	   AND s.TRAIN_COM=#trainCom#
	   AND s.IS_SCHEDULED = 'Y'
	   <dynamic>
			<isNotNull prepend="and" property="batchNo">
			   s.BATCH_NO=#batchNo#
			</isNotNull>
			<isNotNull prepend="and" property="suiteId">
			   s.SUITE_ID=#suiteId#
			</isNotNull>
			<isNotNull prepend="and" property="benchMonth">
			   s.BENCH_MONTH=#benchMonth#
			</isNotNull>
			<isNotNull prepend="and" property="isScheduled">
			   s.IS_SCHEDULED=#isScheduled#
			</isNotNull>
			<isNotNull prepend="and" property="startSuiteNo">
			   s.SUITE_NO between #startSuiteNo# and #endSuiteNo#
			</isNotNull>
	   </dynamic>
	 ORDER BY s.SUITE_NO
	</select>

 

 

这种方式，　查询出来子列表的数据仍然有很多是重复的。　

 

就到　Java  里面处理　过滤。