I am trying to make several read queries in parallel in Oracle database from a Java application. I use CallableTasks to make these queries. The sample of the Callable Task is as follows:
public class MyCallableTask<T> implements Callable<List<T>> {
private String sql;
private Connection connection;
private Class<T> classInstance;
public MyCallableTask(String sql, Connection connection, Class<T> classInstance) {
// the connection object is sent here from a db connection pool, so that each callable has its own connection instance.
this.connection = connection;
this.sql = sql;
this.classInstance = classInstance;
}
@Override
public List<T> call() throws Exception {
PreparedStatement ps = connection.createPreparedStatement(sql);
Resultset rs = ps.executeQuery();
// Using commons-dbutils resultsethandler to map the resultset to a Java Bean
ResultSetHandler<List<T>> resultSetHandler = new BeanListHandler<>(classInstance);
List<T> requiredList = resultSetHandler.handle(rs);
return requiredList;
}
}
My sample main class looks as follows:
public class MyMain {
public static void main() {
MyConnectionPool myConnectionPool = new MyConnectionPool();
Connection connection1 = myConnectionPool.getConnection();
Connection connection2 = myConnectionPool.getConnection();
String studentSql = "Select X, Y from AB.Students Where Z = 2";
String teacherSql = "Select P, Q from AB.Teachers Where R = 1";
ExecutorService executorService = Executors.newFixedThreadPool(6);
Callable<List<Student>> callableStudents = new MyCallableTask<>(studentSql, connection1, Student.class);
Callable<List<Teacher>> callableTeachers = new MyCallableTask<>(teacherSql, connection2, Teacher.class);
Future<List<Student>> studentsFuture = executorService.submit(callableStudents);
Future<List<Teacher>> teachersFuture = executorService.submit(callableTeachers);
List<Student> students = studentsFuture.get();
List<Teacher> teachers = teachersFuture.get();
System.out.println(students + " " + teachers);
}
}
The above code works for each sql query on two different threads as expected, but fails on the line in which I call
List<T> requiredList = resultSetHandler.handle(rs);
The exception thrown is a NoSuchElementException. The case is curious to me because when I run the above synchronously it works perfectly fine. Infact even when I insert breakpoints and run in debug mode, it runs perfectly fine and creates two different worker threads as expected. But it always breaks in a normal run.
Please try to answer if possible. Really appreciate any help.
