Exposing a Database as a Web Service

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

When you look back the computer industry, you can clearly identify different different technologies in different time periods. In any given time period, databases have a very high priority in the industry, from small scale to large scale business. You know that Web Services are becoming today’s technology and everyone is in the process of moving their applications into the SOA or Web Services world. When doing so, you have a number of advantages, although you have to do a considerable amount of work to do get everything working. Therefore, when considering the advantages (such as accessibility, security, extensibility, and so forth), companies are trying to expose their applications as Web Services, or they are trying to give a Web Service interface to their applications. So, exposing and giving a Web service interface to a database is also becoming a hot topic, and DataServices is very good example of that.

There are a number of approaches that industries have employed when they want to expose their databases as Web Services. The DataService approach can be considered as one of the good approaches, and you can find a number of different DataServices solutions as well. You can consider the WSO2 DataService solution as a good example candidate for a DataService solution that is built on Axis2.

An Approach for Exposing a Database as a Web Service

However, in this article you are not going to examine any of the DataServices approaches; rather, you will learn a very simple approach of exposing a databases as a Web Service using Axis2. You can consider that as exposing a database using Axis2 POJO. To get a better understanding about this approach, having good knowledge about Axis2 will be an added advantage; the Reference section has links to the recommended articles. If you follow then, you are in good shape.

Creating a Database

To expose a database as a Web Service, you first need to have the database around, so create a very simple databases with one table to store personal information. The table will have four fields to store ID, name, address, and age. This sample application is based on MySQL databases, but you can do the exact same thing with any given database.

Run the following database script to create the database table. (First, create a DB schema called “dbsample” and then create the table inside that.)

CREATE TABLE PERSON (ID INTEGER NOT NULL,
   NAME VARCHAR (100) NOT NULL,
   ADDRESS VARCHAR (500),
   AGE INTEGER,
   PRIMARY KEY (ID));

Inserting Sample Data

By running the following script, you can populate the database with a set of data.

