|
Bookshelf Index
Chapter 18, Tables
By Matthew Robinson and Pavel Vorobiev
Introduction |
Download Chapter 18| Download Chapter 22
<< NEXT >>
18.6Stocks Table: part V - JDBC
Despite all of our sorting
functionality and enhanced data display, our application is still
quite boring
because it displays only data for a pre-defined day! Of course,
in the
real
world we need to connect such an application to the source of
fresh
information
such as a database. Very often tables are used to display data
retrieved from
databases, or to edit data to be stored in databases. In this
section
we show
how to feed our StocksTable
data extracted from a database using the Java Database
Connectivity
(JDBC) API.
First, we need to create the
database. We chose to use two SQL tables (do not confuse SQL
table
with
JTable) whose structure
precisely corresponds to the market data structure described in
section
18.2:
Table SYMBOLS
| Field
Name | Type |
symbol |
Text |
name
|
Text |
For this example we use the JDBC-ODBC
bridge which is a standard part of JDK since the 1.1 release and
links
Java
programs to Microsoft Access databases. If you are using another
database
engine, you can work with this example as well, but you must make
sure
that
the
structure of your tables is the same. Before running the example
in a
Windows
environment we need to register a database in an ODBC Data Source
Administrator
which is accessible through the Control Panel (this is not a JDBC
tutorial, so
we'll skip the details).
Table Data
| Field Name
| Type |
|---|
| symbol |
Text |
| date1 |
Date/Time |
| last |
Number |
| change |
Number |
| changeproc |
Number |
| open |
Number |
| volume |
Number |
Click Figure 18.5 for full-scale image. Retrieving stock data
from a database for display in JTable.
The Code: StocksTable.java
see \Chapter18\5
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.io.*;
import java.text.*;
import java.sql.*;
import javax.swing.*;
import javax.swing.border.*;
import javax.swing.event.*;
import javax.swing.table.*;
public class StocksTable
extends JFrame
{
protected JTable m_table;
protected StockTableData m_data;
protected JLabel m_title;
public StocksTable() {
// Unchanged code from section 18.4
JMenuBar menuBar = createMenuBar();
setJMenuBar(menuBar);
// Unchanged code
}
protected JMenuBar createMenuBar() {
JMenuBar menuBar = new JMenuBar();
JMenu mFile = new JMenu("File");
mFile.setMnemonic('f');
JMenuItem mData =
new JMenuItem("Retrieve Data...");
mData.setMnemonic('r');
ActionListener lstData =
new ActionListener() {
public void actionPerformed(
ActionEvent e) {
retrieveData();
}
};
mData.addActionListener(lstData);
mFile.add(mData);
mFile.addSeparator();
JMenuItem mExit =
new JMenuItem("Exit");
mExit.setMnemonic('x');
ActionListener lstExit =
new ActionListener() {
public void actionPerformed(
ActionEvent e) {
System.exit(0);
}
};
mExit.addActionListener(lstExit);
mFile.add(mExit);
menuBar.add(mFile);
return menuBar;
}
public void retrieveData() {
SimpleDateFormat frm =
new SimpleDateFormat("MM/dd/yyyy");
String currentDate =
frm.format(m_data.m_date);
String result = (
String)JOptionPane.showInputDialog(
this,
"Please enter date in form mm/dd/yyyy:
", "Input",
JOptionPane.INFORMATION_MESSAGE,
null, null,
currentDate);
if (result==null)
return;
java.util.Date date = null;
try {
date = frm.parse(result);
}
catch (java.text.ParseException ex) {
date = null;
}
if (date == null) {
JOptionPane.showMessageDialog(this,
result+" is not a valid date",
"Warning",
JOptionPane.WARNING_MESSAGE);
return;
}
setCursor( Cursor.getPredefinedCursor(
Cursor.WAIT_CURSOR) );
switch (m_data.retrieveData(date)) {
case 0: // Ok with data
m_title.setText(m_data.getTitle());
m_table.tableChanged(
new TableModelEvent(m_data));
m_table.repaint();
break;
case 1: // No data
JOptionPane.showMessageDialog(this,
"No data found for "+result,
"Warning",
JOptionPane.WARNING_MESSAGE);
break;
case -1: // Error
JOptionPane.showMessageDialog(this,
"Error retrieving data",
"Warning",
JOptionPane.WARNING_MESSAGE);
break;
}
setCursor(Cursor.getPredefinedCursor(
Cursor.DEFAULT_CURSOR));
}
public static void main(String argv[]) {
new StocksTable();
}
}
// Unchanged code from section 18.4
class StockTableData
extends AbstractTableModel
{
static final public
ColumnData m_columns[] = {
// Unchanged code from
//section 18.2
};
protected SimpleDateFormat m_frm;
protected Vector m_vector;
protected java.util.Date m_date;
// conflict with
protected int m_sortCol = 0;
protected boolean m_sortAsc = true;
protected int m_result = 0;
public StockTableData() {
m_frm =
new SimpleDateFormat(
"MM/dd/yyyy");
m_vector = new Vector();
setDefaultData();
}
// Unchanged code from section 18.4
public int retrieveData(
final java.util.Date date) {
GregorianCalendar calendar =
new GregorianCalendar();
calendar.setTime(date);
int month =
calendar.get(Calendar.MONTH)+1;
int day =
calendar.get(Calendar.DAY_OF_MONTH);
int year =
calendar.get(Calendar.YEAR);
final String query =
"SELECT data.symbol,
symbols.name, "+
"data.last, data.open,
data.change,
data.changeproc, "+
"data.volume FROM DATA INNER
JOIN SYMBOLS "+
"ON DATA.symbol =
SYMBOLS.symbol WHERE "+
"month(data.date1)="+month+"
AND day(data.date1)="+day+
" AND year(data.date1)="+year;
Thread runner = new Thread() {
public void run() {
try {
// Load the JDBC-ODBC
//bridge driver
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn =
DriverManager.getConnection(
"jdbc:odbc:Market", "admin", "");
Statement stmt =
conn.createStatement();
ResultSet results =
stmt.executeQuery(query);
boolean hasData = false;
while (results.next()) {
if (!hasData) {
m_vector.removeAllElements();
hasData = true;
}
String symbol =
results.getString(1);
String name =
results.getString(2);
double last =
results.getDouble(3);
double open =
results.getDouble(4);
double change =
results.getDouble(5);
double changePr =
results.getDouble(6);
long volume =
results.getLong(7);
m_vector.addElement(new StockData(
symbol, name, last,
open, change, changePr, volume));
}
results.close();
stmt.close();
conn.close();
if (!hasData)
// We've got nothing
m_result = 1;
}
catch (Exception e) {
e.printStackTrace();
System.err.println(
"Load data error:
"+e.toString());
m_result = -1;
}
m_date = date;
Collections.sort(m_vector,
new StockComparator(m_sortCol,
m_sortAsc));
m_result = 0;
}
};
runner.start();
return m_result;
}
// Unchanged code from section 18.4
}
// Class StockComparator unchanged
//from section 18.4
|
Understanding the Code
Class StocksTable
A JMenuBar instance is created with our custom
createMenuBar() method and
added to our frame.
The createMenuBar() method creates a menu bar
containing a single menu titled "File." Two menu items
are
added:
Retrieve
Data... and Exit with a separator in between. Anonymous
ActionListeners are added
to each. The first calls our custom retrieveData()
method, and
the
second simply kills the
application using System.exit(0).
The retrieveData() method is called in response to a
"Retrieve Data..." menu item activation. First it
prompts
the user
to
enter the
date by displaying a JOptionPane
dialog. Once the date has been entered, this method parses it
using a
SimpleDateFormat object. If
the entered string cannot be parsed into a valid date, the method
shows a
warning message and returns. Otherwise, we connect to JDBC and
retrieve new
data. To indicate that the program will be busy for some time the
wait
mouse
cursor is displayed. The main job is performed by our new
StockTableData
retrieveData()
method (see below), which is invoked on the m_data
object.
StockTableData's retrieveData() method
returns an
error code that
the rest of this method depends on:
0:
Normal finish, some data retrieved. The table model is updated
and
repainted.
1:
Normal finish, no data retrieved. A warning message is displayed,
and no changes in the table model are made.
-1:
An error has occurred. An error message is displayed, and no
changes in the table model are made.
Class StockTableData
First, a minor change is required in
the declaration of the m_date
variable. Since now we've imported the java.sql
package, which also includes the Date class, we have to do
provide the fully qualified calls name
java.util.Date.
A new instance variable is added to
store the result of a data retrieval request in the
retrieveData()
method. As mentioned above, retrieveData() retrieves
a
table's data for a given date of trade. Our implementation uses
the
JDBC
bridge
driver and should be familiar to JDBC-aware readers. The first
thing
we do is
construct an SQL statement. Since we cannot compare a
java.util.Date object and an SQL date stored
in the database, we have to extract the date's components (year,
month, and
day) and compare them separately. An instance of
GregorianCalendar
is used to manipulate the date
object.
We load the JDBC-ODBC bridge driver
to Microsoft Access by using the Class.forName
method,
and then
connect to a database with the
DriverManager.getConnection()
method. If no exception is thrown, we can create a
Statement
instance for the newly created Connection object and
retrieve a ResultSet
by executing the previously constructed query.
While new data is available (checked
with the ResultSet.next()
method), we retrieve new data using basic getXX()
methods, create
a
new StockData instance to
encapsulate the new data, and add it tom_vector.
Note how the hasData local variable is used to
distinguish the case in which we do not have any data in our
RecordSet. The first
time we receive some valid data from our RecordSet
in the
while loop, we set this variable to
true and
clean
up
our m_vector collection. If no
data is found, we have an unchanged initial vector and the
hasData
flag is set to false. Finally we close our
ResultSet, Statement, and
Connection
instances. If
any exception occurs, the method prints the exception trace and
returns a
instances. If any exception occurs, the method prints the
exception
trace and
returns a 1 to indicate an error.
Otherwise our newly retrieved data is sorted with the
Collections.sort() method and a 0 is
returned to
indicate
success.
Page
1
2
3
4
5
6
7
8
9
10
11
<< NEXT >>
Download Chapter 18| Download Chapter 22]
|
Print-friendly Version
