How to invoke a stored procedure asynchronously using PanacheRepository and Hibernate Reactive in Quarkus?

Question

How to invoke a stored procedure asynchronously and accept the returned result using PanacheRepository (not active record pattern) in an application bootstrapped with Quarkus 3.5.0 and Reactive Hibernate with Panache?

I tried injecting the EntityManager within my repository and performed the synchronous operation within a Uni emitter hoping it would offload it to a worker thread as follows

Uni.createFrom().emitter(emitter -> {
  entityManager.createNativeQuery("call procName(1, 2)", Integer.class).executeUpdate();
});

But vert.x complains of a blocking IO and errors out with

> io.quarkus.runtime.BlockingOperationNotAllowedException: You have
> attempted to perform a blocking operation on a IO thread. This is not
> allowed, as blocking the IO thread will cause major performance issues
> with your application. If you want to perform blocking EntityManager
> operations make sure you are doing it from a worker thread.
>         at io.quarkus.hibernate.orm.runtime.session.TransactionScopedSession.checkBlocking(TransactionScopedSession.java:116)
>         at io.quarkus.hibernate.orm.runtime.session.TransactionScopedSession.createNativeQuery(TransactionScopedSession.java:434)
>         at org.hibernate.engine.spi.SessionLazyDelegator.createNativeQuery(SessionLazyDelegator.java:686)
>         at org.hibernate.engine.spi.SessionLazyDelegator.createNativeQuery(SessionLazyDelegator.java:66)
>         at org.hibernate.Session_3a974b6a18ac399f675913d732c105426414d370_Synthetic_ClientProxy.createNativeQuery(Unknown
> Source)

There are no async methods on the Query object. What can I do here?

Answer 1

Doesn't seem like Panache has an API to make the call. Used EntityManagerFactory instead

import jakarta.persistence.EntityManagerFactory;
import org.hibernate.reactive.mutiny.Mutiny;

@Inject
EntityManagerFactory emf;

public Uni<Integer> myFunc() {
  Mutiny.SessionFactory factory = emf.unwrap(Mutiny.SessionFactory.class);
  return factory.withSession(session -> { session.createNativeQuery(storedProcedureCall).getSingleResultOrNull()
}

Answer 2

getSingleResultOrNull only works for queries returning a result, you should use executeUpdate.

This should work:

import org.hibernate.reactive.mutiny.Mutiny;

@Inject
Mutiny.SessionFactory factory;

public Uni<Integer> myFunc() {
    return factory.withTransaction(session -> session
                       .createNativeQuery( storedProcedureCall )
                       .executeUpdate()
    );
}

Note that you can inject the Mutiny.SessionFactory directly, and you need to make sure that there's a transaction because this is an update.

The error message is not user friendly though, we will try to come up with something better.