java - Extract and Insert concurrently into database using JdbcTemplate and BlockingQueue -

i extracting thousands of rows 1 database table , inserting database table. don't want load records memory , insert other database.

because of that, trying use blockingqueue load extractor results using 1 thread , insert database concurrently using thread. using spring jdbctemplate access database.

here plan

    public void performextractioninsertion(jdbctemplate inboundjdbctemplate, jdbctemplate outboundjdbctemplate){          final blockingqueue queue = new linkedblockingqueue<transaction>(50);         executorservice executor = executors.newfixedthreadpool(2);         final string select_query = "select acc_number, date, type  transactions";         final string insert_query = "insert analysis(col1, col2, col3) values(?,?,?)";          executor.execute(new runnable() {             @override             public void run() {                     queue.put(/*implementation of extractor using inboundjdbctemplate*/);             }         });         executor.execute(new runnable() {             @override             public void run() {                 queue.take(/*implementation of inserter using outboundjdbctemplate*/)             }         }); } 

could give me idea of how implement extractor , inserter use same blockingqueue limit number of rows in memory?

is right approach? can still use jdbctemplate? intelligent , convenient way this?

thanks guys

btw, transaction class of object going hold extracted elements inserted.

i've had same scenario (in different setting). not going jdbctemplate mappingsqlquery. think, @ least query better suited needs. given willing change, code this

mappingsqlquery selector = ...;        executor.execute(new runnable() {   public void run() {     list<wrapobject> list = selector.execute();     (wrapobject object : list) {       while (!queue.offer(object)) {         thread.sleep(100);       }     }     while (!queue.offer(wrapobject.null_object)) {       thread.sleep(100);     }   } }); executor.execute(new runnable() {   public void run() {     wrapobject object;     while ((object = queue.take) != wrapobject.null_object) {       outboundjdbctemplate.update(insert_query, object.getparam1(),...)     }   } }); 

given suitable definition of wrapobject, should trick.

working lot performance-critical db-systems found following 2 things.

1. often, reimplementing spring mappers gives better control on things happening database (especially batch-updates, compile-time of preparedstatements, setting batch-sizes)

2. if take peek @ code, learn, part of over-generic spring-jdbc classes relevant 10-20 lines , reimplemented sepcific case while speeding application significantly

3. depending on database might wish use more 1 reader/writer. have worked oracle-clusters 8 parallel read-processes don't start put significant load on hardware