I often want to use a stored procedure to return a scalar value such as a count rather than a mapped object.
I find the best place to do this in the hibernate mapping file (.hbm.xml). In fact this is one of the reasons I like the mapping files over annotations. (I find the annotation syntax for named queries really hard to work with.)
In order for a named query to use a stored procedure, callable must be set to true.
To return a scalar value rather than a mapped object use return-scalar.
I have shown the same thing (getNumSubscritpions2) using native SQL rather than a stored procedure. In this example you don’t need the <![CDATA[ … ]]>, but I always include them just to be safe; if you use <, or > in your SQL, things start to break down if you have not wrapped the SQL with CDATA.
<hibernate-mapping>
<class name="className" table="tablename" select-before-update="true">
</class>
<sql-query name="getNumSubscriptions" callable="true">
<return-scalar column="num_subscriptions" type="java.lang.Integer" />
{ call pr_get_NumSubscriptions( '','', :labId, :programId, :subscriptionYear) }
</sql-query>
<sql-query name="getNumSubscriptions">
<return-scalar column="cnt" type="java.lang.Integer" />
<![CDATA[
select count(distinct subscription_id) as cnt
from v_Subscriptions (nolock)
where lab_entity_id = :labId
and program_id = :programId
and test_event_year = :subscriptionYear
and subscription_type_code not in ('SAMPLE_ONLY')
]]>
</sql-query>
</hibernate-mapping>
and calling a name query in your application is simple:
Integer count = (Integer)_entityManager.createNamedQuery( "getNumSubscriptions")
.setParameter( "labId", labId)
.setParameter( "programId", programId)
.setParameter( "subscriptionYear", subscriptionYear)
.getSingleResult();
if( count != null) {
numSubscriptions = count.intValue();
}