Java Database Connectivity (JDBC)
Java Database Connectivity
(JDBC)
It has been estimated that half of all software
development involves client/server operations. A great promise of Java has been
the ability to build platform-independent client/server database applications.
This has come to fruition with Java DataBase Connectivity (JDBC).
One of the major problems with databases has been the
feature wars between the database companies. There is a “standard”
database language, Structured Query Language (SQL-92), but you must usually know
which database vendor you’re working with despite the standard. JDBC is
designed to be platform-independent, so you don’t need to worry about the
database you’re using while you’re programming. However, it’s
still possible to make vendor-specific calls from JDBC so you aren’t
restricted from doing what you must.
One place where programmers may need to use SQL type
names is in the SQL TABLE
CREATE statement when they are creating a new
database table and defining the SQL type for each column. Unfortunately there
are significant variations between SQL types supported by different database
products. Different databases that support SQL types with the same semantics and
structure may give those types different names. Most major databases support an
SQL data type for large binary values: in Oracle this type is called a
LONG RAW,
Sybase calls it
IMAGE,
Informix calls it
BYTE, and
DB2 calls it LONG VARCHAR FOR BIT
DATA. Therefore, if database portability is a
goal you should try to use only generic SQL type identifiers.
Portability is an issue when writing for a book where
readers may be testing the examples with all kinds of unknown data stores. I
have tried to write these examples to be as portable as possible. You should
also notice that the database-specific code has been isolated in order to
centralize any changes that you may need to perform to get the examples
operational in your environment.
JDBC, like many of the APIs in Java, is designed for
simplicity. The method calls you make correspond to the logical operations
you’d think of doing when gathering data from a database: connect to the
database, create a statement and execute the query, and look at the result set.
To allow this platform independence, JDBC provides a
driver manager that dynamically maintains all the driver objects that
your database queries will need. So if you have three different kinds of vendor
databases to connect to, you’ll need three different driver objects. The
driver objects register themselves with the driver manager at the time of
loading, and you can force the loading using
Class.forName( ).
To open a database, you must create a “database
URL” that specifies:
- That you’re using JDBC with “jdbc.”
- The “subprotocol”: the name of the driver
or the name of a database connectivity mechanism. Since the design of JDBC was
inspired by ODBC, the first subprotocol available is the “jdbc-odbc
bridge,” specified by “odbc.
- The database identifier. This varies with the database
driver used, but it generally provides a logical name that is mapped by the
database administration software to a physical directory where the database
tables are located. For your database identifier to have any meaning, you must
register the name using your database administration software. (The process of
registration varies from platform to platform.)
All this information is combined into
one string, the “database URL.” For example, to connect through the
ODBC subprotocol to a database identified as “people,” the database
URL could be:
String
dbUrl =
"jdbc:odbc:people";
If you’re connecting across a network, the
database URL will contain the connection information identifying the remote
machine and can become a bit intimidating. Here is an example of a CloudScape
database being called from a remote client utilizing RMI:
jdbc:rmi://192.168.170.27:1099/jdbc:cloudscape:db
This database URL is really two jdbc calls in one. The
first part
“jdbc:rmi://192.168.170.27:1099/”
uses RMI to make the connection to the remote database engine listening on port
1099 at IP Address 192.168.170.27. The second part of the URL,
“jdbc:cloudscape:db”
conveys the more typical settings using the subprotocol and database name but
this will only happen after the first section has made the connection via RMI to
the remote machine.
When you’re ready to connect to the database,
call the static method DriverManager.getConnection( ) and
pass it the database URL, the user name, and a password to get into the
database. You get back a Connection object that you can then use to query
and manipulate the database.
The following example opens a database of contact
information and looks for a person’s last name as given on the command
line. It selects only the names of people that have email addresses, then prints
out all the ones that match the given last name:
//:
c15:jdbc:Lookup.java
//
Looks up email addresses in a
//
local database using JDBC.
import
java.sql.*;
public
class
Lookup {
public
static
void
main(String[] args)
throws
SQLException, ClassNotFoundException {
String dbUrl =
"jdbc:odbc:people";
String user =
"";
String password =
"";
// Load the driver (registers
itself)
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection(
dbUrl, user, password);
Statement s = c.createStatement();
// SQL code:
ResultSet r =
s.executeQuery(
"SELECT FIRST, LAST, EMAIL
" +
"FROM people.csv people
" +
"WHERE
" +
"(LAST='"
+ args[0] + "')
" +
" AND (EMAIL Is Not Null)
" +
"ORDER BY
FIRST");
while(r.next())
{
// Capitalization doesn't
matter:
System.out.println(
r.getString("Last")
+ ",
"
+
r.getString("fIRST")
+ ":
" +
r.getString("EMAIL")
);
}
s.close(); // Also closes
ResultSet
}
}
///:~
You can see the creation of the database URL as
previously described. In this example, there is no password protection on the
database so the user name and password are empty strings.
Once the connection is made with
DriverManager.getConnection( ), you can use the resulting
Connection object to create a Statement object using the
createStatement( ) method. With the resulting Statement, you
can call executeQuery( ), passing in a string containing an SQL-92
standard SQL statement. (You’ll see shortly how you can generate this
statement automatically, so you don’t have to know much about SQL.)
The executeQuery( ) method returns a
ResultSet object, which is an iterator: the next( ) method
moves the iterator to the next record in the statement, or returns false
if the end of the result set has been reached. You’ll always get a
ResultSet object back from executeQuery( ) even if a query
results in an empty set (that is, an exception is not thrown). Note that you
must call next( ) once before trying to read any record data. If the
result set is empty, this first call to next( ) will return
false. For each record in the result set, you can select the fields using
(among other approaches) the field name as a string. Also note that the
capitalization of the field name is ignored—it doesn’t matter with
an SQL database. You determine the type you’ll get back by calling
getInt( ), getString( ), getFloat( ), etc.
At this point, you’ve got your database data in Java native format and can
do whatever you want with it using ordinary Java code.
Getting the example to work
With JDBC, understanding the code is relatively
simple. The confusing part is making it work on your particular system. The
reason this is confusing is that it requires you to figure out how to get your
JDBC driver to load properly, and how to set up a database using your database
administration software.
Of course, this process can vary radically from
machine to machine, but the process I used to make it work under 32-bit Windows
might give you clues to help you attack your own situation.
Step 1: Find the JDBC Driver
The program above contains the
statement:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
This implies a directory structure, which is
deceiving. With this particular installation of JDK 1.1, there was no file
called JdbcOdbcDriver.class, so if you looked at this example and went
searching for it you’d be frustrated. Other published examples use a
pseudo name, such as “myDriver.ClassName,” which is less than
helpful. In fact, the load statement above for the jdbc-odbc driver (the only
one that actually comes with the JDK) appears in only a few places in the online
documentation (in particular, a page labeled “JDBC-ODBC Bridge
Driver”). If the load statement above doesn’t work, then the name
might have been changed as part of a Java version change, so you should hunt
through the documentation again.
If the load statement is wrong, you’ll get an
exception at this point. To test whether your driver load statement is working
correctly, comment out the code after the statement and up to the catch
clause; if the program throws no exceptions it means that the driver is loading
properly.
Step 2: Configure the database
Again, this is specific to 32-bit Windows; you might
need to do some research to figure it out for your own platform.
First, open the control panel. You might find two
icons that say “ODBC.” You must use the one that says “32bit
ODBC,” since the other one is for backward compatibility with 16-bit ODBC
software and will produce no results for JDBC. When you open the “32bit
ODBC” icon, you’ll see a tabbed dialog with a number of tabs,
including “User DSN,” “System DSN,” “File
DSN,” etc., in which “DSN” means “Data Source
Name.” It turns out that for the JDBC-ODBC bridge, the only place where
it’s important to set up your database is “System DSN,” but
you’ll also want to test your configuration and create queries, and for
that you’ll also need to set up your database in “File DSN.”
This will allow the Microsoft Query tool (that comes with Microsoft Office) to
find the database. Note that other query tools are also available from other
vendors.
The most interesting database is one that you’re
already using. Standard ODBC supports a number of different file formats
including such venerable workhorses as DBase. However, it also includes the
simple “comma-separated ASCII” format, which virtually every data
tool has the ability to write. In my case, I just took my “people”
database that I’ve been maintaining for years using various
contact-management tools and exported it as a comma-separated ASCII file (these
typically have an extension of .csv). In the “System DSN”
section I chose “Add,” chose the text driver to handle my
comma-separated ASCII file, and then un-checked “use current
directory” to allow me to specify the directory where I exported the data
file.
You’ll notice when you do this that you
don’t actually specify a file, only a directory. That’s because a
database is typically represented as a collection of files under a single
directory (although it could be represented in other forms as well). Each file
usually contains a single table, and the SQL statements can produce results that
are culled from multiple tables in the database (this is called a join).
A database that contains only a single table (like my “people”
database) is usually called a flat-file database. Most problems that go
beyond the simple storage and retrieval of data generally require multiple
tables that must be related by joins to produce the desired results, and these
are called relational databases.
Step 3: Test the configuration
To test the configuration you’ll need a way to
discover whether the database is visible from a program that queries it. Of
course, you can simply run the JDBC program example above, up to and including
the statement:
Connection
c = DriverManager.getConnection(
dbUrl, user, password);
If an exception is thrown, your configuration was
incorrect.
However, it’s useful to get a query-generation
tool involved at this point. I used Microsoft Query that came with Microsoft
Office, but you might prefer something else. The query tool must know where the
database is, and Microsoft Query required that I go to the ODBC
Administrator’s “File DSN” tab and add a new entry there,
again specifying the text driver and the directory where my database lives. You
can name the entry anything you want, but it’s helpful to use the same
name you used in “System DSN.”
Once you’ve done this, you will see that your
database is available when you create a new query using your query tool.
Step 4: Generate your SQL query
The query that I created using Microsoft Query not
only showed me that my database was there and in good order, but it also
automatically created the SQL code that I needed to insert into my Java program.
I wanted a query that would search for records that had the last name that was
typed on the command line when starting the Java program. So as a starting
point, I searched for a specific last name, “Eckel.” I also wanted
to display only those names that had email addresses associated with them. The
steps I took to create this query were:
- Start a new query and use the Query Wizard. Select the
“people” database. (This is the equivalent of opening the database
connection using the appropriate database URL.)
- Select the “people” table within the
database. From within the table, choose the columns FIRST, LAST, and
EMAIL.
- Under “Filter Data,” choose LAST and select
“equals” with an argument of “Eckel.” Click the
“And” radio button.
- Choose EMAIL and select “Is not
Null.”
- Under “Sort By,” choose
FIRST.
The result of this query will
show you whether you’re getting what you want.
Now you can press the SQL button and without any
research on your part, up will pop the correct SQL code, ready for you to cut
and paste. For this query, it looked like this:
SELECT
people.FIRST, people.LAST, people.EMAIL
FROM
people.csv people
WHERE
(people.LAST='Eckel') AND
(people.EMAIL
Is Not Null)
ORDER
BY people.FIRST
Especially with more complicated queries it’s
easy to get things wrong, but by using a query tool you can interactively test
your queries and automatically generate the correct code. It’s hard to
argue the case for doing this by hand.
Step 5: Modify and paste in your query
You’ll notice that the code above looks
different from what’s used in the program. That’s because the query
tool uses full qualification for all of the names, even when there’s only
one table involved. (When more than one table is involved, the qualification
prevents collisions between columns from different tables that have the same
names.) Since this query involves only one table, you can optionally remove the
“people” qualifier from most of the names, like this:
SELECT
FIRST, LAST, EMAIL
FROM
people.csv people
WHERE
(LAST='Eckel') AND
(EMAIL
Is Not Null)
ORDER
BY FIRST
In addition, you don’t want this program to be
hard coded to look for only one name. Instead, it should hunt for the name given
as the command-line argument. Making these changes and turning the SQL statement
into a dynamically-created String produces:
"SELECT
FIRST, LAST, EMAIL " +
"FROM
people.csv people " +
"WHERE
" +
"(LAST='"
+ args[0] + "')
" +
"
AND (EMAIL Is Not Null) "
+
"ORDER
BY FIRST");
SQL has another way to insert names into a query
called stored procedures, which is used for speed. But for much of your
database experimentation and for your first cut, building your own query strings
in Java is fine.
You can see from this example that by using the tools
currently available—in particular the query-building tool—database
programming with SQL and JDBC can be quite straightforward.
A GUI version of the lookup
program
It’s more useful to leave the lookup program
running all the time and simply switch to it and type in a name whenever you
want to look someone up. The following program creates the lookup program as an
application/applet, and it also adds name completion so the data will show up
without forcing you to type the entire last name:
//:
c15:jdbc:VLookup.java
//
GUI version of Lookup.java.
//
<applet code=VLookup
//
width=500 height=200></applet>
import
javax.swing.*;
import
java.awt.*;
import
java.awt.event.*;
import
javax.swing.event.*;
import
java.sql.*;
import
com.bruceeckel.swing.*;
public
class
VLookup
extends
JApplet {
String dbUrl =
"jdbc:odbc:people";
String user =
"";
String password =
"";
Statement s;
JTextField searchFor =
new
JTextField(20);
JLabel completion =
new
JLabel("
");
JTextArea results =
new
JTextArea(40, 20);
public
void
init() {
searchFor.getDocument().addDocumentListener(
new
SearchL());
JPanel p =
new
JPanel();
p.add(new
Label("Last name to search
for:"));
p.add(searchFor);
p.add(completion);
Container cp = getContentPane();
cp.add(p, BorderLayout.NORTH);
cp.add(results, BorderLayout.CENTER);
try
{
// Load the driver (registers
itself)
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection(
dbUrl, user, password);
s = c.createStatement();
}
catch(Exception
e) {
results.setText(e.toString());
}
}
class
SearchL
implements
DocumentListener {
public
void
changedUpdate(DocumentEvent e){}
public
void
insertUpdate(DocumentEvent e){
textValueChanged();
}
public
void
removeUpdate(DocumentEvent e){
textValueChanged();
}
}
public
void
textValueChanged() {
ResultSet r;
if(searchFor.getText().length()
== 0) {
completion.setText("");
results.setText("");
return;
}
try
{
// Name
completion:
r = s.executeQuery(
"SELECT LAST FROM people.csv people
" +
"WHERE (LAST Like
'" +
searchFor.getText() +
"%') ORDER BY
LAST");
if(r.next())
completion.setText(
r.getString("last"));
r = s.executeQuery(
"SELECT FIRST, LAST, EMAIL
" +
"FROM people.csv people
" +
"WHERE
(LAST='" +
completion.getText() +
"') AND (EMAIL Is Not Null)
" +
"ORDER BY
FIRST");
}
catch(Exception
e) {
results.setText(
searchFor.getText() +
"\n");
results.append(e.toString());
return;
}
results.setText("");
try
{
while(r.next())
{
results.append(
r.getString("Last")
+ ",
"
+
r.getString("fIRST")
+
":
" +
r.getString("EMAIL")
+
"\n");
}
}
catch(Exception
e) {
results.setText(e.toString());
}
}
public
static
void
main(String[] args) {
Console.run(new
VLookup(), 500, 200);
}
}
///:~
Much of the database logic is the same, but you
can see that a DocumentListener is added to listen to the
JTextField (see the javax.swing.JTextField entry in the Java HTML
documentation from java.sun.com for details), so that whenever you type a
new character it first tries to do a name completion by looking up the last name
in the database and using the first one that shows up. (It places it in the
completion JLabel, and uses that as the lookup text.) This way, as
soon as you’ve typed enough characters for the program to uniquely find
the name you’re looking for, you can stop.
When you browse the online documentation for JDBC it
can seem daunting. In particular, in the DatabaseMetaData
interface—which is just huge, contrary to most of the interfaces you see
in Java—there are methods such as
dataDefinitionCausesTransactionCommit( ),
getMaxColumnNameLength( ), getMaxStatementLength( ),
storesMixedCaseQuotedIdentifiers( ),
supportsANSI92IntermediateSQL( ),
supportsLimitedOuterJoins( ), and so on. What’s this all
about?
As mentioned earlier, databases have seemed from their
inception to be in a constant state of turmoil, primarily because the demand for
database applications, and thus database tools, is so great. Only recently has
there been any convergence on the common language of SQL (and there are plenty
of other database languages in common use). But even with an SQL
“standard” there are so many variations on that theme that JDBC must
provide the large DatabaseMetaData interface so that your code can
discover the capabilities of the particular “standard” SQL database
that it’s currently connected to. In short, you can write simple,
transportable SQL, but if you want to optimize speed your coding will multiply
tremendously as you investigate the capabilities of a particular vendor’s
database.
This, of course, is not Java’s fault. The
discrepancies between database products are just something that JDBC tries to
help compensate for. But bear in mind that your life will be easier if you can
either write generic queries and not worry quite as much about performance, or,
if you must tune for performance, know the platform you’re writing for so
you don’t need to write all that investigation code.
A more sophisticated example
A more interesting example
involves a multitable database that resides on a server.
Here, the database is meant to provide a repository for community activities and
to allow people to sign up for these events, so it is called the Community
Interests Database (CID). This example will only provide an overview of the
database and its implementation, and is not intended to be an in-depth tutorial
on database development. There are numerous books, seminars, and software
packages that will help you in the design and development of a database.
In addition, this example presumes the prior
installation of an SQL database on a server (although it could also be run on a
local machine), and the interrogation and discovery of an appropriate JDBC
driver for that database. Several free SQL databases are available, and some are
even automatically installed with various flavors of Linux. You are responsible
for making the choice of database and locating the JDBC driver; the example here
is based on an SQL database system called “Cloudscape.”
To keep changes in the connection information simple,
the database driver, database URL, user name, and password are placed in a
separate class:
//:
c15:jdbc:CIDConnect.java
//
Database connection information for
//
the community interests database (CID).
public
class
CIDConnect {
// All the information specific to
CloudScape:
public
static
String dbDriver =
"COM.cloudscape.core.JDBCDriver";
public
static
String dbURL =
"jdbc:cloudscape:d:/docs/_work/JSapienDB";
public
static
String user =
"";
public
static
String password =
"";
}
///:~
In this example, there is no password protection on
the database so the user name and password are empty strings.
The database consists of a set of tables that have a
structure as shown here:
“Members” contains community member
information, “Events” and “Locations” contain
information about the activities and where they take place, and
“Evtmems” connects events and members that would like to attend that
event. You can see that a data member in one table produces a key in another
table.
The following class contains the SQL strings that will
create these database tables (refer to an SQL guide for an explanation of the
SQL code):
//:
c15:jdbc:CIDSQL.java
//
SQL strings to create the tables for the CID.
public
class
CIDSQL {
public
static
String[] sql = {
// Create the MEMBERS
table:
"drop table
MEMBERS",
"create table MEMBERS
" +
"(MEM_ID INTEGER primary key,
" +
"MEM_UNAME VARCHAR(12) not null
unique, "+
"MEM_LNAME VARCHAR(40),
" +
"MEM_FNAME VARCHAR(20),
" +
"ADDRESS VARCHAR(40),
" +
"CITY VARCHAR(20),
" +
"STATE CHAR(4),
" +
"ZIP CHAR(5),
" +
"PHONE CHAR(12),
" +
"EMAIL
VARCHAR(30))",
"create unique index
" +
"LNAME_IDX on
MEMBERS(MEM_LNAME)",
// Create the EVENTS
table
"drop table
EVENTS",
"create table EVENTS
" +
"(EVT_ID INTEGER primary key,
" +
"EVT_TITLE VARCHAR(30) not null,
" +
"EVT_TYPE VARCHAR(20),
" +
"LOC_ID INTEGER,
" +
"PRICE DECIMAL,
" +
"DATETIME
TIMESTAMP)",
"create unique index
" +
"TITLE_IDX on
EVENTS(EVT_TITLE)",
// Create the EVTMEMS
table
"drop table
EVTMEMS",
"create table EVTMEMS
" +
"(MEM_ID INTEGER not null,
" +
"EVT_ID INTEGER not null,
" +
"MEM_ORD
INTEGER)",
"create unique index
" +
"EVTMEM_IDX on EVTMEMS(MEM_ID,
EVT_ID)",
// Create the LOCATIONS
table
"drop table
LOCATIONS",
"create table LOCATIONS
" +
"(LOC_ID INTEGER primary key,
" +
"LOC_NAME VARCHAR(30) not null,
" +
"CONTACT VARCHAR(50),
" +
"ADDRESS VARCHAR(40),
" +
"CITY VARCHAR(20),
" +
"STATE VARCHAR(4),
" +
"ZIP VARCHAR(5),
" +
"PHONE CHAR(12),
" +
"DIRECTIONS
VARCHAR(4096))",
"create unique index
" +
"NAME_IDX on
LOCATIONS(LOC_NAME)",
};
}
///:~
The following program uses the CIDConnect and
CIDSQL information to load the JDBC driver, make a connection to the
database, and then create the table structure diagrammed above. To connect with
the database, you call the static method
DriverManager.getConnection( ), passing it the database URL, the
user name, and a password to get into the database. You get back a
Connection object that you can use to query and manipulate the database.
Once the connection is made you can simply push the SQL to the database, in this
case by marching through the CIDSQL array. However, the first time this
program is run, the “drop table” command will fail, causing an
exception, which is caught, reported, and then ignored. The reason for the
“drop table” command is to allow easy experimentation: you can
modify the SQL that defines the tables and then rerun the program, causing the
old tables to be replaced by the new.
In this example, it makes sense to let the exceptions
be thrown out to the console:
//:
c15:jdbc:CIDCreateTables.java
//
Creates database tables for the
//
community interests database.
import
java.sql.*;
public
class
CIDCreateTables {
public
static
void
main(String[] args)
throws
SQLException, ClassNotFoundException,
IllegalAccessException {
// Load the driver (registers
itself)
Class.forName(CIDConnect.dbDriver);
Connection c = DriverManager.getConnection(
CIDConnect.dbURL, CIDConnect.user,
CIDConnect.password);
Statement s = c.createStatement();
for(int
i = 0; i < CIDSQL.sql.length; i++) {
System.out.println(CIDSQL.sql[i]);
try
{
s.executeUpdate(CIDSQL.sql[i]);
}
catch(SQLException
sqlEx) {
System.err.println(
"Probably a 'drop table'
failed");
}
}
s.close();
c.close();
}
}
///:~
Note that all changes in the database can be
controlled by changing Strings in the CIDSQL table, without
modifying CIDCreateTables.
executeUpdate( ) will usually return the
number of rows that were affected by the SQL statement.
executeUpdate( ) is more commonly used to execute
INSERT,
UPDATE, or
DELETE
statements that modify one or more rows. For statements such as
CREATE
TABLE, DROP
TABLE, and
CREATE
INDEX, executeUpdate( ) always
returns zero.
To test the database, it is loaded with some sample
data. This requires a series of
INSERTs
followed by a
SELECT to
produce result set. To make additions and changes to the test data easy, the
test data is set up as a two-dimensional array of Objects, and the
executeInsert( ) method can then use the information in one row of
the table to create the appropriate SQL command.
//:
c15:jdbc:LoadDB.java
//
Loads and tests the database.
import
java.sql.*;
class
TestSet {
Object[][] data = {
{
"MEMBERS",
new
Integer(1),
"dbartlett",
"Bartlett",
"David",
"123 Mockingbird
Lane",
"Gettysburg",
"PA",
"19312",
"123.456.7890",
"bart@you.net"
},
{
"MEMBERS",
new
Integer(2),
"beckel",
"Eckel",
"Bruce",
"123 Over Rainbow
Lane",
"Crested
Butte",
"CO",
"81224",
"123.456.7890",
"beckel@you.net"
},
{
"MEMBERS",
new
Integer(3),
"rcastaneda",
"Castaneda",
"Robert",
"123 Downunder
Lane",
"Sydney",
"NSW",
"12345",
"123.456.7890",
"rcastaneda@you.net"
},
{
"LOCATIONS",
new
Integer(1),
"Center for
Arts",
"Betty
Wright",
"123 Elk
Ave.",
"Crested
Butte",
"CO",
"81224",
"123.456.7890",
"Go this way then
that." },
{
"LOCATIONS",
new
Integer(2),
"Witts End Conference
Center",
"John
Wittig",
"123 Music
Drive",
"Zoneville",
"PA",
"19123",
"123.456.7890",
"Go that way then
this." },
{
"EVENTS",
new
Integer(1),
"Project Management
Myths",
"Software
Development",
new
Integer(1),
new
Float(2.50),
"2000-07-17
19:30:00" },
{
"EVENTS",
new
Integer(2),
"Life of the Crested
Dog",
"Archeology",
new
Integer(2),
new
Float(0.00),
"2000-07-19
19:00:00" },
// Match some people with
events
{
"EVTMEMS",
new
Integer(1), // Dave is going
to
new
Integer(1), // the Software
event.
new
Integer(0) },
{
"EVTMEMS",
new
Integer(2), // Bruce is going
to
new
Integer(2), // the Archeology
event.
new
Integer(0) },
{
"EVTMEMS",
new
Integer(3), // Robert is going
to
new
Integer(1), // the Software
event.
new
Integer(1) },
{
"EVTMEMS",
new
Integer(3), // ... and
new
Integer(2), // the Archeology
event.
new
Integer(1) },
};
// Use the default data
set:
public
TestSet() {}
// Use a different data
set:
public
TestSet(Object[][] dat) { data = dat; }
}
public
class
LoadDB {
Statement statement;
Connection connection;
TestSet tset;
public
LoadDB(TestSet t)
throws
SQLException {
tset = t;
try
{
// Load the driver (registers
itself)
Class.forName(CIDConnect.dbDriver);
}
catch(java.lang.ClassNotFoundException
e) {
e.printStackTrace(System.err);
}
connection = DriverManager.getConnection(
CIDConnect.dbURL, CIDConnect.user,
CIDConnect.password);
statement = connection.createStatement();
}
public
void
cleanup()
throws
SQLException {
statement.close();
connection.close();
}
public
void
executeInsert(Object[] data) {
String sql = "insert into
"
+ data[0] + "
values(";
for(int
i = 1; i < data.length; i++) {
if(data[i]
instanceof
String)
sql +=
"'"
+ data[i] +
"'";
else
sql += data[i];
if(i
< data.length - 1)
sql += ",
";
}
sql += ')';
System.out.println(sql);
try
{
statement.executeUpdate(sql);
}
catch(SQLException
sqlEx) {
System.err.println("Insert
failed.");
while
(sqlEx !=
null)
{
System.err.println(sqlEx.toString());
sqlEx = sqlEx.getNextException();
}
}
}
public
void
load() {
for(int
i = 0; i< tset.data.length; i++)
executeInsert(tset.data[i]);
}
// Throw exceptions out to
console:
public
static
void
main(String[] args)
throws
SQLException {
LoadDB db =
new
LoadDB(new
TestSet());
db.load();
try
{
// Get a ResultSet from the loaded
database:
ResultSet rs = db.statement.executeQuery(
"select
" +
"e.EVT_TITLE, m.MEM_LNAME,
m.MEM_FNAME "+
"from EVENTS e, MEMBERS m, EVTMEMS
em " +
"where em.EVT_ID = 2
" +
"and e.EVT_ID = em.EVT_ID
" +
"and m.MEM_ID =
em.MEM_ID");
while
(rs.next())
System.out.println(
rs.getString(1) + "
" +
rs.getString(2) + ",
" +
rs.getString(3));
}
finally
{
db.cleanup();
}
}
}
///:~
The TestSet class contains a default set of
data that is produced if you use the default constructor; however, you can also
create a TestSet object using an alternate data set with the second
constructor. The set of data is held in a two-dimensional array of Object
because it can be any type, including String or numerical types. The
executeInsert( ) method uses RTTI to distinguish between
String data (which must be quoted) and non-String data as it
builds the SQL command from the data. After printing this command to the
console, executeUpdate( ) is used to send it to the database.
The constructor for LoadDB makes the
connection, and load( ) steps through the data and calls
executeInsert( ) for each record. cleanup( ) closes the
statement and the connection; to guarantee that this is called, it is placed
inside a finally clause.
Once the database is loaded, an
executeQuery( ) statement produces a sample result set. Since the
query combines several tables, it is an example of a join.
There is more JDBC information available in the
electronic documents that come as part of the Java distribution from Sun. In
addition, you can find more in the book JDBC Database Access with Java
(Hamilton, Cattel, and Fisher, Addison-Wesley, 1997). Other JDBC books
appear regularly.
|
