The Droid Chronicles – Web Services: Using kSOAP2 to Pass Complex Objects

If you have been following the Android Development posts in my blog you have seen a few basic entries. We have covered four topics: (1) calling a web service from […]
Print Friendly
AndRod

AndRod

If you have been following the Android Development posts in my blog you have seen a few basic entries. We have covered four topics: (1) calling a web service from Android using kSOAP2 (See post.), (2) calling a web service from Android using WSClient++ (See post.), (3) passing primitive parameters to a web service using kSOAP2 and WSClient (See Post.), and most recently (4) diagnosing, describing, and dealing with the null only parameter enigma [I call it NOPE - See post.]. In this entry we begin to deal with some of the more complex stuff; namely, handling complex parameters with an Android web service client.

This post sees us working with something more interesting than a glorified greeting service. This time we will look at a web service that returns the results of executing SQL queries against a PostgreSQL to Android clients. The Android client can then turn the results into charts. Interested? Great! But before we dig into lots of code it may be best to start with a simple picture of the architecture that organizes our efforts.

QueryService Context

Figure 1: QueryService Context

We want to create an Android web service client that will be able to extract data from a database of information about various cities. Rather than trying to get JDBC to work on the Android device (highly unrecommended) we will try to create a simple program that works with javax.sql.rowset.WebRowSet objects and displays the results for the user. We will work with a collection of Query objects to get our work done. The Query objects and the services that are provided are made available through a QueryServicePublisher. The diagram below(1) communicates the static relationship between the classes in our work.

QueryService UML Class Diagram

Figure 2: QueryService UML Class Diagram

In the end we hope to have the ability to write queries on the Android-based device (likely a tablet) and then get the results from the database.(2) Still interested? Me too. Let us begin the work.

The Web Service

Figure 2 shows that our web services are comprised of four classes: (1) QueryService – the interface or contract, (2) QueryServiceImplementation – the implementation of the contract, (3) Query – a data transfer object for web service operations between the client and the web service, and (4) QueryServicePublisher – a simple class for publishing the web service using the built-in JDK web server. The first class we ant to look at is QueryService

package com.bif.query;

import java.util.List;

import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebResult;
import javax.jws.WebService;
import javax.jws.soap.SOAPBinding;
import javax.jws.soap.SOAPBinding.Style;

/**
 * This interface is the contract for any class that would
 * provide an implementation of the greeting service.
 * @author <a href="mailto:roderick@biftechnologies.com">Roderick L. Barnes</a>
 */

@WebService
@SOAPBinding(style = Style.DOCUMENT)
public interface QueryService {
	/**
	 * Returns a <code>Query</code> object based on the given query
	 * identifier (<code>stringQueryID</code>).
	 * @param stringQueryID value used to locate the query in the underlying
	 * persistent storage.
	 * @return a <code>Query</code> object if it can be found in storage.
	 * Otherwise this method will return null.
	 */
	@WebMethod public Query getQuery(
			@WebParam( name = "stringQueryID")
			String stringQueryID
		);

	/**
	 * Returns the collection of queries found in persistent storage.
	 * @return the collection of queries found in persistent storage.
	 */
	@WebMethod
	@WebResult( name = "Query")
	public Query[] getQueries();

	/**
	 * Returns the result of executing the query associated with the
	 * given <code>Query</code> object.
	 * @param query object that will be used to get SQL query that will
	 * be executed.
	 * @return <code>WebRowSet</code> XML document based on the
	 * <code>ResultSet</code> produced by executing the query.
	 * @see com.sun.rowset.WebRowSet
	 * @see java.sql.ResultSet
	 */
	@WebMethod public String executeQuery(
			@WebParam ( name = "query")
			Query query
		);

	/**
	 * Updates the query in persistent storage based on the one that is
	 * provided as a parameter.
	 * @param query object encapsulating attributes that need to be passed
	 * onto the version in persistent storage.
	 */
	@WebMethod public void updateQuery(
			@WebParam ( name = "query")
			Query query
		);

	/**
	 * Removes from persistent storage the <code>Query</code> object identified
	 * by the given identifier (<code>stringQueryID</code>).
	 * @param stringQueryID identifier for the record that will be deleted
	 */
	@WebMethod public void deleteQuery(
			@WebParam ( name = "stringQueryID")
			String stringQueryID
		);
}

The code above is just an interface. Actual work is done by QueryServiceImplementation. The code for the implementation is shown below. Compare the method signatures and take a moment to convince yourself that the code that follows actually fulfills the contract.

package com.bif.query;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.jws.WebService;
import javax.sql.rowset.WebRowSet;

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import com.sun.rowset.WebRowSetImpl;

/**
 * Provides a simple implementation of the service endpoint interface
 * <code>QueryService</code>. This version does not persist queries to
 * an actual persistent storage layer. It currently does all work in an
 * internal memory model.
 * @author <a href="mailto:roderick.barnes@biftechnologies.com">Roderick L.
 * Barnes</a>
 */

