spring mybatis where条件中日期问题

我会带着你远行 2023-06-25 08:28 12阅读 0赞

转自： [https://blog.csdn.net/yongjiutongmi53151/article/details/87367346][https_blog.csdn.net_yongjiutongmi53151_article_details_87367346]

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

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

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

[mysql str\_to\_date 字符串转换为日期][mysql str_to_date]： [https://www.cnblogs.com/feiwenstyle/p/9531571.html][mysql str_to_date]

第一个是正确的写法，第二个在查询字段前加上 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>

[https_blog.csdn.net_yongjiutongmi53151_article_details_87367346]: https://blog.csdn.net/yongjiutongmi53151/article/details/87367346
[Mybatis_CDATA_]: https://www.cnblogs.com/coisini/p/9700074.html
[https_blog.csdn.net_justlpf_article_details_82870339]: https://blog.csdn.net/justlpf/article/details/82870339
[mysql str_to_date]: https://www.cnblogs.com/feiwenstyle/p/9531571.html