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

user_76777 0
Viewed 6456

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?

jpa orm hibernate sql java
3 Answers

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?

user_10251180

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.

user_38411610

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)

user_5217990
Related