Inserting million data into DB using multithreading

Question

I am trying to insert millions of data rows into a Database. I am trying to use ThreadPoolExecutor for this purpose. I am creating a batch for every 9000 records and sending the batch to each thread. Here I fixed the ThreadPool Size to 20. After the size increases it is getting failed. How can I check how many threads are available in the ThreadPoolExecutor and how can I wait till the thread pool has free threads.

Hear is my code, Please help if i am wrong.

int threadCount=10;
        ThreadPoolExecutor threadPool = (ThreadPoolExecutor) Executors.newFixedThreadPool(threadCount);
        int i=0;

        StringBuffer sb=new StringBuffer();
        sb.append("BEGIN BATCH");
        sb.append(System.lineSeparator());
        int cnt =metaData.getColumnCount();
        while(rs.next())
        {       
            String query ="INSERT INTO "+table+" ("+columnslist.get(1)+")VALUES("+i;
            for ( int j=1 ; j <= cnt ; j++)
            {
                if(metaData.getColumnTypeName(j).contains("int") || metaData.getColumnTypeName(j).contains("number"))
                {
                        query +=","+ rs.getInt(j);
                }
                else if(metaData.getColumnTypeName(j).contains("varchar") || metaData.getColumnTypeName(j).contains("date") || metaData.getColumnTypeName(j).contains("getTimestamp"))
                {
                        query +=",'"+parseColumnData(rs.getString(j))+"'";
                }
                else
                {
                        query +=",'"+parseColumnData(rs.getString(j))+"'";  
                }
            }
                query +=");";
                sb.append(query);sb.append(System.lineSeparator());
                if(i%9000==0)
                {
                    sb.append("APPLY BATCH");
                    System.out.println(threadPool.getActiveCount());

                    Thread t = new Thread(new ExcecuteTask(sb.toString(),session));
                    threadPool.execute(t);              
                    sb.setLength(0);
                    sb.append("BEGIN BATCH");
                    sb.append(System.lineSeparator());

                }
                i++;
            }
            sb.append("APPLY BATCH");

            Thread t = new Thread(new ExcecuteTask(sb.toString(),session));
            threadPool.execute(t);
             sb.setLength(0);

            threadPool.shutdown();
            while (threadPool.getTaskCount() != threadPool.getCompletedTaskCount())
            {
            }

            System.out.println(table+" Loaded sucessfully");





public class ExcecuteTask implements Runnable 
{
        private String sb;
        private Session session;

        public ExcecuteTask(String s,Session session) 
        { 
            sb = s;
            this.session=session;
        }
        public void run()
        {
            session.executeAsync(sb.toString());
        }
 }

Answer 1

You can find the approximate number of active threads in the ThreadPoolExecutor by calling the getActiveCount method on it. However you shouldn't need to.

From the Java documentation for Executors.newFixedThreadPool

Creates a thread pool that reuses a fixed number of threads operating off a shared unbounded queue. At any point, at most nThreads threads will be active processing tasks. If additional tasks are submitted when all threads are active, they will wait in the queue until a thread is available. If any thread terminates due to a failure during execution prior to shutdown, a new one will take its place if needed to execute subsequent tasks. The threads in the pool will exist until it is explicitly shutdown.

So you should be able to keep submitting tasks to the thread pool and they will be picked up and run as threads become available.

I also note that you are wrapping your tasks in Thread objects before submitting them to the thread pool which is not necessary.