Doma mysql,SQL错误:1064，SQLState:@Query中的42000-JPA，MySQL，Hibernate

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("/visits/{dateParam}")

public ResponseEntity> getDailyTotalUsage(@PathVariable("dateParam") String dateParam) {

LocalDate ld = LocalDate.parse(dateParam.subSequence(0,dateParam.length()));

LOGGER.info("ld: {}", ld.toString());

List websiteTotalUsage = service.getDomainTotal2(ld);

return new ResponseEntity>(websiteTotalUsage, new HttpHeaders(), HttpStatus.OK);

}

JPA Repository:

public interface TotalDomainRepository2 extends JpaRepository {

@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 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 syntax error. The query is successful without where clause. Please, help me with this. Thanks

解决方案

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("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 countTotalDomainUsageByDay2(@Param("startOfDay") LocalDateTime startOfDay,

@Param("endOfDay") LocalDateTime endOfDay);