JDBC Transport Examples

This page describes the examples you can run for the Mule Enterprise JDBC transport. These examples are designed to work with Oracle databases only.

Setup

The Mule Enterprise JDBC Examples are located in your installation at $MULE_HOME/examples/jdbc.

1. Before running any examples, do the following:

• Run the statements in scripts/tables.ddl to create tables in your favorite database.
• Edit the conf/db.properties file with your database connection configuration.
• Copy your database driver to your $MULE_HOME/lib/user directory.

2. Run the examples using the "jdbc.bat" or "jdbc" shell script provided in this directory. After running the script, you will see the following:

$ ./jdbc.batExamples require that you set up your database first. See README for more details.After your database is set up, choose one of the following examples to run:1. Mules are born.  Read in 50 baby mules from a CSV File and create records in mule_source table.2. View Mules.  Generate an XML report of Mules.  (http://localhost:8080/first20)3. Clone Mules.  Clone mules using a Stored Procedure. (http://localhost:8080/clone)4. Retire Mules.  Send mules to a retirement ranch (a.k.a the mule_target table).5. Cleanup Mules.  Reset tables to run examples again (http://localhost:8080/cleanup).Select the one you wish to execute and press Enter...

3. Choose the number of the Example you want to run.

The examples are designed to be run in order.

Example 1 - Mules Are Born

This example loads records from a CSV file to the database. The CSV file ("data/mules.csv") contains information about 50 baby mules born at various local ranches. The goal is to upload these records into a database table called "mule_source".

Configuration

See conf/jdbc-csv.xml

Features Used

This example uses the CSV Transformer to convert the CSV file into JDBC-format Maps and inserts these records into the database using abatch INSERT.

Running the Example

To run the example, choose option "1" after starting the Mule server. When the server starts, Mule will immediately load all the records.

To verify that the example loaded correctly, use your favorite database tool to see that there were 50 records inserted into the mule_source table.

SQL> select count(1) from mule_source;COUNT(1)----------50

If you want to run the example again, first delete the records from mule_source.

Example 2 - See the Baby Mules

This example displays the mule records loaded into the database in Example 1.

Configuration

See conf/jdbc-xml-report.xml

Features Used

This example uses an outbound SELECT query to synchronously query the database. The results are transformed into XML using theXML transformer.

Running the Example

Start the Mule server and choose Example 2. Then, enter the URL http://localhost:8080/first20 into your web browser. You should see the first 20 mule records presented in XML format.

Example 3 - Mule Cloning

The following example assumes that you are using an Oracle database. However, you can adapt the stored procedure for other database platforms.

This example shows how to invoke a stored procedure in Mule. This particular stored procedure clones the records in the mule_source database (doubling the size of the table each time it is run). The goal is to create a large dataset that we can use in Example 4.

Configuration

See conf/jdbc-stored-procedure.xml

Features Used

This page uses an outbound SELECT query and stored procedure support to synchronously call a stored procedure.

Running the Example

Before running this example, run the statements in the scripts/oracle_procs.ddl to create the stored procedure and sequence.

Start the Mule server and choose Example 3. Then, enter the URL http://localhost:8080/clone into your web browser. You should see the current count of the number of mules in mule_source. Each time you refresh the page, the number of mules should double. Try refreshing several times until there are thousands or even millions of mules or more in the table. The mules multiply quickly!

Example 4 - Mass Mule Retirement

This example retires all our mules by transferring them from one database table (mule_source) to another (mule_target). We can think of this as a simple ETL use case.

Configuration

See conf/jdbc-etl.xml

Features Used

This page uses large dataset retrieval, an outbound SELECT query, and batch INSERT statements to transfer arbitrarily large numbers of records from one table another.

Running the Example

Before running this example, you can optionally change the location of the ID store in jdbc-etl.xml:

    <spring:bean id="idStore" class="org.mule.transport.jdbc.util.IdStore">        <spring:property name="fileName" value="/tmp/eg-batches.txt"/>    </spring:bean>

The default location is /tmp/eg-batches.txt on Linux/Unix and c:/tmp/eg-batches.txt on Windows.

Next, for better visibility into batch activity, add the following line to your log4j.properties file (in $MULE_HOME/conf):

log4j.logger.org.mule.providers.jdbc.components=DEBUG

Finally, start the server and choose Example 4. You should see the batches of records being processed in the logs:

DEBUG 2008-04-10 20:20:03,625 \[next_batch.2\] org.mule.transport.jdbc.components.BatchManager: Processing next batchDEBUG 2008-04-10 20:20:03,625 \[next_batch.2\] org.mule.transport.jdbc.components.BatchManager: Next range: {lowerId=1, upperId=3000}DEBUG 2008-04-10 20:20:04,531 \[next_batch.2\] org.mule.transport.jdbc.components.BatchManager: Processing next batchDEBUG 2008-04-10 20:20:04,531 \[next_batch.2\] org.mule.transport.jdbc.components.BatchManager: Next range: {lowerId=3001, upperId=6000}DEBUG 2008-04-10 20:20:05,531 \[next_batch.2\] org.mule.transport.jdbc.components.BatchManager: Processing next batchDEBUG 2008-04-10 20:20:05,531 \[next_batch.2\] org.mule.transport.jdbc.components.BatchManager: Next range: {lowerId=6001, upperId=9000}

In this example, batches are configured to occur every 1 second, with a batch size of 3000.

Note that if you stop and restart the Mule server, the batches should resume processing where they left off.

Re-running the Examples

If you want to run these examples again, just delete all records from both mule_source and mule_target tables, and remove the file "/tmp/eg-batches.txt". In Oracle, this may be most efficiently done by usingtruncate, e.g. truncate table mule_source.

Alternatively, if on Oracle, start the server choose "Example 5" to cleanup the tables mule_source and mule_target. You still need to manually remove the file "/tmp/eg-batches.txt".