UnexpectedAccessToTheDatabase when querying entity with IdClass with association

[markusdlugi]

When you have an entity with a composite identifier that has an association to another entity (e.g., like in Example 134. IdClass with @ManyToOne in the Hibernate User Guide) then any attempt to query such entities will result in an exception.

So given for example the following entities:

@Entity
public class GroceryList implements Serializable {

    @Id @GeneratedValue
    private Long id;

    @OneToMany(mappedBy = "groceryList", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
    private List<ShoppingItem> shoppingItems;

}

@Entity
@IdClass(ShoppingItem.ShoppingItemId.class)
public class ShoppingItem implements Serializable {

    @Id
    @ManyToOne
    @JoinColumn(name = "grocerylistid")
    private GroceryList groceryList;

    @Id
    private String itemName;

    private int itemCount;

    public static class ShoppingItemId implements Serializable {

        private String itemName;
        private GroceryList groceryList;
    }
}

When you try to run a simple query like this:

FROM ShoppingItem WHERE grocerylistid = :id

Then you will receive the following exception:

2022-09-06 14:32:55,518 ERROR [org.hib.rea.errors] (vert.x-eventloop-thread-2) HR000057: Failed to execute statement [select shoppingit0_.grocerylistid as groceryl3_1_, shoppingit0_.itemName as itemname1_1_, shoppingit0_.itemCount as itemcoun2_1_ from ShoppingItem shoppingit0_ where grocerylistid=$1]: could not execute query: java.util.concurrent.CompletionException: org.hibernate.reactive.event.impl.UnexpectedAccessToTheDatabase: Unexpected access to the database
	at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:314)
	at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:319)
	at java.base/java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:1081)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
	at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2073)
	at io.vertx.core.Future.lambda$toCompletionStage$2(Future.java:360)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
	at io.vertx.core.impl.future.FutureImpl.tryComplete(FutureImpl.java:211)
	at io.vertx.core.impl.future.PromiseImpl.tryComplete(PromiseImpl.java:23)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryComplete(QueryResultBuilder.java:102)
	at io.vertx.sqlclient.impl.QueryResultBuilder.tryComplete(QueryResultBuilder.java:35)
	at io.vertx.core.Promise.complete(Promise.java:66)
	at io.vertx.core.Promise.handle(Promise.java:51)
	at io.vertx.core.Promise.handle(Promise.java:29)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.emitSuccess(FutureBase.java:60)
	at io.vertx.core.impl.future.FutureImpl.tryComplete(FutureImpl.java:211)
	at io.vertx.core.impl.future.PromiseImpl.tryComplete(PromiseImpl.java:23)
	at io.vertx.core.impl.future.PromiseImpl.onSuccess(PromiseImpl.java:49)
	at io.vertx.core.impl.future.PromiseImpl.handle(PromiseImpl.java:41)
	at io.vertx.sqlclient.impl.TransactionImpl.lambda$wrap$0(TransactionImpl.java:72)
	at io.vertx.core.impl.future.FutureImpl$3.onSuccess(FutureImpl.java:141)
	at io.vertx.core.impl.future.FutureBase.lambda$emitSuccess$0(FutureBase.java:54)
	at io.netty.util.concurrent.AbstractEventExecutor.runTask(AbstractEventExecutor.java:174)
	at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:167)
	at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:470)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:503)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
	at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.hibernate.reactive.event.impl.UnexpectedAccessToTheDatabase: Unexpected access to the database
	at org.hibernate.reactive.event.impl.DefaultReactiveLoadEventListener.onLoad(DefaultReactiveLoadEventListener.java:108)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:118)
	at org.hibernate.internal.SessionImpl.fireLoadNoChecks(SessionImpl.java:1231)
	at org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:1096)
	at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:706)
	at org.hibernate.type.EntityType.resolve(EntityType.java:465)
	at org.hibernate.type.ManyToOneType.resolve(ManyToOneType.java:265)
	at org.hibernate.type.EntityType.resolve(EntityType.java:458)
	at org.hibernate.type.ComponentType.resolve(ComponentType.java:695)
	at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:881)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:735)
	at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1047)
	at org.hibernate.reactive.loader.hql.impl.ReactiveQueryLoader.getRowsFromResultSet(ReactiveQueryLoader.java:223)
	at org.hibernate.reactive.loader.ReactiveLoaderBasedResultSetProcessor.reactiveExtractResults(ReactiveLoaderBasedResultSetProcessor.java:73)
	at org.hibernate.reactive.loader.hql.impl.ReactiveQueryLoader$1.reactiveExtractResults(ReactiveQueryLoader.java:72)
	at org.hibernate.reactive.loader.ReactiveLoader.reactiveProcessResultSet(ReactiveLoader.java:145)
	at org.hibernate.reactive.loader.ReactiveLoader.lambda$doReactiveQueryAndInitializeNonLazyCollections$0(ReactiveLoader.java:77)
	at java.base/java.util.concurrent.CompletableFuture$UniCompose.tryFire(CompletableFuture.java:1072)
	... 29 more

