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_numberfrom 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">deletefrom 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 mapperPUBLIC "-//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_numberfrom tbl_loan_info</sql><!--列表查询--><select id="selectLoanInfoList" parameterType="com.zyamc.debt.domain.LoanInfo" resultMap="LoanInfoResult">select id, loaner_id, loanee_id, contract_numberfrom 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">deletefrom 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>