mybatis 查询含Date类型参数的SQL时,没有返回值
在使用mybatis查询数据的时候,当参数类型为JAVA的Date类时,查询始终没有返回值,将SQL放到数据库中直接运行却能获取返回值。
select sif.id as id,sif.file_name as fileName,concat(sic.root_path,sif.path) as path,sif.create_date as createDate
from sc_image_file as sif
left join sc_image_config as sic on sic.id = sif.config_id
where del_flag = '0'
<if test="scId !=null and scId != ''">
and sic.sc_id = #{scId}
</if>
<if test="devType">
and sic.dev_type_id = #{devType}
</if>
<if test="startTime!=null and endTime!=null">
and sif.create_date >= #{startTime}
and sif.create_date <= #{endTime}
</if>
执行后命令如下:
==> Preparing: select sif.id as id,sif.file_name as fileName,concat(sic.root_path,sif.path) as path,sif.create_date as createDate from sc_image_file as sif left join sc_image_config as sic on sic.id = sif.config_id where del_flag = '0' and sic.sc_id = ? and sic.dev_type_id = ? and sif.create_date >= ? and sif.create_date <= ?
==> Parameters: 00000000002(String), 1(Integer), 2020-06-13 16:08:07.0(Timestamp), 2020-06-13 16:10:07.0(Timestamp)
<== Total: 0
其中startTime跟endTime为JAVA Date类型。mybatis运行查询结果为0。
最后四处百度,最终定位到当mybatis按datetime条件查询,参数为时间戳时,如果时区没设置对,将获取不到正确的数据。
jdbc:mysql://localhost:3306/table_name?useTimezone=true&serverTimezone=GMT%2B8
在jdbc properties的连接地址上,增加时区为东八区。
GMT%2B8表示东八区
修改后重新测试,结果如下:
==> Preparing: select sif.id as id,sif.file_name as fileName,concat(sic.root_path,sif.path) as path,sif.create_date as createDate from sc_image_file as sif left join sc_image_config as sic on sic.id = sif.config_id where del_flag = '0' and sic.sc_id = ? and sic.dev_type_id = ? and sif.create_date >= ? and sif.create_date <= ?
==> Parameters: 00000000002(String), 1(Integer), 2020-06-13 16:08:07.0(Timestamp), 2020-06-13 16:10:07.0(Timestamp)
<== Columns: id, fileName, path, createDate
<== Row: 1, test.jpg, BJ_1/2020/0620, 2020-06-13 16:09:01
<== Total: 1
成功获取结果。