@WebService(endpointInterface = "com.bif.query.QueryService")
public class QueryServiceImplementation implements QueryService {
	/**
	 * Private attribute for maintaining the collection of <code>Query</code>
	 * objects.
	 */
	private static List<Query> listOfQuery = new ArrayList<Query>();

	/**
	 * Simple no-argument constructor for a new instance of the
	 * query service object. This version creates <code>query</code> objects
	 * and loads them into the private collection attribute.
	 */
	public QueryServiceImplementation() {
		Query query = new Query();
		query.setQueryID(UUID.randomUUID().toString());
		query.setQueryName("City Temperatures");
		query.setJDBCDriverClassName("org.postgresql.Driver");
		query.setDatabaseURL("jdbc:postgresql://localhost:5432/tutorial");
		query.setJDBCUsername("notorious");
		query.setJDBCPassword("rod");
		query.setQueryText("SELECT cd.name, cd.average_temperature " +
				"FROM city_data AS cd");
		this.getQueryCollection().add(query);		

		query = new Query();
		query.setQueryID(UUID.randomUUID().toString());
		query.setQueryName("Average City Gas Price");
		query.setJDBCDriverClassName("org.postgresql.Driver");
		query.setDatabaseURL("jdbc:postgresql://localhost:5432/tutorial");
		query.setJDBCUsername("notorious");
		query.setJDBCPassword("rod");
		query.setQueryText("SELECT cd.name, cd.average_gas_price " +
				"FROM city_data AS cd");
		this.getQueryCollection().add(query);		

		query = new Query();
		query.setQueryID(UUID.randomUUID().toString());
		query.setQueryName("City Population");
		query.setJDBCDriverClassName("org.postgresql.Driver");
		query.setDatabaseURL("jdbc:postgresql://localhost:5432/tutorial");
		query.setJDBCUsername("notorious");
		query.setJDBCPassword("rod");
		query.setQueryText("SELECT cd.name, cd.population " +
				"FROM city_data AS cd");
		this.getQueryCollection().add(query);
	}

	private List<Query> getQueryCollection() {
		return this.listOfQuery;
	}

	/**
	 * Removes the query record identified by the provided identifier
	 * <code>stringQueryID</code>.
	 * @param stringQueryID identifier for the query record
	 */
	@Override
	public void deleteQuery(String stringQueryID) {
		Query queryTarget = null;

		/**
		 * find the query matching the ID
		 */
		for (Query query : this.getQueries()) {
			if (query.getQueryID().equals(stringQueryID)) {
				queryTarget = query;
				break;
			}
		}

		if (queryTarget != null) {
			this.getQueryCollection().remove(queryTarget);
		}
	}

	/**
	 * Executes the SQL query associated with the given Query object.
	 * The query results will be converted into an XML document.
	 * @see javax.sql.rowset.WebRowSet#writeXml(java.io.Writer)
	 */
	@Override
	public String executeQuery(Query query) {
		Connection connection = null;
		Statement statement = null;
		WebRowSet webRowSet = null;
		StringWriter stringWriter = null;

		try {
			Class.forName(query.getJDBCDriverClassName());

			connection = DriverManager.getConnection(
					query.getDatabaseURL(),
					query.getJDBCUsername(),
					query.getJDBCPassword()
				);
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(
					query.getQueryText()
				);
			webRowSet = new WebRowSetImpl();
			webRowSet.populate(resultSet);
			stringWriter = new StringWriter();
			webRowSet.writeXml(stringWriter);

			connection.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return stringWriter.toString();
	}

	@Override
	public Query[] getQueries() {
		return (Query[])this.listOfQuery.toArray(new Query[] {});
	}

	@Override
	public Query getQuery(String stringQueryID) {
		Query queryTarget = null;

		/**
		 * find the query matching the ID
		 */
		for (Query query : this.getQueries()) {
			if (query.getQueryID().equals(stringQueryID)) {
				queryTarget = query;
			}
		}

		return queryTarget;
	}

	@Override
	public void updateQuery(Query queryTarget) {
		/**
		 * find the query matching the ID
		 */
		for (Query query : this.getQueries()) {
			if (query.getQueryID().equals(queryTarget.getQueryID())) {
				query.setDatabaseURL(queryTarget.getDatabaseURL());
				query.setJDBCDriverClassName(queryTarget.getJDBCDriverClassName());
				query.setJDBCPassword(queryTarget.getJDBCPassword());
				query.setJDBCUsername(queryTarget.getJDBCUsername());
				query.setQueryName(queryTarget.getQueryName());
				query.setQueryText(queryTarget.getQueryText());
			}
		}
	}

	public static void main(String[] arrayOfString) {
		QueryServiceImplementation queryServiceImplementation =
			new QueryServiceImplementation();
		Query queryTarget = null;

		// get the queries
		for (Query query : queryServiceImplementation.getQueries()) {
			// print the queries
			System.out.println(query);
			queryTarget = query;
		}		

		// execute a query
		System.out.println(queryServiceImplementation.executeQuery(queryTarget));

		// update a query
		queryTarget.setQueryName("City Average IQ");
		queryTarget.setQueryText("SELECT city_name, average_iq FROM city_data");
		queryServiceImplementation.updateQuery(queryTarget);

		// get the queries
		System.out.println();
		for (Query query : queryServiceImplementation.getQueries()) {
			// print the queries
			System.out.println(query);
		}		

		// delete query
		queryServiceImplementation.deleteQuery(queryTarget.getQueryID());

		// get the queries
		System.out.println();
		for (Query query : queryServiceImplementation.getQueries()) {
			// print the queries
			System.out.println(query);
		}		

	}
}

Maybe you are wondering about how the Query objects are persisted to the database. They are not. That is, in this simple example we have reduced the complexity of the implementation so as not to make this material too long. Instead of persistent storage the Query objects are stored in a memory-based model; a Java collection is used to manage the objects. The class that was used to model the Query objects is shown below

package com.bif.query;

/**
 * Simple class for encapsulating the attributes of a query. The bundling
 * of JDBC information with the query is definitely not recommended. It should
 * be broken out into a separate class for data sources.
 * @author <a href="mailto:roderick.barnes@biftechnologies.com">Roderick Barnes</a>
 */
public class Query {
	/**
	 * provides a place for storing the primary key of the query object.
	 */
	private String stringQueryID;

