qq_29825055
liuhaojavax
采纳率100%
2019-04-11 15:54 浏览 1.3k
已采纳

springboot jpa生成sql语句时间格式不正确?

  • springboot项目中使用jpa,动态生成的sql语句,时间格式不是mysql数据库中的时间格式,导致查询失败

项目配置:

# Server settings (ServerProperties)
server:
  port: 8080
  servlet:
    context-path: /
management:
  endpoints:
    web:
      exposure:
        include: refresh,health

spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
  datasource:
    driverClassName: net.sf.log4jdbc.DriverSpy
    url: jdbc:log4jdbc:mysql://xxx.xxx.xxx.xxx:3306/xxx?useUnicode=true&characterEncoding=utf8&useSSL=false&failOverReadOnly=false
    username: root
    password: root
    #是否展示sql
  jpa:
    show-sql: true
    hibernate:
      naming:
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

查询代码:

 @Test
    public void findRecordList() {
        SafeReport safeReport = new SafeReport();
        safeReport.setId(0);
        safeReport.setTheme("");
        safeReport.setBeginDay(new Date());
        safeReport.setEndDay(new Date());
        safeReport.setRate(0);
        safeReport.setBeginTime(new Date());
        safeReport.setEndTime(new Date());
        safeReport.setIsBubk(0);
        safeReport.setBubkBeginDay("");
        safeReport.setBubkEndDay("");
        safeReport.setBubkBeginTime(new Date());
        safeReport.setBubkEndTime(new Date());
        safeReport.setIsdel(0);
        safeReport.setCreateDate(new Date());

        Example<SafeReport> example = Example.of(safeReport);
        safeReportRepository.findAll(example);
    }

动态生成的sql语句:

SELECT
    safereport0_.id AS id1_1_,
    safereport0_.begin_day AS begin_da2_1_,
    safereport0_.begin_time AS begin_ti3_1_,
    safereport0_.bubk_begin_day AS bubk_beg4_1_,
    safereport0_.bubk_begin_time AS bubk_beg5_1_,
    safereport0_.bubk_end_day AS bubk_end6_1_,
    safereport0_.bubk_end_time AS bubk_end7_1_,
    safereport0_.create_date AS create_d8_1_,
    safereport0_.end_day AS end_day9_1_,
    safereport0_.end_time AS end_tim10_1_,
    safereport0_.is_bubk AS is_bubk11_1_,
    safereport0_.isdel AS isdel12_1_,
    safereport0_.rate AS rate13_1_,
    safereport0_.theme AS theme14_1_
FROM
    safe_report safereport0_
WHERE
    safereport0_.end_day = '04/11/2019 
15:16:25.323'
AND safereport0_.rate = 0
AND safereport0_.id = 0
AND safereport0_.begin_time = '04/11/2019 
15:16:25.323'
AND safereport0_.bubk_end_time = '04/11/2019 15:16:25.323'
AND safereport0_.bubk_begin_time = '04/11/2019 
15:16:25.323'
AND safereport0_.theme = ''
AND safereport0_.bubk_begin_day = ''
AND safereport0_.begin_day = '04/11/2019 
15:16:25.323'
AND safereport0_.is_bubk = 0
AND safereport0_.bubk_end_day = ''
AND safereport0_.create_date = '04/11/2019 
15:16:25.323'
AND safereport0_.isdel = 0
AND safereport0_.end_time = '04/11/2019 15:16:25.323'

这里时间格式应该是 '2019-04-11 15:16:25.323'但是动态生成的时候却是'04/11/2019 15:16:25.323'。

pojo类:


@Getter
@Setter
@ToString
@Builder
@Entity
@Table(name = "safe_report")
public class SafeReport {
    @Id
    private Integer id;
    private String theme;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    @Temporal(TemporalType.DATE)
    @Column(name = "begin_day")
    private Date beginDay;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd")
    @Temporal(TemporalType.DATE)
    @Column(name = "end_day")
    private Date endDay;
    private Integer rate;
    @DateTimeFormat(pattern = "HH:mm")
    @JsonFormat(timezone = "GMT+8", pattern = "HH:mm")
    @Temporal(TemporalType.TIME)
    @Column(name = "begin_time")
    private Date beginTime;
    @DateTimeFormat(pattern = "HH:mm")
    @JsonFormat(timezone = "GMT+8", pattern = "HH:mm")
    @Temporal(TemporalType.TIME)
    @Column(name = "end_time")
    private Date endTime;
    @Column(name = "is_bubk")
    private Integer isBubk;
    @Column(name = "bubk_begin_day")
    private String bubkBeginDay;
    @Column(name = "bubk_end_day")
    private String bubkEndDay;
    @DateTimeFormat(pattern = "HH:mm")
    @JsonFormat(timezone = "GMT+8", pattern = "HH:mm")
    @Temporal(TemporalType.TIME)
    @Column(name = "bubk_begin_time")
    private Date bubkBeginTime;
    @DateTimeFormat(pattern = "HH:mm")
    @JsonFormat(timezone = "GMT+8", pattern = "HH:mm")
    @Temporal(TemporalType.TIME)
    @Column(name = "bubk_end_time")
    private Date bubkEndTime;
    private Integer isdel;
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @Column(name = "create_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date createDate;
}

不知道哪里出了问题,其他项目是好的,只有我这个是重新搭建的demo有这个问题。。。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答 默认 最新

  • 已采纳
    qq_29825055 liuhaojavax 2019-04-12 10:58

    找到问题了 原因是pom文件引入mysql driver时没有写版本号,
    原来的:

    <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    

    改过后的:

    <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.44</version>
            </dependency>
    
    点赞 评论 复制链接分享
  • qq_34444616 咖啡加冰66 2019-04-11 17:56

    @Temporal(TemporalType.TIMESTAMP)

    点赞 评论 复制链接分享

相关推荐