Mybatis框架使用的学习

在沃尔玛项目中的Mybatis框架使用了两种实现CRUD的模式，为Dao文件编写相应的xml实现功能与直接在Dao的方法中直接使用注解开发

在xml中实现

<select id="getBillRefByPreInvoiceIds" resultType="com.xforceplus.wapp.repository.entity.TXfDeductPreInvoiceEntity">  
    select  
    txdpi.*  
    from  
    t_xf_deduct_pre_invoice txdpi  
    left join t_xf_pre_invoice txpi  
    on  
    txdpi.pre_invoice_id = txpi .id  
    where  
    txpi.pre_invoice_status &lt;&gt; 7  
    and txdpi.pre_invoice_id in  
    <foreach collection="preInvoiceIds" item="id" index="index" open="(" close=")" separator=",">  
        #{id}  
    </foreach>  
</select>

注解实现

@Update("update t_dx_record_invoice set remaining_amount = IIF(remaining_amount is null , invoice_amount - #{et.remainingAmount}  , "  
          + "IIF( remaining_amount > invoice_amount,  invoice_amount- #{et.remainingAmount} ,  remaining_amount- #{et.remainingAmount} ))" +  
            "where id = #{et.id} " +  
            "and IIF(remaining_amount is null ,invoice_amount-#{et.remainingAmount}, remaining_amount - #{et.remainingAmount}) >= 0 "  
//            + "and IIF(remaining_amount is null ,remaining_amount - #{et.remainingAmount}) <= invoice_amount"  
    )  
    int deductRemainingAmount(@Param(Constants.ENTITY) TDxRecordInvoiceEntity tDxRecordInvoiceEntity);

当然，对于一些简单的CRUD这两种方式实现都非常方便，但是有时候不同的参数不同需要的SQL也不同，这时候就需要用到动态SQL了，动态SQL就是用if choose (when, otherwise) trim (where, set) foreach来让动态的生成SQL语句

<sql id="bill_query_params">  
    <if test="null != queryTab and null != queryTab.queryParams and queryTab.queryParams.size!=0">  
            <!-- 防止 SQL中  AND(()) 出现 -->  
            <if test="queryTab.queryParams[0].billStatus != null or queryTab.queryParams[0].settlementStatus != null">  
                <!-- 业务单+结算单组合状态对象列表 -->  
                <foreach collection="queryTab.queryParams" item="param" index="index" open="AND ((" close="))" separator=" OR ">  
                    <!-- 业务单状态判断 -->  
                    (  
                    <if test="param.billStatus !=null and param.billStatus.size!=0">  
                        d.status in  
                        <foreach collection="param.billStatus" item="billStatus" index="index" open="(" close=")"  
                                 separator=",">  
                            #{billStatus}  
                        </foreach>  
                    </if>                    <!-- 结算单状态判断 -->  
                    <if test="param.settlementStatus !=null and param.settlementStatus.size!=0">  
                        AND s.settlement_status in  
                        <foreach collection="param.settlementStatus" item="settStatus" index="index" open="("  
                                 close=")" separator=",">  
                            #{settStatus}  
                        </foreach>  
                    </if>                    <!-- 业务单开票状态判断 -->  
                    <if test="param.makeInvoiceStatus !=null and param.makeInvoiceStatus.size!=0">  
                        AND d.make_invoice_status in  
                        <foreach collection="param.makeInvoiceStatus" item="makeStatus" index="index" open="("  
                                 close=")" separator=",">  
                            #{makeStatus}  
                        </foreach>  
                    </if>                    )  
                </foreach>  
            </if>    </if></sql>

沃尔玛项目中这个动态参数SQL就是充分利用了mybatis的灵活性让SQL语句在开发过程中复用率得到极大的提高，简化了大量的SQL代码语句，让我们可以在SQL层面去执行一个逻辑代码。同时，这也是一个SQL片段，可以被多个SQL语句引用，极大的节约了效率。