ibatis总结

1.返回结果集配置

<resultMap type="ProviderRule" id="providerRuleResultMap">

<id property="settleTransRuleID"         column="settleTransRuleId"/>

<result property="instName"         column="inst_name"/>

/*1对多配置*/

<collection property="settleRules" ofType="SettleRule" column="settleTransRuleID" select="selectSettleRulesById">

         </collection>

</resultMap>

<resultMap type="SettleRule" id="settleRuleMap">

<result property="settleRuleId" column="SETTLERULEID" />

<result property="settleType" column="SETTLETYPE" />

</resultMap>

在配置resultMap时候，如果column配置跟数据库字段名不是一样，必须用a.ss as column配置的名称

否则 sql查询处理的结果集无法跟resultMap配置的BEAN做一一对应，无法注解到bean中

<select id="selectSettleRulesById" parameterType="SettleRule" resultMap="settleRuleMap">

SELECT * FROM set_tb_settleRule WHERE SettleRuleID = #{settleTransRuleID}

</select>

其中column为字段别名，实际上对应你的sql中as的别名

如果实体Bean的字段和数据库的字段一样，是一一对应，则可以不用配置resultMap

 

2.动态生存SQL语句（报表,查询字段）

<select id="showOrder" parameterType="HashMap" resultType="CineOnlineOrder">

SELECT *  FROM ${tableName}

 WHERE  ${id} = #{orderId}                

</select>

以上是一种动态的配置需要查询的表名和字段

 

CineOnlineOrder为在配置文件中，

  <!-- 定义实体类的别名 --> 

<typeAliases> 

<typeAlias type="com.mopon.settle.entity.CineOnlineOrder" alias="CineOnlineOrder" />

</typeAliases>

<select id="queryProviderReportForCount" parameterType="HashMap" resultType="int">

SELECT count(*) FROM  AAA

</select>

3.封装公共SQL

如果重复sql多，则可以利用下面

<!-- 获取分组个数的公共SQL条件 -->

<sql id="countSumSql">

<if test="instName!=null and instName!=''">

 AND inst.instName LIKE '%${instName}%'

</if>

<if test="areaName!=null and areaName!=''">

 AND inst.cityName LIKE '%${areaName}%'

</if>

<if test="startTime != null and startTime !='' and endTime != null and endTime !=''" >

                                   AND sSum.settleSumMonth between #{startTime} and #{endTime} 

                           </if>

</sql>

 

<include refid="countSumSql"/>

4.如果在sqlMap中定义了namespace

<sqlMap namespace="user"> 

    <typeAlias alias="user" type="com.lavasoft.ssi.domain.User"/> 

    <resultMap id="result_basc" class="user"> 

        <result property="id" column="id"/> 

        <result property="username" column="username"/> 

        <result property="remark" column="remark"/> 

    </resultMap> 

 

    <resultMap id="result" class="user" extends="result_basc"> 

        <result property="roleList" column="id" select="role.getByUserId"/> 

    </resultMap> 

 

    <insert id="insert" parameterClass="user"> 

        insert into user(username,remark) values(#username#,#remark#) 

        <selectKey keyProperty="id" resultClass="long"> 

            select LAST_INSERT_ID() 

        </selectKey> 

    </insert> 

    <select id="getById" parameterClass="long" resultMap="result_basc"> 

        select * from user where id = #value# 

    </select> 

 

    <select id="getByRoleId" parameterClass="long" resultMap="result_basc"> 

        select u.* from user u where u.id in 

        (select userId from tlink where roleId=#value#) 

    </select> 

</sqlMap>

 

<sqlMap namespace="role"> 

    <typeAlias alias="role" type="com.lavasoft.ssi.domain.Role"/> 

    <resultMap id="result_basc" class="role"> 

        <result property="id" column="id"/> 

        <result property="rolename" column="rolename"/> 

        <result property="descp" column="descp"/> 

    </resultMap> 

        <!--为多对多配置--> 

    <select id="getByUserId" parameterClass="long" resultClass="role" resultMap="result_basc"> 

        select r.* from role r where r.id in 

        (select roleId from tlink where userId=#value#) 

    </select> 

</sqlMap>