Running native SELECT and UPDATE with JTA in one Transaction

Question

i would like to know if it is possible to run a SELECT and an UPDATE statement in Hibernate within just one transaction. There is a very special situation which i cannot change. So i have to call a SQL function and then UPDATE a Table. This function creates some temporary stuff which is used to update the Table. If it is not called within the same transaction a trigger causes the update to fail.

So, please do not tell me i should change that :D i would if i could. But anyhow ... here is my code which should run in one transaction, but which does not do so:

 @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
 public int nativeUpdateWithParams(String currentUser, boolean setCurrentUser, String nativeSql, Object...obj) {        
    try {
        Query query;
        if (nativeSql != null) {
            if (setCurrentUser) {
                String sql = "SELECT module.current_user(:param1)";
                query = em.createNativeQuery(sql);
                query.setParameter("param1", currentUser);
                query.getSingleResult();
            } 
            query = em.createNativeQuery(nativeSql);
            int i=0;
            for (Object param:obj) {
                i++;
                query.setParameter("param"+i, param);
            }
            int result = query.executeUpdate();
            return result;
        } else {
            return 0;
        }
    } catch (Exception ex) {                      
        ex.printStackTrace();
    } finally {            
        return 0;            
    }
}

Hibernate seems to create at least two transactions to execute the SELECT and UPDATE. If i change the Transactionattribute to:

@TransactionAttribute(TransactionAttributeType.MANDATORY)

create a transaction on the client (which i always commit .. no matter what happends) and i write:

String test = "SELECT module.current_user('userXYZ')";
query = em.createNativeQuery(test + ";" + nativeSql);

i do get an execption similar like "update returns something...." but it works. It would be really nice if someone could help me solving this issue!

Thank you

Edit: The exception will not help a bit, because it is the result of the trigger from the "to update" table.

Caused by: org.postgresql.util.PSQLException: FEHLER: You are not a real person.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:308)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:210)
... 72 more

To get in detail: if i do the following on a console/Postgres it works like a charm:

begin transaction;
SELECT module.current_user('userXYZ');
update public.carrier set carrier_comment='whatever' where carrier_id=57300;
commit;

i works!

if i do it like that:

SELECT module.current_user('userXYZ');
update public.carrier set carrier_comment='whatever' where carrier_id=57300;

i do get the error as descriped above: "you are not a real person"

Edit2: If anybody ever has the same problem, the solution is just as simply as stupid :) Just check your glassfish settings (JDBC - JDBC Connection Pools) and make sure that you do not have activated:

Transaction: Non Transactional Connections: Enabled

Thats it. After changing this settings, the transaction are working as expected. So this was not a Hibernate issue...

Greetings!

JB Nizet JB Nizet · Accepted Answer · 2012-01-17T07:40:21

Of course you can. Why couldn't you? What do you think Hibernate does when you tell it to get a object from the DB, and then update this object? It executes a SQL select query to get the object, and then a SQL update query to update it.

Running native SELECT and UPDATE with JTA in one Transaction

1 Answers