Hibernate – Pure native scalar queries are not yet supported

I’m using Hibernate annotations for my database access.  This works fine most of the time, but with complex queries the Hibernate hql can get tricky.  When I know exactly how to write the query using sql I sometimes give in and use a Hibernate NamedNativeQuery.  I know this means I will have a little bit of refactoring to do if I ever decide to change the database, but other than that remote possibility, this also seems to work fine and for me is a much faster approach.  However, if the query just returns a number (i.e., the query is something like ‘select count(id) where…..) I came up against this error

 

org.hibernate.cfg.NotYetImplementedException: Pure native scalar queries are not yet supported

 

I couldn’t find a obvious way around this, so came up with the solution below.

 

The NamedNativeQuery.

@Entity

@NamedNativeQueries({

@NamedNativeQuery(name=”countAllForActivity” ,

query=”select count(distinct st.othertablea_Id) as id” +

” FROM SomeTable st where st.othertableb_Id = ?1″,

resultSetMapping=”countWrapper”)

 

The SqlResultSetMapping.

@SqlResultSetMappings({

@SqlResultSetMapping(name=”countWrapper”,

entities={

@EntityResult(entityClass=CountWrapper.class)

})

})

public class SomeTable implements Serializable {

// the SomeTable class contents

}

 

So CountWrapper is an @Entity I have created just to hold the results of count queries.  CountWrapper has one field.  Id.  Hence the query has to select the count ‘AS ID’.  The query then returns the result as a CountWrapper object with the result in the id field.

 

The CountWrapper class

 

@Entity

public class CountWrapper implements Serializable {

@Id

private Integer id;

 

public Integer getId() {

return id;

}

 

public void setId(Integer id) {

this.id = id;

}

}

 

But that is not enough.  Hibernate gets upset if you have a @Entity class without a corresponding table in the database.  So you also have to create a CountWrapper table.

 

Sql to create the CountWrapper table

CREATE TABLE CountWrapper (

id INTEGER UNSIGNED,

PRIMARY KEY(id)

)

 

The CountWrapper table is an empty table.  It never gets populated or queried.  It’s just there to keep hibernate happy (it probably won’t do such a good job of keeping your DBA happy though 🙂 ).

 

It’s then possible to run the query and get the result using the code below

 

public int count(int activityId) {

int toRet = 0;

Query namedQuery = getEntityManager().createNamedQuery(“countAllForActivity”);

namedQuery.setParameter(1, activityId);

Object namedRes = null;

try {

namedRes = namedQuery.getSingleResult();

} catch (NoResultException nre) {

// can ignore this – just return 0

} catch (Exception e) {

logger.error(“unexpected Exception when searching”, e);

throw new FFFFRuntimeException(“problem with search”, e);

}

if (namedRes != null) {

if (namedRes instanceof CountWrapper) {

toRet = ((CountWrapper)namedRes).getId();

} else {

throw new FFFFRuntimeException(“count returned something that was not an integer”);

}

logger.debug(“toRet is ” + toRet);

}

return toRet;

}

 

This works for me.  Hopefully it’ll help someone.  But I can’t help thinking there must be a proper way provided by hibernate to do this.  I just couldn’t find one.

 

Advertisements
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s