JDBC - ODBC Step by Step

JDBC with ODBC Introduction

The purpose of this application is to introduce the concept of Java Database Connectivity (JDBC) and illustrate the use of JDBC as a tool for database access. JDBC is a mechanism that allows Java to communicate with databases using a standard Application Programming Interface (API) to access databases regardless of the driver and the database product. This servlet-based application provides the capability to connect to any relational database registered as an ODBC data source using JDBC to browse the database meta-data and instance, and to manipulate the database by issuing ad hoc SQL statements. The application can be run using a browser (Netscape 4.0 or Internet Explorer 4.0 or higher).

The example JDBC application uses a three-tier architecture to access a registered ODBC data source. A Java Servlet and a Java Bean act as the middle tier, communicating with the underlying data source using JDBC (through a JDBC-ODBC driver) and returning the results, using Java Server Pages (JSP), formatted in HTML to the browser.

Registering an ODBC data source

To configure the ODBC data source implement the following steps:

1. Open the control panel from Windows settings and start the application "ODBC Data Sources [32bit]"

NOTE: In Windows 2000, the Control Panel does not contain the pointer to the "ODBC Data Sources". Using Help, search on ODBC and then select the topic "Using Data Sources". From there, select "Data Sources" to bring up the ODBC Data Source Administrator window.

2. Select the Add button to add a new data source.

3. Select the appropriate driver for your database and click the Finish button.

4. Enter a string in the Data Source Name that you want to use to register your database, and click the Database Select button.

5. Browse to find the actual file of your database and click OK when you're done.

6. Click OK to exit all the windows.

Installing and Running the application locally

The application is written in Java so the first requirement is a working Java Developer Kit (JDK). Make sure that you also have a Servlet and JSP container that implements version 2.2 of the servlet specification and version 1.1 of the JSP specification such as Tomcat 3.2 or higher which can be downloaded for free

(Note: Tomcat3.2 and JDK1.3 are not compatible on WindowsNT 4.0, you can use Tomcat3.2 with JDK1.2 or you could use a higher version of Tomcat in this case.)

Tomcat 4.1 is the latest stable version. The instructions that follow assume that you are using Tomcat 4.1

Download and install Tomcat 4.1 in C:\tomcat. You must first create a batch file that sets the environment variable JAVA_HOME to the current directory of JDK installation. Open your favorite text editor and add a line such as the following:

                      set JAVA_HOME=C:\jdk1.3\   (With no spaces)

depending on the version of the Java Developer Kit and the directory that it is located in. Save this file as a .bat file. Run the batch file in the command prompt window. Note : If you are working on multiple windows, you will need to run the batch file on each wondow.

Create a directory jspservlets and then add a subdirectory to it named WEB-INF.

Underneath this WEB-INF subdirectory, create two more directories, classes and lib. Inside the classes directory create a subdirectory named com and inside com add a directory components.

Next you will need to download the application files and put them in the correct directories as follows :

JSP files : C:\jspservlets\
Control.java (The servlet) : C:\jspservlets\WEB-INF\classes\
QueryBean.java (The Java bean) : C:\jspservlets\WEB-INF\classes\

With your favorite editor, open the c:\tomcat\conf\server.xml file and before the comment

  <Context path="/jspservlets" docBase="c:\jspservlets" debug="0" reloadable="true" >

  </Context>

In addition, create a new file web.xml and enter the following:

  <!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.// DTD Web Application 1.2//EN" "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">

  < web-app >

  </ web-app >

Save the file in c:\jspservlets\WEB-INF\ directory.

Before you run the application you need to compile the java files (Control.java, QueryBean.java)
You need to set the path and classpath prior to that:

set path=c:\jdk1.3\bin\%path%
set classpath=.;c:\jspservlets;c:\tomcat\lib\servlet.jar;c:\jdk1.3\lib\tools.jar;%classpath%

