gpt4 book ai didi

mysql - 如何重写jpql自定义查询中给定的查询?

转载 作者:行者123 更新时间:2023-11-30 00:07:31 24 4
gpt4 key购买 nike

我有一个 mysql 查询,如下所示:

SELECT * FROM `HOST` WHERE `DATE_OF_SCAN` between 
(SELECT subtime((Select max(`DATE_OF_SCAN`) from `HOST` ), '0 0:5:0'))
and
(Select max(`DATE_OF_SCAN`) from `HOST` );

这基本上是从表中的最大日期范围内返回主机(最大日期 - 5 分钟)。

我正在尝试在 jpql 中重写它:

@Query("SELECT h FROM Host h WHERE h.date between "
+ "(SELECT subtime((Select max(ho.date) from Host ho ), 0 0:5:0)) "
+ "and (Select max(hos.date) from Host hos)")
public List<Host> findHosts();

但它不断抛出异常:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: 0 near line 1, column 126 [SELECT h FROM com.iie.model.Host h WHERE h.date between (SELECT subtime((Select max(ho.date) from com.iie.model.Host ho ), 0 0:5:0)) and (Select max(hos.date) from com.iie.model.Host hos)]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:276)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1537)
at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:285)

最后。我尝试将“0 0:5:0”作为参数传递,但它引发异常,表明这不是正确的 Joda DateTime 格式。

主持人:

@Entity
@Table(name = "HOST")
public class Host {

@Id
@GeneratedValue
@Column(name = "HOST_ID")
private Long id;
@Column(name = "HOSTNAME")
private String hostname;
@Column(name = "IP")
private String ip;
@Column(name = "DATE_OF_SCAN")
@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
private DateTime date;

public Host(Long id, String hostname, String ip, DateTime date) {
this.id = id;
this.hostname = hostname;
this.ip = ip;
this.date = date;
}

public Host(String hostname, String ip, DateTime date) {
this.hostname = hostname;
this.ip = ip;
this.date = date;
}

public Host() {
}

public Long getId() {
return id;
}

public void setId(Long id) {
this.id = id;
}

public String getHostname() {
return hostname;
}

public void setHostname(String hostname) {
this.hostname = hostname;
}

public String getIp() {
return ip;
}

public void setIp(String ip) {
this.ip = ip;
}

public DateTime getDate() {
return date;
}

public void setDate(DateTime date) {
this.date = date;
}

最佳答案

这部分是无效的 SQL

SELECT h FROM Host h

我建议更改为显式列名称

SELECT h.mycolumn FROM Host h

关于mysql - 如何重写jpql自定义查询中给定的查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24395251/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com