Similarly, a LEFT JOIN FETCH doesn't work either. The only way I was able to work around this issue is to use an Entity Graph to fetch GroceryList + ShoppingItem. And of course, as soon as you get rid of the @IdClass, it also works.

Full Reproducer: https://github.com/markusdlugi/hr-composite-id-reproducer

Reproducer uses Hibernate Reactive Panache, but it is also reproducible with vanilla HR.

Tested with Quarkus 2.12.0.Final and Hibernate Reactive 1.1.7.Final.

[DavideD]

Thanks, we will have a look at this soon

[gavinking]

I've reproduced this in our test suite.

[gavinking]

Oh gawd, what a pain, this one can only be fixed by messing about in in hibernate-core.

[gavinking]

mmmm, this looks really hard, and I think it's not worth fixing for now.

[gavinking]

So this code works, as a workaround:

@Entity(name = "GroceryList")
    public static class GroceryList implements Serializable {

        @Id private Long id;

        @OneToMany(mappedBy = "groceryListId", fetch = FetchType.LAZY, cascade = CascadeType.ALL, orphanRemoval = true)
        private List<ShoppingItem> shoppingItems = new ArrayList<>();

    }

    @Entity(name = "ShoppingItem")
    @IdClass(ShoppingItemId.class)
    public static class ShoppingItem implements Serializable {

        @Id Long groceryListId;
        @Id private String itemName;

        @ManyToOne
        @JoinFormula("grocerylistid")
        private GroceryList groceryList;


        private int itemCount;

    }

    public static class ShoppingItemId implements Serializable {
        private ShoppingItemId() {
            itemName = null;
            groceryListId = null;
        }
        private ShoppingItemId(String itemName, Long groceryListId) {
            this.itemName = itemName;
            this.groceryListId = groceryListId;
        }
        private final String itemName;
        private final Long groceryListId;
    }

[markusdlugi]

Cool, thanks for the workaround 👍

[LajosPolya]

Why were the classes made static in the workaround?

[DavideD]

We tend to keep the model for a unit test in the same file containing the tests. This means that the classes are inner classes and must be static for Hibernate to work. Gavin probably copied and pasted the classes that he's used to verify that the workaround works. It's not part of the workaround.

[raedhhindaileh]

I have similar issue but different case , when Teacher.listAll( ) got similar exception as below (trimmed most of it ) PS: i am new to hibernate/java/quarkus

@Entity
public class IdType extends Base {
    @Column(unique = true)
    @NotEmpty
    public String code;
}

