Database Setup Guide

This document explains how to setup a database and some data sources as required by the samples. Most samples require an Apache Derby installation whereas a few would require a MySQL setup.

Contents

Introduction

Apache Synapse has the ability to lookup and update relational databases through JDBC. Any database engine that provides JDBC drivers can be integrated with Synapse using the dblookup and dbreport mediators. Synapse ships with a collection of samples which demonstrates various aspects of dblookup, dbreport mediators and database integration. This article describes how to setup the databases, sample tables and reusable data sources required to try these examples out.

Most samples assume Apache Derby is used as the database engine. And therefore this article also focuses mainly on setting up Apache Derby. Byt in reality any database engine can be used to run these samples. The database schema and SQL queries described here will work with any database engine. However in such cases some minor changes should be made to the Synapse configuration files.

Some samples involve invoking database stored procedures from Synapse. For these samples MySQL database engine is assumed. Therefore this article provides some basic information on setting up MySQL for the example scenarios.

Setting Up Apache Derby

To start with, download the latest binary distribution of Apache Derby. Extract the downloaded archive to a suitable location in the local disk and switch to the 'bin' directory of the installation. Start the Derby network server by executing the 'startNetworkServer' startup script. An output similar to the following will be displayed as the database engine starts up.

Sun Jan 02 10:53:28 IST 2011 : Security manager installed using the Basic server security policy. Sun Jan 02 10:53:30 IST 2011 : Apache Derby Network Server - 10.7.1.1 - (1040133) started and ready to accept connections on port 1527

Now launch the Derby client tool by executing the 'ij' script. This will give a command prompt where you can execute various command and SQL queries. Execute the following connect statement to create a fresh database named 'synapsedb' and obtain a connection to it.

CONNECT 'jdbc:derby://localhost:1527/synapsedb;user=synapse;password=synapse;create=true';

Now execute the following SQL query to create a new table.

CREATE table company(name varchar(10), id varchar(10), price double);

Insert some sample data to the table by executing following statements.

INSERT into company values ('IBM','c1',0.0); INSERT into company values ('SUN','c2',0.0); INSERT into company values ('MSFT','c3',0.0);

Now we have finished setting up the Derby server for the samples. As the final step we should copy the Derby JDBC drivers to Synapse 'lib' directory. Locate the following jar files in Derby installation and copy them into Synapse.

  • derby.jar
  • derbyclient.jar
  • derbynet.jar

Setting Up MySQL

This section assumes that you already have a MySQL server instance up and running. For details on installing MySQL, please refer the relevant MySQL documentation.

Create a new database named 'synapsedb' in MySQL. Then execute the SQL queries given in the previous section to create a table named 'company' and insert some sample data into it. Then execute the following two commands in MySQL client to create two stored procedures.

CREATE PROCEDURE getCompany(compName VARCHAR(10)) SELECT name, id, price FROM company WHERE name = compName; CREATE PROCEDURE updateCompany(compPrice DOUBLE,compName VARCHAR(10)) UPDATE company SET price = compPrice WHERE name = compName;

Then you should download the MySQL JDBC driver and deploy it into the 'lib' directory of Synapse.

Using Other Database Engines

You can run the given samples using any RDBMS engine you prefer. In that case please make sure you do the following.

  • Deploy the JDBC drivers for your database engine into Synapse
  • Update the sample configuration files and change the driver class name and JDBC connection string correctly

Setting Up Data Sources

Synapse is capable of connecting to databases through predefined data sources. This enables database connection pooling and connection reuse. Different instances of the database mediators (dblookup/dbreport) can either use different data sources or share the same data source.

Data sources are configured in the synapse.properties file which can be found in the 'lib' directory of the Synapse installation. Currently Synapse supports following types of data sources.

  • BasicDataSource
  • PerUserPoolDataSource

Both these types of data sources are based on Apache DBCP.

Following section describes how to setup two data sources as required by some of the database integration samples of Synapse. First, it is required to setup two Derby databases. So launch 'ij' client tool for Derby and create two databases named 'lookupdb' and 'reportdb'. Specify the username and password to be 'synapse' for both databases. Create the 'company' table in each database and add some sample data as described under Setting Up Apache Derby section.

Now you can define two data sources for these databases by adding the following entries to the synapse.properties file.

synapse.datasources=lookupds,reportds synapse.datasources.icFactory=com.sun.jndi.rmi.registry.RegistryContextFactory synapse.datasources.providerUrl=rmi://localhost:2199 synapse.datasources.providerPort=2199 synapse.datasources.lookupds.type=BasicDataSource synapse.datasources.lookupds.driverClassName=org.apache.derby.jdbc.ClientDriver synapse.datasources.lookupds.url=jdbc:derby://localhost:1527/lookupdb;create=false synapse.datasources.lookupds.username=synapse synapse.datasources.lookupds.password=synapse synapse.datasources.lookupds.dsName=lookupdb synapse.datasources.lookupds.maxActive=100 synapse.datasources.lookupds.maxIdle=20 synapse.datasources.lookupds.maxWait=10000 synapse.datasources.reportds.type=PerUserPoolDataSource synapse.datasources.reportds.cpdsadapter.factory=org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS synapse.datasources.reportds.cpdsadapter.className=org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS synapse.datasources.reportds.cpdsadapter.name=cpds synapse.datasources.reportds.dsName=reportdb synapse.datasources.reportds.driverClassName=org.apache.derby.jdbc.ClientDriver synapse.datasources.reportds.url=jdbc:derby://localhost:1527/reportdb;create=false synapse.datasources.reportds.username=synapse synapse.datasources.reportds.password=synapse synapse.datasources.reportds.maxActive=100 synapse.datasources.reportds.maxIdle=20 synapse.datasources.reportds.maxWait=10000

Here we are defining two data sources named 'lookupds' and 'reportds'. The first data source is defined as a BasicDataSource and the other one is defined as a PerUserPoolDataSource. Note the various parameters we have specified for each data source thereby further customizing the behavior of each data source.