1

I have below code

public void addNames(){
   List<String> names = new ArrayList<String>
   names.parallelStream().foreach(name->add(name));
}

private void add(String name){
   SQLSession session = SQLSessionFactory.getSession(config);
   Connection con=Session.openConnection();
   con.insert(name);
   con.commit;
   con.close
}

The problem here is “name->add(name)” will execute for each name, there by I am opening and closing connections for each name. If I have millions of records then this is a problem.

I can’t open the connection outside “names.parallelStream().foreach(name->add(name, connection));” and pass it as parameter, because all the threads will get blocked on one connection object.

So, I want to obtain the connection per thread, How can I do this using “names.parallelStream().foreach(name->add(name));” ?

I want do following per thread

  1. Obtain connection
  2. Insert
  3. Insert
  4. Insert —— N Inserts
  5. Commit and close connection

If I am creating and starting a thread I can do this, How can we achieve this per thread using parallelStreams?

In short, I want the thread in parallelStream to obtain connection per thread and execute name->add(name) and once done the thread should commit and close the connection. Is this possible ?

Didier L
  • 18,905
  • 10
  • 61
  • 103
Raj
  • 401
  • 6
  • 20
  • I don't think you would be able to achieve it using parrelStreams. I would prefer storing the records through batch insert or still if you would like to go with above approach consider using Connection Pooling. – pranay jain May 03 '18 at 05:31

1 Answers1

0

I think you should not force the parallel stream for this. You will get the best performances if you do bulk inserts in chunks. Nevertheless, if you still want to do it, you should have a connection pool so each thread gets a connection and releases it after done its work. Your add method will look like:

private void add(String name){
    Connection con=genCoonection(); //just take a connection from pool
    con.insert(name);
    con.commit;//commit after each record
}

You can find multiple examples on how to create and use a connection pool here: How to establish a connection pool in JDBC?

Another option is to provide a custom ThreadLocal as described here and each thread will open the connection in a thread local. Then you shut down the thread pool and the connection will be closed as described here

Liviu Stirb
  • 5,876
  • 3
  • 35
  • 40
  • I already have a connection pool. I get and release connections to the pool. Is there a way to avoid doing this ? as for every record I should do this. I just wanted to do this per thread, so that I can call getConnection and commit at the end instead of each record. In this approach I should call commit as I am releasing the connection back – Raj May 03 '18 at 14:04
  • @Raj I guess you can write a pool wrapper that has its own connection and you call commit when everything ends. you will still have to get an object from the pool at every step. – Liviu Stirb May 03 '18 at 14:40
  • So I think, we don't have a way to setup per thread configurations in parallel stream – Raj May 03 '18 at 16:10
  • can we achieve this in any other framework like fork join or executor services – Raj May 04 '18 at 00:32
  • 1
    @Raj you should stop creating a new session for each element and insert a reasonable number of elements (if not all) at once. Parallel processing can not compensate the fundamental problem of your current approach. – Holger Jun 15 '18 at 08:28