@Embeddable
public class IdDocument {
    public String num;
    @ManyToOne(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    public IdType type;
}

@Entity
public class Teacher extends Base {
    public IdDocument idDocument;
    public LangString name;
}

ERROR [org.hib.rea.errors] (vert.x-eventloop-thread-2) HR000057: Failed to execute statement [select teacher0_.id as id1_13_,  teacher0_.idDocument_num as iddocume8_13_, teacher0_.idDocument_type_id as iddocum12_13_,  from Teacher teacher0_]: could not execute query: java.util.concurrent.CompletionException: org.hibernate.reactive.event.impl.UnexpectedAccessToTheDatabase: Unexpected access to the database
        at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:332)

ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (vert.x-eventloop-thread-2) HTTP Request to /teachers failed, error id: 4ea6f2f0-c6b5-4e2e-99d5-a7772e696178-8: org.hibernate.reactive.event.impl.UnexpectedAccessToTheDatabase: Unexpected access to the database
        at org.hibernate.reactive.event.impl.DefaultReactiveLoadEventListener.onLoad(DefaultReactiveLoadEventListener.java:108)

ERROR [org.jbo.res.rea.com.cor.AbstractResteasyReactiveContext] (vert.x-eventloop-thread-2) Request failed: org.hibernate.reactive.event.impl.UnexpectedAccessToTheDatabase: Unexpected access to the database
        at org.hibernate.reactive.event.impl.DefaultReactiveLoadEventListener.onLoad(DefaultReactiveLoadEventListener.java:108)

ERROR [org.hib.rea.errors] (vert.x-eventloop-thread-2) HR000057: Failed to execute statement [select idtype0_.id as id1_3_0_, idtype0_.code as code5_3_0_,  from IdType idtype0_ where idtype0_.id=$1]: could not load an entity: [rhh.domain.IdType#1]: java.util.concurrent.CompletionException: java.lang.IllegalStateException: Session/EntityManager is closed
        at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:315)

[DavideD]

Sorry, I think this is the same bug but I don't have a workaround at the moment. Do you really need IdDocument as an embeddable? I cannot understand from your example if IdDocument is part of a key (it doesn't seem so).

[raedhhindaileh]

Thanks for your quick response, Yes I need IdDocument as an embeddable, because i need it in many other classes Your are right IdDocument is not part of a key When tracing the SQL logs the strange thing is, that the IdType is fetched in separate query event it is set as

    @ManyToOne(fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)

[DavideD]

What happens if you remove @Fetch?

[DavideD]

What happens if you remove @Fetch?

Nevermind, I've just tried and it doesn't change anything

[DavideD]

Sorry, I don't have a fix for this.

The only workaround I can think of is to not use @Embeddable, but instead you could add a transient method that returns an IdDocument:

	@Entity
	public class IdType  {
		@Id
		@GeneratedValue
		public Long id;

		@Column(unique = true)
		public String code;
	}

	public class IdDocument {
		public String num;

		public IdType type;

		public IdDocument(String idDocumentNum, IdType idDocumentType) {
			this.num = idDocumentNum;
			this.type = idDocumentType;
		}
	}

	@Entity
	public class Teacher {
		@Id
		@GeneratedValue
		public Long id;

		public String name;

		public String idDocumentNum;

		@ManyToOne
		public IdType idDocumentType;

		@Transient
		public IdDocument getIdDocument() {
			return new IdDocument(idDocumentNum, idDocumentType);
		}
	}

[DavideD]

Also FetchMode.JOIN doesn't seem to work in this case (at least in my quick test), but this query will work:

s.createQuery( "select t from Teacher t left join fetch t.idDocumentType", Teacher.class ).getResultList()

Sorry about it, eventually we will fix these issues

[raedhhindaileh]

Thanks a lot for your support, You are right removing FetchMode.JOIN have the same result (it is not working) I have applied your workaround as below and it works fine , but kept IdDocument as @Embeddable

Teacher.find( "select t from Teacher t left join fetch t.idDocument.type").list()

[DavideD]

Note that we have a test in the project to keep track of this issue: CompositeIdManyToOneTest