Contents
This sample demonstrates how to use the JDBC Table Data Construct in an EventFlow module to read from, write to, update, and query an external, persistent JDBC data source.
Note
As shipped, this sample is configured to use the MySQL database because the JDBC drivers for MySQL are publicly available on Maven Central sites. This saves the downloading and installing of a JDBC driver required by other database vendors. However, the two EventFlow modules still do not typecheck because they cannot connect to the example JDBC URL in the included configuration file.
Before this sample can run, you must:
-
If you are connecting to a MySQL database, edit
src/main/configurations/jdbc-mysql5.conf
and provide valid serverURL, userName, and password settings for your actual MySQL instance. -
If you are connecting to a database from a different vendor, download and install into your local Maven repository (or to a site-specific repository) the JDBC driver JAR file or files that provide JDBC access to that database. See Using External JAR Files for instructions on Maven-installing JAR files.
-
Configure a HOCON file of type
com.tibco.ep.streambase.configuration.jdbcdatasource
to specify a name for your data source and its login parameters. You can use one of the files in theconfig-examples
folder as a starting point. -
Select the specified data source name in the Query Table properties for this sample.
The sample uses a JDBC Table Data Construct named OrderBook, which is linked to the external JDBC table specified in the project's configuration.
In StreamBase Studio, import this sample with the following steps:
-
From the top-level menu, click
> . -
Enter
jdbc
to narrow the list of options. -
Select Query operator used with a JDBC data source from the Data Constructs and Operators category.
-
Click
.
StreamBase Studio creates a new project for the sample.
-
Configure a HOCON file in
src/main/configurations
like the provided filejdbc-mysql5.conf
to describe the connection details for your database instance, or edit the provided file to add your database instance. -
Make sure the database you are connecting to is up and active.
-
In the Project Explorer view, open this sample's folder.
Keep an eye on the bottom right status bar of the Studio window. Make sure any
Updating
,Downloading
,Building
, orRebuild project
messages finish before you proceed. -
Open the
src/main/eventflow/
folder.packageName
-
If you know that a table named
OrderBook
already exists on your database instance with the expected schema (perhaps created by a colleague running this sample), proceed to step 11. -
Open the
CreateLoadTable.sbapp
module. In the OrderBook table's Data Source tab, from the drop-down menu, select the name you defined in your configuration file. Notice that the instance nameMySQL5
appears in the drop-down list, as well as any other database instance name you have added. -
Click the
Run button. This opens the SB Test/Debug perspective and starts the module.
-
In the Manual Input view, select the
InsertIn
stream. Enter any values and click . If theOrderBook
table exists, the Output Streams view shows a successful insert operation. If not, the response is an evaluation exception. -
To create the
OrderBook
table, select theCreateOBTable
stream and click . Look for a success message in the Output Streams view.You may need to adjust the SQL statement in the SQLQueryToCreate operator for your database vendor's standards. See
src/main/resources/create-table.txt
for a few suggestions.Optional
If the table exists, but you want to start from a clean slate, you can use the
DropOBTable
stream before theCreateOBTable
stream. Check to make sure this action doesn't step on the work of a colleague who may be running tests against a previously populatedOrderBook
table. -
When the
OrderBook
table exists, press F9 or click theTerminate EventFlow Fragment button.
-
Open the
JDBCQuery.sbapp
module. Again select your configured database instance's name In the OrderBook table's Data Source tab, from the drop-down menu in the control. -
Click the
Run button.
-
In the Test/Debug perspective, go to the Feed Simulations tab, select
OrderBookFeedSim
from the list, and click the button. Rows are inserted to theOrderBook
table through theInsertIn
input stream. If you view theInsertOut
output stream, you can see those data records as they are entered. -
In the Feed Simulations tab, click the
button. -
Switch to the Manual Input view. Select the
ReadIn
input stream from the drop-down menu, and click . This issues a query that returns all rows in the table whereSide='bid'
. -
From the Manual Input view, select the DeleteIn input stream. Based on the results from the previous Read query in the Output Streams view, type a known value for the QuoteID field from near the bottom of the list, and click
.Run step 15 again to confirm that this QuoteID was deleted from the OrderBook table.
-
When done, press F9 or click the
Terminate EventFlow Fragment button.
The following files are included in the sample:
- src/main/eventflow/
packagename
/JDBCQuery.sbapp -
The primary EventFlow module for this sample project. Its
OrderBook
table can be queried using any of three streams (ReadIn
,DeleteIn
, andInsertIn
). Results from each Query operator are sent to its connected output stream. - src/main/eventflow/
packagename
/CreateLoadTable.sbapp -
An EventFlow module to create the
OrderBook
table if it doesn't exist, to delete the table if it does, and to send in tuples that load the table. (This module'sSQLQueryToLoad
operator and the above module'sWriteOrderBook
operator are identical.) - src/main/configurations/jdbcdatasource.conf
-
A configuration file in which to define the connection URL, credentials, and optional parameters for your database instance.
- config-examples/*.conf
-
This folder contains a number of configuration file samples for different databases supported by StreamBase.
- src/main/resources/OrderBookFeedSim.sbfs
-
A simple feed simulation to populate the
OrderBook
table. - src/main/resources/create-table.txt
-
A text file containing SQL code snippets for creating the
OrderBook
table for several database vendors. Insert the snippet for your configured database type in theSQLQueryToCreate
operator in theCreateLoadTable.sbapp
module.
When you load the sample into StreamBase Studio, Studio copies the sample project's files to your Studio workspace, which is normally part of your home directory, with full access rights.
Important
Load this sample in StreamBase Studio, and thereafter use the Studio workspace copy of the sample to run and test it, even when running from the command prompt.
Using the workspace copy of the sample avoids permission problems. The default workspace location for this sample is:
studio-workspace
/sample_jdbc-query
See Default Installation Directories for the default location of studio-workspace
on your system.