How to run native SQL queries in the same Hibernate transaction?

Question

We have a Service which is @Statefull. Most of the Data-Operations are atomic, but within a certain set of functions We want to run multiple native queries within one transaction.

We injected the EntityManager with a transaction scoped persistence context. When creating a "bunch" of normal Entities, using em.persist() everything is working fine.

But when using native queries (some tables are not represented by any @Entity) Hibernate does not run them within the same transaction but basically uses ONE transaction per query.

So, I already tried to use manual START TRANSACTION; and COMMIT; entries - but that seems to interfer with the transactions, hibernate is using to persist Entities, when mixing native queries and persistence calls.

@Statefull
class Service{

   @PersistenceContext(unitName = "service")
   private EntityManager em;

   public void doSth(){
      this.em.createNativeQuery("blabla").executeUpdate();
      this.em.persist(SomeEntity);
      this.em.createNativeQuery("blablubb").executeUpdate();
   }
}

Everything inside this method should happen within one transaction. Is this possible with Hibernate? When debugging it, it is clearly visible that every statement happens "independent" of any transaction. (I.e. Changes are flushed to the database right after every statement.)

---

i've tested the bellow given example with a minimum setup in order to elimnate any other factors on the problem (Strings are just for breakpoints to review the database after each query):

@Stateful
@TransactionManagement(value=TransactionManagementType.CONTAINER) 
@TransactionAttribute(value=TransactionAttributeType.REQUIRED)
public class TestService {

    @PersistenceContext(name = "test")
    private EntityManager em;

    public void transactionalCreation(){
        em.createNativeQuery("INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','c')").executeUpdate();
        String x = "test";
        em.createNativeQuery("INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','c','b')").executeUpdate();
        String y = "test2";
        em.createNativeQuery("INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('c','b','a')").executeUpdate();
    }
}

Hibernate is configured like this:

<persistence-unit name="test">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <jta-data-source>java:jboss/datasources/test</jta-data-source>

        <properties>
          <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />

            <property name="hibernate.transaction.jta.platform"
                value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />

            <property name="hibernate.archive.autodetection" value="true" />
            <property name="hibernate.jdbc.batch_size" value="20" />
          <property name="connection.autocommit" value="false"/>
        </properties>
    </persistence-unit>

And the outcome is the same as with autocommit mode: After every native query, the database (reviewing content from a second connection) is updated immediately.

---

The idea of using the transaction in a manuall way leads to the same result:

public void transactionalCreation(){
        Session s = em.unwrap(Session.class);
        Session s2 = s.getSessionFactory().openSession();
        s2.setFlushMode(FlushMode.MANUAL);
        s2.getTransaction().begin();

        s2.createSQLQuery("INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','c')").executeUpdate();
        String x = "test";
        s2.createSQLQuery("INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','c','b')").executeUpdate();
        String y = "test2";
        s2.createSQLQuery("INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('c','b','a')").executeUpdate();

        s2.getTransaction().commit();
        s2.close();
    }

Answer 1

In case you don't use container managed transactions then you need to add the transaction policy too:

@Stateful
@TransactionManagement(value=TransactionManagementType.CONTAINER)
@TransactionAttribute(value=REQUIRED)

I have only seen this phenomenon in two situations:

• the DataSource is running in auto-commit mode, hence each statement is executed in a separate transaction
• the EntityManager was not configured with @Transactional, but then only queries can be run since any DML operation would end-up throwing a transaction required exception.

Let's recap you have set the following Hibernate properties:

hibernate.current_session_context_class=JTA
transaction.factory_class=org.hibernate.transaction.JTATransactionFactory
jta.UserTransaction=java:comp/UserTransaction

Where the final property must be set with your Application Server UserTransaction JNDI naming key.

You could also use the:

hibernate.transaction.manager_lookup_class=org.hibernate.transaction.JBossTransactionManagerLookup

or some other strategy according to your current Java EE Application Server.