一双跑鞋的mybatis(三)---sql映射的配置

<?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="org.senssic.bean.Person">
            <!-- flushCache='true'每次都不缓存，statementType	 STA TEMENT,PREPARED 或 CALLABLE 的一种。这会让 MyBatis 使用选择使用 Statement,PreparedStatement 或 CallableStatement。 默认值:PREPARED。 -->
            <insert id="add" parameterType="org.senssic.bean.Person" flushCache="true" statementType="PREPARED">
               insert into person(name,age) value(#{name},#{age})
             
            </insert>
            <update id="update" parameterType="org.senssic.bean.Person">
  update person set name = #{name},age= #{age} where id = #{id}
              </update>

             <delete id="delete" parameterType="int">
                  delete from person where id = #{id}
           </delete>
           <select id="find" resultType="org.senssic.bean.Person">
                select * from person
           </select>
           <!-- 分页使用map传入参数 -->
        <select id="paging" parameterType="map" resultType="User" >
        select * from person where name like #{name} limit #{pageSize},#{pageOffset}
      </select>
      <!-- 返回结果以Map形式接受 -->
      <select id="selectUsers" parameterType="int" resultType="map">
          select id, name,age from person where id = #{id}
       </select>
       <!-- 使用别名避免数据库字段和实体字段不一致报错 -->
       <select id="selectUsers" resultType="User">
            select user_id             as "id",
                user_name              as "name",
                user_age               as "age"
                from person
                 where id = #{id}
        </select>
        <!-- 另一种方式避免数据库字段和实体字段不一致报错 -->
        <resultMap id="userResultMap" type="User">
          <id property="id" column="user_id" />
          <result property="username" column="username"/>
          <result property="password" column="password"/>
        </resultMap>
        <!-- 此处的resultMap要指向上面的resultMap的映射 -->
        <select id="selectUsers" resultMap="userResultMap">
           select user_id, user_get
           from person
             where id = #{id}
           </select>
           
           <!-- resultMap做关联映射 
           resultMap
           constructor - 类在实例化时,用来注入结果到构造方法中
           idArg - ID 参数;标记结果作为 ID 可以帮助提高整体效能
           arg - 注入到构造方法的一个普通结果
           id – 一个 ID 结果;标记结果作为 ID 可以帮助提高整体效能
           result – 注入到字段或 JavaBean 属性的普通结果
           association – 一个复杂的类型关联;许多结果将包成这种类型
                                                                  嵌入结果映射 – 结果映射自身的关联,或者参考一个
           collection – 复杂类型的集
                             嵌入结果映射 – 结果映射自身的集,或者参考一个
           discriminator – 使用结果值来决定使用哪个结果映射
           case – 基于某些值的结果映射
                              嵌入结果映射 – 这种情形结果也映射它本身,因此可以包含很多相 同的元素,或者它可以参照一个外部的结果映射。
           -->
           <resultMap type="org.senssic.bean.Person" id="peradResult">
             <id property="id" column="id"/>
             <result property="name" column="name"/>
             <result property="age" column="age"/>
             <collection property="addresses" ofType="org.senssic.bean.Address" >
                <result property="addrs" column="addrs"/>
               
             </collection>
           </resultMap>
           <select id="selectperson" parameterType="int" resultMap="peradResult">
             select
             B.id,
             B.name,
             A.addrs,
            from person B left  join address A on B.id = A.per_id
             where B.id = #{id}
          </select>
          
          <resultMap type="org.senssic.bean.Address" id="AddressResult">
            <result property="addrs" column="addrs"/>
            <association property="person" javaType="org.senssic.bean.Person">
              <id property="id" column="id"/>
              <result property="name" column="name"/>
            </association>
          </resultMap>
          <select id="selectaddress" parameterType="int" resultMap="AddressResult">
             select
             B.id,
             B.name,
             A.addrs,
            from address A left  join person B on B.id = A.per_id
             where B.id = #{id}
          </select>
           
           
     </mapper>

2.集合映射实例

 <resultMap id="shttpmap" autoMapping="true" type="tdh.bu.oms.core.portal.pojo.ShipmentDetail"></resultMap>
    <resultMap id="transorderinfomapper" autoMapping="true" type="tdh.bu.oms.core.portal.pojo.TransOrderInfo">
        <id property="transOrderId" column="transOrderId"/>
        <collection property="shipmentList" resultMap="shttpmap"  javaType="arraylist" ofType="tdh.bu.oms.core.portal.pojo.ShipmentDetail"/>
    </resultMap>
    <select id="queryTransOrder" parameterType="tdh.bu.oms.core.portal.pojo.OrderQvo"  resultMap="transorderinfomapper">
           SELECT
                tro.trans_order_id transOrderId,
                tro.route_id routeName,
                tro.transCom_name carrier,
                tro.trans_order_no orderNo,
              sd.Shipment_No shipmentNo,
              sd.consignee consignee,
              sd.consignee_contact consigneeContact,
              sd.cargo_name cargoName,
              sd.trans_fee	carriage,
              sd.announce_price announcePrice,
              sd.insurance_fee insuranceFee,
              sd.pay_type_cd paymentType,
              sd.status_cd orderStatus,
              sd.weight weight,
              sd.volumn volume,
              sd.qty cargoAmount,
              0 as pickupType,
              0 as deliveryType
            FROM
                trans_order tro
            LEFT JOIN Shipment_Detail sd ON tro.trans_order_id = sd.trans_order_id
    </select>

3.两个基本一样的实体映射

<resultMap id="shttpmap" autoMapping="true" type="tdh.bu.oms.pojo.ShipmentDetail"></resultMap>
    <resultMap id="inventmap" autoMapping="true" type="tdh.bu.oms.pojo.Inventory"></resultMap>
    <resultMap id="transorderinfomapper" autoMapping="true" type="tdh.bu.oms.pojo.TransOrderInfo">
        <id property="transOrderId" column="transOrderId"/>
        <collection property="inventoryList" resultMap="inventmap"  javaType="arraylist" ofType="tdh.bu.oms.pojo.Inventory" columnPrefix="">
            <id column="itransOrderId" property="itransOrderId"></id>
        </collection>
        <collection property="shipmentList" resultMap="shttpmap"    javaType="arraylist" ofType="tdh.bu.oms.pojo.ShipmentDetail">
            <id column="stransOrderId" property="stransOrderId"></id>
        </collection>
    </resultMap>
    <select id="queryTransOrder" parameterType="tdh.bu.oms.pojo.OrderQvo"  resultMap="transorderinfomapper">
        SELECT
	tro.trans_order_id transOrderId,
	tro.route_id routeName,
	tro.transCom_name carrier,
	tro.trans_order_no orderNo,
	sd.trans_order_id stransOrderId,
	sd.Shipment_No shipmentNo,
	sd.consignee consignee,
	sd.consignee_contact consigneeContact,
	sd.cargo_name cargoName,
	sd.trans_fee carriage,
	sd.announce_price announcePrice,
	sd.insurance_fee insuranceFee,
	sd.pay_type_cd paymentType,
	sd.status_cd orderStatus,
	sd.weight weight,
	sd.volumn volume,
	sd.qty cargoAmount,
	0 AS pickupType,
	0 AS deliveryType,
	id.trans_order_id itransOrderId,
	id.Shipment_No ishipmentNo,
	id.consignee iconsignee,
	id.consignee_contact iconsigneeContact,
	id.cargo_name icargoName,
	id.trans_fee icarriage,
	id.announce_price iannouncePrice,
	id.insurance_fee iinsuranceFee,
	id.pay_type_cd ipaymentType,
	id.status_cd iorderStatus,
	id.weight iweight,
	id.volume ivolume,
	id.qty icargoAmount,
	0 AS ipickupType,
	0 AS ideliveryType
FROM
	trans_order tro
LEFT JOIN Shipment_Detail sd ON tro.trans_order_id = sd.trans_order_id
LEFT JOIN item_detail id on  tro.trans_order_id=id.trans_order_id
    </select>

注意:

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
   <property name="basePackage" value="tdh.bu.oms.core.portal.dao" />
   <property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>

自动扫描的时候mybatis会扫描指定包下面所有接口并自动生成代理实现类，所以 不要扫描service等别的接口，只扫描mybatis的dao接口不然都会被mybatis自动生成代理实现类的