	/**
	 * holds the class name of the JDBC driver that will be used to fulfill
	 * the query request
	 */
	private String stringJDBCDriverClassName;

	/**
	 * URL for the database in terms that the JDBC driver class can understand
	 */
	private String stringDatabaseURL;

	/**
	 * user friendly name for the query
	 */
	private String stringQueryName;

	/**
	 * username for the JDBC connection
	 */
	private String stringJDBCUsername;

	/**
	 * password for the JDBC connection
	 */
	private String stringJDBCPassword;

	/**
	 * text of the query
	 */
	private String stringQueryText;

	/**
	 * Simple constructor for new instances of <code>Query</code> objects.
	 */
	public Query() {

	}

	/**
	 * Sets the query primary key for the query object. This should be some type
	 * of universally unique identifier (UUID).
	 * @param stringQueryIDNew primary key for the query object.
	 */
	public void setQueryID(String stringQueryIDNew) {
		this.stringQueryID = stringQueryIDNew;
	}

	/**
	 * Returns the primary key for the query. This is the value used as the primary
	 * key in persistent storage both on the client and in the centralized repository.
	 * @return the primary key for the query.
	 */
	public String getQueryID() {
		return this.stringQueryID;
	}

	/**
	 * Set the class name of the JDBC driver that will be used to connect to the database.
	 * @param stringJDBCDriverClassNameNew name of the JDBC driver class
	 */
	public void setJDBCDriverClassName(String stringJDBCDriverClassNameNew) {
		this.stringJDBCDriverClassName = stringJDBCDriverClassNameNew;
	}

	/**
	 * Returns the name of the JDBC driver class that will facilitate connection to
	 * the databse.
	 * @return the name of the JDBC driver class that will help make the database
	 * connection.
	 */
	public String getJDBCDriverClassName() {
		return this.stringJDBCDriverClassName;
	}

	/**
	 * Sets the URL that will be used to connect to the database. This must
	 * be a format that is recognized by the JDBC driver.
	 * @param stringDatabaseURLNew URL used by JDBC driver to connect to database.
	 */
	public void setDatabaseURL(String stringDatabaseURLNew) {
		this.stringDatabaseURL = stringDatabaseURLNew;
	}

	/**
	 * Returns the database URL.
	 * @return the URL used by the JDBC driver to connect to the database.
	 */
	public String getDatabaseURL() {
		return this.stringDatabaseURL;
	}

	/**
	 * Sets the name of the query. This is user friendly name for the collection
	 * of attributes that are bundled to become a <code>Query</code> object.
	 * @param stringQueryNameNew
	 */
	public void setQueryName(String stringQueryNameNew) {
		this.stringQueryName = stringQueryNameNew;
	}

	/**
	 * Returns the name of the query.
	 * @return the name of the query.
	 */
	public String getQueryName() {
		return this.stringQueryName;
	}

	/**
	 * User name required for authentication with the database.
	 * @param stringJDBCUsernameNew user name required for database authentication
	 */
	public void setJDBCUsername(String stringJDBCUsernameNew) {
		this.stringJDBCUsername = stringJDBCUsernameNew;
	}

	/**
	 * Returns the name used to authenticate with the database
	 * @return the name used to authenticate with the database
	 */
	public String getJDBCUsername() {
		return this.stringJDBCUsername;
	}

