[2.04 Java] PersistenceException when using Ebean not()

[Dave Womer]

Hello everyone.  I'm trying to compare to sets of data from the same table using the following query:

find.where().eq("job_id", firstJobId).where().not(Expr.in("ap_mac",find.where().eq("job_id", secondJobId).findList())).findList();

It works fine if the data from firstJobId has records not in the secondJobId, but when I reverse it I get the exception below.  Anyone seen this before? Or is there a better way to handle this query?

javax.persistence.PersistenceException: No ScalarType registered for class models.AP_Uptime

at com.avaje.ebeaninternal.server.persist.Binder.bindObject(Binder.java:183)

at com.avaje.ebeaninternal.server.query.CQueryPredicates.bind(CQueryPredicates.java:162)

at com.avaje.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:413)

at com.avaje.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:198)

at com.avaje.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:104)

at com.avaje.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:344)

at com.avaje.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1469)

at com.avaje.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:906)

at com.avaje.ebeaninternal.util.DefaultExpressionList.findList(DefaultExpressionList.java:201)

at models.AP_Uptime.pageUptimeDiff(AP_Uptime.java:115)

at controllers.APs.getUptimeDiffBetweenJobs(APs.java:80)

at Routes$$anonfun$routes$1$$anonfun$apply$71$$anonfun$apply$72.apply(routes_routing.scala:452)

at Routes$$anonfun$routes$1$$anonfun$apply$71$$anonfun$apply$72.apply(routes_routing.scala:452)

at play.core.Router$HandlerInvoker$$anon$5$$anon$1.invocation(Router.scala:1090)

at play.core.j.JavaAction$$anon$1.call(JavaAction.scala:33)

at play.GlobalSettings$1.call(GlobalSettings.java:57)

at be.objectify.deadbolt.actions.RestrictionsAction.applyRestriction(RestrictionsAction.java:41)

at be.objectify.deadbolt.actions.AbstractRestrictiveAction.call(AbstractRestrictiveAction.java:44)

at play.core.j.JavaAction$class.apply(JavaAction.scala:74)

at play.core.Router$HandlerInvoker$$anon$5$$anon$1.apply(Router.scala:1089)

at play.core.ActionInvoker$$anonfun$receive$1$$anonfun$6.apply(Invoker.scala:126)

at play.core.ActionInvoker$$anonfun$receive$1$$anonfun$6.apply(Invoker.scala:126)

at play.utils.Threads$.withContextClassLoader(Threads.scala:17)

at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:125)

at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:115)

at akka.actor.Actor$class.apply(Actor.scala:318)

at play.core.ActionInvoker.apply(Invoker.scala:113)

at akka.actor.ActorCell.invoke(ActorCell.scala:626)

at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:197)

at akka.dispatch.Mailbox.run(Mailbox.scala:179)

at akka.dispatch.ForkJoinExecutorConfigurator$MailboxExecutionTask.exec(AbstractDispatcher.scala:516)

at akka.jsr166y.ForkJoinTask.doExec(ForkJoinTask.java:259)

at akka.jsr166y.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:975)

at akka.jsr166y.ForkJoinPool.runWorker(ForkJoinPool.java:1479)

at akka.jsr166y.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:104)

[James Roper]

You might find better help on the ebean mailing list, but can you show your AP_Uptime class?

Also, what happens when you remove the findList() call from the inner query?  Expr.in() accepts a query, and so can be implemented using a nested select in the database, which would be much faster than doing two queries like you're doing below.

[Dave Womer]

Thanks James,

When I remove the .toList() I need to replace it with .query() to take, which ends up looking like this:

find.where().eq("job_id", firstJobId).where().not(Expr.in("ap_mac",find.where().eq("job_id", secondJobId).query())).findList();

When I run that code, I get this error: javax.persistence.PersistenceException: Query threw SQLException:Subquery is not a single column query; SQL statement:

Which makes sense.  I need to look at only the ap_mac field to find what I'm looking for, but I haven't figured out how to query a single column with Ebean.

The relevant AP_Uptime class looks like this:

@Entity

public class AP_Uptime extends Model{

@Id

public Long Id;

public long upTime;

public long assocTime;

    @ManyToOne(optional=false)

    public Job job;

    

    @ManyToOne

    public WLC wlc;

    

    @ManyToOne(cascade = CascadeType.ALL, optional=false)

    @JoinColumn(name="ap_mac",referencedColumnName="mac_address")

    public AP ap; 

public static Model.Finder<Long,AP_Uptime> find = new Model.Finder(Long.class, AP_Uptime.class);

public static List<AP_Uptime> all() {

return find.all();

}

public AP_Uptime(long uptime, long assocTime){

this.upTime = uptime;

this.assocTime = assocTime;

}

...

...

...

[Dave Womer]

Nevermind - I did get the single query to work using .select("column_name") and that appears to have solved the issue with the exception.  Here's what I ended up with:

find.where().eq("job_id", firstJobId).where().not(Expr.in("ap_mac",find.select("ap.mac").where().eq("job_id", secondJobId).query())).findList();

Thanks!