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:

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

select * from littleblackbook where id='3475'

and returns the following:

Implementation

The following steps show you how to write a Java client that creates a Grid Data Service (GDS), queries the database and prints out the results.

1. Create a new directory examples/tutorials/clienttoolkit/. 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.Response;
   import uk.org.ogsadai.client.toolkit.service.DataService;
   import uk.org.ogsadai.client.toolkit.wsrf.WSRFServiceFetcher;
   import uk.org.ogsadai.client.toolkit.activity.sql.SQLQuery;
   

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

     String handle = "http://192.167.2.4:8222/wsrf/services/ogsadai/DataService";
     String id = "MySQLResourceX";
     DataService service = WSRFServiceFetcher.getInstance().getDataService(handle, id);
     

     Make sure that you replace "X" with the number of your data resource (1-35).
   • Construct a new SQLQuery object from your SQL query string...

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

   • ... execute the query ...

     Response response = service.perform( query );
     

   • ... and have a look at the results!

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

   • Now compile and run your client code (an example solution can be found examples/tutorials/clienttoolkit/SimpleExample.java):

     $ javac tutorial/clienttoolkit/SimpleExample.java
     $ java tutorial.clienttoolkit.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="http://ogsadai.org.uk/namespaces/2005/03/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 examples/tutorials/clienttoolkit/SimpleExample.java for an example solution.