时间戳

首页 > 解决方案 > SQL 错误：1064，SQLState：42000 在@Query - JPA，MySQL，Hibernate

java - SQL 错误：1064，SQLState：42000 在@Query - JPA，MySQL，Hibernate

问题描述

我正在为 MySQL 数据库使用 JPA 查询。我正在传递字符串日期

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

as PathVariable- 但在 Query 中的 JPA Repository 中出现错误：我正在使用 Java8LocalDateTime和 for parameter LocalDate。绑定有问题。这是我的课程，错误如下：

控制器

    @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 子句。请在这件事上给予我帮助。谢谢

标签： javamysqlhibernatejpa

解决方案

您不能toLocalDate()直接在 JPQL 中使用。一种更简单的方法来解决此计算开始时间和结束时间localdate以及在使用它们的查询之间进行的操作。

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);

推荐阅读