Hello.

I'm facing a strange problem.
I'm developing an integration tool that will obtain data from other application and update values on a table.
To avoid overhead by hibernate class loading (there is about 7000 updates), I'm trying to execute update querys directly on database.
But on first query execution, the method freezes. Even debugging the code, the thread never finishes and the browser keeps "waiting answer from localhost". None error or exeption message is thrown. With any of the commented code it also happens.

Can somebody help me?

Hibernate version: 2.1.8

Code:
public void updateEstabelecimentosDMR(List<IntegracaoEstabDTO> dtos) throws DatabaseException {
Session session = getSession();
Transaction tx = null;
try{
//tx = session.beginTransaction();
Connection con = session.connection();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
for (IntegracaoEstabDTO dto : dtos)
//stmt.executeUpdate("update Estabelecimento set totalAlunos = " + dto.getTotalAlunos() + " where municipio.codigo = " + dto.getCodigoMunicipio() + " and codigoEstabelecimento = " + dto.getCodigoEstabelecimento());
stmt.addBatch("update Estabelecimento set totalAlunos = " + dto.getTotalAlunos() + " where municipio.codigo = " + dto.getCodigoMunicipio() + " and codigoEstabelecimento = " + dto.getCodigoEstabelecimento());
//stmt.execute("update Estabelecimento set totalAlunos = " + dto.getTotalAlunos() + " where municipio.codigo = " + dto.getCodigoMunicipio() + " and codigoEstabelecimento = " + dto.getCodigoEstabelecimento());

stmt.executeBatch();
con.commit();
con.setAutoCommit(true);
stmt.close();
session.flush();
//tx.commit();
}
catch(HibernateException he){
try{
if(tx!=null) tx.rollback();
}
catch(HibernateException e){
log.error("Erro ao rollback a transação: "+e,e);
}
String erro = agrupaMensagens(he);
log.error("Erro ao atualizar total de alunos dos estabelecimentos (DMR): " + erro,he);
throw new DatabaseException(formataErro(he));
}
catch(SQLException sqle){
log.error("Erro ao atualizar total de alunos dos estabelecimentos (DMR) :" + sqle.getMessage(),sqle);
if(sqle.getNextException()!= null)
log.error("Erro (DMR) - Next Excepion :" + sqle.getNextException().getMessage(),sqle.getNextException());
throw new DatabaseException(sqle);
}
finally{
close(session);
}
}


Name and version of the database you are using: PostgreSQL 7.4.1

Try to remove transaction management on connection and use standard Hibernate TX management (Note: it is not quite according to documentation. In H3 we must manage transaction boundaries on connection because H3 returns new connection, but H2 returns current H session connection)

Thank you very much for your answer.
This is the code resultig after your suggestion:



It works, executing all the querys.
The problem now is the time that every query spends running, about 0.75 seconds. Considering that there are about 7000 querys, it will take 1 hour to run. If I run this code, without get the connection from hibernate, all the process takes about 40 seconds.
Is something I'm doing wrong?
Thanks in advice.

I am not sure what you mean by saying ‘every query’.
Did you try to profile this piece of code and see where time has gone?
As a suggestion: try preparing all your queries outside of transactional boundaries like:
List qs = new ArrayList();
Statement stmt = con.createStatement();
for (Iterator iter = dtos.iterator(); iter.hasNext();) {
IntegracaoEstabDTO dto = (IntegracaoEstabDTO) iter.next();

qs.add ("update Estabelecimento set totalAlunos = " + dto.getTotalAlunos() + " where municipio.codigo = " + dto.getCodigoMunicipio() + " and codigoEstabelecimento = " + dto.getCodigoEstabelecimento());
}

and then execute all the queries within a transaction.

As a wild guess: I do not know what JDBC driver and DB you are using but problem might be with addBatch/executeBatch because the methods are optional and their implementation might be suboptimal.