mysql - How to rewrite given query in jpql custom query? -

mysql - How to rewrite given query in jpql custom query? -

i have mysql query this:

select * `host` `date_of_scan` between (select subtime((select max(`date_of_scan`) `host` ), '0 0:5:0')) , (select max(`date_of_scan`) `host` );

which returning hosts range of max date in table , (max date - 5 min).

i'm trying rewrite in jpql:

@query("select h host h h.date between " + "(select subtime((select max(ho.date) host ho ), 0 0:5:0)) " + "and (select max(hos.date) host hos)") public list<host> findhosts();

but throwing exception:

caused by: org.hibernate.hql.internal.ast.querysyntaxexception: unexpected token: 0 near line 1, column 126 [select h com.iie.model.host h h.date between (select subtime((select max(ho.date) com.iie.model.host ho ), 0 0:5:0)) , (select max(hos.date) com.iie.model.host hos)] @ org.hibernate.hql.internal.ast.querysyntaxexception.convert(querysyntaxexception.java:54) @ org.hibernate.hql.internal.ast.querysyntaxexception.convert(querysyntaxexception.java:47) @ org.hibernate.hql.internal.ast.errorcounter.throwqueryexception(errorcounter.java:79) @ org.hibernate.hql.internal.ast.querytranslatorimpl.parse(querytranslatorimpl.java:276) @ org.hibernate.hql.internal.ast.querytranslatorimpl.docompile(querytranslatorimpl.java:180) @ org.hibernate.hql.internal.ast.querytranslatorimpl.compile(querytranslatorimpl.java:136) @ org.hibernate.engine.query.spi.hqlqueryplan.<init>(hqlqueryplan.java:101) @ org.hibernate.engine.query.spi.hqlqueryplan.<init>(hqlqueryplan.java:80) @ org.hibernate.engine.query.spi.queryplancache.gethqlqueryplan(queryplancache.java:119) @ org.hibernate.internal.abstractsessionimpl.gethqlqueryplan(abstractsessionimpl.java:214) @ org.hibernate.internal.abstractsessionimpl.createquery(abstractsessionimpl.java:192) @ org.hibernate.internal.sessionimpl.createquery(sessionimpl.java:1537) @ org.hibernate.ejb.abstractentitymanagerimpl.createquery(abstractentitymanagerimpl.java:285)

finally. i've tried pass '0 0:5:0' parameter, throws exception not proper joda datetime format.

the host:

@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() { homecoming id; } public void setid(long id) { this.id = id; } public string gethostname() { homecoming hostname; } public void sethostname(string hostname) { this.hostname = hostname; } public string getip() { homecoming ip; } public void setip(string ip) { this.ip = ip; } public datetime getdate() { homecoming date; } public void setdate(datetime date) { this.date = date; }

this part not valid sql

select h host h

i suggest changing explicit column names

select h.mycolumn host h

mysql sql jpa jpql