Hibernate’s “Pure native scalar queries are not yet supported”

22 April 2012, by: Arjan Tijms

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

Tags: , ,

14 comments to “Hibernate’s “Pure native scalar queries are not yet supported””

  1. clasificado says:

    i owe you one

  2. john says:

    thank you thank you!

  3. negora says:

    Thank you so much. I had done the same thing using Annotations but not in XML files. My mistake was to put the tag before .

  4. negora says:

    Ops! This ate my XML tags.

    In my previous message I simply meant “My mistake was to put the tag <sql-result-set-mapping> before the <named-native-query> tags. Once I moved it below them, it got solved. So thanks ;) .

  5. arjan tijms says:

    You’re welcome. Glad it now works for you :)

  6. JavaPins says:

    Hibernate’s “Pure native scalar queries are not yet supported”…

    Thank you for submitting this cool story – Trackback from JavaPins…

  7. Tim says:

    /**
    * Simple wrapper entity work around for the limited hibernate pure native query support. Where an HQL query is not possible
    */
    @Entity
    @NamedNativeQueries({
    @NamedNativeQuery(name=”COUNT_QUERY”, resultClass = CountDTO.class)})
    public class CountDTO {
    @Id
    @Column(name = “COUNT”)
    private Long count;

    public Long getCount() {
    return count;
    }

    public void setCount(Long count) {
    this.count = count;
    }
    }

  8. ifti says:

    Thanks man.

  9. dev says:

    This is great stuff..
    Can we do it for stored procedure as well?

  10. Kawu says:

    According to https://hibernate.onjira.com/browse/HHH-4412 this has been fixed on 2013-03-06…

  11. Ray says:

    Just ran into this right this morning, found your post, avoided a lot of frustration. Thanks. :-)

    However, maybe we’ll just upgrade the Hibernate version to see if it works now…

  12. ccfRobotics says:

    Looking at the ticket you mentioned, it appears this has now been fixed in versions 4.2 (CR3). Regardless, for those who haven’t updated yet, good workaround – thanks!

  13. Exceção: Pure native scalar queries are not yet supported | Rafael Liu Blog says:

    [...] exceção é muito bem explicada neste blog. Ainda assim achei que valia um post para mostrar como fazer com [...]

  14. Thomas says:

    Also big thanks for the CountDTO.

    I’ve put ‘resultClass = CountDTO.class’ into my native query and it finally works!

Type your comment below:

best counter