问题如下：

1、JPA自动生成的SQL语句：

<code> select wxvisit0_.id as id1_34_, wxvisit0_.create_time as create_t2_34_, wxvisit0_.deleted as 
deleted3_34_, wxvisit0_.from_company as from_com4_34_, wxvisit0_.from_phone as from_pho5_34_, 
wxvisit0_.from_user as from_use6_34_, wxvisit0_.from_user_number as from_use7_34_, wxvisit0_.from_user_vehicle 
as from_use8_34_, wxvisit0_.open_id as open_id9_34_, wxvisit0_.remarks as remarks10_34_, wxvisit0_.update_time 
as update_11_34_, wxvisit0_.visit_company as visit_c12_34_, wxvisit0_.visit_company_index as 
visit_c13_34_, wxvisit0_.visit_photo as visit_p14_34_, wxvisit0_.visit_remarks as visit_r15_34_, 
wxvisit0_.visit_time as visit_t16_34_, wxvisit0_.visit_type as visit_t17_34_, wxvisit0_.visit_user 
as visit_u18_34_, wxvisit0_.visit_user_phone as visit_u19_34_ from wx_visit wxvisit0_ where 
(wxvisit0_.create_time between '02/29/2020 00:00:00.000' and '03/06/2020 23:59:59.000') order by wxvisit0_.create_time desc /<code>

2、mysql表中create_time字段数据如下：

1与2的格式不一致，导致SQL查询无记录

分析解决

1、查看mysql版本

2、查看Springboot 项目maven依赖：未设定mysql driver版本号

<code> 

        <dependency>
            <groupid>mysql/<groupid>
            <artifactid>mysql-connector-java/<artifactid>
            <scope>runtime/<scope>
        /<dependency>
        
        <dependency>
            <groupid>com.alibaba/<groupid>
            <artifactid>druid-spring-boot-starter/<artifactid>
            <version>${druid.version}/<version>
        /<dependency>/<code>

3、添加mysql driver版本号

<code>          

        <dependency>
            <groupid>mysql/<groupid>
            <artifactid>mysql-connector-java/<artifactid>
            <version>5.1.47/<version>
            <scope>runtime/<scope>
        /<dependency>/<code>

4、查看JPA自动生成的SQL语句：格式正确，问题解决

<code>select wxvisit0_.id as id1_34_, wxvisit0_.create_time as create_t2_34_, wxvisit0_.deleted as 
deleted3_34_, wxvisit0_.from_company as from_com4_34_, wxvisit0_.from_phone as from_pho5_34_, 
wxvisit0_.from_user as from_use6_34_, wxvisit0_.from_user_number as from_use7_34_, wxvisit0_.from_user_vehicle 
as from_use8_34_, wxvisit0_.open_id as open_id9_34_, wxvisit0_.remarks as remarks10_34_, wxvisit0_.update_time 
as update_11_34_, wxvisit0_.visit_company as visit_c12_34_, wxvisit0_.visit_company_index as 
visit_c13_34_, wxvisit0_.visit_photo as visit_p14_34_, wxvisit0_.visit_remarks as visit_r15_34_, 
wxvisit0_.visit_time as visit_t16_34_, wxvisit0_.visit_type as visit_t17_34_, wxvisit0_.visit_user 
as visit_u18_34_, wxvisit0_.visit_user_phone as visit_u19_34_ from wx_visit wxvisit0_ where 
 (wxvisit0_.create_time between '2020-02-29 00:00:00' and '2020-03-06 23:59:59') order by wxvisit0_.create_time desc /<code>

碰到的坑

如果将mysql driver 版本设为8.0. x，问题依旧，最终设为5.x版本问题才解决。猜测Spring Boot JPA与mysql 8.0适配仍然有不确定的问题待解决。

閱讀更多 智慧zhuhuix 的文章

關鍵字: Java MySQL SQL