InstantDB Tips & Tricks
and "the Hello World of JDBC"
By Peter Hearty, Lutris Technologies UK Ltd.
This article is one of an intermittent series of "tips and
tricks" articles on Java database programming in general and
InstantDB in particular. Sometimes there’ll be something for absolute
beginners; sometimes there’ll be something for more advanced users
and occasionally, a little bit of both.
OK. You’ve downloaded the InstantDB .zip or .tar.gz file, you’ve
followed the instructions and, with a bit of luck, a whole load
of stuff has gone wizzing off the top of the screen. So, flushed
with success, you sit staring at the screen and think “Now what?”.
Well, lets start by reviewing what happened when you typed the
command:
java org.enhydra.instantdb.ScriptTool
The Java interpreter will have searched through your class path
and found the class: org.enhydra .instantdb.ScriptTool in the file
idbexmpl.jar. It will then have loaded and started running the class
at its Main method.
The ScriptTool program is nothing more than a simple interpreter.
It opens a file, starts reading commands from the file and as a
result will normally make calls to the Java DataBase Connectivity
(JDBC) API. The default ScriptTool command file supplied in the
InstantDB Examples directory (called sql1.txt) tells ScriptTool
to do the following:
1. load the InstantDB
JDBC driver;
2. open an InstantDB
database;
3. send some Structured
Query Language (SQL) commands to that database.
Although ScriptTool is aware of some of the special features of
InstantDB, it could in fact be quite happily used with any relational
database that has a JDBC driver, i.e. nearly all of them. We’re
not going to dwell too much on the intricacies of ScriptTool in
this article (that’s for a later day). The main point is that any
Java program can fairly easily access any Relational Database provided
there is a suitable JDBC driver available. The JDBC driver is the
“glue” that binds a Java database program to an actual database.
The ScriptTool program is a very simple example of a JDBC program.
However it’s also quite large and it can be a little difficult to
see what it’s doing at times. Instead, we’re going to write a much
smaller program that will show you just how easy it really is to
write a database program in Java.
Traditionally, when introducing a new programming language or API
(or at least, since Brian Kernighan’s famous “Hello World” C example),
we begin with the simplest possible program. Figure 1 shows just
about the simplest functioning JDBC program that you can write.
Unfortunately, it doesn’t say “Hello World”, so my apologies to
Mr. Kernighan.

Figure
1 - The "Hello World" Program
Let’s examine the above program line by line.
import java.sql.*;
In order to talk to databases we need to have access to the JDBC
API. This is defined in the java.sql package which is imported above.
There then follows the usual syntactic elements needed to start
a Java class and to define its Main method.
try {
...
} catch (Exception ex) {}
Nearly all of the JDBC API calls can generate java.sql.SQLException
exceptions. We must be ready to catch these wherever possible. If
we don’t, then out program will exit without closing its connection.
Depending on the database, this might be undesirable. Here, we catch
all exceptions, but do nothing with them.
Class.forName ("org.enhydra.instantdb.jdbc.idbDriver");
Now we get on to something a little more interesting. Except that,
on the face of it, the above method invocation doesn’t seem to do
anything at all! We would normally call Class.forName when we want
to be able to find the java.lang.Class object for a given named
class. The above line of code seems to do this and then just throw
it away. So why do we need it?
The trick is, that in order to use a java.lang.Class object, the
class itself must be loaded. Our call to Class.forName ensures that
the class in brackets will actually get loaded into the Java Virtual
Machine. This is vital for what happens next. Every JDBC driver
has a Class constructor that adds the driver to java.sql.DriverManager’s
list of known JDBC drivers. So what the above call has achieved
is actually very simple. It has found the class: org.enhydra.instantdb.jdbc.idbDriver,
which is InstantDB’s JDBC driver; loaded it into memory, and, thanks
to idbDriver’s Class constructor, the driver has been registered
with DriverManager.
If you wanted to talk to databases other than InstantDB you would
load a different driver at this point.
Connection con = DriverManager.getConnection("jdbc:idb:c:/InstantDB/Examples/sample.prp");
This is the line of code which obtains a Connection to the database.
You need a java.sql.Connection object in order to talk to a database
from Java. The above line is giving the name of a database (the
bit in brackets) to DriverManager and asking it to see if one of
its registered JDBC drivers recognises the name. DriverManager simply
asks each JDBC driver in turn “is this one of your databases?”,
and each driver either says “no” or proceeds to create a connection
to the database.
But how do drivers know which databases belong to them? That’s
where the database naming convention comes in. All database names
in java begin with the url prefix “jdbc:” (a “url” is just another
fancy name for a... well... “name”. This is much the same as the
way that all web sites begin with “http:” and all FTP connections
begin with “ftp:”).
What follows after the “jdbc:” is the “type” of the database. It
is this database type which drivers use to identify their own particular
databases. All InstantDB databases have the type “idb:”, so the
InstantDB database driver will recognise the above url as the name
of one of its databases.
What happens next is very much database specific. For some databases
the url has to specify things like what machine the database is
on, which port to connect to it on, and so on. In the case of InstantDB
the bit after “jdbc:idb:” is just treated as a file name. We’ll
examine the contents of InstantDB’s .prp files in detail in a later
article. For now, all we need to know is that it tells InstantDB
everything that it needs to know about the database it’s connecting
to. If the database doesn’t actually exist, then InstantDB will
create it.
By the time the driver manager returns with a Connection object,
InstantDB will have: opened (or created) the database specified
in c:/InstantDB/Examples/sample.prp; issued its copyright banner
to standard out, and be waiting to receive commands.
Statement stmt = con.createStatement ();
In order to issue commands to the database we first have to create
a java.sql.Statement object. We can get one of these just by asking
the database connection to create one. That’s what’s happening in
the line above. Once we have a statement, we’ll be ready to ask
the database to do things for us.
stmt.execute ("DROP TABLE tmp");
Here, we’re finally telling the database to do something. In a
relational database, data is stored in tables. Each table
consists of rows and columns of data, very similar
to a spreadsheet. The above statement is simply telling InstantDB
to delete the table called “tmp”. If it exists then the table will
be deleted and all of the data in all of the rows and columns in
that table will be lost. If the table does not exist, then InstantDB
will just ignore the command.
stmt.close();
con.close();
And that’s just about it, except that, since we’re well mannered
programmers, we’ll return all of the resources we’ve been using
to the database. The above lines allow InstantDB to get rid of any
objects that it doesn’t need any more. In addition, InstantDB will
spot that the one and only connection to the database has been closed
down and so will close the database itself.
Create a file called Simple.java and cut and paste the above program.
You’ll have to edit the line that provides the database url to point
to the sample.prp file provided in the InstantDB Examples directory.
Compile and run and, voila, you’ll have your very first database
program!
Next time, we’ll expand on what we’ve done in this article and
show you how to migrate your data from other databases into InstantDB.
|