JPQL In clause error - Statement too complex

Question

Following is the code which is blowing up if the list which is being passed in to "IN" clause has several values. In my case the count is 1400 values. Also the customer table has several thousands (arround 100,000) of records in it. The query is executing against DERBY database.

    public List<Customer> getCustomersNotIn(String custType, List<Int> customersIDs) {

    TypedQuery<Customer> query = em.createQuery("from Customer where type=:custType and customerId not in (:customersIDs)", Customer.class);

    query.setParameter("custType", custType);
    query.setParameter("customersIDs", customersIDs);   
    List<Customer> customerList  = query.getResultList();


    return customerList;
}

The above mentioned method perfectly executes if the list has less values ( probably less than 1000 ), if the list customersIDs has more values since the in clause executes based on it, it throws an error saying "Statement too complex"

Since i am new to JPA can any one please tell me how to write the above mention function in the way described below.. * PLEASE READ COMMENTS IN CODE *

    public List<Customer> getCustomersNotIn(String custType, List<Int> customersIDs) {

    // CREATE A IN-MEMORY TEMP TABLE HERE...

    // INSERT ALL VALUES FROM customerIDs collection into temp table

    // Change following query to get all customers EXCEPT THOSE IN TEMP TABLE
    TypedQuery<Customer> query = em.createQuery("from Customer where type=:custType and customerId not in (:customersIDs)", Customer.class);

    query.setParameter("custType", custType);
    query.setParameter("customersIDs", customersIDs);   
    List<Customer> customerList  = query.getResultList();

    // REMOVE THE TEMP TABLE FROM MEMORY

    return customerList;
}

Answer 1

Ok here is my solution that worked for me. I could not change the part generating the customerList since it is not possible for me, so the solution has to be from within this method. Bryan your explination was the best one, i am still confuse how "in" clause worked perfectly with table. Please see below solution.

public List<Customer> getCustomersNotIn(String custType, List<Int> customersIDs) {

// INSERT customerIds INTO TEMP TABLE 
storeCustomerIdsIntoTempTable(customersIDs)


// I AM NOT SURE HOW BUT,  "not in" CLAUSE WORKED INCASE OF TABLE BUT DID'T WORK WHILE PASSING LIST VALUES.
TypedQuery<Customer> query = em.createQuery("select c from Customer c where c.customerType=:custType and  c.customerId not in (select customerId from TempCustomer)");
query.setParameter("custType", custType);
List<Customer> customerList  = query.getResultList();

// REMOVE THE DATA FROM TEMP TABLE 
deleteCustomerIdsFromTempTable()
return customerList;
}


private void storeCustomerIdsIntoTempTable(List<Int> customersIDs){

// I ENDED UP CREATING TEMP PHYSICAL TABLE, INSTEAD OF JUST IN MEMORY TABLE
    TempCustomer tempCustomer = null;
    try{
        tempCustomerDao.deleteAll();
        for (Int customerId : customersIDs) {
            tempCustomer = new TempCustomer();
            tempCustomer.customerId=customerId;         
            tempCustomerDao.save(tempCustomer);
        }
    }catch(Exception e){
        // Do logging here
    }   

}

private void deleteCustomerIdsFromTempTable(){

    try{
        // Delete all data from TempCustomer table to start fresh
        int deletedCount= tempCustomerDao.deleteAll();
        LOGGER.debug("{} customers deleted from temp table", deletedCount);

    }catch(Exception e){
        // Do logging here
    }       


}   

Answer 2

The Derby IN clause support does have a limit on the number of values that can be supplied in the IN clause.

The limit is related to an underlying limitation in the size of a single function in the Java bytecode format; Derby currently implements IN clause execution by generating Java bytecode to evaluate the IN clause, and if the generated bytecode would exceed the JVM's basic limitations, Derby throws the "statement too complex" error.

There have been discussions about ways to fix this, for example see:

• DERBY-6784
• DERBY-6301, or
• DERBY-216

But for now, your best approach is probably to find a way to express your query without generating such a large and complex IN clause.

Answer 3

JPA and the underlying Hibernate simply translate it into a normal JDBC-understood query. You wouldn't write a query with 1400 elements in the IN clause manually, would you? There is no magic. Whatever doesn't work in normal SQL, wouldn't in JPQL.

I am not sure how you get that list (most likely from another query) before you call that method. Your best option would be joining those tables on the criteria used to get those IDs. Generally you want to execute correlated filters like that in one query/transaction which means one method instead of passing long lists around.

I also noticed your customerId is double - a poor choice for a PK. Typically people use long (autoincremented/sequenced, etc.) And I don't get the "temp table" logic.