英文:
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("/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 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 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("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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论