Introduction to Databases for the Web: Pt 4

Monday Nov 23rd 1998 by Selena Sol
Share:

The conclusion of this introductory series discusses using Java to access Web databases.

JDBC

To tell you the truth, if I were to recommend a backend architecture for a web-based database interface, I would probably recommend the CGI architecture we just discussed.

In my opinion and experience, CGI is still the most stable, cross-platform, easy to maintain method for managing such a mission critical application. Alternative technologies like CORBA, Java, Active-X, and Cold Fusion are too difficult, too proprietary, or too buggy.

Nevertheless, no discussion of Web Databases would be complete without a discussion of JDBC.

What is JDBC

JDBC is Java's version of the DBI module in Perl 5. It provides a standard interface between you and the database server.

As with DBI, JDBC provides a standard API which you can use regardless of what Database is being used in the backend. Also, just as DBI is supported by a host of DBD (Database Dependent) modules to connect DBI to the multitude of proprietary systems, JDBC is supported by a large set of JDBC drivers.

[JDBC Architecture]
Click for full size image

Fortunately, as in the case of DBI, the JDBC interface and most of the JDBC drivers you will need are installed by default so you already have them if you have installed the JDK. Drivers that are not installed by default can be downloaded by going to www.javasoft.com/products/jdbc/index.html.

This tutorial assumes that you are already familiar with Java programming. If you are not, you may want to read Intro to Web Programming.

Limitations of JDBC

Of course, if you have been keeping up with the news, you will know that Java is still somewhat buggy and slow. However, the language promises to be with us as a basic tool for many years to come.

And though it still is a bit painful to use Java in an internet setting in which one cannot control the browsers, operating systems, or hardware specs of the users, it can still be worthwhile for you to learn and play with it.

At very least, JDBC is extremely useful for server-side application development in which you can control the environment to a much greater degree.

So to conclude this tutorial on web databases, we will take a walk through JDBC technology and present an actual Java GUI that uses JDBC to connect to the datasource we installed for the CGI section.

JDBC Application Environment Setup

Assuming that you have already been working through this tutorial, you will already be set to run a JDBC application. However, before we get into the nitty gritty, let's do a recap.

Installing a Database

The first thing you will need, of course is an actual running database with a table that you can query and modify. In the SQL and CGI sections, we explained how to setup an MSAccess database and create a test table. We will continue to use that table and database here. In particular, we will use the CUSTOMERS table from the Test_db.mdb database that we gave a datasource name of "Access" in our 32-bit ODBC control panel.

Installing the JDK

You will also need to install the JDK. I recommend installing JDK 1.17, but that you code your application to the JDK 1.02 standard. That way your application will be accessible to a wider audience of web browsers, many of who may not have browsers that support JDK 1.1 APIs.

As we said before, most of the JDBC drivers you will need are installed by default so you already have them when you install the JDK. Drivers that are not installed by default can be downloaded by going to www.javasoft.com/products/jdbc/index.html. You will install them in the "lib" directory in the Java files tree. I put mine in "c:\java\sun\jdk1.1.7\lib\sun\jdbc\".

Installing a Local Web Server

Finally, you will need a web server to serve your Java application. In the CGI section, we discussed how you could setup the Sambar Web Server on your local workstation. Using a local web server means that you can do all your development and testing locally and then transfer the final application to the web server on the internet. This section assumes you have already installed Sambar and the rest.

Using JDBC to Connect to a Database

Once your environment is all set up, you are ready to start sending SQL commands to your database using JDBC. To do so, you will need to first connect to the database, which is a fairly simple process. Specifically, you will need to load the database driver and then request a connection.

These two things are achieved with the following lines of code:

Class.forName([LOCATION OF DRIVER]);
Connection jdbcConnection = 
      DriverManager.getConnection
      ([LOCATION OF DATASOURCE]);

For example, to connect to my Access (I specified the "Access" DataSource Name in the 32-bit ODBC control panel. The name "Access" corresponds to my Test_db.mdb file created by MsAccess) datasource I use the following:

Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
Connection jdbcConnection = 
   DriverManager.getConnection
   (jdbc:odbc:Access);

Note that the JdbcOdbcDriver is part of the basic JDK distribution (You can find it in the classes.zip file in the "lib" directory probably). If you are using a driver other than ODBC, you should check to see what syntax it requires for specifying data source name. mSQL, for example, uses something like

Connection jdbcConnection = 
       DriverManager.getConnection
       (jdbc:msql://hostname:9999/test_db");

Preparing a Statement Object

After you have setup a connection to the database, you will need to instantiate a statement object that you can use to pass SQL back and forth between the database and your application. Creating a statement object is simple and follows the form:

Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);
Connection jdbcConnection = 
       DriverManager.getConnection
       (jdbc:odbc:Access);
Statement sql = jdbcConnection.createStatement();

Using JDBC to Query a Database

Once you have the statement handle, it is very easy to query the database. To do so, you use the statement handle to send standard SQL to the database and then parse through the results returned from the database. Take a look at the following example.

Click here for code example 2.

Note that if the field is an Integer, you should use the getInt() method in ResultSet instead of getString().

Using JDBC to Modify a Database

Modifying a database is just as simple as querying a database. However, instead of using executeQuery(), you use executeUpdate() and you don't have to worry about a result set. Consider the following example:

Click here for code example 3.

As you can see, there is not much to it. Add, modify and delete are all handled by the executeUpdate() method. You compose the SQL and send it through JDBC in one simple call.

More on JDBC

There are many, many more things you can do with JDBC. The API is as complex as the proprietary databases they serve. However, for most web-applications, you will only need to query and modify the database. So that is all we have decided to go over here. If your project is more demanding, you can easily browse the JDK API and documentation at http://www.javasoft.com/products/jdbc/index.html.

JDBC Example

Okay, well, as I have always said, an example is worth a thousand pictures which is heck of a lot of words. So I would like to conclude this tutorial with a little JDBC application I put together.

Essentially, this application will hook into my MSAccess database and allow me to search or modify the contents of the CUSTOMERS table. If you have been following along in this tutorial, you will already have set up such a database and table. If not, you can imagine. Or better, yet, you can modify a few lines, recompile and get this working on your own databases and tables.

I have coded the application so that it should be very easy for you to modify it for use at your site. All the GUI design and site specific information have been separated out. You need not worry about the implementation of the code besides changing a few simple GUI sections and Strings.

There are 5 files in the application code:

  • DataGrid.java - A Microline Component Toolkit Grid Object. The MCT is free and available at Neuron Data. There is no need for you to edit this file.
  • GlobalResources.java - There are some implementation specific things defined here. You should change them for your site and recompile. I have noted with comments which lines you should change.
  • MainFrame.java - The Main display frame. Leave this alone.
  • StartButton.java - The applet frontend. Leave this alone.
  • UserInterface.java - The definition of the User Interface. You should change this to reflect your own DB Table and recompile. Changes you need to make are commented.

The entire application with compiled classes is zipped up here. When you run it, you will see the following interface:

[JDBC Interface]

Enjoy!

Additional Resources:

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved