SQL错误:1064，SQLState:@Query中的42000-JPA，MySQL，Hibernate [英] SQL Error: 1064, SQLState: 42000 in @Query - JPA, MySQL, Hibernate

问题描述

我正在使用JPA Query for MySQL数据库.我正在传递字符串日期

I am using JPA Query for MySQL database. I am passing String date in

http://localhost:8081/stat/visits/2020-07-29

http://localhost:8081/stat/visits/2020-07-29

as PathVariable-但在查询的JPA存储库中出现错误: 我正在使用Java8 LocalDateTime和参数LocalDate.绑定有问题.这是我的课程，下面是错误提示:

as PathVariable - but getting an error in JPA Repository in Query: I am using Java8 LocalDateTime and for parameter LocalDate. Something is wrong with binding. Here are my classes and bellow the error:

控制器

    @GetMapping("/visits/{dateParam}")
    public ResponseEntity<List<WebsiteDailyTotal>> getDailyTotalUsage(@PathVariable("dateParam") String dateParam) {

        LocalDate ld = LocalDate.parse(dateParam.subSequence(0,dateParam.length()));
        LOGGER.info("ld: {}", ld.toString());

        List<WebsiteDailyTotal> websiteTotalUsage = service.getDomainTotal2(ld);
        return  new ResponseEntity<List<WebsiteDailyTotal>>(websiteTotalUsage, new HttpHeaders(), HttpStatus.OK);
    }

JPA存储库:

        public interface TotalDomainRepository2 extends JpaRepository<SuperStatEntityTime, Long> {
        @Query("SELECT new com.proctorio.webtracker.entity.WebsiteDailyTotal(c.domain, SUM(c.duration)) FROM SuperStatEntityTime AS c WHERE c.start.toLocalDate() = :localDate GROUP BY c.domain ORDER BY c.domain ASC")
        public List<WebsiteDailyTotal> countTotalDomainUsageByDay2(@Param("localDate") LocalDate localDate);
    }

实体类:

    @Entity
    @Table(name = "super_stat2")
    public class SuperStatEntityTime {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        @Column
        private String uuid;
    
        @Column
        private String domain;
    
        @Column(name = "start", columnDefinition = "TIMESTAMP")
        private LocalDateTime start;
    
        @Column(name = "end", columnDefinition = "TIMESTAMP")
        private LocalDateTime end;
    
        @Column
        private Long duration;

数据库表:

  CREATE TABLE `super_stat2` (
      `id` bigint NOT NULL AUTO_INCREMENT,
      `domain` varchar(255) DEFAULT NULL,
      `start` datetime DEFAULT '0000-00-00 00:00:00',
      `end` datetime DEFAULT '0000-00-00 00:00:00',
      `duration` bigint DEFAULT '0',
      `uuid` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) 

结果类:

public class WebsiteDailyTotal {
    
    private String domainUrl;
    private Long totalTime;
    
    public WebsiteDailyTotal() {
    }
    
    public WebsiteDailyTotal(String domainUrl, Long totalTime) {
        this.domainUrl = domainUrl;
        this.totalTime = totalTime;
    }

有错误:

    Hibernate: 
        select
            superstate0_.domain as col_0_0_,
            sum(superstate0_.duration) as col_1_0_ 
        from
            super_stat2 superstate0_ 
        where
            c.start.toLocalDate()=? 
        group by
            superstate0_.domain 
        order by
            superstate0_.domain ASC
    
    
     o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [DATE] - [2020-07-29]
    o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000

是语法错误.没有where子句的查询将成功.请在这件事上给予我帮助.谢谢

It is syntax error. The query is successful without where clause. Please, help me with this. Thanks

推荐答案

您不能直接在JPQL中使用toLocalDate().一种更简单的方法来解决此问题，它可以计算localdate的一天的开始和结束时间，并在使用它们进行查询之间进行.

You can't use toLocalDate() directly in JPQL. An easier way to fix this calculate start of day and end of day of localdate and do between query using them.

LocalDateTime startOfDay = localDate.atTime(LocalTime.MIN);
LocalDateTime endOfDay = localDate.atTime(LocalTime.MAX);

和类似c.start between BETWEEN :startOfDay AND :endOfDay

@Query("SELECT new com.proctorio.webtracker.entity.WebsiteDailyTotal(c.domain, SUM(c.duration)) " 
       +"FROM SuperStatEntityTime AS c WHERE c.start between BETWEEN :startOfDay AND :endOfDay GROUP BY c.domain ORDER BY c.domain ASC")
public List<WebsiteDailyTotal> countTotalDomainUsageByDay2(@Param("startOfDay") LocalDateTime startOfDay,
                                                           @Param("endOfDay") LocalDateTime endOfDay);

这篇关于SQL错误:1064，SQLState:@Query中的42000-JPA，MySQL，Hibernate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！