(Note: servlet.jar can be downloaded from http://java.sun.com/products/servlet/download.html)

You must compile the files into their directories.
From the directory that has the *.java files, type javac -d . *.java in the command prompt. This will compile the files and put the class files in the correct directory according to their respective package statements.

You also need to start the server. Open a dos window, change directory to c:\tomcat\bin\ and type startup to do that.

Finally open your browser and access the application using the following URL: http://localhost:8080/jspservlets/MainPage.jsp

When you are finished working with the application, type shutdown within c:\tomcat\bin\ to stop the server.

Opening a Database

When the application is running your browser should be at the following screen.

Enter the registered name of the ODBC data source and press submit. The application will establish a connection to the database using the given data source name. The following lines of code demonstrate how a connection can be established.

String url = "jdbc:odbc:" + dbName;

try

   {

       /* Load the jdbc-odbc driver */

           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

       /* Open a connection to the odbc data source entered by the user */

           con =DriverManager.getConnection(url,"","");  

   }

In the above code dbName is the data source name entered. If the data source name cannot be found an exception is caught and the following screen is displayed.

The following code catches the exception thrown by the above try when an error is encountered while establishing a connection to the data source. The control is transfered to a JSP error page

catch (Exception e)

        {   // if there is an error with the database source, close the connection

            // and transfer control to an error page.

            con = null;

            dispatcher = context.getRequestDispatcher("/errorDbSourse.jsp");

            dispatcher.forward(req, res);

        }

Database MetaData

After the connection to the database has been established, two browser windows are opened: a Database Manipulation window and a Database Metadata window. The database MetaData is used to browse the database metadata and the current database instance.

The Database MetaData window consists of two frames. The top frame displays the table names and their attributes from the currently opened database. When a table name is selected, the bottom frame displays the selected table's instance, showing the attribute names and types in the header. The following code shows how the application retrieves the database meta-data

 /* Holds the Meta-Data */

 DatabaseMetaData dbmd;

 /* ResultSet objects to hold the database table names and database column names */

 ResultSet rsTables = null;          

 ResultSet rsColumns = null;                  

 /* Retrieve meta-data from the database through the connection established*/

 dbmd = con.getMetaData(); 

 /* Get the table names in the ResultSet object using the 

    getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) 

    method matching the catalog, schema, table name and type criteria specified. 

    In this case all the tables are returned */

 rsTables = dbmd.getTables(null, null, null, null); 

/* Iterate through the ResultSet object and store the tables and columns in the QueryBean */

while (rsTables.next())

{

    tableName = rsTables.getString("TABLE_NAME");

    ttype=rsTables.getString("TABLE_TYPE");

    /* checks if it's a user-defined table*/

    if (ttype.equals("TABLE"))

    {

        /* store the table name in a StringBuffer Object */

        ...

        /* Get the column names in the ResultSet */

        rsColumns = dbmd.getColumns(null,null,tableName,null);

        while (rsColumns.next())

        {

            /* store the attribute names in the StringBubber Object */

            ...        

        }

        /* Store the StringBuffer Object in the bean as a String */

        ...

    }

}

/* transfer control to the appropriate JSP page */

...

Database Manipulation

The Database Manipulation window allows the user to query the database and to manipulate the data stored in the database including insertions, updates, and deletions. The submit button executes the SQL statement entered in the text window, displaying the result of the statement execution in the bottom frame. The Clear button clears the text window. The Open MetaData button opens the Database MetaData window if the one opened by default was inadvertently closed. The Close Database button closes the current database, returning the application to its main page.

The user can enter any valid SQL statement in the text window. In particular the user can enter any type of SELECT, INSERT, UPDATE, or DELETE statement. Any other type of statement will not be processed and the application will respond with an error message.

Query

If a SELECT statement is entered in the text window, the application, through JDBC and the java.sql class, will execute the statement, and get the results, if any, in a ResultSet object. The data stored in the ResultSet can be retrieved by iterating through the ResultSet object. The following window shows the query select * from project from the company database and the results.

The following code shows how to perform a query on the database

try

{

    /* Initializes  a statement object to the connection*/

    Statement stmt = con.createStatement();

    /* ResultSet object to hold the result of the query */

    ResultSet rs = null;        

    /* Get the query entered by the user as a string using the request object */

    String query = req.getParameter("query"); 

    ...

    /* execute the sql statement and put the results in the ResultSet object*/

    rs = stmt.executeQuery(query);

    /* Holds the Meta-Data */

        ResultSetMetaData rsmd = rs.getMetaData();

    /* Get the number of columns */

    int numCols = rsmd.getColumnCount (); 

    /* Store numCols in the QueryBean */

    qBean.setNumColumns(numCols);

    for ( int i = 1; i <= numCols; i++)

    {

        /* Store the column names and their types in the QueryBean using  

           rsmd.getColumnLabel(i) and rsmd.getColumnTypeName(i)*/

        ...

    }

    while(rs.next())

    {

           /* Iterate through the ResultSet and store the actual data in the QueryBean */

              using rs.getString(i) */ 

    } 

    ...

    ...

        /* transfer control to the appropriate jsp page */

    ...

}

Insert/Update/Delete

If an INSERT, UPDATE, or DELETE statement is entered in the text window, the application will execute the statement through JDBC and the java.sql. This is managed by the executeUpdate method of a Statement object, in this case the statement entered by the user. If the execution is not successful, an SQL exception is caught and displayed. The following window shows the statement insert into dept_locations values ('7', 'Tempe') from the company database and the results.

The following code shows how to perform an insert, update, or delete statement.

try

{

    /* usrstmt is the statement entered by the user */

    stmt.executeUpdate(usrstmt); 

    /* output appropriate message */

        ...

}

Closing a Database

To close the current database, the user should close the Database MetaData window and then press the Close Database button in the Database Manipulation window. This will bring up the Main Page of the application where the user can enter another registered ODBC data source name or quit. The following is some of the code needed for closing the database.

finally

{

    /* close the resultset */

    rs = null; 

    /* close the connection */

    con = null;

}