查找某一天的数据
- 核心是采用字符串拼接的方式来查找对应的时间的数据
select * from sys_logs
where substr(concat(createdTime), 1, 10) = #{time}
order by createdTime
调用
temp = new Date();
list = sysLogDao.findLogByDay(new SimpleDateFormat("yyyy-MM-dd").format(temp));
mybatis
<select id="findIsExistSchedule" resultType="java.util.Map">
select * from user_schedule
where 1=1
<if test="startTime != null and startTime != ''">
and substr(concat(start_time), 1, 10) = #{startTime}
</if>
</select>
查找某一个时间范围内的数据
- 核心是使用数据库DATE_FORMAT()来格式化时间字符串 然后来进行比较
- 注意小时间放前面
测试
SELECT * FROM sys_logs
WHERE DATE_FORMAT(createdTime, "%Y-%m-%d")
BETWEEN DATE_FORMAT('2020-11-01', "%Y-%m-%d") AND DATE_FORMAT('2020-11-10', "%Y-%m-%d")
普通写法
SELECT * FROM sys_logs WHERE DATE_FORMAT(createdTime, "%Y-%m-%d")
BETWEEN DATE_FORMAT(#{startTime,jdbcType=VARCHAR}, "%Y-%m-%d")
AND DATE_FORMAT(#{endTime,jdbcType=VARCHAR}, "%Y-%m-%d")
mybatis 写法
SELECT * FROM sys_logs
where 1 = 1
<if test="startTime != null and endTime != null and startTime != '' and endTime != ''">
AND DATE_FORMAT(COMMENT_TIME, "%Y-%m-%d")
BETWEEN DATE_FORMAT(#{startTime,jdbcType=VARCHAR}, "%Y-%m-%d")
AND DATE_FORMAT(#{endTime,jdbcType=VARCHAR}, "%Y-%m-%d")
</if>
调用同样是
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
List<SysLog> logRangFirst = sysLogDao.findLogByRange(format.format(startTime), format.format(endTime));
补充 1=1
当两个条件成立的时候 select * from table where 1=1 and starttime =2015-04-05 and endtime = 2015-04-07, 语句正确
当两个条件不满足时 select * from table where 1=1 ,语句正确,会返回table表的所有数据
如果没有这句话 where后面就没有东西了但是现在有mybatis <where></where>可以代理这个东西 但是有些时候可能不行吧
eg:
<sql id = "queryWhereId">
from sys_logs
<if test="username != null and username != ''">
<where>
username like concat("%", #{username}, "%")
</where>
</if>
</sql>