前端传递两个时间参数,开始时间和结束时间,然后从数据库中筛选出某个时间属性在此范围的数据。
Mybatis的动态sql的写法。
实现
<if test="ksrq != null">
AND date_format(d.ddsj,'%y%m%d') >= date_format(#{ksrq},'%y%m%d')
</if>
<if test="jsrq != null">
AND date_format(#{jsrq},'%y%m%d') >= date_format(d.ddsj,'%y%m%d')
</if>
注:
其中ksrq是前端传递过来的开始日期参数,jsrq是前端传递过来的结束日期参数。
d是数据库的表,d.ddsj是数据库中做筛选的时间字段。
完整xml参考
<select id="getListBySx" resultMap="KqDdjlResult">
SELECT
d.id,
d.gh,
d.sfcl,
j.xm,
d.ddlb,
d.ddsj,
s.dept_name AS mbdw,
ss.dept_name AS ydw,
d.ddxs
FROM
dp_ddjl d
LEFT JOIN dp_jbxx j ON j.gh = d.gh
LEFT JOIN sys_dept s ON d.mbdw = s.dept_id
LEFT JOIN sys_dept ss ON d.ydw = ss.dept_id
<where>
<if test="array != null">
and j.bmid in
<foreach collection="array" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
<if test="xm != null and xm != ''">
and j.xm like concat('%', #{xm}, '%')
</if>
<if test="ddlx != null and ddlx!= ''"> and d.ddlb = #{ddlx}</if>
<if test="sfclwc != null and sfclwc!= ''"> and d.sfcl = #{sfclwc}</if>
<if test="ksrq != null">
AND date_format(d.ddsj,'%y%m%d') >= date_format(#{ksrq},'%y%m%d')
</if>
<if test="jsrq != null">
AND date_format(#{jsrq},'%y%m%d') >= date_format(d.ddsj,'%y%m%d')
</if>
</where>
</select>