<!-- 訂單查詢關聯(lián)用戶的resultMap將整個查詢的結果映射到cn.itcast.mybatis.po.Orders中   -->  
    <resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersUserResultMap">  
        <!-- 配置映射的訂單信息 -->  
        <!-- id：指定查詢列中的唯 一標識，訂單信息的中的唯 一標識，如果有多個列組成唯一標識，配置多個id ，column：訂單信息的唯 一標識列 ，property：訂單信息的唯 一標識 列所映射到Orders中哪個屬性  -->  
 
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/> 
        <result column="number" property="number"/> 
        <result column="createtime" property="createtime"/>
        <result column="note" property=note/>          
 
        <!-- 配置映射的關聯(lián)的用戶信息 --> 
        <!-- association：用于映射關聯(lián)查詢單個對象的信息property：要將關聯(lián)查詢的用戶信息映射到Orders中哪個屬性 -->  
 
        <association property="user"  javaType="cn.itcast.mybatis.po.User">
            <!-- id：關聯(lián)查詢用戶的唯 一標識
            column：指定唯 一標識用戶信息的列
            javaType：映射到user的哪個屬性-->
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
        </association> 
    </resultMap>

<resultMap type="cn.itcast.mybatis.po.Orders" id="OrdersAndOrderDetailResultMap" extends="OrdersUserResultMap">
        <!-- 訂單信息 --> 
        <!-- 用戶信息 -->
        <!-- 使用extends繼承，不用在中配置訂單信息和用戶信息的映射 -->
        <!-- 訂單明細信息一個訂單關聯(lián)查詢出了多條明細，要使用collection進行映射
        collection：對關聯(lián)查詢到多條記錄映射到集合對象中
        property：將關聯(lián)查詢到多條記錄映射到cn.itcast.mybatis.po.Orders哪個屬性 
        ofType：指定映射到list集合屬性中pojo的類型 -->  
 
         <collection property="orderdetails" ofType="cn.itcast.mybatis.po.Orderdetail">
            <!-- id：訂單明細唯 一標識 
            property:要將訂單明細的唯 一標識 映射到cn.itcast.mybatis.po.Orderdetail的哪個屬性-->  
            <id column="orderdetail_id" property="id"/>
            <result column="items_id" property="itemsId"/>
            <result column="items_num" property="itemsNum"/>
            <result column="orders_id" property="ordersId"/>
         </collection>
    </resultMap> 

<resultMap type="hdu.terence.bean.Message" id="MessageResult"> 
    <!--存放Dao值--><!--type是和數(shù)據(jù)庫對應的bean類名Message-->
    <id column="id" jdbcType="INTEGER"property=" id"/><!--主鍵標簽-->
    <result column="COMMAND" jdbcType="VARCHAR" property="command"/>
    <result column="DESCRIPTION" jdbcType="VARCHAR" property="description"/>
    <result column="CONTENT" jdbcType="VARCHAR" property="content"/>
  </resultMap> 
 
  <select id="queryMessageList" parameterType="hdu.terence.bean.Message" resultMap="MessageResult">
    SELECTID,COMMAND,DESCRIPTION,CONTENT FROM message WHERE 1=1    
    <if test="command!=null and!&quot;&quot;.equals(command.trim())">
    and COMMAND=#{command}
    </if>
    <if test="description!=null and!&quot;&quot;.equals(description.trim())">
    and DESCRIPTION like '%' #{description} '%'
    </if> 
  </select>

<select resultType="User" id="findAll">select *from user </select>

<select resultType="com.itxiaotong.pojo.User" id="findById" parameterType="int">select *from user where id = #{userId} </select>

<select resultType="com.itxiaotong.pojo.User" id="findByUsernameLike" parameterType="string">
<bind value="'%'+username+'%'" name="likeName"/>
select * from user where username like #{likeName} 
</select>

<select resultType="com.itxiaotong.pojo.User" id="findPage">select * from user limit #{param1},#{param2} </select>
 
<select resultType="com.itxiaotong.pojo.User" id="findPage1">select * from user limit #{startIndex},#{pageSize} </select>

<select resultType="User" id="findPage2" parameterType="PageQuery">select * from user limit #{startIndex},#{pageSize} </select>

package com.itxiaotong.pojo;
 
public class PageQuery {
    private int startIndex;
    private int pageSize;
 
    public PageQuery() {
    }
 
    public PageQuery(int startIndex, int pageSize) {
        this.startIndex = startIndex;
        this.pageSize = pageSize;
    }
 
    public int getStartIndex() {
        return startIndex;
    }
 
    public void setStartIndex(int startIndex) {
        this.startIndex = startIndex;
    }
 
    public int getPageSize() {
        return pageSize;
    }
 
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
}

<select resultType="com.itxiaotong.pojo.User" id="findPage3" parameterType="map">select * from user limit #{startIndex},#{pageSize} </select>
 
<select resultType="int" id="findCount">select count(id) from user </select>

 <select id="queryCommandListByPage" resultMap="CommandResult" >
      select <include refid="columns"/> from command a left join command_content b 
    on a.id=b.command_id
      <where>
          <if test="command.name != null and !"".equals(command.name.trim())">
            and a.name=#{command.name}
        </if>
        <if test="command.description != null and !"".equals(command.description.trim())">
            and a.description like '%' #{command.description} '%'
        </if>
      </where>
      <if test="flag==1">
        group by aid
    </if>
      order by id
  </select>
  <sql  id="columns">
      a.id aid,a.name,a.description,b.content,b.id,b.command_id
  </sql>

 /**
     * 攔截器實現(xiàn)分頁
     */
    public List<command> queryCommandListByPage(Map<String,Object>parameter);

<insert id="add" parameterType="com.itxiaotong.pojo.User">insert into user(username, sex, address)values (#{username}, #{sex}, #{address}) </insert>

<update id="update" parameterType="com.itxiaotong.pojo.User">update user set username = #{username},sex = #{sex},address=#{address} where id = #{id} </update>

<delete id="delete" parameterType="int">delete from user where id = #{id} </delete>

<insert id="add2" parameterType="com.itxiaotong.pojo.User">
 
     <!-- keyProperty:主鍵屬性名 keyColumn:主鍵列名 resultType:主鍵類型 order:執(zhí)行時機 -->
 
     <selectKey resultType="int" order="AFTER" keyColumn="id" keyProperty="id">SELECT LAST_INSERT_ID(); </selectKey>
insert into user(username, sex, address)values (#{username}, #{sex}, #{address}) 
</insert>

<select resultType="com.itxiaotong.pojo.User" id="findPage3" parameterType="map">select * from user limit #{startIndex},#{pageSize} </select>