How to Use Java and JDBC To Access a Database

Sites covering similar information to this one:

http://cse-ferg41.unl.edu/docs/DevelopmentDocs/jdbcpage/jdbc.htm.
http://home-1.worldonline.nl/~bmc88/java/jdbc/
Note, the second site is a more recent version of the first.   Both contain slight inaccuracies regarding the use of JDK 1.1 and up (steps 2 and 3 are unnecessary when using JDK 1.1+.)

Slides I've created covering similar information are at: this location.

Related sites:  (Tutorials, links, code)  date links last checked : 1-12-01.

JavaWorld  : Integrating Databases with Java via JDBC
DATABASE ACCESS FOR JAVA PROGRAMS
Java Database Programming Book Website
JDBC Driver Types
 

WHAT IS JDBC?

JDBC is similar to Microsoft's ODBC (Open Database Connectivity), except that it is supposed to bring the added advantage of being platform independent(because it is written in Java).

To use either JDBC or ODBC, however, you need a database driver that they can communicate with. The driver is specific to the particular type of database you're using. Most of the JDBC drivers that I've seen are generally expensive.

On the other hand, drivers for ODBC are already available. (They come with Windows NT and '95.)  Since JDBC and ODBC are very similar, a set of routines were written to translate JDBC commands to ODBC, thus allowing JDBC to use ODBC drivers. These routines are called the JDBC-ODBC bridge.       This bridge is free, and comes with versions of the JDK past 1.1.   It is also available for JDK 1.0 users from http://splash.javasoft.com/jdbc.

To access an MS Access database from Java, you need the JDBC classes, including the JDBC-ODBC bridge, and the Microsoft Desktop Database Drivers, which come with Windows NT and 98.     (They are on the install disks for these systems, but aren't necessarily part of a typical install.      My home machine uses 98 and had them preinstalled.    When I've installed NT 4.0,   they were not preinstalled, so I had to get out the install disks.)

(Note, this document does not discuss installing ODBC drivers on a UNIX operating system, because I haven't done that.)

Background :  Applets vs Applications

Another big difference between applets and applications is that applets have more security restrictions. For example, you can't write a file to a client machine from an applet, nor can you read a file from the client machine.  Also, with an applet, you can't connect to any location on the web except the server from which the applet was downloaded.  (Note: you can get around these restrictions using the concept of "trusted applets" in Communicator.   This involves getting a security certificate for your applet.  Users of the applet must then grant read/write permissions to the applet for their machines.)

A final important difference between applets and applications is that applets are automatically downloaded and run when you open the web page containing them. To run a Java application, a user must treat it like any other application: it must be first downloaded, then run.

These factors should determine your choice of whether to create an application or an applet, and are particularly important when you want your web program to access a database.

The distinction between applets and applications becomes particularly important if you want to use the JDBC-ODBC bridge.

This is because the bridge is affected by the restrictions on applets:  it needs to communicate with ODBC code written in C which is not downloaded with your applet, thus the ODBC drivers would need to be installed on every machine that might run your code.     Also, you cannot directly use the JDBC-ODBC bridge to access desktop databases such as MS Access across a network.     This is because Access is not networked:  that is, it doesn't listen on a port for clients to communicate with it.      (Microsoft SQL Server, on the other hand, is networked, and understands HTTP URLs.)    See the JDBC faq at Javasoft. [Note: I've also included this particular FAQ question at the end of this document.]

Database Access from Applets:

Here are some ways to access database data from an applet:

(1)  Create a CGI application  (or some variation on CGI) on your web server, and call that CGI application from within your applet.   In this case, your applet is communicating only with the web server from which your applet is downloaded, so there are no security violations.
(2)  Use Java servlets.   Servlets are like CGI, but are more memory-efficient.    They also are run by the web server.
(3)  Use Active Server Pages or Java Server Pages, and invoke them from an applet.   (Not sure this is workable, but I'm including it as something to consider.)    These are also run by the web server.
(4)  Create a server application that runs on your web server, and open a socket to that server application from your Java applet. Once again, no security violations are created.
(5)  Find a commercial database server, such as Symantec's dbAnywhere, SQL Server, or a shareware or freeware database server(such as RmiJDBC, described below) and a JDBC driver to communicate with it.

It should be noted that if you choose the first or fourth option, you are not limited to using Java on the server to do your database calls.   A CGI program can be written in any language that reads from standard in and writes to standard out, and can invoked from within an applet.     (The "Java Pro" link below explains how.)    Similarly, you could write a server application in C that would receive requests over a socket port from your applet.

In terms of ease of programming, the best option is the fifth one.      With a JDBC driver communicating directly with a database sever, you can communicate with a remote database almost exactly as if it were local.

The best combination of cost and ease is RmiJDBC.     It's free, and allows you to write pure JDBC code.    It is described below.

NOTE:

This document assumes you really need an applet to communicate with your database.      There are  many sophisticated ways to put database results onto a web page that don't use applets.       Most of the techniques described here could also be used to query a web server and return database results in the form of web pages.     When combined with techniques such as JavaScript, DHTML, plug-ins, etc., pretty sophisticated applications can be developed without Java.

I chose to use an applet because I wanted to created a multi-windowed application that featured complex widgets that are not available (or are inferior) in HTML.    For example, I wanted to populate a treeview with database data, and display it on a web page.
 

METHODS OF ACCESSING DATABASES FROM APPLETS
 

1. Using CGI (or ISAPI, NSAPI, FastCGI)  From an Applet

To intercept the results, you would open a socket to the web server from your Java program, and then send an HTML "GET" or "POST" command over this socket. The results of your query will be returned to your Java program, not the browser. If you do things this way, you would write a CGI program to process the query at the server.

If you used this technique, you'd be essentially using the web server in place of a database server.   (The topic of database servers is covered below.)

The following link, from "Ask the Java Pro", talks about how to access databases from within applets, and explains the use of CGI with from applets in particular.   (Note: there is one inaccurate statement at the beginning of the article: It says that "once the JDBC specification is released, database access from applets will be easy".   That is only true if you have a JDBC driver that understands HTTP URLs, or some other method (such as RMI) for accessing remote database objects.  Also note: this article focuses on the problem of a developer who wants to use C to do database access on the Server side.   Thus, the use of JDBC is not discussed.)

"http://cse-ferg41.unl.edu/bore/BoreDocs/JavaPro.html"

(This is a copy of the document. The "Ask the Java Pro" site is at http://www.inquiry.com/techtips/java_pro/.)

While the article above does not discuss this, using Java for the server-side CGI program is an option.   One plus for JDBC if you're using CGI is that the sample program provided with JDBC (mentioned below) already writes the results of any query to standard out.   Though I haven't attempted to create a Java CGI program, I understand one problem is that  because Java is interpreted,  the CGI program you are calling is actually "Java", not your application.  This leads to difficulties.    This site had details, though the site has been reorganized and I don't know the specific page.

The major inconveniences of this method are:   (1) you might not administrate your web server, and thus might not be able to run CGI-type programs.     (2)   CGI programs receive their input from standard input on the web server, which means a CGI program must parse the input.     (3)  The CGI program will send its results back to your applet in the form of strings, which your applet must then parse.     

With regard to CGI, some special considerations:

CGI is known to have security loopholes(which should be carefully looked for and trapped).   Also, CGI programs use more memory than the other alternatives discussed here, because each invocation of a CGI program starts a complete new process on the Server.   Ie., if 50 users start CGI program X, then 50 copies of program X are placed in memory.   A better alternative would be for users to share the copy of program X.

There are alternatives to CGI:   FastCGI, ISAPI, and NSAPI are examples.      These generally do not load multiple copies of the program into memory, and are considered by most to be superior to CGI.      As far as I know, they have the same potential security problems, though.

Note:   If you go the CGI route, your should know that parsing strings in Java object is made easier by the "StringTokenizer" class. It is described at:

StringTokenizer Documentation
 

2.  Using Java Servlets

Java Servlets are like CGI programs in that they run on the server.   However, they use Java threads to avoid the memory disadvantages of CGI.    I don't know enough about them to know about whether they suffer from the same security problems as CGI programs.

Using servlets, you can write a program that resides on the server which handles all your JDBC database calls.    The web server calls this program when it receives a query.      Similar to CGI, servlets generally return a complete web page.      As with CGI, to call a servlet from an applet, you'd open a socket to the web server, send your request, and receive the results in your applet.

An important consideration for servlets is that your web server be capable of running them.     Most web servers can be made to run servlets, if they don't already.      A product called JRun can be used to allow servlets to be run using Microsoft's IIS web server.

JavaSoft has a servlet development kit at this location.  This kit comes with a simple "servlet engine" which allows testing of the servlet without using a web server.    The JSDK also comes with web server add-ons that allow a variety of web servers, including the microsoft IIs web server, to run servlets.

I haven't used servlets, so I'm not clear on whether there is a way to get around the problem of having to parse your query results.
Could a servlet return results to an applet in the form of serialized objects, or could one use Java's Remote Method Invocation to call servlet methods?    I don't know.     If one could do these things, you could make JDBC calls to your servlet exactly as if you were communicating with a local database.      Let me know if you have information on this subject.

3:   Use ASPs (Active Server Pages) or JSPs (Java Server Pages)

These are alternative methods for incorporting database access into web applications.     Both involve putting special HTML tags into web pages, and having the web server replace these tags with (for example) data retrieved from a database.

I'm not sure about whether ASPs or JSPs could be usefully accessed from an applet.     I assume that they could be used as usefully as CGI or Servlet programs.     As with CGI, string parsing would need to be done when the ASP or JSP returned its results.

ASPs work primarily with Microsoft's IIS web server.    I'm not sure what other web servers can use them.   Information on ASPs is accessible at Microsoft's site.

JSPs can be run on  a variety of web servers.    An important note is that they are an extension of Java Servlets, described above.   This means (as I understand it) that JSPs are creating by running a servlet which interprets the special HTML tags.     JavaSoft's page describing them is here.

Combining servlets and JSPs is yet another option.   (As is combining servlets and ASPs.)

The article above on CGI from "Ask the Java Pro" suggests that writing a server program to handle socket connections is complex. However, that is not the case when working in Java.   Here is sample code that implements a socket server in Java.  Extending this program to do database queries would simply be a matter of sending queries through the socket and using JDBC on the receiving end to do the database access.

ServerTest.html
Server.java
AppletClient.java

The exact process for setting up a database for use with JDBC is described later in this document.

In addition to the relative ease of socket programming in Java, using JDBC for most database purposes is not difficult.   The sample program for JDBC, "SimpleSelect.java" shows how to do a simple query(Source).   Creating a simple database server would essentially be a matter of combining SimpleSelect.java with the socket server code above.

Problems with writing your own server:   none that I can think of.   :o)

Actually, I wrote my own server, and it was not all that complex, though it was time-consuming.     Special problems with writing your own server:   how to keep it running all the time (eg., getting it to run as an NT service); parsing problems if you send data back and forth using text strings; socket coding.

You should not write your own server if you want to concentrate your attention on your application, not the database server.

 Also, new Java development tools, such as Visual Cafe  from WebGain (formerly from Symantec), come with built-in tools for accessing databases from applets.    Until recently, Cafe used a database server, dbAnywhere,  to handle database calls, along with a JDBC driver.    (Now that Cafe has been bought by WebGain, it uses a different database server that I'm not familiar with, though it undoubtedly has comparable or superior JDBC features.)     Obviously, programs like Cafe have many conveniences that allow you to populate user interface widgets with database data without doing any coding.     Products from Microsoft and Borland have similar features.    I'm not sure if they come with database servers, though I think they do.     Usually the server is a scaled-down version of an expensive database server, such as SQL Server or Oracle.

Commercial database servers have features that would be transaction logging, data buffering, connection time limiting, simultaneous session limiting, control over error message output, and remote administration.   Also, for NT users, commercial database servers runs as an NT service, and thus can run unobtrusively in the background.

If you are interested in remote database access, you should find out whether a commercial product actually supports database access over the internet, not just whether it supports JDBC.     For example, when I originally looked into Cafe's "standard" version, promotional materials said it offerred  "remote database connectivity using JDBC".    But to actually achieve this connectivity, you needed to buy the "Professional" version, which came with the dbAnywhere database server.    If the product says nothing about a database server, then you will not get remote database connectivity using the product alone.

RmiJDBC

A Remote Method Invocation(RMI) technique for using JDBC has been devised, and is free. This program allows using the JDBC-ODBC bridge with drivers that don't understand URLs, such as the MS Access driver. See this site.   This solution, like the indirect solution using sockets described above, also requires a database server that is continually running.  However, it is superior in some ways to a crude server application or servlets in that that it allows you to use JDBC objects directly, as if they were on the local machine.   No socket coding is needed.

One disadvantage of using RmiJDBC that I have found is that it is somewhat slow.    It is about half as fast for typical queries than dbAnywhere.

In using RmiJDBC, I discovered another disadvantage, regarding Windows NT (and no doubt, 2000).     Documentation says that RmiJDBC can be run as an NT "service".     The importance of running your server as a service is that the server will remain running no matter who logs onto the machine, and even if no one logs on.     (You must have administrative priviledges on the machine to administer services.)    While RmiJDBC can be run as a service, it can't be run with anywhere near the convenience of a commercial database server.     For example, since RmiJDBC is a java program, it runs in a DOS window.     Thus, every user who logs on will see this window.     As far as I know, nothing can stop a user from closing the window.    (The window will not go down if you log off, however.)

Obviously, this is unworkable in situations where you are running NT and want multiple users to be able to log onto the machine while leaving your server running.

Commercial database servers truly run in the background, and can be brought forward whenever you want to administer them.

It may be that by configuring your DOS window properly, RmiJDBC will can be run without a visible DOS window, but the RmiJDBC site had no information on this when I checked.

If you don't need to have your server running all the time, or you can put the server on a dedicated machine, then this problem should not be a concern.

For those who need a way to run a server as a Windows/NT Service, this can be done. See the letters section for more information, or just go to this site. I've installed our own small database server as an NT service, and a brief description of how I did it is at this address.

A letter I've received on installing and configuring RMI-Jdbc is in the letters section at the end of this document.

STEPS REQUIRED TO USE JDBC :

2. Make the changes to your Java CLASSPATH variable specified in the installation instructions for JDBC and JDBC-ODBC.  (Again, no changes are needed  to the CLASSPATH if you're using any JDK past 1.1.)

Example path for those using JDK 1.1 and up:

    .;C:\jdk1.1.8\lib\classes.zip;

Example path for Windows NT, using JDK 1.0.2 : (all one line)

    .;%SystemRoot%\java\lib\classes.zip;%SystemRoot%\java\Jdbc\jdbc\classes\;
    %SystemRoot%\java\Jdbc\jdbc-odbc\classes\;

Also note: omitting  "." at the start of your path is a common mistake.

You can always set the variables by placing a "SET CLASSPATH " command in your autoexec.bat file. You can also temporarily set the CLASSPATH by typing "SET CLASSPATH <...>" at the command prompt in a DOS Window. This setting only applies to that particular DOS Window, and thus is less convenient to use.

Changes in the autoexec.bat file don't become effective until you restart the machine. Changing environmental variables in Windows NT/2000, however, does not require restarting the machine.    However, on NT, setting any environment variable from the control panel has no effect on any open DOS windows, so open a new DOS window to see the effects of your CLASSPATH changes.

3. Install the Microsoft Desktop Database drivers on your web server. These may not be included in a normal Windows NT/2000 or 95/98 installation. Thus, you might need to do a custom installation. The drivers should be under some section of the installation dealing with ODBC. Also install anything that relates to enabling ODBC (if it isn't already installed).

4. Create your database. (Using, say, MS Access.) Place a copy of it on the web server.

5. Register the database as an ODBC database. To do this, go into the Control Panel, open up "ODBC", click "Add", click "Microsoft Access Driver" (if you're using an Access database), then type in the name of your database, and click "Select" to browse for your database. That's all you should need to do.

User vs. System DSN's:
Note that if you want your database to be available regardless of who is logged onto your machine, you must register the database as a "System DSN".  Click the appropriate tab on the ODBC control panel to add a System DSN.  If your machine has only one user,  a "User DSN" is fine, provided you remain logged on as that user.

For those wanting a visual illustration of setting up a datasource, check this (local) link.

6. Compile and run the sample program. The sample is called "SimpleSelect.java" and it is in the JDBC-ODBC directory.   It is also available at this site.

To run the sample, start by editing the file by changing the name of the database to your database.     The code you need to change looks like this: String url = "jdbc:odbc:MyDatabase.mdb";

The "jdbc:odbc:" part indicates that you intend to use the JDBC-ODBC bridge to open the database called "MyDatabase.mdb".  Change the name of the database to the database you plan to use.  The name of the database should exactly match the name you gave to the database when you registered it in step 5.

( Note : A good utility for working with Java programs is WinEdit, and it would be a good idea to install that for editing your Java files. I don't know where to obtain it on the net, though you could probably find it by doing a net search. I also have access to a CD that has it. It is a shareware product.     Look at zdnet.com for other good programming editors, if you aren't already using a product like Cafe or Visual J++.)

You will also need to amend the sample query to an SQL query meaningful for your database.    See the end of this document for a brief discussion of SQL.

Once you've amended the sample program, save it, and compile it with the command

        javac SimpleSelect.java

        java SimpleSelect

The sample program has a routine called "dispResultSet" which simply displays, on your terminal, all the rows in your database that satisfy the query.  You should see the result of your query print out on the DOS console.  You're done!

Error messages and Their Possible Causes:

Data Source Not Found
If you get an error similar to "datasource not found", this means that either your database is improperly registered(or not registered) through ODBC, or the name you gave your database in the code  is not exactly the same as the name you gave it when you registered it.  Another possible source of this error:  attempting to connect to a database on another machine.   You can't do this directly through JDBC-ODBC, unless your database driver understands HTTP URLs.  (Access doesn't.)  See below.
Can't find Class:
If the classes are not ones you've created, then some necessary classes are not in your classpath.  You need to locate those classes and modify your classpath, as described above.     If the classes are ones you've created, the likely reason is that you did not put the current directory in your classpath, or you are attempting to run your application from somewhere other than the current directory.   (Add "." to your classpath to add the current directory.)
 

Creating a Database Server : example

As I said above, you could create a simple database server by combining the socket server code with the simpleselect example.

I've done this.  My rewritten version includes code to send SQL queries to the socket server, and return and parse query results.   It also contains a slightly modified version of "simpleselect" which handles both queries and updates, and sends the results back to the client.

The code for this example can be found in ZIP format at this location.

Note that if you have a web server running on your home machine(eg., Personal Web Server for 98, or IIS), the web server does not need to be connected to the internet in order for you to run this example.     In the steps below, just use "localhost" as the name of your web server, and you can use your local web server.

Steps to run the sample:

1. Unzip it into a directory that is accessible by your web server.   You may need to create a virtual directory in order for the web server to recognize the directory.
2. Register the small database included with ODBC, as described above.    It can be registered as a User or System database.   It must have the name cse496, or you will need to revise some code.
3. Edit the small file "CommonData.java".    Set the variable "theURLString" to the URL of your web server.    Compile this file.
4. The class files are included so no other compilation should be necessary.
5. From a DOS window, navigate to the directory containing the files and type "java Server".   You should see the message : "Database cse496 constructor started."
6. Open the HTML file DBClientDemo.html in your browser (using a path relative to your web server, not a file path).    You should see activity in the DOS  window.   It should say:  "Connected to jdbc:odbc:cse496", among other things.    If it says something about "datasource not found", then check the datasource name you set up, and make sure it's cse496.
7. Click on the words "send query" and it should send a default query.   You can also type in queries of your own and send them.   It also times the queries.

A second example is at this location.

Note, this  second example is intended to explain more than just JDBC.   It also illustrates how to integrate database queries with LiveConnect (Netscape's technology for achieving communication between Java and JavaScript).    I believe my particular example will not work in Internet Explorer 5.0, though this is strictly because of the LiveConnect code, not the database code.    An illustration of how to achieve LiveConnect compatibility between IE 5.0 and Communicator 4.7+ is at this location.

If you know nothing of LiveConnect, the JavaScript code will be baffling.   The main thing to understand is that the JavaScript code (particularly in "Choose.html") is calling methods in the applet, and displaying the results in HTML format.   As for the "top.type" variable, it is set to "type1" (in ItemSelection.html) the moment you click the "Select" button.   It can only assume the value "type1", so it is a rather useless variable.   (So why don't I rewrite it?   Maybe later...)   And, just to make things a little more irritating, the "Display.html" file does nothing but display itself.

An apology on the second example:   it contains some convoluted code.   (But I wasn't the only author!)    For example, the HTML file "Hidden.html" declares the applet, and passes it certain parameters.   But the only parameter that is actually used is the port number.

A further note on the example:  since it uses LiveConnect, any changes to the applet would require putting the LiveConnect classes in your CLASSPATH, which would mean adding the following to your path(assuming the default location of Communicator)

    ;C:\Program Files\Netscape\Communicator\Program\java\classes\java40.jar

Some Limitations of the sample client/server:
 

(1) The server does not currently return database error messages to the client.   Obviously, that's not good.  On the server, the server's DOS window prints out the error messages.    Adding code to do this would not be too difficult.   Theoretically, you could return the JDBC error message as a String, then use it to construct an SQLException on the client, and then throw the exception.

(2)  Special characters are a problem for this simple server.   It assumes each row of data is contained in a line, terminated by a newline.    But what if the database data contains newlines?   Tabs are also a problem.    Read the DBAccess.java code to learn more about this.

There are many more limitations.    However, some good can be said of it:  in a test comparing its speed to RmiJDBC and dbAnywhere, it ran twice as fast as RmiJDBC and about the same speed as dbAnywhere.    Also, this server and client(with modifications and improvements) has been used successfully on a number of student projects.    Those who wish to compare it to RmiJDBC in terms of its API  can take a look at this sample of an RmiJDBC applet(this is the HTML file for the sample).   The applet is exactly the same as the first example applet for our little database server, described above, except that it uses RmiJDBC to query the database.   This was the same applet I used to make the speed comparisons between RmiJDBC, dbAnywhere and our server.

You're welcome to email me if you're interested in obtaining a somewhat more full-featured version of the database server and client.

JavaSoft JDBC Information

JDBC Documentation
JDBC Tutorial
JDBC FAQ

For those who need a way to run a database server(or RmiJDBC server) as a Windows/NT Service, this can be done. See the letters section for more information, or just go to this site or this site. I've installed our own small database server as an NT service, and a brief description of how I did it is at this address.    description might be useful to those using RmiJDBC since the RmiJDBC server does not run as a service.

Letters in response to this document:

 Can the JDBC-ODBC Bridge be used across the internet, provided you're using a Java application, not an applet? (revised answer)

Setting Up Rmi-JDBC: Short Version
Setting Up Rmi-JDBC: Long Version

How to Set up a web server

How to Set up a database server to run as an NT Service; How to Set up a web server

What technique would you recommend for using JDBC?

Could you explain the difference between web server and database server, and how they're used?

I'm working in Access and JDBC and I'd like to speed up my work

I want to Write an applet that connects to an Access database, and I want to put it the applet on my school's web server
 

Quote from JavaSoft's JDBC FAQ:

Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendor's of these database products haven't implemented all-Java JDBC drivers.

The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC drivers page for a list of available JDBC drivers.

The JDBC-ODBC bridge from JavaSoft does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren't networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge , however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.
 

An example SQL query might be

"SELECT * from Books where NumSold > 11"

Here, "*" means all columns in a database table, and Books is the table. (A database essentially just a bunch of tables.) NumSold is another column in the table. The result of the query is to return rows from the table "Books" where the value of "NumSold" is greater than 11.

Note: SQL is pretty easy to learn, since you are nearly always using one of three commands : SELECT, UPDATE and DELETE. If you've learned these, you will be able to do most of the things people normally want to do with databases.

Two SQL links:

http://w3.one.net/~jhoffman/sqltut.htm
http://www.contrib.andrew.cmu.edu/~shadow/sql.html