Working with a Database Adapter - Part 1

This blogs helps you to work with a Database Adapterprovided in Oracle SOA Suite.

Please go through my previous blog on configuring a Database Adapter.

Why Database Adapter?

Service Oriented Architecture is all about services, its about your entire business application modeled as Services.

In a Business Application, it is obvious that you need database to interact with. But a database by itself cannot be exposed as a service. For ex, in order to interact with it, you need to write a Java program, and using JDBC connectivity, you connect and interact with the database. If this is the case, how can you use it in a 100% SOA based application?

The only way to use it is to expose it as a service.  And how is a database exposed as a Service?

The Answer to it will be to introduce a layer over it, so that it uses the database it covers, and yet exposes it as a service, i.e. something like a wrapper.

And Oracle SOA Suite 11g provides a similar solution to it called a Database Adapter. With this, you can use your existing database as a service in your SOA Application.

It also provides various other functionalities like polling a database, checking for any changes in a specific table in a database, etc.

The Example below gives you an idea on how to use Database Adapter with the SOA Suite.

It is assumed that you have a database up and running.

In this example, we take the user input which will be the EmployeeId, and return his FullName, Salary.

This example uses the concepts of mediator, transformation of data, webservice.

Working with a DBAdapter

Create a new SOA Project, name it DBAdapterExample 

This creates a new project folder, with an empty composite.xml
You need  to first define the input and output xml formats used in the example.

In this example, Input is EmployeeId(Int) and Output is FullName, Salary

Create a new XML schema that has 2 nodes with input and output format types

rt click on xsd folder in the project --> new XML Schema, name it DBAdapterFormat.xsd

Define the schema as below

Creating a Database Adapter

• Drag and Drop a Database Adapter from the Component Pallette, into the "External References" swimline in the composite.xml, name it EmpDBAdapterService
• Create a database connection, and give the JNDI name for the outbound connection pool for the DBAdapter that you've configured(verify my prev<link> post for this)
• Select the operation that you want, Select in this case
• Import the database table you want to interact with, Employees in this case
• You may remove the relationships that you see in Step 6, or leave it as it is
• Select all the fields that you will use in this application(no problem if you even select all and later on do not use it)
• In Step 8, you have to create a bind variable(parameter), and use it in the where clause of the SQL query. In this example, we send the employeeId, and so, create a bind variable by some name(bndEmpId), and use it in the where clause as shown
• Finish the wizard to last step, and you will see a new Database Adapter in the composite.xml

Now that DBAdapter is created, in other words, the underlying database is exposed as a service, you can talk to the service to get its data.

But since the service is not exposed directly to clients (Services only in Exposed Services swim lane can be accessed by external clients)

So, you now have to create an exposed web service whose input and output parameters are same as the Database Adapter. Please remember that the Exposed service is just a client to access the Database Adapter, and has no relevance to the database or its created Adapter.

contd. in Part 2