jdbcDemos.htm
05-06-2002 Bikle
-----------------

Some JDBC Demos

This page contains a few simple JDBC demos.  Several months ago I
wrote some JSP pages which used JDBC to connect to an Oracle database.
Since then, I frequently returned to those pages to guide my syntax as
I wrote other Java programs.  I decided it would be a good idea to
write up some simple demos to help me in the future.  JDBC syntax is
not the easiest thing to memorize so it is nice to have some simple
demos in a convenient location.

The first demo is linked below: 

jdbcCreateTableDemo-java.txt

Hopefully the comments in the above program make it a stand-alone
instructional device.  The only comment I might add is that I do most
of my software development in emacs.  Emacs is well suited for working
with rectangular pieces of text.  I like rectangular pieces of text
in my code; I think they make my code easier to read.  

The above demo shows how to connect to both Oracle and MySQL.

One enhancement I should make to the above demo is the addition of
connection techniques to all the major types of databases: SQL Server,
DB2, Sybase, Postgres, Informix, and Hypersonic SQL.  

Another nice addition would be a demo of how to move the connection
syntax into another .java file or make use of a properties file.  In
most environments, this is well worth the effort.  This allows me to
specify the type of database I'm using in only one location.  If I
decide that one week I want to use Oracle and the next week I want to
use Postgres, I'd only need to make a minimal change to my software in
just one place.

Also, I'll point out that I depend on a couple of JDBC driver files to
connect to either Oracle or MySQL.

For MySQL I use mysql204.jar which I downloaded from here:

http://www.mysql.com/downloads/download.php?file=Downloads/Contrib/

For Oracle I use classes12.zip which I downloaded from here:

http://download.oracle.com/otn/utilities_drivers/jdbc/9201/classes12.zip

Once the reader gets the above demo to run, he/she should have a table
in place which is ready to accept INSERT commands.  The demo below shows
how to issue INSERT commands:

jdbcInsertDemo-java.txt

Once the reader gets the above demo to run, he/she should have a table
in place which is ready to accept UPDATE commands.  The demo below shows
how to issue UPDATE commands:

jdbcUpdateDemo-java.txt

The next demo shows how to issue a DELETE command:

jdbcDeleteDemo-java.txt

The next demo shows how to issue a DROP TABLE command:

jdbcDropTableDemo-java.txt

All of the above demos are straightforward and easy to understand.
Issuing SELECT statements via JDBC is a bit more complex.  A SELECT
statement asks the database for data.  When the database returns it
we need to do something with it.  So, that is one reason why the demos
below are more complicated than the above demos.

The demo below shows the bare minimum of issuing a SELECT statement
and then dealing with the data returned from the database:

jdbcSelectDemo-java.txt

The above demo shows a couple of important concepts.  The first
concept is use of a Java object named a "Prepared Statement".  A
Prepared Statement allows me to make use of bind variables in my WHERE
clauses.  

I know of a large production JSP application which does not make use
of Prepared Statements (and thus bind variables).  All SELECT
statements sent to the database are unique (since they contain no bind
variables).  The back end database for this application is Oracle
8.1.7.  The Java developers for this application lacked adequate
training; they did not know about the use of Prepared Statement objects.

The database reacted to all of these unique SELECT statements by
trying to cache all of them in its SQL cache.  Of course, the SQL
cache filled up and performance became very poor.  Fortunately, Oracle
software allows the DBA to deal with this situation.  If the DBA sets
the init.ora parameter CURSOR_SHARING=FORCE, then the Oracle kernel
will keep a close eye on its SQL cache.  It is sophisticated enough to
collapse many SQL statements into just one stored copy in the SQL
cache (assuming they only differ in the values of pseudo bind
variables).

The next important concept illustrated by the above demo is use of
an object named the "ResultSet".  This object is aptly named.  The
developer may conceptualize it as a container of data which has been
filled by the database in response to a SELECT command.

The demo below demonstrates how to access some useful information
which resides inside an object of type "ResultSetMetaData".
Specifically the object tells me how many columns are in the rows of
data returned in the ResultSet object.  I make use of this information
inside of a nested loop while looping through the rows in the
ResultSet object:

jdbcSelectDemo2-java.txt

The demo below demonstrates combine some simple HTML with the data
in the ResultSet object:

jdbcSelectDemo3-java.txt

The demo below extends the demo above; it shows how to get column
names out of the ResultSetMetaData object and combine them with
some HTML and data from the ResultSet object:

jdbcSelectDemo4-java.txt

The last demo is useful if you want to send a simple SELECT with
no WHERE clause (and thus no bind variables to worry about):

jdbcSelectDemo5-java.txt