	/**
	 * Sets the password that will be used to authenticate with the database.
	 * @param stringJDBCPasswordNew password used to authenticate with the
	 * database.
	 * @see java.sql.DriverManager#getConnection(String, String, String)
	 */
	public void setJDBCPassword(String stringJDBCPasswordNew) {
		this.stringJDBCPassword = stringJDBCPasswordNew;
	}

	/**
	 * Returns the password that will be used to authenticate with the
	 * database.
	 * @return the authentication password for the database
	 */
	public String getJDBCPassword() {
		return this.stringJDBCPassword;
	}

	/**
	 * SQL (or other type of query) used to get data from the database
	 * @param stringQueryTextNew SQL used to get data from the database
	 */
	public void setQueryText(String stringQueryTextNew) {
		this.stringQueryText = stringQueryTextNew;
	}

	/**
	 * Returns the SQL statement used to get data from the database.
	 * @return the SQL statement used to get data from the database
	 */
	public String getQueryText() {
		return this.stringQueryText;
	}

	/**
	 * Returns information required for a basic printing of a
	 * <code>Query</code> object.
	 */
	public String toString() {
		return "Query ID: " + this.getQueryID() + " Name: " + this.getQueryName();
	}
}

Publishing the service is handled by QueryServicePublisher. To keep things simple we are not using Tomcat, Axis 2, or full JAX-WS. Our services are being delivered by the built-in web server that comes with JDK 6.

package com.bif.query;

import javax.xml.ws.Endpoint;

/**
 * Publishes the <code>QueryService</code> using the JDK built-in HTTP server.
 * @author <a href="mailto:roderick.barnes@biftechnologies.com">Roderick Barnes</a>
 */
public class QueryServicePublisher {
	/**
	 * Simple launch harness for the service publisher.
	 * @param arrayOfString parameters for the launch of the service.
	 * @throws Exception thrown if there is a problem encountered while
	 * launching the service.
	 */
	public static void main(String[] arrayOfString) throws Exception {
		/**
		 *  insist that the user provide all of the startup arguments
		 */
		if (arrayOfString.length < 3) {
			System.out.println("  Usage: java com.bif.query.QueryServicePublisher " +
					"[service url] [service port] [service path]");
			System.out.println("Example: java com.bif.query.QueryServicePublisher " +
					"http://192.168.1.10 9876 BIFWebServices");
		}

		/**
		 *  extract the publication URL (1st argument)
		 */
		String stringServiceURL = arrayOfString[0];

		/**
		 *  extract the port number
		 */
		String stringPort = arrayOfString[1];
		int intPort = 9876;
		try {
			intPort = Integer.parseInt(stringPort);
		} catch (NumberFormatException numberFormatException) {
			numberFormatException.printStackTrace();
		}

		/**
		 *  extract the publication path
		 */
		String stringPublicationPath = arrayOfString[2];

		/**
		 * publish the service using the publication URL, port number,
		 * and publication path
		 */
		System.out.println("Service: " + stringServiceURL +
				":" + intPort +
				"/" + stringPublicationPath
			);
		Endpoint.publish(
				stringServiceURL + ":" +
				intPort + "/" +
				stringPublicationPath,
				new QueryServiceImplementation()
			);
	}
}

That is it for the service. If you have any questions on that part feel free to post a comment. I will gladly respond as soon as I can. But now onto the matter of the client.

Receiving an Array of Complex Objects on the Android Client

Getting an array of complex objects with kSOAP2 is not difficult… once you know what to do. First, make sure you are working with the latest libraries.(3) Caveat Lector! What follows is a load of crap.

Also, If we are going to get a complex parameter back from our web service we are going to need to implement the interface KVMSerializable. That is, you will need to make sure that the client code has an equivalent Query object that can have data deserialized into it. There is already a Query object on the web service side. You are going to create another one with the properties that you want to receive on the client side.

Okay, that is the end of the load of crap. I implemented the KVMSerializable and put in the line to cause mapping to work. Contrary to the misleading posts of some bloggers, you will not get a Vector of the KVMSerializable object. And when I commented out all the lines that were supposed to be sine qua non in making the serialization work… it worked anyway. What mattered most was using the right libraries and knowing what types I could expect to get back. The following fragment of code is all that was needed to get the array of objects.

