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 –