How to query from multiple tenants of hibernate6 in a single request?

Question

I used TenantResolver from hibernate6 in Quarkus to perform multi tenant filtering, and of course, using CurrentTenantIdentifierResolver in springboot was the same issue. I found that TenantResolver only executes once in a request

Background

CustomTenantResolver.java

@PersistenceUnitExtension
@RequestScoped
public class CustomTenantResolver implements TenantResolver {

  @Override
  public String getDefaultTenantId() {
    return TenantUtil.NONE.toString();
  }

  @Override
  public String resolveTenantId() {
    var tenantId = TenantUtil.getTenantId().toString();
    System.out.println("resolve tenantId " + tenantId);
    return tenantId;
  }
}

TenantUtil

public class TenantUtil {

  private static final ThreadLocal<Serializable> tl = new ThreadLocal<>();
  public static final Serializable ROOT = "-1";
  public static final Serializable NONE = "-999";

  public static void clear() {
    tl.remove();
  }

  public static Serializable getTenantId() {
    return tl.get();
  }

  public static void setTenantId(Serializable tenantId) {
    tl.set(tenantId);
  }
}

TenantFilter

@Priority(0)
@Provider
public class TenantFilter implements ContainerRequestFilter, ContainerResponseFilter {
  @Override
  public void filter(ContainerRequestContext requestContext) {
    String tenantId = requestContext.getUriInfo().getQueryParameters().getFirst("tenantId");
    TenantUtil.setTenantId(Objects.requireNonNullElse(tenantId, TenantUtil.NONE));
  }

  @Override
  public void filter(
      ContainerRequestContext requestContext, ContainerResponseContext responseContext) {
    TenantUtil.clear();
  }
}

RoleResource

@Path("/role")
public class RoleResource {

  @Inject RoleRepository roleRepository;

  @GET
  @Path("list")
  public List<SysRole> list() {
    return roleRepository.select().fetch();
  }

  @GET
  @Path("test")
  public void test() {
    TenantUtil.setTenantId("123");
    System.out.println(roleRepository.select().fetch());
    TenantUtil.setTenantId("456");
    System.out.println(roleRepository.select().fetch());
  }

}

Expected

curl http://127.0.0.1/role/list?tenantId=123

[{"id":"1", "tenantId": "123"}]

curl http://127.0.0.1/role/list?tenantId=456

[{"id":"2", "tenantId": "456"}]

Problem

curl http://127.0.0.1/role/test

[{"id":"1", "tenantId": "123"}]
[]

The CustomTenantResolver.resolveTenantId only executed once

Answer 1

The tenant is determined upon EntityManager creation, and the EntityManager is generally bound to your transaction. Or by default to your request, but don't rely on that, transactions are your friend and Hibernate ORM in Quarkus is read-only outside of transactions anyway.

So I think you'll have to use a different transaction for each tenant:

public class RoleResource {

  @Inject RoleRepository roleRepository;

  @GET
  @Path("list")
  @Transactional // You really should use transactions
  public List<SysRole> list() {
    return roleRepository.select().fetch();
  }

  @GET
  @Path("test")
  public void test() {
    QuarkusTransaction.requiringNew().run(() -> {
        TenantUtil.setTenantId("123");
        System.out.println(roleRepository.select().fetch());
    });
    QuarkusTransaction.requiringNew().run(() -> {
        TenantUtil.setTenantId("456");
        System.out.println(roleRepository.select().fetch());
    });
  }

}

See this documentation for how to work with transactions, and in particular this section about QuarkusTransaction. Alternatively you can use the standard UserTransaction but it's much less practical IMO.