Hibernate select and insert strange behaviour

Question

I'm developing a Spring + Hibernate application and everything is working pretty fine. Making a method I found out a strange behaviour that I can't really explain, so I'll show you what I got and maybe we'll find a solution.

This method retrieves a list of soccer players parsing a web page and I try to find if I already have a player with the same name already on the database; if I already have it, I set some parameters and update that object. If I have no player with that name I want to insert it. I obviously can't use the saveOrUpdate method as my parsed objects have no id as I didn't retrieve them from the db.

This is the code snippet that generates the error (it's in the Service layer, then declared as Transactional):

List<Calciatore> calciatoriAggiornati = PopolaDbCalciatori.getListaCalciatori(imagesPath);

        for(Calciatore calciatore: calciatoriAggiornati){
            Calciatore current = calciatoreDao.getCalciatoreByNome(calciatore.getNome());
            if( current != null){
                current.setAttivo(true);
                current.setRuolo(calciatore.getRuolo());
                current.setUrlFigurina(calciatore.getUrlFigurina());
                current.setSquadraReale(calciatore.getSquadraReale());
                calciatoreDao.update(current);
            }
            else{
                calciatore.setAttivo(true);
                calciatoreDao.insert(calciatore);               
            }
        }
        return true;
    }

The getCalciatoreByName method is the following (it's working if used alone):

public Calciatore getCalciatoreByNome(String nomeCalciatore) {
        List<Calciatore> calciatori = getSession().createCriteria(Calciatore.class)
                .add(Restrictions.eq("nome",nomeCalciatore)).list();
        return calciatori.size() == 0? null : calciatori.get(0);
    }

The insert method, inherited by the class BaseDaoImpl works when used standalone too, and is the following:

public Boolean insert(T obj) throws DataAccessException {
        getSession().save(obj);
        return true;
    }

The result is strange: the first object of the list passes the method getCalciatoreByNome without problem; as I have no instances on the database, the flow goes to the insert. After the first round of the for is over, this is the console:

Hibernate: 
    select
        this_.kid as kid1_0_3_,
        this_.attivo as attivo2_0_3_,
        this_.dataDiNascita as dataDiNa3_0_3_,
        this_.nome as nome4_0_3_,
        this_.ruolo as ruolo5_0_3_,
        this_.squadraCorrente_kid as squadraC9_0_3_,
        this_.squadraReale as squadraR6_0_3_,
        this_.urlFigurina as urlFigur7_0_3_,
        this_.version as version8_0_3_,
        squadrafan2_.kid as kid1_7_0_,
        squadrafan2_.attiva as attiva2_7_0_,
        squadrafan2_.nome as nome3_7_0_,
        squadrafan2_.utenteAssociato_kid as utenteAs5_7_0_,
        squadrafan2_.version as version4_7_0_,
        utente3_.kid as kid1_10_1_,
        utente3_.attivo as attivo2_10_1_,
        utente3_.hashPwd as hashPwd3_10_1_,
        utente3_.ruolo_kid as ruolo_ki6_10_1_,
        utente3_.username as username4_10_1_,
        utente3_.version as version5_10_1_,
        ruolo4_.kid as kid1_5_2_,
        ruolo4_.nome as nome2_5_2_,
        ruolo4_.version as version3_5_2_ 
    from
        Calciatore this_ 
    left outer join
        SquadraFantacalcio squadrafan2_ 
            on this_.squadraCorrente_kid=squadrafan2_.kid 
    left outer join
        Utente utente3_ 
            on squadrafan2_.utenteAssociato_kid=utente3_.kid 
    left outer join
        Ruolo ruolo4_ 
            on utente3_.ruolo_kid=ruolo4_.kid 
    where
        this_.nome=?
Hibernate: 
    call next value for SEQ_CALCIATORE

As you can see no exception is raised but the behaviour is already compromised, as no insert is really executed! Last line of log show only the sequence generator!

On the second round of the for cycle, as the flow approaches the getCalciatoreByNome method, this is the console log:

Hibernate: 
    insert 
    into
        Calciatore
        (attivo, dataDiNascita, nome, ruolo, squadraCorrente_kid, squadraReale, urlFigurina, version, kid) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?)
24/06/2015 09:03:27 - INFO  - (AbstractBatchImpl.java:208) - HHH000010: On release of batch it still contained JDBC statements
24/06/2015 09:03:27 - WARN  - (SqlExceptionHelper.java:144) - SQL Error: -5563, SQLState: 42563
24/06/2015 09:03:27 - ERROR - (SqlExceptionHelper.java:146) - incompatible data type in operation
24/06/2015 09:03:39 - DEBUG - (AbstractPlatformTransactionManager.java:847) - Initiating transaction rollback

Wow, that's strange. As I try to execute the second time the select method, Hibernate tries to make the insert generating that error that I can't really find anywhere, and the rollback\exception generation is started.

I tried to debug as much as I could, but I can't really understand what's going on, as when I execute these operation as standalone everything seems to work fine.

Any suggestion?

Answer 1

When you use AUTO flushing, the current pending changes are flushed when:

• the transaction commits
• a query is executed

When you issue the insert, Hibernate only add an EntityInsertAction in the action queue, but it delays the INSERT until flush time.

The reason you see the insert executed on the second iteration cycle is because the select query triggers a flush.