JDBC Connection Example

Summary

This demo illustrates how one can use JDBC in conjunction with the JMSL Library. This example uses the JDBC-ODBC bridge and so only runs on the Windows platform. Java™ code except for the connection parts could be used with any JDBC connection method on other platforms, however. For more information about JDBC consult java.sun.com/products/jdbc/.

Usage

To run the demo, the included MS Access database xau.mdb must first be registered in the ODBC Manager. This can be done following these steps:

  1. Open Control Panel -> Administrative Tools -> Data Sources (ODBC)
  2. Click "Add...", select Microsoft Access Driver (*.mdb), Click "Finish"
  3. Type "xau" (no quotes) in the Data Source Name field. Add a description if you wish.
  4. Click "Select..." and browse to the xau.mdb file included in SampleJDBC. Click "OK"
  5. That's all. Close the ODBC Data Source Administrator by clicking "OK"

The table contains 6 columns: index, date, open, high, low, close. The first column is an int, the second a Date, and the rest are doubles. You should see some output in the console as the result of the query is parsed. A Candlestick chart of the results of the query is plotted. Note that only 50 rows are read of the over 3000 in the table.

JMSL Library Charting Classes

The chart is created using com.imsl.chart.Candlestick, where on days the index closes higher, the sticks are green and on days the index closes lower, the sticks are red:

Candlestick cs = new Candlestick(axis, startDate, listHigh, listLow, listClose, listOpen);
cs.getUp().setFillColor("green");
cs.getDown().setFillColor("red");

Java Code

The main part of this example is connecting to the database and retrieving results:

// connect to ODBC database
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:xau";

// connect
java.sql.Connection con = java.sql.DriverManager.getConnection(url, "xau", "sql");

// create Statement object
java.sql.Statement stmt = con.createStatement();
stmt.setMaxRows(MAXROWS);
String sqlselect = "Select id, date, open, high, low, close from xau";

// run query
java.sql.ResultSet rs = stmt.executeQuery(sqlselect);

Link to Source Code

SampleJDBC.java This is the main class that connects to the database, runs a query and plots the results.

Running This Demo

Two alternatives are available to run this demo:

1) Use the source code in your development environment as any other Java code. More information is available in the How To.

2) An executable jar file containing all of the demos referenced in this guide is included in the jmsl/lib directory. On Windows, you may double-click the file to run it if files with a ".jar" extension are properly registered with javaw.exe. Alternatively, for both Windows and UNIX environments, the jar file may be executed from the command line using java -jar gallery.jar.

As list of buttons, one for each demo, is created. Demos can be subsetted as they relate to specific areas (Math, Stat, Finance, Charting) by choosing the appropriate selection on the JComboBox. To run the Additional Demos, select Quick Start in the JComboBox.