2

Im using Hibernate OGM with MongoDB. My JPA QL is below:

String checkquery = "SELECT p FROM pppoe_test p where p.sourceIP=:source_ip and p.login>:logentrytime and (p.logout>:logentrytime OR p.logout IS NULL)";

I get an error like:

no viable alternative at input 'NULL'

HQL000002: The query SELECT p FROM PPPoESession p where p.sourceIP=:source_ip and p.login>:logentrytime and (p.logout>:logentrytime OR p.logout IS NULL) is not valid

Exception in thread "main" org.hibernate.hql.ParsingException: HQL000002: The query SELECT p FROM PPPoESession p where p.sourceIP=:source_ip and p.login>:logentrytime and p.logout>:logentrytime OR p.logout IS NULL is not valid; Parser error messages: [[statement, statementElement, selectStatement, queryExpression, querySpec, whereClause, logicalExpression, expression, logicalOrExpression, logicalAndExpression, negatedExpression, equalityExpression]: line 1:144 state 0 (decision=51) no viable alt; token=[@51,144:147='NULL',<75>,1:144]].
at org.hibernate.hql.QueryParser.parseQuery(QueryParser.java:70)
at org.hibernate.ogm.datastore.mongodb.query.parsing.impl.MongoDBBasedQueryParserService.parseQuery(MongoDBBasedQueryParserService.java:40)
at org.hibernate.ogm.query.impl.OgmQueryTranslator.getQuery(OgmQueryTranslator.java:169)
at org.hibernate.ogm.query.impl.OgmQueryTranslator.getLoader(OgmQueryTranslator.java:134)
at org.hibernate.ogm.query.impl.OgmQueryTranslator.list(OgmQueryTranslator.java:128)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)

I have a native mongo query that works, but not able to get it working through this JPA route. Any pointers will help.

Here is the mongodb doc, that I had earlier inserted via the OGM.

{
"_id" : "cc88a708-c222-4e52-b151-c0f40556b27e",
"connectionTimeInSeconds" : NumberLong(100),
"location" : "MyCastle",
"datatransferIn" : NumberLong(7777777),
"sourceIP" : "172.168.10.123",
"packetsOut" : NumberLong(33333),
"login" : ISODate("2016-07-09T20:45:36.492Z"),
"logout" : null,
"user" : "dvinod@xyz.com",
"packetsIn" : NumberLong(22222),
"datatransferOut" : NumberLong(5555555)

}

I do have the attributes setup properly in the entity

@Entity (name="pppoe_test")
public class PPPoESession { ...

@Temporal(TemporalType.TIMESTAMP)
private Date logout;

@Basic(optional = false)
private String user;

@Basic(optional = false)
private String sourceIP;

Thanks.

UPDATE1 : Issue is not specific to Date type, but for Strings as well. Wondering if the support for IS NULL is in place for the MongoDB provider. Have posted on the Hibernate OGM Forum. Shall update when I hear back from them.

UPDATE2 : Apparently the issue is with the case-sensitivity of the parser for 'null', 'false' and 'true'. The issue is in the current latest release of OGM v5.0.1. A issue was raised in the bug tracker and is being worked-on (https://hibernate.atlassian.net/browse/OGM-1118)

Sanne
  • 6,027
  • 19
  • 34
Vinod
  • 81
  • 9
  • Can you make one experiment for me. Can you remove the parantes and just execute the same query without them. – Alexander Petrov Jul 09 '16 at 21:19
  • doesnt your JPA provider print out what the native MongoDB query it is executing ? (in the log). The JPA provider I use for MongoDB does – Neil Stockton Jul 10 '16 at 06:22
  • Tried with the braces and got the same error result – Vinod Jul 10 '16 at 06:24
  • @NeilStockton The parser complains that there is a problem with the syntax, so the equivalent query is probably not formed yet. – Vinod Jul 10 '16 at 06:45
  • The Query is totally valid ... as JPQL (assuming the classes have those fields ... do you really have a class called "pppoe_test" ??). Are you inputting it as NATIVE query or JPQL query? If JPQL then raise a bug on your JPA provider since that is valid JPQL – Neil Stockton Jul 10 '16 at 08:12
  • Have you tried lowercase null? – Jimmy T. Jul 10 '16 at 08:38
  • JPQL keywords are case insensitive. – Neil Stockton Jul 10 '16 at 09:11
  • at NeilStockton I had aliased the entity as below: @Entity (name="pppoe_test") public class PPPoESession { ... If you noticed the error message, the query correctly resolves pppoe_test to PPPoESession. It is JPQL and the entity has those fields alright. @Temporal(TemporalType.TIMESTAMP) @ColumnDefault("null") private Date logout; @Basic(optional = false) private String user; @Basic(optional = false) private String sourceIP; – Vinod Jul 10 '16 at 09:44
  • So raise a BUG on your JPA provider as I already said. JPQL is valid. – Neil Stockton Jul 10 '16 at 10:27
  • @JimmyT. Actually your suggestion worked. I had blogged about this and was told that the parser had a issue with case-sensitivity. – Vinod Jul 13 '16 at 11:14

0 Answers0