GroovyWithSQLDatabaseAccess

Groovy with SQL Database access

how to use Groovy with SQL database access ?

To illustrate how its working let's have a look at the following example :

With XSP, to display a football list players from my database i do this

<esql:connection>
 <esql:pool>foot</esql:pool>
 <esql:execute-query>
   <esql:query>SELECT * FROM m_player </esql:query>
    <esql:results>
       <esql:row-results>
          <player>
              <id_player><esql:get-int column="id_player"/></id_player>
              <player_name><esql:get-string column="player_name"/></player_name>
              <player_birth><xsp:expr><esql:get-string column="player_birth"/></xsp:expr></player_birth>
              <player_town><esql:get-string column="player_town"/></player_town>
              <player_size><esql:get-string column="player_size"/></player_size>
              <player_weight><esql:get-string column="player_weight"/></player_weight>
              <player_start><esql:get-string column="player_start"/></player_start>
              <player_photo><esql:get-string column="player_photo"/></player_photo>
              <player_position><esql:get-string column="player_position"/></player_position>
              <id_club><esql:get-string column="id_club"/></id_club>
           </player>
           </esql:row-results>
       </esql:results>
    </esql:execute-query>
</esql:connection>

Now with Groovy i just have to do this

import groovy.sql.Sql
// Generate XML using the (way cool) GroovyMarkup syntax
import groovy.xml.SAXBuilder

sql = Sql.newInstance('jdbc:postgresql://localhost:5432/foot, "footlogin","footpass","org.postgresql.Driver")

// Create a Groovy Builder which writes directly to the Cocoon pipeline
contentHandler = bsf.lookupBean("contentHandler")
xml = new SAXBuilder(contentHandler)

contentHandler.startDocument()
xml.root() {

   sql.eachRow('select * from m_player') { plr |
   xml.player() {
         id_player(plr.id_player);
         player_name(plr.player_name);
         player_url_name(plr.player_url_name);
         player_birth(plr.player_birth);
         player_town(plr.player_town);
         player_size(plr.player_size);
         player_weight(plr.player_weight);
         player_start(plr.player_start);
         player_photo(plr.player_photo);
         player_position(plr.player_position);
         id_club(plr.id_club);
     }
   }

}

sql.close()
contentHandler.endDocument()
// thats it.

To use your groovy script, as describe in the sample BSF block do the following in your sitemap :

The generator :

    <map:generator logger="sitemap.generation.scriptgenerator" name="script" src="org.apache.cocoon.generation.ScriptGenerator">
        <!-- Groovy support -->
        <add-languages>
            <language name="groovy" src="org.codehaus.groovy.bsf.GroovyEngine">
              <extension>groovy</extension>
              <extension>gy</extension>
            </language>
        </add-languages>
    </map:generator>

The matcher

      <map:match pattern="generator/*.gy">
        <map:generate type="script" src="generator/{1}.gy"/>
        <map:transform src="context://samples/hello-world/style/xsl/page2html.xsl"/>
        <map:serialize type="html"/>
      </map:match>

I hope this helps.

-- olivier demah --

how to use Groovy to get values of parameters from Forms ?