Hibernate's "Pure native scalar queries are not yet supported"

In JPA one can define JPQL queries as well as native queries. Each of those can return either an Entity or one or more scalar values. Queries can be created on demand at run-time from a String, or at start-up time from an annotation (or corresponding XML variant see Where to put named queries in JPA?).

Of all those combinations, curiously Hibernate has never supported named native queries returning a scalar result, including insert, update and delete queries which all don't return a result set, but merely the number of rows affected.

It's a curious case, since Hibernate does support scalar returns in non-native named queries (thus a scalar return and named queries is not the problem), and it does support scalar returns in dynamically created native queries (thus scalar returns in native queries are not the problem either).

An example of this specific combination:

<named-native-query name="SomeName">
    <query>
        INSERT INTO
            foo
        SELECT
            *
        FROM
            bar
    </query>
</named-native-query>

If you do try to startup with such a query, Hibernate will throw an exception with the notorious message:

Pure native scalar queries are not yet supported

Extra peculiar is that this has been reported as a bug nearly 6 years(!) ago (see HHH-4412). In that timespan the advances in IT have been huge, but apparently not big enough to be able to fix this particular bug. "Not yet" certainly is a relative term in Hibernate's world.

A quick look at Hibernate's source-code reveals that the problem is within org.hibernate.cfg.annotations.QueryBinder, more specifically the bindNativeQuery method. It has the following outline:

public static void bindNativeQuery(org.hibernate.annotations.NamedNativeQuery queryAnn, Mappings mappings){
    if (BinderHelper.isEmptyAnnotationValue(queryAnn.name())) {
        throw new AnnotationException("A named query must have a name when used in class or package level");
    }
    NamedSQLQueryDefinition query;
    String resultSetMapping = queryAnn.resultSetMapping();
    if (!BinderHelper.isEmptyAnnotationValue(resultSetMapping)) {
        query = new NamedSQLQueryDefinition (
            // ...
        );
    }
    else if (!void.class.equals(queryAnn.resultClass())) {        
        // FIXME should be done in a second pass due to entity name?
        final NativeSQLQueryRootReturn entityQueryReturn =
            new NativeSQLQueryRootReturn (
                // ...
            );
    }
    else {
        throw new NotYetImplementedException( "Pure native scalar queries are not yet supported" );
    }
}

Apparently, the NamedNativeQuery annotation (or corresponding XML version), should either have a non-empty resultset mapping, or a result class that's something other than void.

So, isn't a workaround for this problem perhaps to just provide a resultset mapping, even though we're not doing a select query? To test this, I tried the following:

<named-native-query name="SomeName" result-set-mapping="dummy">
    <query>
        INSERT INTO
            foo
        SELECT
            *
        FROM
            bar
    </query>
</named-native-query>

<sql-result-set-mapping name="dummy">
    <column-result name="bla" />
</sql-result-set-mapping>

And lo and behold, this actually works. Hibernate starts up and adds the query to its named query repository, and when subsequently executing the query there is no exception and the insert happens correctly.

Looking at the Hibernate code again it looks like this shouldn't be that impossible to fix. It's almost as if the original programmer just went out for lunch while working on that code fragment, temporarily put the exception there, and then after lunch completely forgot about it.

Until this has been fixed in Hibernate itself, the result-set-mapping workaround might be useful.

Arjan Tijms