This discussion is archived
1 Reply Latest reply: Nov 7, 2012 2:51 AM by gimbal2 RSS

DB connection and Mass insert

859967 Newbie
Currently Being Moderated
Hi,

I need to do bulk insert to DB. And i don't want to manually create JDBC because i have to store DB credentials in a file that it may bring troubles when i do clustering latter.

The approach is to use JNDI to lookup SQL Datasource and get connection object then create statement and add batch.

I don't know if after doing .executeBatch() and commit; whether i have to close the connection? I am thinking the connection is still in the pool so that manually closing the connection is not necessary and even causes close sql connection while program execution.

If you have another approach is also appreciated.

the code is more less like this:
            statement = conn.prepareStatement(sqlString);
            conn.setAutoCommit(false); // is there any problem for container here?
            
            for(int i = 0; i < itemList.size(); i++){
                statement.setLong(1, val1);
                statement.setLong(2, val2);
                statement.setString(3, valString);
                statement.addBatch();
            }
            
            statement.executeBatch();
            conn.commit();
thanks,

-CM

Edited by: CardM on Nov 6, 2012 7:56 PM

Edited by: CardM on Nov 6, 2012 7:59 PM
  • 1. Re: DB connection and Mass insert
    gimbal2 Guru
    Currently Being Moderated
    CardM wrote:
    I don't know if after doing .executeBatch() and commit; whether i have to close the connection? I am thinking the connection is still in the pool so that manually closing the connection is not necessary and even causes close sql connection while program execution.
    Yes, you must close it. The close actually returns the connection to the pool so it can be used for another purpose.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points