Using JDBC to Create Database Objects
by James W. Cooper Watson Research Center IBM Corp.
Excerpted from: Principles of Object-Oriented Programming in Java 1.1
Abstract
This paper analyzes Java DataBase Connectivity (JDBC), an object-oriented
approach to connecting to databases in Java. JDBC is an object-oriented
wrapping and redesign of ODBC API that is easier to learn and to use and
that allows one to write vendor-independent code for querying and manipulating
databases. JDBC provides some fairly low-level methods for accessing
databases, as well as more powerful objects for handling databases at a higher
level of abstraction.
What is a Database?
A database is a series of tables of information in
some sort of file structure that allows you to access these tables, select
columns from them, sort them, and select rows based on various criteria.
Databases usually have indexes associated with many of the columns
in these tables, so that we can access them as rapidly as possible.
Databases are used more than any other kind of structure
in computing. You'll find databases as central elements of employee records
and payroll systems, in travel-scheduling systems, and all through product
manufacturing and marketing.
In the case of employee records, you could imagine a table
of employee names and addresses and of salaries, tax withholding, and benefits.
Let's consider how these might be organized. You can imagine one table
of employee names, addresses and phone numbers. Other information that
you might want to store would include salary, salary range, last raise,
next raise, employee performance ranking, and so forth.
Should this all be in one table? Almost certainly not.
Salary ranges for various employee types are probably invariant between
employees; thus you would store only the employee type in the employee
table and the salary ranges in another table, which is pointed to by the
type number. Consider the following:
Key |
Lastname |
SalaryType |
|
SalaryType |
Min |
Max |
1 |
Adams |
2 |
|
1 |
30000 |
45000 |
2 |
Johnson |
1 |
|
2 |
45000 |
60000 |
3 |
Smyth |
3 |
|
3 |
60000 |
75000 |
4 |
Tully |
1 |
|
|
|
|
5 |
Wolff |
2 |
|
|
|
|
The data in the SalaryType
column refers to the second table. We could imagine many categories for such tables
for things such as state of residence and tax values for each state, health
plan withholding, and so forth. Each table will have a primary key column
like the ones at the left of each table above and several more columns
of data. Building tables in databases has evolved to both an art and a
science. The structure of these tables is refered to by their normal
form. Tables are said to be in first, second, or third normal form,
abbreviated as 1NF, 2NF or 3NF.
1st. Each cell in a table should have only
one value (never an array of values). (1NF)
2nd. 1NF and every non-key column is fully
dependent on the key column. This means there is a 1-to-1 relationship
between the primary key and the remaining cells in that row. (2NF)
3rd. 2NF and all non-key columns are mutually
independent. This means that there are no data columns containing values
that can be calculated from other columns' data. (3NF)
Today nearly all databases are constructed so that all
tables are in Third Normal Form (3NF). This means that there are usually
a fairly large number of tables, each with relatively few columns of information.
Getting Data out of Databases
Suppose we wanted to produce a table of employees and their
salary ranges for some planning exercise. This table doesn't exist directly
in the database, but it can be constructed by issuing a query to the database.
We'd like to have a table that looked like the following:
Name
|
Min
|
Max
|
Adams |
$45,000.00 |
$60,000.00 |
Johnson |
$30,000.00 |
$45,000.00 |
Smyth |
$60,000.00 |
$75,000.00 |
Tully |
$30,000.00 |
$45,000.00 |
Wolff |
$45,000.00 |
$60,000.00 |
or maybe sorted by increasing salary as
Name
|
Min
|
Max
|
Tully |
$30,000.00 |
$45,000.00 |
Johnson |
$30,000.00 |
$45,000.00 |
Wolff |
$45,000.00 |
$60,000.00 |
Adams |
$45,000.00 |
$60,000.00 |
Smyth |
$60,000.00 |
$75,000.00 |
We find that the query we issue to obtain these tables has the form
SELECT DISTINCTROW Employees.Name, SalaryRanges.Min,
SalaryRanges.Max FROM Employees INNER JOIN SalaryRanges ON Employees.SalaryKey = SalaryRanges.SalaryKey
ORDER BY SalaryRanges.Min;
This language is called Structured Query Language or SQL
(often pronounced "sequel"), and it is the language of virtually all databases
currently available. There have been several standards issued for SQL over
the years, and most PC databases support much of these ANSI standards. The
SQL-92 standard is considered the floor standard, and there have been several
updates since. However, none of these databases support the later SQL versions
perfectly, and most offer various kinds of SQL extensions to exploit various
features unique to their database.
Kinds of Databases
Since the PC became a major office tool, there have been
a number of popular databases developed that are intended to run by themselves
on PCs. These include elementary databases like Microsoft Works, as well as more
sophisticated ones like Approach, dBase, Borland Paradox, Microsoft Access,
and FoxBase.
Another category of PC databases includes those databases
intended to be accessed from a server by a number of PC clients. These
include IBM DB/2, Microsoft SQL Server, Oracle, Sybase, SQLBase, and XDB.
All of these database products support various relatively similar dialects
of SQL, and all of them thus would appear at first to be relatively interchangeable.
The reason they are not interchangeable, of course, is that each
was designed with different performance characteristics involved and each
with a different user interface and programming interface. While you might
think that since they all support SQL, programming them would be similar,
quite the opposite is true, since each database has its own way of receiving
the SQL queries and its own way of returning the results. This is where
the next proposed level of standardization came about: ODBC.
ODBC
It would be nice if we could somehow write code that was
independent of the particular vendor's database and that would allow us to
get the same results from any of these databases without changing our calling
program. If we could only write some wrappers for all of these databases
so that they all appeared to have similar programming interfaces, this
would be quite easy to accomplish.
Microsoft first attempted this feat in 1992, when they
released a specification called Object Database Connectivity. It was supposed
to be the answer for connection to all databases under Windows. Like all
first software versions, this suffered some growing pains, and another version
was released in 1994, which was somewhat faster, as well as more stable.
It also was the first 32-bit version. In addition, ODBC began to move to
other platforms than Windows and has by now become quite pervasive in the
PC and Workstation world. ODBC drivers are provided by nearly every major
database vendor.
However, ODBC is not the panacea we might at first suppose.
Many database vendors support ODBC as an "alternate interface" to their
standard one, and programming in ODBC is not trivial. It is much like other
Windows programming, consisting of handles, pointers and options that make
it hard to learn. Finally, ODBC is not an independently-controlled standard.
It was developed and is being evolved by Microsoft, which, given the highly
competitive software environment we all work in, makes its future hard
to predict.
What Is JDBC?
JDBC is a set of initials that once stood for "Java DataBase
Connectivity," but it is now a trademarked symbol on its own. It is an
object-oriented wrapping and redesign of ODBC API that is much easier
to learn and to use and that really does allow you both to write vendor-independent
code to query and to manipulate databases. While it is object-oriented, as
all Java APIs must be, it is not a very high level set of objects, and
we will be developing some higher-level approaches in the course of the
remainder of this chapter.
Most database vendors other than Microsoft have embraced
JDBC and provide JDBC drivers for their databases; this makes it quite easy
for you to really write almost completely database-independent code. In
addition, JavaSoft and Intersolv have developed a product called the JDBC-ODBC
Bridge, which allows you to connect to databases for which no direct JDBC
driver yet exists. All of the databases which support JDBC must at a minimum
support the SQL-92 standard. This makes for a great degree of portability
across databases and platforms.
Installing and Using JDBC
The JDBC classes are grouped in the java.sql
package and are installed when you install the Java JDK 1.1 or later. However,
if you want to use the JDBC-ODBC bridge, you must install two other packages.
First, if you are using Windows 95, you must upgrade your ODBC driver to
the 32-bit driver, which you can download free from Microsoft's Web site.
This driver is difficult to find on their site; search for DataAcc.exe
and download and install it.
The
JDBC-ODBC Driver is available from the Sun Java site (http://java.sun.com)
and is easily located and downloaded. After you expand and install this
driver, you must do the following:
-
add the \jdbc-odbc\classes;
path to your PATH environment variable.
-
add the \jdbc-odbc\classes;
path to your CLASSPATH environment variable.
-
Under Windows 95, put these into your autoexec.bat file and
reboot so they take effect for all tasks.
-
Under Windows NT, add these to your Environment tab of your
System object in the Control panel, and log off and on again for them to
take effect.
Types of JDBC Drivers
There are actually four ways for a Java program to connect
to a database:
-
JDBC-ODBC bridge and an ODBC driver--In this case,
it is a local solution, since the ODBC driver and the bridge code must
be present on each user's machine. This is fundamentally an interim solution.
-
Native code plus Java driver--This replaces ODBC and the
bridge with another local solution: native code for that platform that
can be called by Java.
-
JDBC-Net pure Java driver--The Java driver-translated JDBC
calls into an independent protocol that is passed to a server. The server
can then connect to any of a number of databases. This method allows you
to call a server from an applet on your client machine and return the results
to your applet. In this case, middleware software providers can provide
the server.
-
Native protocol Java driver--The Java driver translates directly
to the protocol for that database and makes the calls. This method also
can be used over a network and can then display results in a Web browser
applet. In this case, each database vendor would supply the driver.
If you want to write code to manipulate a PC client database
like dBase, Foxbase, or Access, you will probably use method 1 and have
all the code on the user's machine. Larger client-server database products,
like IBM's DB2, already provide level 3 drivers for you.
Two-Tier and Three-Tier Models
When the database and the application which interrogates
it are on the same machine and there is no server code interposed, we refer
to the resulting program as a two-tier model. One tier is the application
and the other is the database. This is usually the case in JDBC-ODBC bridge
systems.
When there is an application or applet that calls a server
that in turn calls the database, we call this a three-tier model.
This is usually the case when there is a program called a "server" that
you make you calls to.
Writing JDBC Code to Access Databases
Now we are going to start looking into how you write the
Java programs themselves to access databases. The database we are going
to use is a Microsoft Access database called groceries.mdb. The data in
this database consist of the prices for some common commodities at three local
grocery stores. The food table looks like this:
FoodKey
|
FoodName
|
1 |
Apples |
2 |
Oranges |
3 |
Hamburger |
4 |
Butter |
5 |
Milk |
6 |
Cola |
7 |
Green
beans |
The store table like this:
StoreKey
|
StoreName
|
1 |
Stop
and Shop |
2 |
Village
Market |
3 |
Waldbaum's |
And the grocery-store pricing table simply consists of keys from these
tables followed by prices:
FSKey
|
StoreKey
|
FoodKey
|
Price
|
1 |
1 |
1 |
$0.27 |
2 |
2 |
1 |
$0.29 |
3 |
3 |
1 |
$0.33 |
4 |
1 |
2 |
$0.36 |
5 |
2 |
2 |
$0.29 |
6 |
3 |
2 |
$0.47 |
7 |
1 |
3 |
$1.98 |
8 |
2 |
3 |
$2.45 |
9 |
3 |
3 |
$2.29 |
10 |
1 |
4 |
$2.39 |
11 |
2 |
4 |
$2.99 |
12 |
3 |
4 |
$3.29 |
13 |
1 |
5 |
$1.98 |
14 |
2 |
5 |
$1.79 |
15 |
3 |
5 |
$1.89 |
16 |
1 |
6 |
$2.65 |
17 |
2 |
6 |
$3.79 |
18 |
3 |
6 |
$2.99 |
19 |
1 |
7 |
$2.29 |
20 |
2 |
7 |
$2.19 |
21 |
3 |
7 |
$1.99 |
Registering Your Database with ODBC
Before you can access an ODBC database under Windows 95 or
NT, you must register it with the ODBC driver control panel. Under Windows
95, this is the ODBC icon in the Control Panel program. Under Windows NT,
you will find the program on the Start menu. (If you don't, you will need
to install the ODBC driver WX1350.exe we described above.)
Double-click on the ODBC icon and then click on Add as
shown in Figure 1. Then select a database driver (here use Microsoft Access)
and click on OK. Type in a Data Source name (Grocery prices) and a Description
(in local stores) for the database (neither of these need be related to
the filename), and then click on Select to locate the database and select
it. After locating the database, your screen will appear as shown in Figure
2. Click on OK and then on Close to close the panels.
Figure 1: The ODBC control panel setup screen.
Figure 2: Selecting a database and description in the
ODBC control panel.
Connecting to a Database
All of the database objects and methods are in the java.sql
package, and you must import java.sql.*
into any programs that use JDBC. To connect to
an ODBC database, you must first load the JDBC-ODBC bridge driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
This statement loads the driver and creates an instance of
that class. Then, to connect to a particular database, you must create
an instance of the Connection class and refer to the database using URL
syntax.
String url = "jdbc:odbc:Grocery prices";
Connection con = DriverManager.getConnection(url);
Note that the database name you use here is the one you enter
as the Data Source name in the ODCB setup panel.
The URL syntax can be quite varied for different types
of databases.
jdbc:subprotocol:subname
The first characters represent the connection protocol
and are always jdbc.
There may also be a subprotocol, and here that subprotocol is specified
as odbc. It defines
the connectivity mechanism for a class of databases. If you are connecting
to a database server on another machine, you may specify that machine and
a subdirectory as well:
jdbc:bark//doggie/elliott
Finally, you may specify a username and password as part
of the connection string:
jdbc:bark//doggie/elliot;UID=GoodDog;PWD=woof
Accessing the Database
Once you have connected to the database, you can request
information on the names of the tables and on the names and contents of their
columns, and you can run SQL statements that either query the database
or add to or modify its contents. The objects that you can use to obtain
information from the database are:
DatabaseMetaData |
Information about the database as a whole: table names,
table indexes, database product name and version, actions supported by
database. |
ResultSet |
Information about a table
or result of a query. You access
the data row by row, but you can access the columns in any order. |
ResultSetMetaData
|
Information about
the column names and types in a ResultSet. |
While each of these objects has a large number of methods that allow you
to get very detailed information about the elements of the database, there
are a few major methods in each object that give you the most significant
information about your data. However, if you are looking for more information
than we show here, we encourage you to study the documentation for descriptions
of the remaining methods.
The ResultSet
The ResultSet object is the most important single object
in JDBC. It is essentially an abstraction of a table of general
width and unknown length. Nearly all methods and queries return data as
a ResultSet. A ResultSet contains any number of named columns that you
can ask for by name. It also consists of one or many rows, which you can
move through sequentially from top to bottom one at a time.
Before you can use a ResultSet, you need to ask how many
columns it contains. This information is stored in the ResultSetMetaData
object.
//get the number of columns from the metadata
ResultSetMetaData rsmd;
rsmd = results.getMetaData();
numCols = rsmd.getColumnCount();
When you obtain a ResultSet, it points just before the
first row. You use the next()
method to obtain each additional row, and the method returns false
when no more rows remain. Since fetching data from a database may result
in errors, you must always enclose your result set manipulations in a try
block.
try
{
rsmd = results.getMetaData();
numCols = rsmd.getColumnCount();
boolean more = results.next();
while (more)
{
for (i = 1; i <= numCols; i++)
System.out.print(results.getString(i)+" ");
System.out.println();
more = results.next();
}
results.close();
}
catch(Exception e)
{System.out.println(e.getMessage());}
You can fetch data in a ResultSet in many forms, depending
on the data type stored in each column. Further, you can obtain the contents
of a column either by column number or by column name. Note that column
numbers start at 1, not at 0. Some of the more common methods for the ResultSet
object are shown below.
getInt(int);
|
Returns contents of the column numbered int
as an integer. |
getInt(String);
|
Returns contents of the column named String
as an integer. |
getFloat(int);
|
Return contents of the column numbered int
as a float.. |
getFloat(String);
|
Returns contents of the column named String
as a float. |
getDate(int);
|
Return contents of the column numbered int
as a date. |
getDate(String);
|
Return contents of the column named String
as a date. |
next();
|
Moves the row pointer to the next row. Returns false
if no rows remain. |
close();
|
Closes the result set. |
getMetaData();
|
Returns the ResultSetMetaData
object. |
ResultSetMetaData
You obtain the ResultSetMetaData
object from the ResultSet
using the getMetaData()
method. You can use this object to discover the number and type of columns
and the names of each column.
getColumnCount();
|
Returns the number of columns in the ResultSet. |
getColumnName(int);
|
Returns the name of column nnumber int. |
getColumnLabel(int);
|
Returns the suggested label for that column. |
isCurrency(int);
|
Returns true
if this column contains a number in currency units. |
isReadOnly(int);
|
Returns true
if the column is read only. |
isAutoIncrement(int);
|
Returns true
if this column is autoincrement. Such columns are usually keys and are
always read-only. |
getColumnType(int);
|
Returns the SQL data type for this column. These data types
include
BIGINT
BINARY
BIT
CHAR
DATE
DECIMAL
DOUBLE
FLOAT
INTEGER
LONGVARBINARY
LONGVARCHAR |
NULL
NUMERIC
OTHER
REAL
SMALLINT
TIME
TIMESTAMP
TINYINT
VARBINARY
VARCHAR |
|
DatabaseMetaData
The DatabaseMetaData
object gives you information about the entire database. You use it primarily
to get the names of the tables in a database and the names of the columns
in a table. Since various databases also support different variants of
SQL, there are also a large number of methods querying the database about
what SQL methods it supports.
getCatalogs()
|
Return a list of catalogs of information in that database.
With the JDBC-ODBC Bridge driver, you get a list of databases registered
with ODBC. This is seldom used in JDBC-ODBC databases. |
getTables(catalog, schema, tableNames, columnNames)
|
Returns a description of the table names for all tables
matching tableNames
and all columns matching columnNames. |
getColumns(catalog, schema, tableNames, columnNames)
|
Returns a description of the table column names for all
tables matching tableNames
and all columns matching columnNames. |
getURL();
|
Get the name of the URL you are connected to. |
getDriverName();
|
Get the name of the database driver you are connected to. |
Getting Information on Tables
You can get information on the tables in a database using
the getTables()
method on the DataBaseMetaData. This method has the following four String
arguments:
results =
dma.getTables(catalog, schema, tablemask, types[]);
where the arguments have the following meanings:
catalog
|
The name of the catalog to look in for table names. For
JDBC-ODBC databases, and many others, this can be set to null.
The catalog entry for these databases is actually their absolute pathname
in the file system. |
schema
|
The database "schema" to include. Many databases do not
support schema, and for others it is the user name of the owner of the
database. It is usually set to null. |
tablemask
|
A mask describing the names of the tables you want to retrieve.
If you want to retrieve all table names, set it to the wildcard character
%. Note that the
wildcard character in SQL is the %-sign and not the usual PC user's
*-sign. |
types[]
|
This is an array of Strings describing the kinds of tables
you want to retrieve. Databases frequently contain a number of tables for
internal housekeeping that are of little value to you as a user. If this
is null, you will get all these tables. If you make this a one-element
array containing the string "TABLES",
you will get only the tables of interest to users. |
Simple code for getting the table names in a database amounts to getting
the DatabaseMetaData object
and retrieving the table names from it:
con = DriverManager.getConnection(url);
//get the database metadata
dma =con.getMetaData();
//now dump out the names of the tables in the database
String[] types = new String[1];
types[0] = "TABLES"; //set table type mask
//note the %-sign is a wild card (not '*')
results = dma.getTables(null, null, "%", types);
Then we can print out the table names, just as we did above:
boolean more = results.next();
while (more)
{
for (i = 1; i <= numCols; i++)
System.out.print(results.getString(i)+" ");
System.out.println();
more = results.next();
}
Enclose all the code in a try
block as before.
Executing SQL Queries
Now that we understand the basic JDBC objects, we can execute
SQL queries. Queries are executed as methods of Statement
objects, and you can easily obtain a statement
object from the Connection
object:
String query = "SELECT FoodName FROM Food;";
ResultSet results;
try
{
Statement stmt = con.createStatement();
results = stmt.executeQuery(query);
}
catch (Exception e)
{System.out.println("query exception");}
Note that this simple query returns the contents of the entire
FoodName column from the Food table. You use simple queries like these
to obtain the contents of a complete column. Note that the result of a
query is itself a ResultsSet, which you can handle just as we have above.
Printing out ResultSets
Since we always have to print out data from ResultSets, we
can design a simple method to dump out an entire ResultSet, including the
table name metadata. This routine is shown below:
private void dumpResults(String head)
{
//this is a general routine to print out
//column headers and the contents of each column
System.out.println(head);
try
{
//get the number of columns from the metadata
rsmd = results.getMetaData();
numCols = rsmd.getColumnCount();
//print out the column names
for (i = 1; i<= numCols; i++)
System.out.print(rsmd.getColumnName(i)+" ");
System.out.println();
//print out the column contents
boolean more = results.next();
while (more)
{
for (i = 1; i <= numCols; i++)
System.out.print(results.getString(i)+" ";
System.out.println();
more = results.next();
}
}
catch(Exception e)
{System.out.println(e.getMessage());}
}
A Simple JDBC Program
Now that we've covered all the basic features of JDBC, we
can write a simple program to open a database, print out its table names
and a table column's contents, and then execute a query on it. This program
is shown below:
import java.net.URL;
import java.sql.*;
import java.util.*;
class JdbcOdbc_test
{
ResultSet results;
ResultSetMetaData rsmd;
DatabaseMetaData dma;
Connection con;
int numCols, i;
//------------------------------------------------
public JdbcOdbc_test()
{
String url = "jdbc:odbc:Grocery prices";
String query = "SELECT DISTINCTROW FoodName FROM Food " + "WHERE (FoodName like 'C%');";
try
{
//load the bridge driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//connect to the database
con = DriverManager.getConnection(url);
//get the database metadata
dma =con.getMetaData();
System.out.println("Connected to:"+dma.getURL());
System.out.println("Driver "+dma.getDriverName());
//now dump out the names of the tables in the database
String[] types = new String[1];
types[0] = "TABLES";
//note the %-sign is a wild card (not '*')
results = dma.getTables(null, null, "%", types);
dumpResults("----Tables----");
results.close();
}
catch (Exception e)
{System.out.println(e);}
//get Table Column names
System.out.println("----Column Names----");
try {
results =
dma.getColumns(null, null, "FoodPrice", null);
ResultSetMetaData rsmd = results.getMetaData();
int numCols = rsmd.getColumnCount();
while (results.next() )
String cname = results.getString("COLUMN_NAME");
System.out.print(cname + " ");
System.out.println();
results.close();
}
catch (Exception e)
{System.out.println(e);}
//List out the contents of a column-- this is a query
try {
Statement stmt = con.createStatement();
results =
stmt.executeQuery("SELECT FOODNAME FROM FOOD;");
}
catch (Exception e)
{System.out.println("query exception");}
dumpResults("----Contents of FoodName column----");
//try actual SQL statement
try
{
Statement stmt = con.createStatement();
results = stmt.executeQuery(query);
}
catch (Exception e)
{System.out.println("query exception");}
dumpResults("----Results of Query-----");
}
The printout from this program for our Grocery price database
is shown below:
C:\Projects\objectJava\chapter19>java JdbcOdbc_test
Connected to:jdbc:odbc:Grocery prices
Driver JDBC-ODBC Bridge (ODBCJT32.DLL)
----Tables----
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME TABLE_TYPE REMARKS
groceries null Food TABLE null
groceries null FoodPrice TABLE null
groceries null Stores TABLE null
----Column Names----
FSKey StoreKey FoodKey Price
----Contents of FoodName column----
FOODNAME
Apples
Oranges
Hamburger
Butter
Milk
Cola
Green beans
----Results of Query-----
FoodName
Cola
Building Higher Level JDBC Objects
It is plain from the foregoing example that it would be very
helpful if we could encapsulate some of the behavior we have been using
in a few higher-level objects. Not only could we enclose the try blocks,
but we could make access to the ResultSet
methods a little simpler.
In this section, we'll build a new resultSet
object which encapsulates the JDBC ResultSet
object and returns the data in a row as an array of Strings. We discovered
that you always needed to obtain the number of columns and the names of
the columns from the ResultSetMetaData
object, and thus it seems quite reasonable to create a new object that
also encapsulates the metadata.
Further, we frequently need to fetch elements of a row
by name or integer index, and it would be useful not to have to always
wrap those fetched in try
blocks. And, finally, when we want the contents of an entire row, it may
well be more convenient to obtain it as a String array. In our resultSet
object shown below, we address these objectives:
<>class resultSet
{
//this class is a higher level abstraction
//of the JDBC ResultSet object
ResultSet rs;
ResultSetMetaData rsmd;
int numCols;
public resultSet(ResultSet rset)
{
rs = rset;
try
{
//get the meta data and column count at once
rsmd = rs.getMetaData();
numCols = rsmd.getColumnCount();
}
catch (Exception e)
{System.out.println("resultset error"
+e.getMessage());}
}
//-----------------------------------
public String[] getMetaData()
{
//returns an array of all the column names
//or other meta data
String md[] = new String[numCols];
try
{
for (int i=1; i<= numCols; i++)
md[i-1] = rsmd.getColumnName(i);
}
catch (Exception e)
{System.out.println("meta data error"+
e.getMessage());}
return md;
}
//-----------------------------------
public boolean hasMoreElements()
{
try{
return rs.next();
}
catch(Exception e){return false;}
}
//-----------------------------------
public String[] nextElement()
{
//copies contents of row into string array
String[] row = new String[numCols];
try
{
for (int i = 1; i <= numCols; i++)
row[i-1] = rs.getString(i);
}
catch (Exception e)
{System.out.println("next element error"+
e.getMessage());}
return row;
}
//-------------------------------------
public String getColumnValue(String columnName)
{
String res = "";
try
{
res = rs.getString(columnName);
}
catch (Exception e)
{System.out.println("Column value error:"+
columnName+e.getMessage());}
return res;
}
//-------------------------------------
public String getColumnValue(int i)
{
String res = "";
try
{
res = rs.getString(i);
}
catch (Exception e)
{System.out.println("Column value error:"+
columnName+e.getMessage());}
return res;
}
//----------------------------------------------
public void finalize()
{
try{rs.close();}
catch (Exception e)
{System.out.println(e.getMessage());}
}
}
We can easily wrap any ResultSet
object in this class by simply creating one on
the spot using the new operator:
ResultSet results = .. //get a ResultSet as usual
//create this more useful object from it
resultSet rs = new resultSet(results);
and use it in any JDBC program.
Building a Database Object
The other part of our attempt to move up the OO food chain
will be our creation of a Database
object which encapsulates the behavior of the Connection,
Statement, and
DatabaseMetaData
objects, as well as the SQL Query and the resultSet we just built. Our
Database object
allows us to create a connection, get the table names, move through
the database, and get values of rows and columns in a simpler manner. Note
that the Execute
method returns a resultSet object
that you can operate on directly.
class Database
{
//this class encapculates all of the functions of
//a JDBC database into a single object
Connection con;
resultSet results;
ResultSetMetaData rsmd;
DatabaseMetaData dma;
String catalog;
String types[];
public Database(String driver)
{
types = new String[1];
types[0] = "TABLES"; //initialize types
try{Class.forName(driver);} //load Bridge driver
catch (Exception e)
{System.out.println(e.getMessage());}
}
//-----------------------------------
public void Open(String url, String cat)
{
catalog = cat;
try {con = DriverManager.getConnection(url);
dma =con.getMetaData(); //get the meta data
}
catch (Exception e)
{System.out.println(e.getMessage());}
}
//-----------------------------------
public String[] getTableNames()
{
String[] tbnames = null;
Vector tname = new Vector();
//add the table names to a Vector
//since we don't know how many there are
try {
results =
new resultSet(dma.getTables(catalog, null,
"%", types));
while (results.hasMoreElements())
tname.addElement(results.getColumnValue("TABLE_NAME"));
}
catch (Exception e) {System.out.println(e);}
//copy the table names into a String array
tbnames = new String[tname.size()];
for (int i=0; i< tname.size(); i++)
tbnames[i] = (String)tname.elementAt(i);
return tbnames;
}
//-----------------------------------
public String[] getTableMetaData()
{
// return the table type information
results = null;
try{
results =
new resultSet(dma.getTables(catalog, null,
"%", types));
}
catch (Exception e)
{System.out.println(e.getMessage());}
return results.getMetaData();
}
//-----------------------------------
public String[] getColumnMetaData(String tablename)
{
//return the data on a column
results = null;
try {
results =
new resultSet(dma.getColumns(catalog, null,
tablename, null));
}
catch (Exception e)
{System.out.println(e.getMessage());}
return results.getMetaData();
}
//-----------------------------------
public String[] getColumnNames(String table)
{
//return an array of Column names
String[] tbnames = null;
Vector tname = new Vector();
try {
results =
new resultSet(dma.getColumns(catalog, null,
table, null));
while (results.hasMoreElements() )
tname.addElement(results.getColumnValue("COLUMN_NAME"));
}
catch (Exception e) {System.out.println(e);}
tbnames = new String[tname.size()];
for (int i=0; i< tname.size(); i++)
tbnames[i] = (String)tname.elementAt(i);
return tbnames;
}
//-----------------------------------
public String getColumnValue(String table,
String columnName)
{
//return the value of a given column
String res = null;
try
{
if (table.length()>0)
results =
Execute("Select " + columnName +
" from " + table +
" order by "+columnName);
if (results.hasMoreElements())
res = results.getColumnValue(columnName);
}
catch (Exception e)
{System.out.println("Column value error" +
columnName+ e.getMessage());}
return res;
}
//-----------------------------------
public String getNextValue(String columnName)
{
// return the next value in that column
//using the remembered resultSet
String res = "";
try
{
if (results.hasMoreElements())
res = results.getColumnValue(columnName);
}
catch (Exception e)
{System.out.println("next value error"+
columnName+ e.getMessage());}
return res;
}
//-----------------------------------
public resultSet Execute(String sql)
{
//execute an SQL query on this database
results = null;
try
{
Statement stmt = con.createStatement();
results = new resultSet(stmt.executeQuery(sql));
}
catch (Exception e)
{System.out.println("execute error"+
e.getMessage());}
return results;
}
}
A Visual Database Program
To wrap up the material we've covered in this chapter, let's
write a simple GUI program which allows us to display the table names,
column names, and column contents of a database. We'll also include a text
area where you can type in an SQL query to execute on that Database. You
will find the resultSet and
Database classes
used in this program, called dbFrame.java, in the \chapter20 subdirectory
on the Companion CD-ROM. The display of the program is show in Figure 3.
Figure 3: The dbFrame.java program for displaying data
in a JDBC-connected database.
In this program, the table names of our default database,
groceries.mdb, are shown in the left column. When you click on one of the
table names, the column names are shown in the middle column. Finally,
when you click on a row in the middle column, the contents of that column
are shown in the right-hand column.
The crux of this program is simply the receiving the list
selections and clearing and filling the correct list box:
public void itemStateChanged(ItemEvent e)
{
Object obj = e.getSource();
if (obj == Tables) //put in column names
showColumns();
if (obj == Columns) //put in column contents
showData();
}
//------------------------------------
private void loadList(List list, String[] s)
{
//clear and fill specified list box
list.removeAll();
for (int i=0; i< s.length; i++)
list.add(s[i]);
}
//------------------------------------
private void showColumns()
{
//display the column names
String cnames[] =
db.getColumnNames(Tables.getSelectedItem());
loadList(Columns, cnames);
}
//------------------------------------
private void showData()
{
String colname = Columns.getSelectedItem();
String colval =
db.getColumnValue(Tables.getSelectedItem(),
colname);
Data.setVisible(false);
Data.removeAll();
Data.setVisible(true);
colval =
db.getNextValue(Columns.getSelectedItem());
while (colval.length()>0)
{
Data.add(colval);
colval =
db.getNextValue(Columns.getSelectedItem());
}
}
Executing a Query
The text area at the bottom of the display allows you to
type in any SQL query you want. The one that is built into the demonstration
program is
String queryText =
"SELECT DISTINCTROW FoodName, StoreName, Price "+
"FROM (Food INNER JOIN FoodPrice ON "+
"Food.FoodKey = FoodPrice.FoodKey) " +
"INNER JOIN Stores ON "+
"FoodPrice.StoreKey = Stores.StoreKey "+
"WHERE (((Food.FoodName)=\'Oranges\')) "+
" ORDER BY FoodPrice.Price;";
This query simply lists the price for oranges at each grocery
store.
When you click on the Run Query button, it executes
this query and passes the resultSet object to a dialog box to display:
public void actionPerformed(ActionEvent e)
{
Object obj = e.getSource();
if (obj == Quit)
System.exit(0);
if (obj == Search)
clickedSearch();
}
//------------------------------------
private void clickedSearch()
{
resultSet rs = db.Execute(query.getText());
String cnames[] = rs.getMetaData();
queryDialog q = new queryDialog(this, rs);
q.show();
}
The Query Result Dialog
The query dialog takes the resultSet
object and puts each row in a String array and puts the String arrays
in a Vector, where they can be accessed quickly during a paint()
routine.
private void makeTables()
{
//puts each row in a String array
//and all the string arrays into a Vector
tables = new Vector();
String t[] = results.getMetaData();
tables.addElement( t);
while (results.hasMoreElements())
tables.addElement(results.nextElement());
}
We display the data by drawing them into a Panel using the
drawString()Graphics
method. Just as in the Printer object, we have to keep track of
the x and y positions ourselves.
public void paint(Graphics g)
{
String s[];
int x=0;
//compute the font height
int y =g.getFontMetrics().getHeight();
//compute an estimated column width
int deltaX = (int)1.5f*
(g.getFontMetrics().stringWidth("wwwwwwwwwwwwww"));
//move through the table vector
for (int i=0; i< tables.size(); i++)
{
s = (String[])tables.elementAt(i);
//and draw each row from the string array
for (int j =0; j< s.length; j++)
{
String st= s[j];
g.drawString(st, x, y);
x += deltaX; //move over to next column
}
x = 0; //start a new row
y += g.getFontMetrics().getHeight();
//extra space between column labels and their data
if (i == 0) y += g.getFontMetrics().getHeight();
}
}
The queryDialog is displayed in Figure 4 for the built-in
query.
Figure 4: The queryDialog display in the dbFrame program,
showing the results of the default query.
Example Files
groceries.zip
dbFrame.zip
DataAcc.exe
jdbc-odbc
Bridge
Summary
In this article, we've discussed databases and ways of
examining them and executing queries on them. We've seen that the JDBC provides
a platform and a database-independent, object-oriented way to access these
data, and we've looked at the major objects of JDBC: the ResultSet,
ResultSetMetaData,
and DatabaseMetaData. After
writing a simple program using these objects, we designed higher-level
resultSet and
Database objects
which we used to build a simple visual interface to display database information.
If you are familiar with the power of databases, you will
realize that the SQL language allows you to carry out many more powerful
operations than we've summarized here. For example, you could create new
tables, add columns, and add, change, or delete rows, columns, or individual
cells of a table. All of this becomes quite general and easy to manipulate
using JDBC.
If you are using a platform-specific database driver like
the JDBC-ODBC Bridge, you are restricted to writing applications, since
applets cannot connect to this bridge running on another computer. Other
client-server databases, like IBM's DB2, do allow you to connect to them
using an applet JDBC.
For more information on how Java and database connectivity on the Web, visit
http://www.cnet.com/Content/Builder/Programming/Databases/?st.bl.fd.prog1.feat
|