Ibatis使用Map作为映射结果，进行多表连接查询

第一步：XML文件ResultMap和查询语句的编写

1、resultMap内容

 <resultMap id="OrderMap" class="java.util.HashMap" >
    <result column="ID" property="id" jdbcType="DECIMAL" />
    <result column="ORDER_NO" property="orderNo" jdbcType="VARCHAR" />
    <result column="CREATE_DATE" property="createDate" jdbcType="TIMESTAMP" javaType="java.util.Date"/> <!--可以避免出现oracle.sql.TIMESTAMP cannot be cast to java.util.Date-->
    <result column="CREATE_USER_NAME" property="createUserName" jdbcType="VARCHAR" />
    <result column="TOTAL_FEE" property="totalFee" jdbcType="DECIMAL" />
    <result column="ORDER_STATUS" property="orderStatus" jdbcType="VARCHAR" />
    <result column="ROOM_NO" property="roomNo" jdbcType="VARCHAR" />
    <result column="ROOM_TYPE" property="roomType" jdbcType="VARCHAR" />
    <result column="NUM" property="num" jdbcType="DECIMAL" /> 
    <result column="DEPOSIT" property="deposit" jdbcType="DECIMAL" />
   </resultMap>

2、查询用到的SQL语句

<select id="selectOrdersByMap"  resultMap="OrderMap" parameterClass="map" remapResults="true">
  	 select tr.id,
  	        tr.order_no,
            tr.status order_status,
	        tu.name create_user_name,
	        tr.create_date,
	        tr.total_fee,
	        td.room_no,
	        tm.type room_type,
	        td.num,
	        td.deposit
     from t_order tr
     left join t_order_detail td
     on tr.id = td.order_id
     left join t_user tu
     on tr.create_user_id = tu.id
     left join t_room tm 
     on td.room_no = tm.room_no
     where 1=1
  		  <isNotEmpty property="orderNo">
  		      and tr.order_no = #orderNo#
  		  </isNotEmpty>
  		  <isNotEmpty property="orderStatus">
  		      and tr.status = #orderStatus#
  		  </isNotEmpty>
  		  <isNotEmpty property="roomNo">
  		      and td.room_no = #roomNo#
  		  </isNotEmpty>
  		   <isNotEmpty property="startTime">
  		        <![CDATA[ and  tr.create_date >= #startTime#]]>
  		  </isNotEmpty>
  		  <isNotEmpty property="endTime">
  		       <![CDATA[  and  tr.create_date <= #endTime# ]]>
  		  </isNotEmpty>
  </select>

第二步：DAO层的编写

public List<Map> selectOrderListByMap(Map map) {
		return getSqlMapClientTemplate().queryForList("T_ORDER.selectOrdersByMap", map);
	}