SQL错误:1064,SQL状态:42000在@Query – JPA,MySQL,Hibernate中

huangapple go评论79阅读模式
英文:

SQL Error: 1064, SQLState: 42000 in @Query - JPA, MySQL, Hibernate

问题

Controller

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

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

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

JPA Repository:

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 class:

@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;
}

Database table:

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

The Result class:

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

There is an error:

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

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

英文:

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

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

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:

Controller

    @GetMapping(&quot;/visits/{dateParam}&quot;)
    public ResponseEntity&lt;List&lt;WebsiteDailyTotal&gt;&gt; getDailyTotalUsage(@PathVariable(&quot;dateParam&quot;) String dateParam) {

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

        List&lt;WebsiteDailyTotal&gt; websiteTotalUsage = service.getDomainTotal2(ld);
        return  new ResponseEntity&lt;List&lt;WebsiteDailyTotal&gt;&gt;(websiteTotalUsage, new HttpHeaders(), HttpStatus.OK);
    }

JPA Repository:

        public interface TotalDomainRepository2 extends JpaRepository&lt;SuperStatEntityTime, Long&gt; {
        @Query(&quot;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&quot;)
        public List&lt;WebsiteDailyTotal&gt; countTotalDomainUsageByDay2(@Param(&quot;localDate&quot;) LocalDate localDate);
    }

Entity class:

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

Database table:

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

The Result class:

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

There is an error:

    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

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

答案1

得分: 0

你不能直接在JPQL中使用toLocalDate()。修复这个问题的更简单的方法是计算localdate的一天的开始和结束,然后使用它们进行between查询。

LocalDateTime startOfDay = localDate.atTime(LocalTime.MIN);
LocalDateTime endOfDay = localDate.atTime(LocalTime.MAX);
并且查询如下:`c.start 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 :startOfDay AND :endOfDay GROUP BY c.domain ORDER BY c.domain ASC")
public List<WebsiteDailyTotal> countTotalDomainUsageByDay2(@Param("startOfDay") LocalDateTime startOfDay,
                                                           @Param("endOfDay") LocalDateTime endOfDay);
英文:

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

and query like c.start between BETWEEN :startOfDay AND :endOfDay

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

huangapple
  • 本文由 发表于 2020年8月11日 14:37:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/63352754.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定