Embeddedable not mapped correctly as JSON

[nielsvhaldvb]

Hi, an application of ours broke using Hibernate Reactive in Quarkus when updating quarkus to 3.14.1 and fixed by reverting to 3.13.3.
Quarkus 3.14 started using Hibernate ORM 6.6 / Reactive 2.4.

org.hibernate.HibernateException: io.vertx.core.impl.NoStackTraceThrowable: Parameter at position[3] with class = [java.math.BigDecimal] and value = [200] can not be coerced to the expected class = [java.lang.String] for encoding

Usage: ( jakarta.persistence.criteria.CriteriaBuilder )
predicates.add( criteriaBuilder.between( criteriaBuilder .function( JSONB_EXTRACT_PATH, String.class, amountPath, criteriaBuilder.literal(AMOUNT)) .as(BigDecimal.class), filter.getAmountGe(), filter.getAmountLe()));

Are there any known issues within 6.6/2.4?

[DavideD]

Which database?

[nielsvhaldvb]

Postgres 15.4 in production environment.

Also does not work locally in a testcontainer with 16.1-alpine postgres image

[DavideD]

Would you be able to provide a test case?

[DavideD]

We test with BigDecimal and it seems to work fine:

hibernate-reactive/hibernate-reactive-core/src/test/java/org/hibernate/reactive/types/BasicTypesAndCallbacksForAllDBsTest.java

Line 363 in 80e4ed9

Basic basic = new Basic();

But it seems you are using criteria and a function. So I don't know if there's something different compare to what we are doing.

[DavideD]

Actually, the test I linked doesn't run a query. So it might not really be relevant

[DavideD]

@nielsvhaldvb I've created this testcase and it seems to work.

Could you update it to make it look more similar to your case, please?

[stefvanderweldevolksbanknl]

@nielsvhaldvb I've created this testcase and it seems to work.

Could you update it to make it look more similar to your case, please?

Hi, thanks for your testcase! Could it have something to do with the property being within a jsonb type?
I currently have connectivity issues with Gradle, so I am unable to make a commit. However, our Entity looks something like this:

	@Entity(name = "Book")
	@Table(name = BOOK_TABLE)
	static class Book {

		@Column(name = "price")
		@JdbcTypeCode(SqlTypes.JSON)
		@JdbcType(PostgreSQLJsonPGObjectJsonbType.class)
		Amount price;
		
		class Amount {
			BigDecimal amount;
		}
	}

We then use jsonb_extract_path_text() to query like this:

final var bookRoot = query.from(Book.class);

criteriaBuilder.between(
              criteriaBuilder
                  .function(
                      "jsonb_extract_path_text",
                      String.class,
                      bookRoot.get("price"),
                      criteriaBuilder.literal("amount"))
                  .as(BigDecimal.class),
              filter.getBookingAmountGe(),
              filter.getBookingAmountLe());

[DavideD]

There are 2 problems:

1. The JSON support in Hibernate Reactive is not as powerful as the one in ORM at the moment.
   It's not possible to map the JSON as an embeddable out of the box (I think). They way a JSON can be mapped is described in this issue: Add examples for mapping a Json in the documentation #1606

   The entity mapping should look something like this:

   @Entity(name = "Book")
   @Table(name = "BookWithJson")
   public static class Book {
   
   	@Id
   	Integer id;
   
   	String title;
   
   	@Column(name = "price")
   	JsonObject price;

2. I couldn't find a way to write the query
   Even when I use the following native query:

   select id,title,price from BookWithJson b where (b.price ->> 'amount')::decimal between ?1 and ?2

   I get a NullPointerException:

   java.lang.NullPointerException: Cannot invoke "String.hashCode()" because "<local6>" is null
   at org.hibernate.dialect.PostgreSQLDialect.resolveSqlTypeDescriptor(PostgreSQLDialect.java:320)
   at org.hibernate.sql.results.jdbc.internal.ResultSetAccess.resolveType(ResultSetAccess.java:101)
   at org.hibernate.reactive.sql.results.internal.ReactiveDeferredResultSetAccess.resolveType(ReactiveDeferredResultSetAccess.java:109)
   at org.hibernate.sql.results.jdbc.spi.JdbcValuesMetadata.resolveType(JdbcValuesMetadata.java:41)
   at org.hibernate.reactive.sql.results.internal.ReactiveDeferredResultSetAccess.resolveType(ReactiveDeferredResultSetAccess.java:104)
   at org.hibernate.query.results.ResultSetMappingImpl.makeImplicitDomainResult(ResultSetMappingImpl.java:322)
   at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:222)
   at org.hibernate.reactive.sql.results.ReactiveResultSetMapping.lambda$reactiveResolve$0(ReactiveResultSetMapping.java:62)
   at java.base/java.util.concurrent.CompletableFuture$UniApply.tryFire(CompletableFuture.java:646)
   at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:510)
   at java.base/java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2179)
   at io.vertx.core.Future.lambda$toCompletionStage$3(Future.java:601)
   at io.vertx.core.impl.future.FutureImpl$4.onSuccess(FutureImpl.java:176)
      ...
   

   I will have a better look next week.

[DavideD]

By the way, I've created a new test case here: DavideD@4cd00b7

[stefvanderweldevolksbanknl]

Strange, we are able to map JSON fields to classes just fine in Quarkus 3.13.3 (Hibernate 6.5.2). Since our update to Quarkus 3.14.1 (Hibernate 6.6.0) we started to experience these problems.

[DavideD]

It's probably a regression due to some changes between 6.5 and 6.6.
We also didn't work a lot on the support for JSON in Hibernate Reactive.

Anyway, I'm looking into it.

[DavideD]

This issue should be fixed in the next release.
There are still some problems when running native queries, but we need to wait for a fix in ORM: #1999

[DavideD]

By the way, @stefvanderweldevolksbanknl, with this fix, you won't need to specify @JdbcType(PostgreSQLJsonPGObjectJsonbType.class). @JdbcTypeCode(SqlTypes.JSON) should be enough.

Anyway, I'm reviewing what we support and I will update the documentation as part of #1606