WSDL Web Service querying a DB (Eclipse w/ client in PHP)

Prologue: I started out as your average guy with solid knowledge in WSDL and web services, to test out and build a minimal database connected service in Eclipse, and consume it in a web application somewhere else (anywhere else to be more specific, that is to make the data source available to any number of web servers having our application installed). And the reason I wrote this article is that I’ve had about three hours of “feeling dumb” penitence doing just that.

Prerequisites: Eclipse 3.0 (I use the BIRT reporting bundle – get it here); Apache Tomcat 6.0 (get it here); mySQL 5 or latest (get it here); mySQL connector for Java 5.1 (get it here) – and for testing: NuSOAP (get it here); any web server supporting PHP 5 (I use wamp – get it here).

The Service: This is fairly easy – here are the steps (assuming everything is installed and ready – namely Tomcat, MySQL and the PHP enabled web server):

• Start Eclipse, choose the Java EE Perspective.
• Menu>New>Dynamic Web Project
• Right Click on the project title in the Project Explorer and New>Other>Java>Class. This is the service source, containing all the methods that will be exposed by the service. Here is some sample code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class wsSource {
	public String testDB(String name)
	{
		String ret="No DB response";
		Connection conn = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "yourDataBase";
		String driver = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "";
		    try {
		      Class.forName(driver).newInstance();
		      conn = DriverManager.getConnection(url+dbName,userName,password);
		      ret="No result";
		      Statement statement = conn.createStatement();
		      ResultSet resultSet = statement.executeQuery ("select author from templates where template_name='"+name+"'");
		      if(resultSet.next()) ret=resultSet.getString(1);
		      conn.close();
		    } catch (Exception e){ret=e.toString();}
			return ret;
		}
}

• After your class is written, right click on it, Web Services>Create Web Service, fill the gauge on the Service picture, leave the Client empty and Next until it is generated. Use the Web Service Explorer to test your newly created WSDL file (which can be found in yourProject/WebContent/wsdl/). Double click on it to open it in the nice WSDL Editor, for which you can get a tutorial here.
• Export your project to a WAR file in your Tomcat server’s webapps folder.
• Attention!! The mysql driver (or any other DB driver) jar which contains the class invoked here (i.e. ‘com.mysql.jdbc.Driver’) must be present in the Tomcat lib folder, or you will get a “java.lang.ClassNotFoundException: com.mysql.jdbc.Driver” exception. (this accounted for an hour of hair pulling on my side ) )

OK, that much for the web service part.

The Client: You can, of course, auto-generate a client in Eclipse, by simply filling up the Client gauge in the wizard you just used (or creating a separate Dynamic Web Project that consumes the service you have just created). But the interesting point in web services is that they are “cross-” a lot of things (e.g. platform, technology, language, political opinion, religion, etc. but keep in mind the first two ) ). So, I have chosen to use PHP. And here are the steps:

• Create a folder in your “www” root and copy the NuSOAP files that you have downloaded there (unarchived, doh…)
• Create a new PHP file to test your web service – like this:

getError();
if ($err) {
    echo '<h2>Constructor error</h2><pre>' . $err . '</pre>';
}
//call the method you want from the web service; if it takes parameters, input them as an associative array
$result = $client->call('testDB',array('name' => 'budget_2008'));

if ($client->fault) {
    echo '<h2>Fault</h2><pre>';
    print_r($result);
    echo '</pre>';
} else {
    $err = $client->getError();
    if ($err) {
        echo '<h2>Error</h2><pre>' . $err . '</pre>';
    } else {
        echo '<h2>Result</h2><pre>';
        print_r($result);
    echo '</pre>';
    }
}

And without further ado, start the web server and test your service. Voila!

Security. It seems that NuSOAP does not support WS-Security implementation (but I may be wrong, as time goes by – my info source is a bit old. Therefore I’ll just concentrate on SSL for the moment.
There are two places you would want to secure – the application dealing with the public and the web service server. Depending on who you trust and on the architecture, you might choose to secure both or just one. For instance, if the web service can be consumed by any web site freely, then you must allow access to any web site and therefore you must secure that access (and it’s their business how they secure public access to their application). If the web service and the application consuming it are both yours, and it is just a matter of distributing various servers geographically, then you can restrict access to the web service to just a number of hosts, which makes it less interesting to actually add SSL to the web service – instead, you should just concentrate on securing the side which is open to the web public.
To do that, a good practical way is to add SSL to your web server.
For the Tomcat server (web service server) I found a great walkthrough here. In addition to that, you will need to specify the algorithm as RSA, otherwise Firefox 3 will not let you in, as per this description. To sum up, what you need to write in the command line to create a key looks like:

keytool -genkey -alias myalias -keypass mypass -keystore mykey.bin -storepass mypass -keyalg rsa

For Wamp, things got a bit more complicated. I spent about an hour tinkering with httpd.conf and httpd-ssl.conf, until I gave up and found the correct directions here.
Attention! Both for the Java keytool and the OpenSSL, when you create the key, at the “Last name / first name” or respectively “Common Name” prompt input the host name that will be used right after “https://” – e.g. “www.mydomain.com”. Anything else will result in a security error saying that the certificate domain does not match the host domain. If you are using localhost, do not input “localhost” at the “Common Name” prompt, instead use “127.0.0.1″, otherwise you will get a bug error in Firefox 3, as it is gracefully explained here. You will still need to add a security exception in any browser, because the certificate is self signed, instead of being signed by an authority.
Once the domain is made public, you can get a signature from a certification authority, in order to avoid the nuisance of having to confirm / add exceptions on each new machine or web browser.
Note: If you want to use IIS for the whole architecture, you can find an article here, describing just how to get the security issue done.