1 Reply Latest reply: Nov 7, 2012 4:51 AM by gimbal2 RSS

    DB connection and Mass insert

    859967
      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
          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.