    /**
     * Simple AsyncTask subclass for doing network operations. Note: This
     * is necessary because network operations cannot be done on the main
     * UI thread. This particular task leverages kSOAP2 to invoke a
     * web service method that returns an array of objects.
     */
    private class AsyncTaskRefreshQueryCollection extends AsyncTask<Void, Void, Void> {
    	protected Void doInBackground(Void...voids) {
    		List<Query> listOfQueryTemp = new ArrayList<Query>();
    		/**
    		 * Write a letter (SoapObject).
    		 *
    		 */
			SoapObject soapObjectRequest = new SoapObject(
					BIFQueryActivity.stringNamespace,
					BIFQueryActivity.stringMethodNameGetQueries
				);

			/**
    		 * Prepare an envelope for the letter
    		 */
			SoapSerializationEnvelope soapSerializationEnvelope = new SoapSerializationEnvelope(
					SoapEnvelope.VER11
				);
			soapSerializationEnvelope.dotNet = false;

			/**
			 * Put the letter in the envelope.
			 */
			soapSerializationEnvelope.setOutputSoapObject(soapObjectRequest);

			/**
			 * Add a deciphering key to the letter envelope contents. This does nothing.
			 * You can comment it out if you like.
			 */
			soapSerializationEnvelope.addMapping(
					BIFQueryActivity.stringNamespace,
					"Query",
					com.bif.query.Query.class
				);

			try {
				/**
				 * Mail the letter.
				 */
				HttpTransportSE httpTransportSE = new HttpTransportSE(BIFQueryActivity.stringURL);
				httpTransportSE.debug = true;
				httpTransportSE.call(
						BIFQueryActivity.stringNamespace +
						BIFQueryActivity.stringMethodNameGetQueries,
						soapSerializationEnvelope
					);

				Vector<SoapObject> vectorOfSoapObject = (Vector<SoapObject>)soapSerializationEnvelope.getResponse();

				/**
				 * move the properties into Query objects
				 */
				for (SoapObject soapObject : vectorOfSoapObject) {
					Query query = BIFQueryActivity.this.convertToQuery(soapObject);
					/**
					 * move the query objects into a collection
					 */
					listOfQueryTemp.add(query);
				}
				BIFQueryActivity.this.getQueryCollection().clear();
				BIFQueryActivity.this.getQueryCollection().addAll(listOfQueryTemp);

				/**
				 * Update the user interface
				 */
				Bundle bundle = new Bundle();
				bundle.putString("signal", "bogus object");
				Message message = new Message();
				message.setData(bundle);
				BIFQueryActivity.this.handlerOfRefresh.sendMessage(message);

				return null;
			} catch (Exception exception) {
				exception.printStackTrace();
			}

			return null;
    	}
    }

I manually moved the properties into Query objects using the following code.

    /**
     * Convert <code>SoapObject</code> to a <code>Query</code> object.
     * @param soapObject encapsulates the properties of the <code>Query</code> object.
     * @return <code>Query</code> object
     */
    private Query convertToQuery(SoapObject soapObject) {
    	Query query = new Query();

    	query.setDatabaseURL(soapObject.getPropertyAsString("databaseURL"));
    	query.setJDBCDriverClassName(soapObject.getPropertyAsString("JDBCDriverClassName"));
    	query.setJDBCUsername(soapObject.getPropertyAsString("JDBCUsername"));
    	query.setJDBCPassword(soapObject.getPropertyAsString("JDBCPassword"));
    	query.setQueryID(soapObject.getPropertyAsString("queryID"));
    	query.setQueryName(soapObject.getPropertyAsString("queryName"));
    	query.setQueryText(soapObject.getPropertyAsString("queryText"));

    	return query;
    }

The User Interface

When it was all said and done I was able to press a button and request the array of objects from my web service. When the objects arrived I was able to move them into a GridView object in the user interface. The following screenshot shows you what I saw after I pressed the button labeled Refresh.

Figure 3: Screenshot of Query Objects in Grid View
Figure 3: Screenshot of Query Objects in Grid View

I know, it leaves much to be desired. But it does show you the query objects in the GridView.

Sending Complex Objects Using kSOAP2

Transmitting Your Objects is Like Beaming Down to the Planet Surface

Transmitting Your Objects is Like Beaming Down to the Planet Surface

While you do not need to implement KVMSerializable in order to receive a single object or an array of objects, you do need to implement the interface in order to send a complex object to a web service. Let me say that again. Receiving either a single object or an array of objects has nothing to do with KVMSerializable. However, if you are sending… if you are transmitting… if you are beaming your objects down to the planet’s surface you will need to make sure that the object implements KVMSerializable. In our case if we want to send an updated Query object to the web service we have three things to do: (1) ensure that Android-based Query object implements KVMSerializable, (2) ensure that the type of the object is known, (3) load the object into the SOAP request, and (4) beam it (Okay, I am going to stop using trekkie language.).

(1) Implementing KVMSerializable – I have provided the code for the Android-based Query class. Notice that it is essentially the same as the one defined earlier. The prior class was part of the web service. You cannot count on being able to modify the objects that are part of the web service. But you can ensure that your version of those same complex objects implements the interface so that they can be transmitted without difficulty.

package com.bif.query;

import java.util.Hashtable;
import org.ksoap2.serialization.KvmSerializable;
import org.ksoap2.serialization.PropertyInfo;

/**
 * Android-based version of <code>Query</code>.
 * @author <a href="mailto:roderick.barnes@biftechnologies.com">Roderick L. Barnes, Sr.</code>
 */
public class Query implements KvmSerializable  {
	private static final int INT_PROPERTY_COUNT						= 7;
	private static final int INT_PROPERTY_QUERY_ID					= 0;
	private static final int INT_PROPERTY_QUERY_NAME				= 1;
	private static final int INT_PROPERTY_QUERY_TEXT				= 2;
	private static final int INT_PROPERTY_JDBC_DRIVER_CLASS_NAME	= 3;
	private static final int INT_PROPERTY_DATABASE_URL				= 4;
	private static final int INT_PROPERTY_JDBC_USERNAME				= 5;
	private static final int INT_PROPERTY_JDBC_PASSWORD				= 6;

