java 如何在同一个 Hibernate 事务中运行本机 SQL 查询？

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

提问by dognose

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 querieswithin one transaction.

我们有一项服务，它是@Statefull. 大多数数据操作是原子的，但在一组特定的函数中，我们希望native queries在一个事务中运行多个。

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

我们注入EntityManager了事务范围的持久性上下文。创建“一堆”普通实体时，使用em.persist()一切正常。

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.

但是当使用本机查询时（有些表没有由 any 表示@Entity）Hibernate 不会在同一个事务中运行它们，而是基本上每个查询使用一个事务。

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.

因此，我已经尝试使用手册START TRANSACTION;和COMMIT;条目 - 但这似乎会干扰事务，当混合本机查询和持久性调用时，hibernate 用于持久化实体。

@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.)

此方法中的所有内容都应在一个事务中发生。这可以通过 Hibernate 实现吗？在调试它时，可以清楚地看到每条语句的发生都“独立于”任何事务。（即更改会在每个语句之后立即刷新到数据库中。）

---

---

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:

Hibernate 是这样配置的：

<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();
    }

回答by Vlad Mihalcea

In case you don't use container managed transactionsthen 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 DataSourceis running in auto-commit mode, hence each statement is executed in a separate transaction
• the EntityManagerwas not configured with @Transactional, but then only queries can be run since any DML operation would end-up throwing a transaction required exception.

• 所述DataSource在运行自动提交模式，因此每个语句在单独的事务执行
• 在EntityManager没有配置用@Transactional的，但是它们的查询可以运行，因为任何DML操作将最终投掷所需的交易例外。

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

让我们回顾一下您设置了以下 Hibernate 属性：

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.

必须使用您的应用程序服务器 UserTransaction JNDI 命名键设置最终属性。

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.

或根据您当前的 Java EE 应用程序服务器的其他策略。

回答by dognose

After reading about the topic for another bunch of hours while playing around with every configuration property and/or annotation I could find a working solution for my usecase. It might not be the best or only solution, but since the question has received some bookmarks and upvotes, i'd like to share what i have so far:

在玩弄每个配置属性和/或注释的同时阅读了另一个小时的主题之后，我可以找到适用于我的用例的工作解决方案。这可能不是最好的或唯一的解决方案，但由于该问题已收到一些书签和赞成票，我想分享我迄今为止的内容：

At first, there was no way to get it working as expected when running the persistence-unit in managed mode. (<persistence-unit name="test" transaction-type="JTA">- JTA is default if no value given.)

起初，在托管模式下运行持久性单元时，没有办法让它按预期工作。（<persistence-unit name="test" transaction-type="JTA">- 如果没有给出值，则 JTA 是默认值。）

I decided to add another persistence-unit to the persistence xml, which is configured to run in unmanaged mode: <persistence-unit name="test2" transaction-type="RESOURCE_LOCAL">.

我决定另一持久性单元加入到持久性XML，其被配置为在非托管模式下运行：<persistence-unit name="test2" transaction-type="RESOURCE_LOCAL">。

(Note: The waring about Multiple Persistence Unitsis just cause eclipse can't handle. It has no functional impact at all)

（注意：关于Multiple Persistence Units的警告只是因为eclipse无法处理。它根本没有功能影响）

The unmanaged persitence-context requires local configuration of the database, since it is no longer container-provided:

非托管持久性上下文需要数据库的本地配置，因为它不再由容器提供：

<persistence-unit name="test2" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <class>test.AEntity</class>

        <properties>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost/test"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.connection.password" value="1234"/>
            <property name="hibernate.connection.username" value="root"/>
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.archive.autodetection" value="true" />
            <property name="hibernate.jdbc.batch_size" value="20" />
            <property name="hibernate.connection.autocommit" value="false" />
        </properties>
    </persistence-unit>

A change required to the project would now be, that you add an unitName, whenever you use the @PersistenceContextannotation to retrieve a managed instance of the EntityManager.

现在，项目所需的更改是unitName，只要您使用@PersistenceContext注释检索 EntityManager 的托管实例，就添加 , 。

But be aware, that you can only use @PersistenceContextfor the managed persistence-unit. For the unmanaged one, you could implement a simple Producerand Inject the EntityManager using CDI whenever required:

但请注意，您只能@PersistenceContext用于托管持久性单元。对于非托管的，您可以实现一个简单的Producer并在需要时使用 CDI 注入 EntityManager：

@ApplicationScoped
public class Resources {

    private static EntityManagerFactory emf;

    static {
        emf = Persistence.createEntityManagerFactory("test2");
    }

    @Produces
    public static EntityManager createEm(){
        return emf.createEntityManager();
    }
}

Now, in the example given in the original Post, you need to Inject the EntityManager and manuallytake care about transactions.

现在，在原始帖子中给出的示例中，您需要注入 EntityManager 并手动处理事务。

@Stateful
public class TestService {

    @Inject
    private EntityManager em;

    public void transactionalCreation() throws Exception {

        em.getTransaction().begin();

        try {
            em.createNativeQuery(
                    "INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','a')")
                    .executeUpdate();
            em.createNativeQuery(
                    "INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','b')")
                    .executeUpdate();
            em.createNativeQuery(
                    "INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','c')")
                    .executeUpdate();
            em.createNativeQuery(
                    "INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','d')")
                    .executeUpdate();

            AEntity a = new AEntity();
            a.setName("TestEntity1");
            em.persist(a);

            // force unique key violation, rollback should appear.
//          em.createNativeQuery(
//                  "INSERT INTO `ttest` (`name`,`state`,`constraintCol`)VALUES('a','b','d')")
//                  .executeUpdate();
            em.getTransaction().commit();
        } catch (Exception e) {
            em.getTransaction().rollback();
        }
    }
}

My tests so far showed that mixing of native queries and persistence calls lead to the desired result: Either everything is commited or the transaction is rolledback as a whole.

到目前为止，我的测试表明，本地查询和持久性调用的混合会导致预期的结果：要么提交所有内容，要么将事务作为一个整体回滚。

For now, the solution seems to work. I will continue to validate it's functionality in the main project and check if there are any other sideeffects.

目前，该解决方案似乎有效。我将继续在主项目中验证它的功能，并检查是否有任何其他副作用。

Another thing I need to verify is if it would be save to:

我需要验证的另一件事是它是否会保存到：

• Inject both Versions of the EM into one Bean and mix usage. (First checks seem to work, even when using both ems at the same time on the same table(s))
• Having both Versions of the EM operating on the same datasource. (Same data source would most likely be no problem, same tables I assume could lead to unexpected problems.)

• 将两个版本的 EM 注入一个 Bean 并混合使用。（第一次检查似乎有效，即使在同一张桌子上同时使用两个 em 时也是如此）
• 让两个版本的 EM 在同一个数据源上运行。（相同的数据源很可能没有问题，我认为相同的表可能会导致意外问题。）

ps.: This is Draft 1. I will continue to improve the answer and point out problems and/or drawbacks I'm going to find.

ps：这是草案1。我将继续改进答案并指出我将要发现的问题和/或缺点。

回答by walerik

You have to add <hibernate.connection.release_mode key="hibernate.connection.release_mode" value="after_transaction" />to your properties. After a restart should the Transaction handling work.

您必须添加<hibernate.connection.release_mode key="hibernate.connection.release_mode" value="after_transaction" />到您的属性。重新启动后，事务处理应该工作。