INSERT INTO PERSON (ID, NAME, ADDRESS, AGE)
   Values (100, "Deepal Jayasinghe", "No 59, Flower Road,
           Colombo, Sir Lanka", 29)
INSERT INTO PERSON (ID, NAME, ADDRESS, AGE)
   Values (101, "Franck", "San Jose, CA", 30)
INSERT INTO PERSON (ID, NAME, ADDRESS, AGE)
   Values (102, "Samisa Abeysinghe", "Colombo, Sri Lanka", 34)

You can add some more data if you want. You can either insert data by running SQL or you can just insert the data by using the MySQL Query Browser.

In the rest of this article, you will learn different ways of exposing the database as a Web service:

List all the people in the DB
List the names of all the people in the DB
List the names and ages of given people in the DB
Insert a person into the DB

Now, it is time to write your POJO class to perform the functionality you want. Before you do this, you need to address a few questions:

Where will you create the database connection?
Where will you store the database connection?
How will you close the database connection?

Service Life Cycle Management and Database Connection Handling

To answer all those questions, Axis2 has something called ServiceLifeCycle management support. Therefore, the correct and best approach would be to get the life cycle management support from Axis2. First, you need to write the life cycle management class and create and store the database connection there. It should be noted here that when Axis2 starts up (at the time of service deployment), the ServiceLifeCycle class will be invoked; also, when the system goes down, the s ServiceLifeCycle class will be invoked again. As you can see, you are going to create the DB connection at the service deployment time and store the database connection inside the ConfigurationContext object.

You can download the source code for the s ServiceLifeCycle implementation class from the Download section (DBSampleServiceLifeCycle.java).

Creating POJO Classes

Now, you have written code to open the DB connection and to store that in ConfigurationContext, so now it’s time to write the POJO classes. In this case, you will write a JavaBean object to represent the Person with four fields (id, name, address, and age). When listing all the people in the DB, you just create an array of Person objects and return that (Person.java).

When listing names of all the persons in the DB, you just return the String array. When getting the name and age for a given person, you create new JavaBean to represent those two fields and return that (NameAge.java). In the case of inserting a person object into the DB, you write a method in the POJO class (PersonDBService.java) to get four method parameters.

Your service implementation class, which does all four operations described above, is shown below.

package dbsample;

import org.apache.axis2.context.MessageContext;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class PersonDBService {

   public Person[] listAllPeople() {
      // implementation logic
   }

   public String[] listPeopleNames() {
      // implementation logic
   }

   public NameAge getNameAge(int id) {
      // implementation logc
   }

   public void insertPerson(int id,
      String name,
      String address,
      int age) {
      // implementation logic
   }

}

You can download the source code for all the classes from the Download section.

The service description file, or the services.xml for your service, will be a very simple one. You can download that from the Download section. The services.xml file will look like the following:

<service name="DBSampleService"
         class="dbsample.DBSampleServiceLifeCycle">
   <description>Exposing a DB as a Web Service</description>
      <messageReceivers>
         <messageReceiver
            mep="http://www.w3.org/2004/08/wsdl/in-only"
            class="org.apache.axis2.rpc.receivers.
                   RPCInOnlyMessageReceiver"/>
         <messageReceiver
            mep="http://www.w3.org/2004/08/wsdl/in-out"
            class="org.apache.axis2.rpc.receivers.
                   RPCMessageReceiver"/>
   </messageReceivers>
   <parameter name="ServiceClass">
      dbsample.PersonDBService
   </parameter>
</service>

Deploying the Service

Before you deploy the service, you need to create a service archive file using your compiled classes and services.xml. You can use any available tools, or you can just create a zip file from the compiled code and services.xml and rename that as dbsample.aar. I have created a service archive file from the compiled code so that you can download that and just try it out.

Because you need to have the DB connection jar, first you have to copy the mysql-connector jar file in the class path or to <TOMCATHOME>/webappes/axis2/WEB-INF/lib. Next, you can copy your service archive file into <TOMCATHOME>/webappes/axis2/WEB-INF/services directory. Then start Tomcat (or your application server).

Now, type http://localhost:8080/axis2/services/DBSampleService?wsdl in your browser (the port may vary depending on the application server configurations); then, you will be able to see the WSDL file for your sample DB service. This is simply an indicator that your service is up and running. If you do not get the WSDL file, something has gone wrong with your database or database driver.

Invoking the Service

In this sample, you are not going to write a Java client to invoke the service; if you want, you can try that out. However, you will focus more on invoking the service just using the browser or REST manner.

List all the People in the DB

To see all the people in the database, you can invoke the “listAllPeople” method in your service. Just type the following in the browser and see what you are getting. (This is how you invoke the listAllPeople method in the REST manner.)

http://localhost:8080/axis2/services/DBSampleService/listAllPeople

You will get something like the following, which is simply all the people in the DB.

<ns:listAllPeopleResponse>
   <ns:return type="dbsample.Person">
      <ax21:address>
         No 59, Flower Road, Colombo, Sir Lanka
      </ax21:address>
      <ax21:age>29</ax21:age>
      <ax21:id>100</ax21:id>
      <ax21:name>Deepal</ax21:name>
   </ns:return>
   <ns:return type="dbsample.Person">
      <ax21:address>San Jose, CA</ax21:address>
      <ax21:age>30</ax21:age>
      <ax21:id>101</ax21:id>
      <ax21:name>Franck</ax21:name>
   </ns:return>
   <ns:return type="dbsample.Person">
      <ax21:address> Colombo, Sri Lanka</ax21:address>
      <ax21:age>34</ax21:age>
      <ax21:id>102</ax21:id>
      <ax21:name>Samisa Abeysinghe</ax21:name>
   </ns:return>
</ns:listAllPeopleResponse>

List the Names of All the People in the DB

Getting a list of names of the people is almost equal to the method invocation above. To get all the names, just type the following in the browser and see what you get:

http://localhost:8080/axis2/services/DBSampleService/listPeopleNames

Then, you will see something like the following, which is exactly all the names of the people in the DB.

<ns:listPeopleNamesResponse>
   <ns:return>Deepal</ns:return>
   <ns:return>Franck</ns:return>
   <ns:return>Samisa Abeysinghe</ns:return>
</ns:listPeopleNamesResponse>

Getting the Name and Age for a Given Person

Now, you will the name and age of a given person, so will be a matter of giving the person ID and the Web Service gives you the name and age of the person represented by the id. Invoking that service is just a matter or typing the following in the browser:

http://localhost:8080/axis2/services/DBSampleService/getNameAge?id=100

Then, you get the following output:

<ns:getNameAgeResponse>
   <ns:return type="dbsample.NameAge">
      <ax21:age>29</ax21:age>
      <ax21:name>Deepal</ax21:name>
   </ns:return>
</ns:getNameAgeResponse>

Inserting a New Person Object

Inserting a person object also can be done using a REST call. You need to pass the id, name, address, and the age to add a new person into the table. You can pass them as URL query parameters, as you can see below.

http://localhost:8080/axis2/services/
DBSampleService/insertPerson?id=130
&name=Peter&address=No 5, Colombo, Sri Lanka&age=56

This will call the service and insert a new raw to the database. If you want to make sure that the new data in the database just lists all the people again, you can see the new person also in the DB.

Conclusion

You now have a very good understanding about exposing a database as a Web Service using Axis2. The sample you did was a very simple one, but you can write any complex example using this approach. If you have a good understanding about Databases and Axis2, you can do very cool stuff. Trying out the sample and creating your own sample will help you to understand the concept very clearly.

Downloads

References

Axis2 deployment architecture: www.developer.com/open/article.php/3557741
Six ways of deployment: www-128.ibm.com/developerworks/library/ ws-axis2soap/index.html
Axis2 POJO: http://www.developer.com/java/other/article.php/ 10936_3726461_3

Exposing a Database as a Web Service

Introduction

An Approach for Exposing a Database as a Web Service

Creating a Database

Inserting Sample Data

Service Life Cycle Management and Database Connection Handling

Creating POJO Classes

Deploying the Service

Invoking the Service

List all the People in the DB

List the Names of All the People in the DB

Getting the Name and Age for a Given Person

Inserting a New Person Object

Conclusion

Downloads

References

Get the Free Newsletter!

Latest Posts

What Is the Role of a Project Manager in Software Development?

How to use Optional in Java

Overview of the JAD Methodology

Microsoft Project Tips and Tricks

How to Become a Project Manager in 2023

Related Stories

Advertisers

Menu

Our Brands