actual combat 44 动态sql，crud模板

crud

查询select

<!--列表查询-->
<select id="selectLoanInfoList" parameterType="com.zyamc.debt.domain.LoanInfo" resultMap="LoanInfoResult">
    select id, loaner_id, loanee_id, contract_number
    from tbl_loan_info
    <where>
        <if test="loanerId != null "> and loaner_id = #{loanerId} </if>
        <if test="loaneeId != null "> and loanee_id = #{loaneeId} </if>
        <if test="contractNumber != null  and contractNumber != ''"> and contract_number like concat('%', #{contractNumber}, '%') </if>
    </where>
    order by `status` desc, expire_date asc
</select>

<!--遍历idList查询-->
<select id="selectLoanInfoByIds" parameterType="Long" resultMap="LoanInfoResult">
    <include refid="selectLoanInfoVo"/>
    <where>
        <if test="ids != null and ids.size() != 0">
            id in
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

<!--通过id查询-->
<select id="selectLoanInfoById" parameterType="Long" resultMap="LoanInfoResult" flushCache="true">
    <include refid="selectLoanInfoVo"/>
    where id = #{id}
</select>

新增insert

<!--条件新增-->
<insert id="insertLoanInfo" parameterType="com.zyamc.debt.domain.LoanInfo" useGeneratedKeys="true"
        keyProperty="id">
    insert into tbl_loan_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="loanerId != null">loaner_id, </if>
        <if test="loaneeId != null">loanee_id, </if>
        <if test="contractNumber != null and contractNumber != ''"> contract_number, </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="loanerId != null"> #{loanerId}, </if>
        <if test="loaneeId != null"> #{loaneeId}, </if>
        <if test="contractNumber != null and contractNumber != ''"> #{contractNumber}, </if>
    </trim>
</insert>

修改update

<!--条件修改-->
<update id="updateLoanInfo" parameterType="com.zyamc.debt.domain.LoanInfo">
    update tbl_loan_info
    <trim prefix="SET" suffixOverrides=",">
        <if test="loanerId != null"> loaner_id =  #{loanerId}, </if>
        <if test="loaneeId != null"> loanee_id = #{loaneeId}, </if>
        <if test="contractNumber != null and contractNumber != ''"> contract_number = #{contractNumber}, </if>
    </trim>
    where id = #{id}
</update>

删除delete

<!--根据id删除-->
<delete id="deleteLoanInfoById" parameterType="Long">
    delete
    from tbl_loan_info where id = #{id}
</delete>

<!--根据ids（逗号隔开的字符串）删除-->
<delete id="deleteLoanInfoByIds" parameterType="String">
    delete from tbl_loan_info where id in
    <foreach item="id" collection="array" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

全文件

<?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="com.zyamc.debt.mapper.LoanInfoMapper">

    <resultMap type="com.zyamc.debt.domain.LoanInfo" id="LoanInfoResult">
            <result property="id" column="id"/>
            <result property="loanerId" column="loaner_id"/>
            <result property="loaneeId" column="loanee_id"/>
            <result property="contractNumber" column="contract_number"/>
    </resultMap>

    <sql id="selectLoanInfoVo">
        select id, loaner_id, loanee_id, contract_number
        from tbl_loan_info
    </sql>

    <!--列表查询-->
    <select id="selectLoanInfoList" parameterType="com.zyamc.debt.domain.LoanInfo" resultMap="LoanInfoResult">
        select id, loaner_id, loanee_id, contract_number
        from tbl_loan_info
        <where>
            <if test="loanerId != null "> and loaner_id = #{loanerId} </if>
            <if test="loaneeId != null "> and loanee_id = #{loaneeId} </if>
            <if test="contractNumber != null  and contractNumber != ''"> and contract_number like concat('%', #{contractNumber}, '%') </if>
        </where>
        order by `status` desc, expire_date asc
    </select>

    <!--遍历idList查询-->
    <select id="selectLoanInfoByIds" parameterType="Long" resultMap="LoanInfoResult">
        <include refid="selectLoanInfoVo"/>
        <where>
            <if test="ids != null and ids.size() != 0">
            id in
                <foreach collection="ids" item="id" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

    <!--通过id查询-->
    <select id="selectLoanInfoById" parameterType="Long" resultMap="LoanInfoResult" flushCache="true">
            <include refid="selectLoanInfoVo"/>
            where id = #{id}
    </select>
	
    <!--条件新增-->
    <insert id="insertLoanInfo" parameterType="com.zyamc.debt.domain.LoanInfo" useGeneratedKeys="true"
            keyProperty="id">
        insert into tbl_loan_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="loanerId != null">loaner_id, </if>
            <if test="loaneeId != null">loanee_id, </if>
            <if test="contractNumber != null and contractNumber != ''"> contract_number, </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="loanerId != null"> #{loanerId}, </if>
            <if test="loaneeId != null"> #{loaneeId}, </if>
            <if test="contractNumber != null and contractNumber != ''"> #{contractNumber}, </if>
        </trim>
    </insert>

    <!--条件修改-->
    <update id="updateLoanInfo" parameterType="com.zyamc.debt.domain.LoanInfo">
        update tbl_loan_info
        <trim prefix="SET" suffixOverrides=",">
            <if test="loanerId != null"> loaner_id =  #{loanerId}, </if>
            <if test="loaneeId != null"> loanee_id = #{loaneeId}, </if>
            <if test="contractNumber != null and contractNumber != ''"> contract_number = #{contractNumber}, </if>
        </trim>
        where id = #{id}
    </update>

    <!--根据id删除-->
    <delete id="deleteLoanInfoById" parameterType="Long">
        delete
        from tbl_loan_info where id = #{id}
    </delete>

    <!--根据ids（逗号隔开的字符串）删除-->
    <delete id="deleteLoanInfoByIds" parameterType="String">
        delete from tbl_loan_info where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>

时间条件过滤举例

方式一：

<if test="startDate != null"> and lend_date <![CDATA[>=]]> #{startDate}</if>
<if test="endDate != null"> and lend_date <![CDATA[<=]]> #{endDate}</if>

方式二：

<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
    and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
    and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
</if>