MyBatis + MyBatis Plus日期范围的查询问题

112 阅读 0 评论 74 点赞

我是靠谱客的博主想人陪夏天，这篇文章主要介绍MyBatis + MyBatis Plus日期范围的查询问题，现在分享给大家，希望可以做个参考。

情况1：从前端接收到字符串类型的日期

复制代码

1
2
3
4
5
6
7
 <if test="startTime!=null and startTime.trim() neq ''">
and date_format(p.create_time,'%Y-%m-%d %H:%i:%s') &gt;= str_to_date(#{startTime},'%Y-%m-%d %H:%i:%s')
</if>
<if test="endTime!=null and endTime.trim() neq ''">
and date_format(p.create_time,'%Y-%m-%d %H:%i:%s') &lt;= str_to_date(#{endTime},'%Y-%m-%d %H:%i:%s')
</if>

或者

复制代码

1
2
3
4
5
6
7
8
9
 <if test="beginTime != null and beginTime !='' ">
and
<![CDATA[p.create_time >=DATE_ADD(#{beginTime },interval 1 day)]]>
</if>
<if test="endTime != null and endTime !=''">
and
<![CDATA[p.create_time <=DATE_ADD(#{endTime},interval 1 day)]]>
</if>

情况2：从前端接收到Date类型

复制代码

1
2
3
4
5
6
7
 <if test="beginTime !=null">
<![CDATA[and DATE_FORMAT(t1.create_Time,'%Y-%m-%d')<=DATE_FORMAT(#{beginTime },'%Y-%m-%d')]]>
</if>
<if test="endTime !=null">
<![CDATA[and DATE_FORMAT(t1.create_Time,'%Y-%m-%d')>=DATE_FORMAT(#{endTime },'%Y-%m-%d')]]>
</if>

情况3：从前端接收到字符串类型的日期，结合MP

复制代码

1
2
3
4
5
List<CustomerIntegral> customerIntegrals = customerIntegralService.list(new QueryWrapper<CustomerIntegral>().
apply(StringUtils.isNotEmpty(todayString ),"date_format (create_time,'%Y-%m-%d') >= date_format('" + todayString + "','%Y-%m-%d')").
apply(StringUtils.isNotEmpty(tomorrowString ),"date_format (create_time,'%Y-%m-%d') <= date_format('" + tomorrowString + "','%Y-%m-%d')"
));

打印出的SQL

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
customer_integral_id,
customer_data_id,
integral,
update_id,
change_status,
create_id,
create_time,
update_time,
consumption_reasons
FROM
customer_integral
WHERE
date_format ( create_time, '%Y-%m-%d' ) >= date_format( 'yyyy-MM-dd', '%Y-%m-%d' )
AND date_format ( create_time, '%Y-%m-%d' ) <= date_format( 'yyyy-MM-dd', '%Y-%m-%d' )