How to register multiple JDBC Types.OTHER mappings when building scalar projections for Hibernate native SQL queries

Question

I want to do a hibernate native scalar projection (with Tuple.class) using both UUID and Range type from hibernate-types-52 (@Vlad Mihalcea).

I'm using:

• spring boot (v2.2.6.RELEASE)
• hibernate (v5.4.12.Final)
• hibernate-types-52 (v2.9.7)
• postgres (v11)

I have declare a custom PostgreSQL dialect

class CustomPostgreSQLDialect : PostgreSQL10Dialect() {

    init {
        this.registerColumnType(Types.BLOB, "bytea");
        this.registerHibernateType(Types.OTHER, PostgresUUIDType::class.java.name)
        //this.registerHibernateType(Types.OTHER, PostgreSQLRangeType::class.java.name)
    }

    override fun remapSqlTypeDescriptor(sqlTypeDescriptor: SqlTypeDescriptor): SqlTypeDescriptor {
        return if (sqlTypeDescriptor.sqlType == Types.BLOB) {
            BinaryTypeDescriptor.INSTANCE
        } else super.remapSqlTypeDescriptor(sqlTypeDescriptor)
    }
}

When the following line is comment

this.registerHibernateType(Types.OTHER, PostgreSQLRangeType::class.java.name)

It's working fine if and only if I cast the range column.

val statement = "SELECT uuidColumn, CAST(rangeColumn as TEXT) FROM ...."
entityManager.createNativeQuery(statement, Tuple::class.java)

If i'm right, i can only declare one mapping for the java.sql.Types.OTHER, if i declare multi one, the last one is used. Indeed, if both are uncomment, i have a ClassCastException for UUID can't be cast to PGObject.

Is there a way todo a native scalar projection (using Tuple.class), with both UUID and Range types without the need to cast one or another? Finally i would like to write my query like the following

val statement = "SELECT uuidColumn, rangeColumn FROM ...."
entityManager.createNativeQuery(statement, Tuple::class.java)

I known the following options

@SqlResultSetMapping(
  name="ProjectioName",
  columns=[
    ColumnResult(name="uuidColumn", type = PostgresUUIDType::class),
    ColumnResult(name="rangeColumn", type = PostgreSQLRangeType::class)
  ]
)

entityManager.createNativeQuery(statement, "ProjectioName")

The problem is i don't want to declare a 'SqlResultSetMapping' for all my projections with an UUID and another specific column (JSON, Range, ...)

or

entityManager.createNativeQuery(statement, Tuple::class.java)
  .unwrap(NativeQuery::class.java)
  .addScalar("uuidColumn", PostgresUUIDType.INSTANCE)
  .addScalar("rangeColumn", PostgreSQLRangeType.INSTANCE)

This option could be fine, but i have a lot of columns, if i start added ".addScalar(...)", i need to define all columns. Idealy, i would like to use ".addScalar(...)" for some specific columns and fallback to the default 'strategy' for those i don't declare, it's possible?

EDIT

Something i didn't mention as mandatory is my need to do native query, due to some queries (a lot) using CTE or Windows Function or Recursive query or specific Postgres operators.... The Criteria API can't be use for native query, right?

It's possible to use .addScalar(...) for some specific columns (UUID, Range, JSON, ...) and the others columns in the query not declare with .addScalar() fallback to the default resolution?

I want the best of the two world, maybe it's a utopia, but be abled to only specified the type with ".addScalar" for some columns and the others ones continue to work like without ".addScalar" would be so great.

Answer 1

The problem

The JDBC Types interface defines all the common SQL column types, like VARCHAR, INTEGER, DATE.

However, for database-specific columns, you only have the Types.OTHER entry to reference them.

So, if one of your tables defines both a UUID column and a Range one, then, when you fetch rows from this table, both these columns will use the Types.OTHER JDBC type.

So, you can not register a default Hibernate Type to cover Types.OTHER, as you need different types depending on the database-specific column type.

The solution

You need to define the Hibernate Type explicitly at the query-level:

List<Tuple> tuples = entityManager
.createNativeQuery(
    "SELECT uuidColumn, CAST(rangeColumn as TEXT) " +
    "FROM book ", Tuple.class)
.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("uuidColumn", PostgresUUIDType.INSTANCE)
.addScalar("rangeColumn", PostgreSQLRangeType.INSTANCE)
.getResultList();

Now, your problem was that:

This option could be fine, but I have a lot of columns if I start added ".addScalar(...)", I need to define all columns.

And, there is a solution.

You can use Criteria API to build queries dynamically. Based on the entity Metamodel, you can apply the right Hibernate Type when building the projection query.

How about native SQL queries?

If you want to build the native SQL query dynamically, you can use jOOQ.

Conclusion

There is no current off-the-shelf solution to solve your problem. You need to create a template DAO method that facilitates the Types.OTHER projection mapping, whether if you are using Criteria API or jOOQ.

Answer 2

Maybe you should give Blaze-Persistence a try which allows you to stick to your JPA model yet write advanced SQL like e.g. recursive CTEs, set operations etc. With Entity-Views you could even simplify the DTO creation. It could look like the following:

CriteriaBuilder<MyCte> cb = criteriaBuilderFactory.create(entityManager, MyCte.class)
  .withRecursive(MyCte.class)
    .from(Book.class, "b")
    .bind("uuid").select("b.uuid")
    .bind("range").select("NULL")
    .where("b.id").eq(1)
  .unionAll()
    .from(Book.class, "b")
    .from(MyCte.class, "cte")
    .bind("uuid").select("b.uuid")
    .bind("range").select("NULL")
    // Your logic
  .end();
List<MyDto> myDtos = entityViewManager.applySetting(EntityViewSetting.create(MyDto.class), cb)
  .getResultList();

@CTE
@Entity
public class MyCte {
  @Id UUID uuid;
  Range range;
}

@EntityView(MyCte.class)
interface MyDto {
  UUID getUuid();
  Range getRange();
}

And would produce a query similar to this

WITH RECURSIVE MyCte(uuid, range) AS(
  SELECT b.uuid, null FROM book b
  UNION ALL
  SELECT b.uuid, null FROM book b, MyCte cte --your logic
)
SELECT m.uuid, m.range FROM MyCte m