Execute SQL
 | Example of how to execute SQL from a wiki page |
| Type | Snippet |
| Category | |
| Developed by | Unknown |
| Rating | |
| License | GNU Lesser General Public License 2.1 |
Table of contents
Description
Using Hibernate
One way is to use Hibernate's ability to execute SQL. To do so, you just need to get access to the XWikiHibernateStore component as show below.
import com.xpn.xwiki.store.*
def hibStore = services.component.getInstance(XWikiStoreInterface.class, "hibernate")
def ctx = xcontext.context
hibStore.checkHibernate(ctx)
def bTransaction = hibStore.beginTransaction(false, ctx)
def session = hibStore.getSession(ctx)
def statement = session.connection().createStatement();
def rset = statement.executeQuery("select xwd_fullname from xwikidoc limit 10");
while (rset.next()) {
out.println("* ${rset.getString(1)}")
}
hibStore.endTransaction(ctx, true)
{{/groovy}}
Using JDBC in Groovy
Another way is to write JDBC in Groovy using Groovy's SQL feature. For example:
import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:jtds:sqlserver://serverName/dbName-CLASS;domain=domainName', 'username', 'password', 'net.sourceforge.jtds.jdbc.Driver' )
sql.eachRow( 'select * from tableName' ) { println "$it.id -- ${it.firstName} --" }
{{/groovy}}
Tutorial using Components
Using a Component to Connect
You can hide some of the access details within a component, either as an external JAR or as a separate Groovy page. For instance, you could have a method getConnection() provided by your component democomponent:
import java.sql.DriverManager;
public Connection getConnection() {
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql:demodb", "dbuser", "dbpass");
return conn;
}
This can then be called from the Groovy script within the XWiki page and used in the same way as before:
import groovy.sql.Sql
def sql = new Sql(services.democomponent.getConnection())
println "|= Surname"
sql.eachRow("SELECT surname FROM Person"){
println "|${it.surname}"
}
sql.close()
{{/groovy}}
Pushing down into a Component
To avoid SQL injection attacks and prevent users executing arbitrary SQL queries, the database access can be limited to a set of operations that can be executed from within the methods of a component. This also allows them to be called from a Velocity script. Here there are two operations: addPerson to insert a new row into the Person table, and getSurnames to query everyone in the Person table.
* Add new entry to Person table
* @returns Value of person_id (some auto-incremented attribute) for new row upon success, else null
*/
@Override
public Integer addPerson(String firstname, String surname) {
try (Connection conn = DriverManager.getConnection("jdbc:postgresql:demodb", "dbuser", "dbpass")) {
PreparedStatement ps = conn.prepareStatement("INSERT INTO Person(first_name, surname) VALUES (?,?) RETURNING person_id");
ps.setString(1, firstname);
ps.setString(2, surname);
ResultSet rset = ps.executeQuery();
if (rset.next()) {
return rset.getInt("person_id");
}
} catch (Exception e) {
this.logger.error("DemoComponent.addPerson(" + firstname + "," + surname + ")", e);
}
return null;
}
/**
* Get surnames of everyone in Person
* @returns List of all surnames
*/
@Override
public ArrayList<String> getSurnames() {
ArrayList<String> surnames = new ArrayList<>();
String query = "SELECT surname FROM Person";
try (Connection conn = DriverManager.getConnection("jdbc:postgresql:demodb", "dbuser", "dbpass")) {
PreparedStatement ps = conn.prepareStatement(query);
ResultSet rset = ps.executeQuery();
while (rset.next()) {
surnames .add(rset.getString(1));
}
} catch (Exception e) {
this.logger.error("DefaultAlertDb.listStudies()", e);
}
return studies;
}
The component methods are called from the following page, which has a form to submit new people (using addPerson) and prints a table of the current people by surname (using getSurnames).
#if ($request.addPerson== "true")
{{info}}
Request received to add person $request.firstname $request.surname : $services.democomponent.addPerson($request.firstname, $request.surname)
{{/info}}
#end
#set($people = $services.democomponent.getSurnames())
|= Surname
#foreach($person in $people)
| $person
#end
{{/velocity}}
{{html}}
<form action="" id="newperson" method="post"><div>
<input type="hidden" name="addPerson" value="true" />
<label for="firstname">First Name</label>
<input id="firstname" name="firstname" size="30" type="text" class="withTip" value="First Name" />
<label for="surname">Surname</label>
<input id="surname" name="surname" size="30" type="text" class="withTip" value="Surname" />
<span class="buttonwrapper"><input type="submit" class="button" value="Add"/></span>
</div></form>
{{/html}}
Using within a LiveTable
You can make use of the LiveTable macro to perform AJAX queries of an external database. LiveTables are quite strongly bound to the XWiki object model and so require customisation to work more generally.
The script containing the LiveTable needs to include details of the custom page that will generate the required JSON (in this case DemoSpace.PeopleJSON and the attributes of the table (just surname here).
{{velocity}}
#set($columns = ["surname"])
#set($options = {
"resultPage":"DemoSpace.PeopleJSON"
})
#set($columnsProperties = {
"surname" : { "type" : "text", "displayName": "Surname", "filterable": false }
})
#livetable("protocolTable" $columns $columnsProperties $options)
{{/velocity}}
This LiveTable then calls DemoSpace.PeopleJSON, which outputs JSON instead of generating a typical wiki page. In this example the JSON is obtained via a Groovy SQL call that also obtains all the other info required by the LiveTable.
#if("$!{request.xpage}" == 'plain')
$response.setContentType('application/json')
#end
$xcontext.put('reqNo',$util.parseInt($request.reqNo))
##==============================
## Offset = item # at which to start displaying data
##==============================
#set($offset = $util.parseInt($request.get('offset')))
## offset starts from 0 in velocity and 1 in javascript
#set($offset = $offset - 1)
#if($offset < 0)
#set($offset = 0)
#end
$xcontext.put('offset',$offset)
##==================
## Limit = # of items to display
##==================
#set($limit = $util.parseInt($request.get('limit')))
#if($limit < 1)
#set($limit = 1)
#end
$xcontext.put('limit',$limit)
##==================
## Tag = one parameter per tag
##==================
#if($request.tag)
## #foreach($tag in $request.getParameterValues('tag'))
## TODO: Add code to filter items based on the selected tags
## #end
#end
##==========
## Sort direction
##==========
#set($order = "$!request.sort")
$xcontext.put('order',$order)
#if($order != '')
#set($orderDirection = "$!{request.get('dir').toLowerCase()}")
#if("$!orderDirection" != '' && "$!orderDirection" != 'asc')
#set($orderDirection = 'desc')
#end
$xcontext.put('orderDirection',$orderDirection)
#end
## ===========
## Filter Data here...
## ===========
## TODO: Add code to filter data
## Each column can be filtered and the filter for a column can be retrieved with:
## #set($filterValue = "$!{request.get(<columnname>)}")
## ===
## JSON
## ===
{{/velocity}}
{{groovy}}
import groovy.sql.Sql
def basequery = "SELECT true AS doc_viewable, surname FROM Person"
// Augment the base query with additional ORDER and LIMIT parameters
def mainquery = basequery
def params = []
if (xcontext.order !="") {
mainquery += " ORDER BY ? " + xcontext.orderDirection
params.add(xcontext.order)
}
mainquery += " LIMIT ? OFFSET ?"
params.add(xcontext.limit)
params.add(xcontext.offset)
def sql = Sql.newInstance('jdbc:postgresql:demodb', 'dbuser', 'dbpass', 'org.postgresql.Driver')
//
// Format the JSON string
//
println '{'
// Retrieve full number of available row from full query to support paging
println '"totalrows": ' + sql.firstRow("SELECT count(*) FROM ( " + basequery + " ) AS X")[0] + ','
// Don't bother with tags (this is XWiki stuff that we don't need to support)
println '"matchingtags": {},'
println '"tags" : [],'
// Pass back some state info
println '"offset": ' + (xcontext.offset+1) +','
println '"reqNo": ' + xcontext.reqNo + ','
// Use PostgreSQL's JSON aggregation function to avoid doing it manually
def rowdata = sql.firstRow("SELECT count(*) AS returnedrows, json_agg(X) AS rows FROM ( "+ mainquery + ") AS X", params)
println '"returnedrows": ' + rowdata['returnedrows'] + ','
println '"rows": ' + rowdata['rows']
println '}'
sql.close()
{{/groovy}}
Using a JNDI Datasource
You can facilitate connection pooling by declaring a JNDI datasource that Tomcat can maintain for you. Within xwiki.xml you need to declare the datasource, e.g.:
<Resource name="jdbc/demodb"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql:demodb"
username="dbuser"
password="dbpass"
maxActive="10"
maxIdle="4"
maxWait="-1"
/>
You also need to make a local reference to this resource in web.xml:
<description>DemoDB Connection</description>
<res-ref-name>jdbc/demodb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
You can then get connections via the JNDI DataSource interface rather than creating new connections directly. This example is for Java:
if ( cxt == null ) {
throw new Exception("Uh oh -- no context!");
}
DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/demodb" );
if ( ds == null ) {
throw new Exception("Data source not found!");
}
Connection conn = ds.getConnection();
Prerequisites & Installation Instructions
