Performing Queries

A data service's perform operation accepts so-called requests which are composed of activities. These activities are capabilities or operations that the service can perform. Some of them can be stringed together so one activity uses the output of another for further processing. A particular data service may only support a subset of all available activities - this depends on the implementation.

In the client toolkit, all activity classes extend a common class uk.org.ogsadai.client.toolkit.activity.Activity and can be composed to form a request. This page shows you how to run simple SQL and XPath queries against a database.

Performing a Basic SQL Query

In this example you will run a simple SQL query across a MySQL database and print out the results. For example, select * from littleblackbook where id='3475' will select a single row from the table littleblackbook.

  1. Locate a data service as was described on a previous page.
  2. Construct a new SQLQuery object from your SQL query string. SQLQuery extends the Activity class.
    SQLQuery query =
      new SQLQuery("select * from littleblackbook where id='3475'");
    
  3. Now call the Perform operation to run the request. This will execute the query and return the results in the Response object.
    Response response = service.perform( query );
    
  4. Have a look at the results. You can convert the Response object to a string as follows:
    String responseString = response.getAsString();
    
    The response is an XML document which contains a result element for each activity. Each result describes the status of its activity (COMPLETED, ERROR, UNSTARTED) and holds data that the activity may have produced. In this example, these are the results of the SQL query in WebRowSet format.

See examples/tutorials/clienttoolkit/SimpleSQLQueryExample.java for an example solution.

Performing a Sequence of Queries

When you have more than one query, you can wrap a number of operations into one request.

  1. First, construct two SQL queries from strings as shown above.
    SQLQuery query1  =
      new SQLQuery("select * from littleblackbook where name like '%Krause'");
    SQLQuery query2  =
      new SQLQuery("select * from littleblackbook where name like '%Sugden'");
    
  2. Now construct a Request object. The Request constructor takes an array of Activity objects as an argument, such as SQLQuery.
    Request request =
      new ActivityRequest( new SQLQuery[] { query1, query2 }  );
    
  3. Alternatively, you can create an empty request and add activities to it one by one. This enables you to add a number of activities using a loop, for example.
    ActivityRequest request = new ActivityRequest();
    SQLQuery query =
      new SQLQuery("select * from littleblackbook where id = '2359'");
    request.add( query );
    
  4. When the request is sent to the GDS it will perform the sequence of queries and return the results in one Response document.
    Response response = service.perform( request );
    

See examples/tutorials/clienttoolkit/SQLQuerySequence.java for an example solution.

Performing a Parameterised SQL Query Repeatedly

This example demonstrates how to evaluate a parameterised SQL query repeatedly against the same data resource, changing the parameter values for each iteration.

  1. Construct a new ActivityRequest.
    ActivityRequest request =  new ActivityRequest();
    
  2. You have to create a new SQLQuery object for each iteration:
    SQLQuery query  =
      new SQLQuery("select * from littleblackbook where id>? and id<?");
    
    The ? represent SQL parameters as usual.
  3. You can add parameter values to your query as follows:
    query.setParameter(1, String.valueOf(i));
    query.setParameter(2, String.valueOf(i + 20));
    
    The first argument is the position of the parameter in the SQL string (starting with 1) and the second argument is the value of the parameter.
  4. When your query is ready, add it to the request:
    request.add( query );
    
  5. Write a request which retrieves blocks of rows with IDs between 100 and 120, 1100 and 1120, ..., 9100 and 9120.

See examples/tutorials/clienttoolkit/ParameterisedSQLQuery.java for an example solution.

Performing an XPath Query on an XML Database

If you have access to an XML database, you can perform XPath queries using the XPathQuery object. The request is performed as before. For example, the XPath expression /entry[@id<10] selects all entry elements with an attribute id whose value is less than 10.

XPathQuery query = new XPathQuery( "/entry[@id<10]" );

See examples/tutorial/clienttoolkit/SimpleXPathQueryExample.java for an example.