MyBatis

MyBatis

Using MyBatis to query data by time range is inconsistent with the database query (time zone problem)

If you live in GMT+8 Time Zone, you may need to use serverTimezone=Asia/Shanghai.

Build status
Build status
Build Status

Problem description

Recently I encountered a problem about database query.

I need to accurately query the data of a certain day. It is normal to query in the database, but I can't find the data in mybatis. I can query the data by copying the SQL statement of the console to the database for execution.

SQL details are as follows:

1. Query the data of a certain day directly in MySQL

select * from tbl_user where date_format(create_time, '%Y-%m-%d') = '2022-02-28';

2. Query the data of a certain day via MyBatis

    <if test="queryDate !=null ">
      <![CDATA[  and DATE_FORMAT(create_time, '%Y-%m-%d') =
                        DATE_FORMAT(#{queryDate}, '%Y-%m-%d')   ]]>
    </if>

Reason

Because the MySQL version number is above 8.0, you need to set serverTimezone, useSSL and other parameters.

Solution

Finally, it was found that it was a time zone problem, and the solution was to add serverTimezone=Asia/Shanghai after the jdbc.url.

Code is as follows:

<property name="url" value="jdbc:mysql://localhost:3306/db_name?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai" />