JPA update query without transaction - is transaction mandatory?

Question

I'm trying to do insert via a native query with JPA, but I don't want to create a transaction:

Query query = em.createNativeQuery("INSERT INTO person (id, firstname, lastname) VALUES ('1','Ronnie','Dio')");
int count = query.executeUpdate();

this ends up with the TransactionRequiredException:

javax.persistence.TransactionRequiredException: Executing an update/delete query

at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:99)

However, if I unwrap the Hibernate session and execute the same query - it works:

Session session = em.unwrap(Session.class);
Query query = session.createSQLQuery("INSERT INTO person (id, firstname, lastname) VALUES ('1','Ronnie','Dio')");
int count = query.executeUpdate();

So my question is - what am I doing wrong in the first sample? Is transaction really required to execute insert/update/delete queries in JPA? If it is, is there any link to documentation that specifies it clearly? If it's not - what am I doing wrong?

Answer 1

It seems you are building an application that does not run inside a container supporting JTA managed transactions. In such an environment, you have to handle/manage transactions for yourself, i.e., you have to control when transactions are opened, committed or rolled back. This scenario is referred to as resource-local entity manager.

In section 7.5.2 Resource-local EntityManagers of the official JPA 2.2 specification (p. 345) we find:

An entity manager whose transactions are controlled by the application through the EntityTransaction API is a resource-local entity manager. A resource-local entity manager transaction is mapped to a resource transaction over the resource by the persistence provider. Resource-local entity managers may use server or local resources to connect to the database and are unaware of the presence of JTA transactions that may or may not be active

Further down in the spec document the EntityTransaction interface is given. It allows you to call

1. begin() to "Start a resource transaction"
2. commit() to "Commit the current resource transaction, writing any unflushed changes to the database."
3. rollback() to "Roll back the current resource transaction." in case something went wrong on the database side while committing changes.

That's for the theory part. For your code example, you might want to change it as follows:

EntityTransaction tx = null;
try {
    tx = em.getTransaction();
    // start a new transaction, i.e. gather changes from here on...
    tx.begin();

    // do your changes here
    Query query = em.createNativeQuery("INSERT INTO person (id, firstname, lastname) VALUES ('1','Ronnie','Dio')");
    int count = query.executeUpdate();

    // write changes to database via commit
    tx.commit();
} catch(RuntimeException re) {
    if(tx != null && tx.isActive()) {
        // ensure no semi-correct changes are pending => cleanup
        tx.rollback();
    }
    // handle exception, log it somewhere...
}

This should avoid the TransactionRequiredException you encounter. Moreover, you should avoid the use createNativeQuery, as you are mis-using a fundamental concept of an Object-Relational-Mapper (ORM), i.e. the mapper will transform objects into tuples and vice versa for you. This - in general - should ease the pain of writing insert/update queries for a large amount of domain entities.

Have a look into section 3.1.1 EntityManager Interface (p. 65) of the spec document linked above and make use of the methods

• persist(..) - "Make an instance managed and persistent." or
• merge(..) - "Merge the state of the given entity into the current persistence context."

For more infos on the difference of both approaches see the posts here and here.

Hope it helps.

Answer 2

Instead of creating a native query , I would recommend to create a JPA entity for the person and with JPARepository you can use a save method for the person to insert any record.