spring mybatis where条件中日期问题

转自： https://blog.csdn.net/yongjiutongmi53151/article/details/87367346

Mybatis映射文件<!CDATA[[]]> 转义问题: https://www.cnblogs.com/coisini/p/9700074.html

Mybatis映射文件<!CDATA[[]]> 转义问题： https://www.cnblogs.com/coisini/p/9700074.html

mybatis3 date 的处理：https://blog.csdn.net/justlpf/article/details/82870339

mysql str_to_date 字符串转换为日期： https://www.cnblogs.com/feiwenstyle/p/9531571.html

 

 

 

第一个是正确的写法，第二个在查询字段前加上 DATE_FORMAT(checkin_time, '%Y-%m')，

就会出现问题

Closing non transactional SqlSession
 

mybatis SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d450bc0] was not registered for synchronization because synchronization is not active
<select id="selectCRToolbarList" resultMap="BaseResultMap" parameterType="hashmap">
        SELECT a.id,a.emp_id,b.emp_no as empNo,b.emp_name as empName,a.checkin_time,MAX(a.checkin_time) AS timeOut,MIN(a.checkin_time) AS timeIn,
            a.checkin_category,a.audit_by,a.audit_time,a.is_enable
        FROM
            t_checkin_record a
        LEFT JOIN
            t_employee b
        ON
            a.emp_id=b.id
        <where>
            <if test="true">a.is_enable=1 AND emp_id=#{id} AND DATE_FORMAT(checkin_time, '%Y-%m')= DATE_FORMAT(CURRENT_DATE(), '%Y-%m')</if>
            <if test="startTime!=null">
                <![CDATA[AND checkin_time>=STR_TO_DATE(#{startTime},'%Y-%m-%d')]]>
            </if>
            <if test="endTime">
                <![CDATA[AND checkin_time<=STR_TO_DATE(#{endTime},'%Y-%m-%d')]]>
            </if>
        </where>            
        GROUP BY
            a.emp_id,DATE_FORMAT(a.checkin_time, '%Y-%m-%d')
    </select>
<select id="selectCRToolbarList" resultMap="BaseResultMap" parameterType="hashmap">
        SELECT a.id,a.emp_id,b.emp_no as empNo,b.emp_name as empName,a.checkin_time,MAX(a.checkin_time) AS timeOut,MIN(a.checkin_time) AS timeIn,
            a.checkin_category,a.audit_by,a.audit_time,a.is_enable
        FROM
            t_checkin_record a
        LEFT JOIN
            t_employee b
        ON
            a.emp_id=b.id
        <where>
            <if test="true">a.is_enable=1 AND emp_id=#{id} AND DATE_FORMAT(checkin_time, '%Y-%m')= DATE_FORMAT(CURRENT_DATE(), '%Y-%m')</if>
            <if test="startTime!=null">
                <![CDATA[AND DATE_FORMAT(checkin_time, '%Y-%m')>=STR_TO_DATE(#{startTime},'%Y-%m-%d')]]>
            </if>
            <if test="endTime">
                <![CDATA[AND DATE_FORMAT(checkin_time, '%Y-%m')<=STR_TO_DATE(#{endTime},'%Y-%m-%d')]]>
            </if>
        </where>            
        GROUP BY
            a.emp_id,DATE_FORMAT(a.checkin_time, '%Y-%m-%d')
    </select>