	private String stringQueryID;
	private String stringQueryName;
	private String stringQueryText;
	private String stringJDBCDriverClassName;
	private String stringDatabaseURL;
	private String stringJDBCUsername;
	private String stringJDBCPassword;

	public Query() {
	}

	public Query(String stringQueryIDNew, String stringQueryNameNew, String stringQueryTextNew) {
		this.setQueryID(stringQueryIDNew);
		this.setQueryName(stringQueryNameNew);
		this.setQueryText(stringQueryTextNew);
	}

	public void setQueryID(String stringQueryIDNew) {
		this.stringQueryID = stringQueryIDNew;
	}

	public String getQueryID() {
		return this.stringQueryID;
	}

	public void setQueryName(String stringQueryNameNew) {
		this.stringQueryName = stringQueryNameNew;
	}

	public String getQueryName() {
		return this.stringQueryName;
	}

	public void setQueryText(String stringQueryTextNew) {
		this.stringQueryText = stringQueryTextNew;
	}

	public String getQueryText() {
		return this.stringQueryText;
	}

	public void setJDBCDriverClassName(String stringJDBCDriverClassNameNew) {
		this.stringJDBCDriverClassName = stringJDBCDriverClassNameNew;
	}

	public String getJDBCDriverClassName() {
		return this.stringJDBCDriverClassName;
	}

	public void setDatabaseURL(String stringDatabaseURLNew) {
		this.stringDatabaseURL = stringDatabaseURLNew;
	}

	public String getDatabaseURL() {
		return this.stringDatabaseURL;
	}

	public void setJDBCUsername(String stringJDBCUsernameNew) {
		this.stringJDBCUsername = stringJDBCUsernameNew;
	}

	public String getJDBCUsername() {
		return this.stringJDBCUsername;
	}

	public void setJDBCPassword(String stringJDBCPasswordNew) {
		this.stringJDBCPassword = stringJDBCPasswordNew;
	}

	public String getJDBCPassword() {
		return this.stringJDBCPassword;
	}

	public Object getProperty(int intPropertyIndex) {

		switch (intPropertyIndex) {
		case Query.INT_PROPERTY_QUERY_ID:
			return this.getQueryID();
		case Query.INT_PROPERTY_QUERY_NAME:
			return this.getQueryName();
		case Query.INT_PROPERTY_QUERY_TEXT:
			return this.getQueryText();
		case Query.INT_PROPERTY_DATABASE_URL:
			return this.getDatabaseURL();
		case Query.INT_PROPERTY_JDBC_DRIVER_CLASS_NAME:
			return this.getJDBCDriverClassName();
		case Query.INT_PROPERTY_JDBC_USERNAME:
			return this.getJDBCUsername();
		case Query.INT_PROPERTY_JDBC_PASSWORD:
			return this.getJDBCPassword();
		}

		return null;
	}

	public int getPropertyCount() {
		return Query.INT_PROPERTY_COUNT;
	}

	public void getPropertyInfo(int intPropertyIndex, Hashtable arg1, PropertyInfo info) {
		switch (intPropertyIndex) {
		case Query.INT_PROPERTY_QUERY_ID:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "queryID";
			break;
		case Query.INT_PROPERTY_QUERY_NAME:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "queryName";
			break;
		case Query.INT_PROPERTY_QUERY_TEXT:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "queryText";
			break;
		case Query.INT_PROPERTY_DATABASE_URL:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "databaseURL";
			break;
		case Query.INT_PROPERTY_JDBC_DRIVER_CLASS_NAME:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "JDBCDriverClassName";
			break;
		case Query.INT_PROPERTY_JDBC_USERNAME:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "JDBCUsername";
			break;
		case Query.INT_PROPERTY_JDBC_PASSWORD:
			info.type = PropertyInfo.STRING_CLASS;
			info.name = "JDBCPassword";
			break;
		default:
			break;
		}
	}

