MyBatis 日期比较方式

create_time在数据库中是datetime类型

下列示例的结构是：mapper\xml中的SQL\idea控制台打印出来的日志。

其中1、2（1）、3的结果是一致的

 

1、Integer countTargetRewardSettleAccounts(

 @Param("startTime") String startTime, @Param("endTime") String endTime);

SELECT

COUNT(*)

FROM target_reward_settle_accounts_daily

WHERE

<![CDATA[

create_time >= str_to_date( #{startTime},'%Y-%m-%d %H:%i:%s')

AND create_time < str_to_date( #{endTime},'%Y-%m-%d %H:%i:%s')

]]>

[13:54:26.453] [http-nio-3004-exec-4] [DEBUG] [countTargetRewardSettleAccounts-159] [7e5e190e662402b1] - <==> Preparing: SELECT COUNT(*) FROM target_reward_settle_accounts_daily WHERE create_time >= str_to_date( ?,'%Y-%m-%d %H:%i:%s') AND create_time < str_to_date( ?,'%Y-%m-%d %H:%i:%s') >

[13:54:26.482] [http-nio-3004-exec-4] [DEBUG] [countTargetRewardSettleAccounts-159] [7e5e190e662402b1] - <==> Parameters: 2019-02-26 18:00:00(String), 2019-02-27 18:00:00(String)>

 

2、Integer countTargetRewardSettleAccountsByDate(

@Param("startTime") Date startTime, @Param("endTime") Date endTime);

(1)

SELECT

COUNT(*)

FROM target_reward_settle_accounts_daily

WHERE

<![CDATA[

create_time >= #{startTime,jdbcType=TIMESTAMP}

AND create_time < #{endTime,jdbcType=TIMESTAMP}

]]>

[13:36:56.564] [http-nio-3004-exec-1] [DEBUG] [countTargetRewardSettleAccountsByDate-159] [10a02614f0124664] - <==> Preparing: SELECT COUNT(*) FROM target_reward_settle_accounts_daily WHERE create_time >= ? AND create_time < ? >

[13:36:56.566] [http-nio-3004-exec-1] [DEBUG] [countTargetRewardSettleAccountsByDate-159] [10a02614f0124664] - <==> Parameters: 2019-02-26 18:00:00.0(Timestamp), 2019-02-27 18:00:00.0(Timestamp)>

#{startTime,jdbcType=TIMESTAMP}，jdbcType=TIMESTAMP 时，就指定日期只用”年月日时分秒”,所有入参变为了‘yyyy-MM-dd HH24:mi:ss'’，如果什么都不加的话，默认是timestamp

(2)

SELECT

COUNT(*)

FROM target_reward_settle_accounts_daily

WHERE

<![CDATA[

create_time >= #{startTime,jdbcType=DATE}

AND create_time < #{endTime,jdbcType=DATE}

]]>

[13:55:23.732] [http-nio-3004-exec-4] [DEBUG] [countTargetRewardSettleAccountsByDate-159] [7e5e190e662402b1] - <==> Preparing: SELECT COUNT(*) FROM target_reward_settle_accounts_daily WHERE create_time >= ? AND create_time < ? >

[13:55:23.735] [http-nio-3004-exec-4] [DEBUG] [countTargetRewardSettleAccountsByDate-159] [7e5e190e662402b1] - <==> Parameters: 2019-02-26(Date), 2019-02-27(Date)>

#{startTime,jdbcType=DATE}，jdbcType=Date 时，就指定日期只用”年月日”,所有入参变为了‘yyyy-MM-dd’。这个查询出来的数据不准确！！！！！！！！！！

 

(3) Integer countTargetRewardSettleAccountsByStr(

@Param("startTime") String startTime, @Param("endTime") String endTime);

SELECT

COUNT(*)

FROM target_reward_settle_accounts_daily

WHERE

<![CDATA[

create_time >= #{startTime}

AND create_time < #{endTime}

]]>

[13:38:14.528] [http-nio-3004-exec-1] [DEBUG] [countTargetRewardSettleAccountsByStr-159] [10a02614f0124664] - <==> Preparing: SELECT COUNT(*) FROM target_reward_settle_accounts_daily WHERE create_time >= ? AND create_time < ? >

[13:38:14.529] [http-nio-3004-exec-1] [DEBUG] [countTargetRewardSettleAccountsByStr-159] [10a02614f0124664] - <==> Parameters: 2019-02-26 18:00:00(String), 2019-02-27 18:00:00(String)>