playframeworkebean

Ebean doesn't recognize plus (+) operator in where clause


I am using Ebean 11.32.x (PlayEbean plugin 5.0.1 for Play Framework 2.7.2).

I would like to use the plus (+) operator in the where clause for filtering, e.g. " where cost >= 50 and ((regA >=1 and regB + regC <= 0) or (regB + regC >= 1 and regA <= 0))".

The problem is, that Ebean doesn't recognize this operator. It works fine if I use the query in MySQL directly. The following exception is thrown:

play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution exception[[IllegalArgumentException: line 1:96 no viable alternative at input 'regB+']]
    at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:351)
    at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:267)
    at play.core.server.AkkaHttpServer$$anonfun$1.applyOrElse(AkkaHttpServer.scala:448)
    at play.core.server.AkkaHttpServer$$anonfun$1.applyOrElse(AkkaHttpServer.scala:446)
    at scala.concurrent.Future.$anonfun$recoverWith$1(Future.scala:417)
    at scala.concurrent.impl.Promise.$anonfun$transformWith$1(Promise.scala:41)
    at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:64)
    at akka.dispatch.BatchingExecutor$AbstractBatch.processBatch(BatchingExecutor.scala:55)
    at akka.dispatch.BatchingExecutor$BlockableBatch.$anonfun$run$1(BatchingExecutor.scala:92)
    at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
Caused by: java.lang.IllegalArgumentException: line 1:96 no viable alternative at input 'regB+'
    at io.ebeaninternal.server.grammer.EqlParser$ErrorListener.syntaxError(EqlParser.java:45)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:136)
    at io.ebeaninternal.server.grammer.antlr.EQLParser.any_expression(EQLParser.java:1744)
    at io.ebeaninternal.server.grammer.antlr.EQLParser.conditional_primary(EQLParser.java:1577)
    at io.ebeaninternal.server.grammer.antlr.EQLParser.conditional_factor(EQLParser.java:1531)
    at io.ebeaninternal.server.grammer.antlr.EQLParser.conditional_term(EQLParser.java:1475)
    at io.ebeaninternal.server.grammer.antlr.EQLParser.conditional_expression(EQLParser.java:1406)

Is there a way to make it work?

Test Ebean entity with filter() method:

package models.entities;

import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Table;

import io.ebean.Ebean;
import io.ebean.EbeanServer;
import io.ebean.Model;
import io.ebean.Query;


@Entity
@Table(name="test")
public class Test extends Model {

    @Column(nullable=false, name="cost")
    public int cost;

    @Column(nullable=false, name="regA")
    public int regA;

    @Column(nullable=false, name="regB")
    public int regB;

    @Column(nullable=false, name="regC")
    public int regC;

    public static List<Test> filter(){

        EbeanServer ibs = Ebean.getServer("ibs");

        Query<Test> query = ibs.createQuery(Test.class, " where cost >= 50 and ((regA >=1 and regB + regC <= 0) or (regB + regC >= 2 and regA <= 0))");

        return query.findList();
    }
}

Solution

  • Rob Bygrave the primary maintainer of Ebean, suggested the following solution at https://groups.google.com/forum/#!forum/ebean which worked fine:

    Query<Test> query = ibs.createQuery(Test.class)
      .where()
      .raw("cost >= 50 and ((regA >=1 and regB + regC <= 0) or (regB + regC >= 2 and regA <= 0))")
      .query();