	public void setProperty(int intPropertyIndex, Object objectPropertyNewValue) {
		switch (intPropertyIndex) {
		case Query.INT_PROPERTY_QUERY_ID:
			this.setQueryID((String)objectPropertyNewValue.toString());
			break;
		case Query.INT_PROPERTY_QUERY_NAME:
			this.setQueryName((String)objectPropertyNewValue.toString());
			break;
		case Query.INT_PROPERTY_QUERY_TEXT:
			this.setQueryText((String)objectPropertyNewValue.toString());
			break;
		case Query.INT_PROPERTY_DATABASE_URL:
			this.setDatabaseURL((String)objectPropertyNewValue.toString());
			break;
		case Query.INT_PROPERTY_JDBC_DRIVER_CLASS_NAME:
			this.setJDBCDriverClassName((String)objectPropertyNewValue.toString());
			break;
		case Query.INT_PROPERTY_JDBC_USERNAME:
			this.setJDBCUsername((String)objectPropertyNewValue.toString());
			break;
		case Query.INT_PROPERTY_JDBC_PASSWORD:
			this.setJDBCPassword((String)objectPropertyNewValue.toString());
			break;
		default:
			break;
		}
	}

	public String toString() {
		return this.getQueryID() + "::" + this.getQueryName();
	}
}

(2) Ensure that the Object Type is Known – In the code that sends the complex Query object you will have to prepare it for beaming… I mean transport. This is easily done using an instance of PropertyInfo.

    		/**
    		 * Write a letter (SoapObject).
    		 */
			SoapObject soapObjectRequest = new SoapObject(BIFQueryActivity.stringNamespace, BIFQueryActivity.stringMethodNameUpdateQuery);
			query.setQueryName("altered: " + query.getQueryName());
			PropertyInfo propertyInfo = new PropertyInfo();
			propertyInfo.setName("query");
			propertyInfo.setType(Query.class);
			propertyInfo.setValue(query);

(3) Load the object into the SOAP request.

			soapObjectRequest.addProperty(propertyInfo);

(4) Send it! You thought I was going to say beam.

			/**
    		 * Prepare an envelope for the letter
    		 */
			SoapSerializationEnvelope soapSerializationEnvelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
			soapSerializationEnvelope.dotNet = false;

			/**
			 * Put the letter in the envelope.
			 */
			soapSerializationEnvelope.setOutputSoapObject(soapObjectRequest);

			/**
			 * Add a deciphering key to the letter envelope contents
			 */
			soapSerializationEnvelope.addMapping(BIFQueryActivity.stringNamespace, "Query", com.bif.query.Query.class);

			try {
				/**
				 * Mail the letter.
				 */
				HttpTransportSE httpTransportSE = new HttpTransportSE(BIFQueryActivity.stringURL);
				httpTransportSE.debug = true;
				httpTransportSE.call(BIFQueryActivity.stringNamespace + BIFQueryActivity.stringMethodNameGetQuery, soapSerializationEnvelope);

				SoapObject soapObjectResponse = (SoapObject)soapSerializationEnvelope.getResponse();

				/**
				 * move the properties into Query objects
				 */

				return null;
			} catch (Exception exception) {
				exception.printStackTrace();
			}

Working with RowSet Objects on the Android Client

At this point let us turn our attention to the matter of getting query results from the web service. In particular let us look at calling the method executeQuery(Query query) and how we can show the results to the users. In showing the results we would like to provide two formats:

  • Scrolling Table – The query results will be placed in a scrolling GridView that will be dynamically configured to have the correct number of columns and rows.
  • Bar Chart – The query results will be placed in a simple bar chart that will display (1) a title based on the query name, (2) bars labeled with the city name, and (3) value labels that will communicate the quantitative value of each chart.
  • Pie Chart – The query results will be placed in a simple pie chart that will display (2) a title based on the query name, (2) slices labeled with the name of the city, and (3) value labels for each slice.

The query results are returned from the client as an XML document. Our first challenge is to convert them into a ResultSet. To do this we could try to work with the various implementations of javax.sql.rowset.WebRowSet. However, those implementations rely on classes that are not part of the core set of packages that were ported to Android. Shucks! My little company (BIF Technologies, Corp.) is working on an implementation of WebRowSet that will work on Android. Until that work is finished I put together a poor man's implementation: BIFRowSet (see Android WebRowSet Implementation - BIFRowSet). The code required to get the ResultSet XML is shown below.

