Grails URLMapping

Given this URLMapping

"/news/show/$id?/item/$itemId?" {
 controller = "news"
 action = "show"
 }

results in the following params:

URL: http://localhost:8080/rfw/news/show
params: [“action”:”show”, “controller”:”news”]

URL: http://localhost:8080/rfw/news/show/1
params: [“id”:”1″, “action”:”show”, “controller”:”news”]

URL: http://localhost:8080/rfw/news/show/1/item
params: [“id”:”1″, “action”:”show”, “controller”:”news”]

URL: http://localhost:8080/rfw/news/show/1/item/4
params: [“id”:”1″, “action”:”show”, “controller”:”news”, “itemId”:”4″]

Hibernate Mapping

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();
 }