Dear All,

I'm troubleshooting an issue of a function which shoots up the database connection usage. The function is to update values for a number of sales records, to be done in a transaction for a batch of multiple records at one go. There could be 200 or 300+ records to be updated within the transaction, to be triggered by a command/action from UI.

We thought only one database connection is needed for each user/sessino running this function. However, the no. of database connections used is equivalent to the no. of sales records to be updated. That means, when there are 300 sales records to be updated, at least 300 database connection is utilized. This abrupt consumption of connection resources is undesirable.

Question: can anyone advise me how to avoid this? Is it supposed to be the case (one record update requires one database connection resource.)? I don't think so. There must be some statements or hiberate configurations hint you guys can lead me to the right direction. Thanks in advance!

We set up JNDI at weblogic server for the database connections. Below is the code segment for your advice:



public List<TransRec> updSalesRecList(List<TransRec> TransRecList,String auditUsername){

Session session = this.getHibernateTemplate().getSessionFactory().openSession();
Transaction tx = null;
Iterator<TransRec> iterator = TransRecList.iterator();
SalesRec salesrec=null;
int i=0;
int count=0;

try{
tx = session.beginTransaction();
tx.setTimeout(180);
while(iterator.hasNext()){
salesrec=new SalesRec();
iterator.next();
salesrec=(SalesRec)session.get(SalesRec.class, TransRecList.get(i).getSalesRecId());
salesrec.setIsChecked(1L);
// setting other values....

if ( ++count % 20 == 0 ) {
//flush a batch of updates and release memory:
session.flush();
session.clear();
}

i++;
}
session.flush();
tx.commit();

}
catch(Exception e){
if (tx != null) {
tx.rollback();
}
errLogger.error("checkSalesRecList Transaction rollback - " + e, e);

}
finally{
if (session != null && session.isOpen()) {
session.close();
session = null;
}
auditor.info("checkSalesRecList:" + TransRecList + " completed.");
//return succeeded;
}
return TransRecList;
}