    /**
     * Simple AsyncTask subclass for doing network operations. Note: This
     * is necessary because network operations cannot be done on the main
     * UI thread. This particular task leverages kSOAP2 to invoke the
     * web service method.
     */
    private class AsyncTaskExecuteQuery extends AsyncTask<Query, Void, Void> {
    	protected Void doInBackground(Query...arrayOfQuery) {
    		/**
    		 * Extract the query identifier passed by the calling program
    		 */
    		Query query = arrayOfQuery[0];

    		/**
    		 * Write a letter (SoapObject).
    		 */
			SoapObject soapObjectRequest = new SoapObject(BIFQueryActivity.stringNamespace, BIFQueryActivity.stringMethodNameExecuteQuery);
			PropertyInfo propertyInfo = new PropertyInfo();
			propertyInfo.setName("query");
			propertyInfo.setType(Query.class);
			propertyInfo.setValue(query);
			soapObjectRequest.addProperty(propertyInfo);

			/**
    		 * Prepare an envelope for the letter
    		 */
			SoapSerializationEnvelope soapSerializationEnvelope = new SoapSerializationEnvelope(SoapEnvelope.VER11);
			soapSerializationEnvelope.dotNet = false;

			/**
			 * Put the letter in the envelope.
			 */
			soapSerializationEnvelope.setOutputSoapObject(soapObjectRequest);

			/**
			 * Add a deciphering key to the letter envelope contents
			 */
			soapSerializationEnvelope.addMapping(BIFQueryActivity.stringNamespace, "Query", com.bif.query.Query.class);

			try {
				/**
				 * Mail the letter.
				 */
				HttpTransportSE httpTransportSE = new HttpTransportSE(BIFQueryActivity.stringURL);
				httpTransportSE.debug = true;
				httpTransportSE.call(BIFQueryActivity.stringNamespace + BIFQueryActivity.stringMethodNameExecuteQuery, soapSerializationEnvelope);

				/**
				 * pass the results to a handler
				 */
				Object objectResponse = (Object)soapSerializationEnvelope.getResponse();
				Bundle bundle = new Bundle();
				bundle.putString("webrowset", objectResponse.toString());
				Message message = new Message();
				message.setData(bundle);
				BIFQueryActivity.this.handlerOfQueryExecute.sendMessage(message);

				return null;
			} catch (Exception exception) {
				exception.printStackTrace();
			}

			return null;
    	}
    }

The code to transform the XML into a RowSet is shown below. The code below shows the population of a chart through a Handler object.

    /**
     * UI Handler used to alter a view in the user interface. we create a new
     * handler and override the <code>handleMessage(Message message)</code> method.
     */
	public Handler handlerOfQueryExecute = new Handler() {
		public void  handleMessage(Message msg) {
			Bundle bundle = msg.getData();
			Object object = bundle.get("webrowset");
			String stringXMLWebRowSet = object.toString();

			try {
				BIFRowSet bifRowSet = new BIFRowSet(stringXMLWebRowSet);

				/**
				 * setup the chart
				 */
				TChart chart = new TChart(BIFQueryActivity.this);
				Series bar = new Bar(chart.getChart());
				TableLayout tableLayout = (TableLayout)findViewById(R.id.tableLayout1);
				tableLayout.addView(chart);
				chart.getAxes().getBottom().setIncrement(1);

				while (bifRowSet.next()) {
					bar.add(Integer.parseInt(bifRowSet.getString(2)), bifRowSet.getString("name"), Color.BLUE);
				}
			} catch (SQLException sqlException) {
				sqlException.printStackTrace();
			}
		}
	};

The screenshot reflecting the results of running this query and pushing the results into a chart is shown below.

Application Screenshot

Application Screenshot with Chart

While the user interface is not impressive the juices should start flowing on your end. That is, with the ability to get complex objects, complex object arrays, and query results from a remote database there are quite a few interesting user interfaces that could be built.

Conclusion

We have used kSOAP2 to invoke a method on a web service from an Android client. Cool stuff, eh? Well, I think it is. And in this installment we received from that same method an array of complex objects. We also looked at what it means to execute a query, get the results, and display them in the user interface. In the next post we will look at user interface issues:

  • Editing – This will involve creating a user interface that facilitates the editing of attributes for a query object.
  • Saving – In this element of the post we will commit the attributes of the post to a local SQLLite database.
  • Printing – We will learn how to send the results of queries to a printer.
  • Scheduling – We will learn how to send the results of a query to a list of contacts on a predetermined schedule.

I am really looking forward to working on this with you.

In His grip by His grace,
Roderick L. Barnes, Sr.

1 Corinthians 10:31

End Notes

(1) This terrible diagram was created using the UML2 Module in MyEclipse. While I am a MyEclipse fan, the module is rife with bugs and does not adequately support the modeling needs of even casual developers. I hope it continues to improve.
(2) The applications for the ability to execute queries remotely and get the results are quite large. Areas where remote query results could be made useful on the mobile platform include Google Analytics, QuickBooks-based financials, real-time patient information, etc. What remains is the framework for easily assembling dashboards for the mobile platform.
(3) Make sure that you are using the latest kSOAP2 libraries. I spent too much time debugging problems that had been corrected in a later release. The problems I was having went away when I simple acquired the latest download. You can go here to get the latest: kSOAP2 Download.
(4) sine qua non - refers to an indispensable and essential action, condition, or ingredient.

 

About The Notorious R.O.D.

Roderick resides in San Antonio, Texas with his wife (Darlene) and six boys. When not programming or parenting he can be found pondering things not too profound. (Deuteronomy 6:4-5)