This TechNote presents working Java code
for accessing relational databases using JDBC and SQL.
Download the compressed source code for this example here.
Connecting to relational databases with JDBC is really very
simple. The emphasis in this TechNote is on getting you started, and
giving you a template that you can reuse and customize as you learn
how to manipulate databases with Java. Once you understand this
example, you'll have no trouble figuring out how to do what you need.
When you're ready for all the details, JavaSoft's JDBC documentation has them.
Of all the things you have to think about,
getting Java connected to your relational database is the least of your worries.
SqlServer shows you how to do it.
It loads a JDBC driver, which forges your database connection for you.
It also has several methods that process the common SQL statements
in a safe and simple way:
connections are opened and closed with every SQL statement.
If you're building applications that are expected to have high transaction rates,
other strategies may work better.
For example,
if you expect to perform several related updates on a record
once you have accessed it,
you will probably prefer to hold one open database connection
through all of those updates.
Because a database connection is an object,
just keep it in a variable for as long as you need it.
Your application will be able to process those updates much more quickly,
but with the risk that other users may not be able to do any work
until the users currently holding connections are finished.
The example assumes you have a database available to use. JDBC
drivers exist for many current databases, with more coming all the
time. The one used here is called a JDBC/ODBC Bridge, which allows
you to connect to just about any database in existence. All you need
is the ODBC part, which is widely available. You can even use
textfiles as your database if your drivers were installed correctly.
Furthermore, some IDE products on the market are delivered with small
but powerful SQL database systems. Development of this TechNote was
done with SQL Anywhere from Sybase.
The schema for this program sample is simple: three fields called
name, address, and phone in a table called Person..
All fields hold strings.
name and address are each 32 characters long; phone is 12.
The database itself is called Persons.
Once you have learned the basic ideas presented here,
you can easily write programs that handle much more complicated schemas.
If you're coming from a relational background,
you'll be pleased with this applet's simplicity.
There are methods that help to generate SQL from simple Query-by-Example forms.
These methods are found in the class SqlPerson,
which is an extension of an underlying Person class.
These methods don't build entire SQL statements,
they build some of the common subordinate clauses of SQL,
which increases their utility.
You'll notice that they're extremely regular in structure,
so you can easily reuse the coding pattern in your own applications.
Don't write code when you can steal it from a Netscape TechNote!
How This Example Works
The first order of business is to load the JDBC driver
that works with your database.
SqlServer.java contains this line of code
in the method loadDriver():
(In the actual code, the string is passed as a parameter from the applet;
it has been literalized here for clarity.)
If everything goes well, this is all you have to do to load the driver.
There is a little more code in this method to handle exceptions
if something goes wrong, which, frankly, isn't likely.
Once the driver is loaded, you can connect to your database.
This common line in the method executeQuery(poString) makes the connection.
oConnection = DriverManager.getConnection("jdbc:odbc:Persons",
"username",
"password");
(Once again, the strings have been literalized here for clarity.)
In your code, be sure to pass the appropriate "username"
and "password" strings for your own database. A word
of warning: the jdbc strings used in Class.forName()
and DriverManager.getConnection() look very similar; be sure to
note that the first is delimited with periods, the second with colons.
The string used in Class.forName() identifies a particular Java
class in a library; the string used in DriverManager.getConnection()
is actually a URL. If you use the wrong delimiters, nothing works.
Now we're ready to talk to the database.
First, create an SQL statement object with this line.
This makes an SQL statement object, which gives you SQL capability.
In the method executeQuery(poString),
this line actually executes the SQL that was passed in as a parameter.
oResult now contains a ResultSet object.
By enumerating oResult,
a Vector of SqlPersons is constructed and returned.
SqlApplet is instantiated as your user interface,
but most of the work is carried out by its superclass, DbApplet.
This UI is simple and adequate, but it won't win any design awards.
The amount of code devoted to UI was intentionally minimized in this TechNote.
Good Object Oriented Stuff
In the class SqlPerson,
there are methods to create SET, VALUES, and WHERE clauses that are specific to Person.
These methods are used by the class SqlAssistant.
If you want to perform SQL on databases that represent other classes,
you only need to write sqlSetString(),
sqlValuesString(),
sqlWhereString(),
and a related input method called sqlReadResult() for those classes.
sqlReadResult() takes data from its argument, a JDBC ResultSet, and reconstructs objects from that data.
Because SqlAssistant works with any object that implements these methods,
they are specified as an interface, SqlObject.
In these examples,
the code is formatted to maximize your ability to reuse their structure in your applications.
Here is a complete list of the files that were used to build this example.
Compile and run them on your own machine, and connect to an appropriate database.
A future TechNote will show you how to construct two- and three-tiered architectures,
based on the code shown here.
Interfaces used in both examples
NamedObject.java is needed by objects displayed by a ListApplet.
ObjectWithUI.java is needed by objects that provide a GUI to a containing applet.
DbManager.java specifies the messages needed by the back end of a DbApplet.
Objects used in both examples
Person.java is the basic data object that is stored and retrieved in these examples.
Applets used in both examples
ObjectApplet.java provides a common interface for composable GUI applets.
PersonApplet.java is the basic GUI for a Person.
ListApplet.java is a generic GUI that displays a list and a detail view.
UpdateApplet.java is a generic GUI that provides "before" and "after" views.
AppletFrame.java is a generic wrapper for external windows.
DbApplet.java is a simple and generic front end for a database.
Interfaces used in this example
SqlObject.java specifies the messages needed to generate SQL automatically.
Objects used in this example
SqlAssistant.java is a utility class that helps construct SQL.
SqlPerson.java implements the messages specified in the SqlObject interface.
SqlServer.java executes SQL statements.
SqlManager.java composes SQL statements and handles their results.
Applets used in this example
SqlApplet.java supplies a small amount of extra information to a DbApplet.
Files used to build this example
make.bat compiles the sources in the context of this directory structure.
sql.html specifies the applet and its arguments.
view.bat runs the applet in the appletviewer.
Download the compressed source code for this example here.
TN-JAVA-02-9704
- Related Reading:
Any sample code included above is provided for your use on an "AS IS" basis, under the Netscape License Agreement - Terms of Use
