XWIKI SQL

GradSoft Ltd. http://www.gradsoft.ua

Ruslan Shevchenko.


Introduction


XwikSqlPlugin is useful in situation, when we need connect from xwiki application to foreign relational databases. With help of xwikisql plugin we can use sql statements in velocity templates. In addition application programmer cand not care about connection management: xwiki sql plugin do this automatically. So, programming in xwiki become efficient alternative to LAMP, applicable in java-based environment.


Download:

You can download project artifacts from google project space or from our local site:


License is GPL v3.

Installation:



Usage:


Now it is possible to use sql statements in velocity templates. Detailed reference you can find in XwikiSqlApi, below is few code snippets:

## connect to database.
#set ($mydb=$xwiki.sql.getDabase(“MyDS”))

## print top 10 records from our table.
<table>
#foreach($row in $mydb.executeQuery(
“select id, name
from (select id, name, rownum from test_table)
where rownum < 10”
))
<tr>
<td> $row.getLong(“id”) </td>
<td> $row.getString(“name”) </td>
</tr>
#end
</table>


Of course better use bind variables:



##receive database
#set ($mydb=$xwiki.sql.getDabase(“MyDS”))

## print n pages from our table.
## set ($lowRow=$currentPage*$nRowsInPage)
## set ($hightRow=($currentPage+1)*$nRowsInPage)
<table>
#foreach($row in $mydb.executeQuery(
“select id, name
from (select id, name, rownum from test_table)
where rownum > ? and rownum < ?”
, [ $lowRow, $hightRow ]
))
<tr>
<td> $row.getLong(“id”) </td>
<td> $row.getString(“name”) </td>
</tr>
#end
</table>


Example of update statements:


##receive database
#set ($mydb=$xwiki.sql.getDabase(“MyDS”))

#set ($result=$mydb.executeUpdate(
“update test_table set name=? where id=?”,
[$myname,$myid])


Sometimes we need explicit set type of binded parameters, in such case we can write pair from typename and value (instead value on the same place), where typename is name of appropriative constants in javax.sql.Types API.

Example:


#set ($result=$mydb.executeUpdate(
“update test_table set name=? when=? where id=?”,
[$myname,[TIME “2008-01-01T12:00:00”],$myid])


will set value of when to midday of 01.01.2008. As you see, date and time values are mapped to strings according to IS0 86601.

Yet one note about types: for now supported only primitive data types (if you need more – contact us).

That's all in glance. API description is available (reference).

Yet one not about error handling: usually errors during exceptions are throwed to xwiki top-level, but sometimes we need handle sql errors in velocity code. It is possible by setting database property throwExceptions to false, as in next code snippet:

$mydb.setThrowExceptions(false)
#set ($result=$mydb.executeQuery($sql))
#if($mydb.wasError())
Error during evaluating $sql: $mydb.getLastException().getMessage()
#else
sql sentence $sql was executed without errors. Received $rs.getNRows() rows.
#end

Configuration:

XwikiSqlPlugin can be configured or through set of variables in xwiki.cfg, prefixed by 'xwikisql.' or through own configuration file. (name of file can be set in xwikisql.configuration.fname).

Configurated variables are next:

Appropriative section of xwik.cfg can look as:


xwikisql.logSql=1
xwikisql.throwExceptions=0
xwikisql.maxRowsRetrieved=200

Or, when we want to keep xwikisql configuration in separate file, than in xwiki.cfg we have:

xwikisql.configuration.fname=<fname>

and in <fname> - serialized from of class ua.gradsoft.xwikisql.XWikiSqlPluginConfiguration, according to JSR57:

<?xml version="1.0" encoding="UTF-8"?>
<java version="1.5.0" class="java.beans. XMLDecoder">
<object class="ua.gradsoft.xwikisql.XWikiSqlPluginConfiguration">
<void property=”throwExceptions”>
<boolean>true</boolean>
</void>
<void property=”logSql”>
<boolean>true</boolean>
</void>
<void property=”maxRowsRetrieved”>
<int>300</int>
</void>
</object>
</java>

Also logging can be configured from xwiki-wide log4j.properties – to enable debug just append next string to log4j configuration and restart xwiki.

log4j.logger.ua.gradsoft.xwikisql=debug


Changes:

2008-05-07 revision-1.5

2008-04-05 revision-1.4

2008-03-07 revision-1.3

2008-02-27 revision-1.2

2008-02-11 revision-1.1

2008-02-06 Initial revision.