How to run a Hibernate NativeQuery in a type-safe manner instead of returning an Object[]

Question

I'm migrating an application from Hibernate 4.x to Hibernate 5.3.6. The application has queries like this:

SQLQuery query = getSession().createSQLQuery("SELECT a.a, a.b, a.c FROM aTable");

As the method createSQLQuery has been deprecated, I first replaced the method call with the alternative suggested in the Hibernate Javadoc, namely using createNativeQuery:

NativeQuery query = getSession().createNativeQuery("SELECT a.a, a.b, a.c FROM aTable");

The problem with this is that it produces a compiler warning "NativeQuery is a raw type. References to generic type NativeQuery should be parameterized". Furthermore, of course I'd like to benefit from typed queries, now that they are available. So I changed the query to

NativeQuery<Object[]> query = getSession().createNativeQuery("SELECT a.a, a.b, a.c FROM aTable", Object[].class);

Now the problem is that executing the query with

List<Object[]> retList = query.list();

produces the error

javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown entity: [Ljava.lang.Object;

Researching the problem seems to indicate that it is not possible to use non-mapped entities when using typed native queries (which seems like a serious and unnecessary restriction, but I digress here).

The question: is there any way to execute a native SQL query returning an array of Objects using Hibernate without producing compiler warnings while achieving type safety? If not, is there any sensible alternative?

Answer 1

There are much better alternatives for such projections instead of the default Object[].

1. You could use the JPA javax.persistence.Tuple result set which, since Hibernate ORM 5.2.11 works for native SQL:

    List<Tuple> postDTOs = entityManager
    .createNativeQuery(
        "SELECT " +
        "       p.id AS id, " +
        "       p.title AS title " +
        "FROM Post p " +
        "WHERE p.created_on > :fromTimestamp", Tuple.class)
    .setParameter( "fromTimestamp", Timestamp.from(
        LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
            .toInstant( ZoneOffset.UTC ) ))
    .getResultList();
   

2. You could use the Hibernate-specific ResultTransformer which allows you to build very complex DTO structures (e.g. graphs):

    List postDTOs = entityManager
    .createNativeQuery(
        "select " +
        "       p.id as \"id\", " +
        "       p.title as \"title\" " +
        "from Post p " +
        "where p.created_on > :fromTimestamp")
    .setParameter( "fromTimestamp", Timestamp.from(
        LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
    .unwrap( org.hibernate.query.NativeQuery.class )
    .setResultTransformer( Transformers.aliasToBean( PostDTO.class ) )
    .getResultList();
   

3. You could also use a named native query:

    List<PostDTO> postDTOs = entityManager
    .createNamedQuery("PostDTO")
    .setParameter( "fromTimestamp", Timestamp.from(
        LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
            .toInstant( ZoneOffset.UTC ) ))
    .getResultList();
   

   where the PostDTO query is a named native SQL query that looks as follows:

    @NamedNativeQuery(
        name = "PostDTO",
        query =
            "SELECT " +
            "       p.id AS id, " +
            "       p.title AS title " +
            "FROM Post p " +
            "WHERE p.created_on > :fromTimestamp",
        resultSetMapping = "PostDTO"
    )
    @SqlResultSetMapping(
        name = "PostDTO",
        classes = @ConstructorResult(
            targetClass = PostDTO.class,
            columns = {
                @ColumnResult(name = "id"),
                @ColumnResult(name = "title")
            }
        )
    )
   

Cool, right?

Answer 2

Just create it with the call

createNativeQuery("SELECT a.a, a.b, a.c FROM aTable");

and it will default to returning a rows of Object[].

The warning is irrelevant for your case, so just suppress it.

Answer 3

If not, is there any sensible alternative?

Since you're asking for alternatives (probably including ones that don't involve Hibernate), I think it's fair to mention jOOQ here, where you would write your query like this:

Result<Record3<Integer, String, Long>> result =
ctx.select(A.A, A.B, A.C)
   .from(A)
   .fetch();

Denoting generics can be avoided with just var:

var result =
ctx.select(A.A, A.B, A.C)
   .from(A)
   .fetch();

Or, if you want to continue using the string version of your query, you can access with a bit less type safety, using conversions:

Result<?> result = ctx.fetch("SELECT a.a, a.b, a.c FROM aTable");
for (Record record : result) {
    int a = record.get(0, int.class);
    String b = record.get(1, String.class);
    long c = record.get(2, long.class);
}

Or, you use a DTO / POJO

class POJO {
  int a;
  String b;
  long c;
}

List<POJO> list = ctx.fetch("SELECT a.a, a.b, a.c FROM aTable").into(POJO.class);

If your results are entities, you can execute that query easily on an EntityManager, using jOOQ and Hibernate together

(Disclaimer, I work for the company behind jOOQ)