How to handle Java Oracle exception – maximum number of expressions in a list is 1000

Last Updated on :March 1, 2024

Java Oracle exception – maximum number of expressions in a list is 1000 occurs when you try to use more than 1000 elements in an IN clause in SQL.

Oracle has a limitation on the number of elements that can be used in an IN clause in SQL. This limitation is in place to prevent performance issues.

Here’s an example of how this exception can occur:

SELECT *
FROM employee
WHERE empid IN (1, 2, 3, ..., 1000,1001,1002);

To resolve this issue, you have couple of options:

1.Use a Subquery : Instead of providing a long list of values in the IN clause, you can use a subquery to fetch the values from the source table.

SELECT *
FROM employee
WHERE empid IN (SELECT empid FROM departments WHERE some condition);

2. Use temporary table : Another approach is you can insert the values into a temporary table and then join your main query with this temporary table.

CREATE GLOBAL TEMPORARY TABLE emp_temp_ids (empid NUMBER);
-- Insert your values into the temporary table

SELECT *
  FROM employee
      WHERE empid IN (SELECT empid FROM emp_temp_ids);

3. List into a batch of size 1000 : By breaking the list into a batch of size 1000 and joining it using OR.
Here is the query re-written

select * from employee where some conditions and (
                             empid in(empid[0...999]) OR
                             empid in(empid[1000...1999]) OR
                             empid in(empid[2000...2999]) OR .... );
 

Here is brief example how to do it in Java

package app;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import pojo.Employee;
import util.HibernateUtil;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

public class EmployeeApp {

    public static void main(String[] args) {

        // Get the Hibernate SessionFactory
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();

        // Create a session
        Session session = sessionFactory.getCurrentSession();

        try {
            // Begin a transaction
            Transaction transaction = session.beginTransaction();

            List<Long> employeeIds = generateEmployeeIds(1,1050);


            int empIdChunkSize = 998;
            AtomicInteger empCounter = new AtomicInteger();
            StringBuilder tempEmpIdsSql = new StringBuilder();
            String empIdsSql = "";

            final Collection<List<Long>> partitionedEmpIdList =
                    employeeIds.stream().collect(Collectors.groupingBy(i -> empCounter.getAndIncrement() / empIdChunkSize))
                            .values();

            List<List<Long>> partitionedEmpArrayList = new ArrayList(partitionedEmpIdList);

            for (int i = 0; i < partitionedEmpArrayList.size(); i++) {
                String empIdsCommastr = "";
                List<Long> subList = partitionedEmpArrayList.get(i);
                empIdsCommastr = subList.stream().map(String::valueOf).collect(Collectors.joining(","));

                // Check if this is the last element in the list
                if (i == partitionedEmpArrayList.size() - 1) {
                    tempEmpIdsSql.append("employee_id in ("+ empIdsCommastr +")");
                }else {
                    tempEmpIdsSql.append("employee_id in ("+ empIdsCommastr +") OR ");
                }
            }

            if(tempEmpIdsSql.length() > 1 ){
                empIdsSql = " AND (" + tempEmpIdsSql.toString() +")";
            }

            String sqlQuery = "select * from employee where 1=1 "+ empIdsSql;
            System.out.println(sqlQuery);

            List<Employee> employees = session.createSQLQuery(sqlQuery)
                    .addEntity(Employee.class)
                    .list();

            // Display the retrieved employees
            for (Employee employee : employees) {
                System.out.println(employee);
            }
            // Commit the transaction
            transaction.commit();

        } finally {
            sessionFactory.close();
        }

    }

    private static List<Long> generateEmployeeIds(int start, int end) {
        List<Long> employeeIds = new ArrayList<>();
        for (long i = start; i <= end; i++) {
            employeeIds.add(i);
        }
        return employeeIds;
    }

}