A Simple Example: Running an SQL Query

In this example, you will run a simple SQL query across a table littleblackbook which looks like this:

id	name	address	phone
1	Ally Antonioletti	826 Hume Crescent, Southampton	01670061244
2	Amy Atkinson	583 Atkinson Drive, Southampton	06312054624
3	Andrew Borley	354 Jackson Road, Edinburgh	01057075166
4	Charaka Chue Hong	750 Pearson Crescent, Southampton	09945916393
5	Dave Hardman	079 Borley Gardens, Winchester	06725558505
...	...	...	...

For example, the following SQL statement selects one row from the table littleblackbook:

select * from littleblackbook where id='3475'

and returns the following:

3475

James Antonioletti

163 Palansuriya Avenue, Winchester

008979852295

Implementation

The following steps show you how to write a Java client that contacts a data service, queries the database and prints out the results.

Create a new directory for your example code. Within this directory, open a new Java class with a main method, called SimpleClient.java, for example. This class has to import the following client toolkit classes:

import uk.org.ogsadai.client.toolkit.GenericServiceFetcher;
import uk.org.ogsadai.client.toolkit.Response;
import uk.org.ogsadai.client.toolkit.activity.ActivityRequest;
import uk.org.ogsadai.client.toolkit.activity.sql.SQLQuery;
import uk.org.ogsadai.client.toolkit.activity.sql.WebRowSet;
import uk.org.ogsadai.client.toolkit.service.DataService;

Before you can access the database you must connect to the Data Service with a given URL. Use the uk.org.ogsadai.client.toolkit.GenericServiceFetcher to get a data service:

String handle = "http://localhost:8080/wsrf/services/ogsadai/DataService";
String id = "MySQLResource";
DataService service = GenericServiceFetcher.getInstance().getDataService(handle, id);

Now construct a new SQLQuery object from your SQL query string.

SQLQuery query = new SQLQuery("select * from littleblackbook where id='3475'");

Then choose an output format for your query so it can be delivered in the response. We will use WebRowSet which is an XML representation of a JDBC ResultSet object.
```
WebRowSet rowset = new WebRowSet(query.getOutput());
```

Create a request which holds both the query and the conversion ...

ActivityRequest request = new ActivityRequest();
request.add(query);
request.add(rowset);

... execute the request ...

Response response = service.perform(request);

... and have a look at the results!

System.out.println(response.getAsString());

Now compile and run your client code:

$ javac SimpleExample.java
$ java SimpleExample

The results are wrapped in an element which contains the SQL results in web row set format. A WebRowSet document is a XML representation of a SQL RowSet and consists of a properties section, a metadata section and a data section. The metadata gives information about the columns in the result set. The actual data is contained within the data element. Here's what your query should return:

<?xml version="1.0" encoding="UTF-8"?>
<ns1:response xmlns:ns1="https://ogsadai.org.uk/namespaces/2005/10/types">
    <ns1:request status="COMPLETED"/>
    <ns1:result name="SQLQuery-ogsadai-104efff5bd7" status="COMPLETED"/>
    <ns1:result name="ogsadai-104efff5bd8" status="COMPLETED"><![CDATA[<?xml version="1.0" encoding="UTF-8"?>
<webRowSet xmlns="http://java.sun.com/xml/ns/jdbc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/jdbc http://java.sun.com/xml/ns/jdbc/webrowset.xsd">
 <properties>
  <command><null/></command>
  <concurrency>1007</concurrency>
  <datasource><null/></datasource>
  <escape-processing>true</escape-processing>
  <fetch-direction>1000</fetch-direction>
  <fetch-size>0</fetch-size>
  <isolation-level>0</isolation-level>
  <key-columns>
  </key-columns>
  <map></map>
  <max-field-size>1048576</max-field-size>
  <max-rows>0</max-rows>
  <query-timeout>0</query-timeout>
  <read-only>true</read-only>
  <rowset-type>ResultSet.TYPE_FORWARD_ONLY</rowset-type>
  <show-deleted>false</show-deleted>
  <table-name><null/></table-name>
  <url><null/></url>
 </properties>
 <metadata>
  <column-count>4</column-count>
  <column-definition>
   <column-index>1</column-index>
   <auto-increment>false</auto-increment>
   <case-sensitive>false</case-sensitive>
   <currency>false</currency>
   <nullable>1</nullable>
   <signed>true</signed>
   <searchable>true</searchable>
   <column-display-size>11</column-display-size>
   <column-label>id</column-label>
   <column-name>id</column-name>
   <schema-name></schema-name>
   <column-precision>11</column-precision>
   <column-scale>0</column-scale>
   <table-name>littleblackbook</table-name>
   <catalog-name></catalog-name>
   <column-type>4</column-type>
   <column-type-name>INTEGER</column-type-name>
  </column-definition>
  <column-definition>
   <column-index>2</column-index>
   <auto-increment>false</auto-increment>
   <case-sensitive>false</case-sensitive>
   <currency>false</currency>
   <nullable>1</nullable>
   <signed>false</signed>
   <searchable>true</searchable>
   <column-display-size>64</column-display-size>
   <column-label>name</column-label>
   <column-name>name</column-name>
   <schema-name></schema-name>
   <column-precision>64</column-precision>
   <column-scale>0</column-scale>
   <table-name>littleblackbook</table-name>
   <catalog-name></catalog-name>
   <column-type>12</column-type>
   <column-type-name>VARCHAR</column-type-name>
  </column-definition>
  <column-definition>
   <column-index>3</column-index>
   <auto-increment>false</auto-increment>
   <case-sensitive>false</case-sensitive>
   <currency>false</currency>
   <nullable>1</nullable>
   <signed>false</signed>
   <searchable>true</searchable>
   <column-display-size>128</column-display-size>
   <column-label>address</column-label>
   <column-name>address</column-name>
   <schema-name></schema-name>
   <column-precision>128</column-precision>
   <column-scale>0</column-scale>
   <table-name>littleblackbook</table-name>
   <catalog-name></catalog-name>
   <column-type>12</column-type>
   <column-type-name>VARCHAR</column-type-name>
  </column-definition>
  <column-definition>
   <column-index>4</column-index>
   <auto-increment>false</auto-increment>
   <case-sensitive>false</case-sensitive>
   <currency>false</currency>
   <nullable>1</nullable>
   <signed>false</signed>
   <searchable>true</searchable>
   <column-display-size>20</column-display-size>
   <column-label>phone</column-label>
   <column-name>phone</column-name>
   <schema-name></schema-name>
   <column-precision>20</column-precision>
   <column-scale>0</column-scale>
   <table-name>littleblackbook</table-name>
   <catalog-name></catalog-name>
   <column-type>12</column-type>
   <column-type-name>VARCHAR</column-type-name>
  </column-definition>
 </metadata>
 <data>
  <currentRow>
   <columnValue>3475</columnValue>
   <columnValue>James Antonioletti</columnValue>
   <columnValue>163 Palansuriya Avenue, Winchester</columnValue>
   <columnValue>00897985229</columnValue>
  </currentRow>
 </data>
</webRowSet>]]></ns1:result>
</ns1:response>

See OGSA-DAI/examples/src/uk/org/ogsadai/examples/clienttoolkit/SimpleExample.java for an example solution.