Database Overview

There are two main ways that you can use databases in JavaServer Page-enhanced web sites:

• collecting data from your users

• presenting data to your users

This topic will teach you how to present data that is stored in a database to your users. To learn how to collect data from your users and store that in a database, see Collecting information from forms.

Prerequisites

Before you can complete the steps in this tutorial, you must have installed the Java SDK and the Tomcat web server on your local machine.

You must also have a Microsoft Access ODBC driver on your system. If you do not have an Access driver, and you want to work with a different ODBC-compliant database, and you have that DBMS and its drivers installed, you can do that, instead. If you use a database other than Access, you will need to create a database to use for this tutorial.

Setting up database access for JavaServer Pages

Setting up access to an ODBC database using the ODBC-JDBC bridge that comes with the Java SDK that you installed is actually very simple.

Disclaimer: The following steps explain how to set up a simple test case for a single user connecting to your JSP pages. When you get to a production environment, where you have many users simultaneously accessing your web-based documentation, you will need to work with your web site administrator to set up a native JDBC driver for your database, and set up connection pooling, so that your web server doesn't suffer performance degradation. Fortunately, the code in your JSP pages will not need to change very much to compensate for that change; most of the changes will occur on the web server.

To set up database access for JSP:

1. Create the following directory:

   <Tomcat installation directory>\examples\jsp\dbtutorial

2. Download the Access database used in this tutorial.

3. Extract the database from the file dbtutorialMDB.zip into the directory you created in step 1.

4. Create a System DSN for the database. Show me! (~1.5MB)

5. Download the JSP pages that demonstrate connecting to the database.

6. Unzip the contents of the file dbtutorialJSP.zip file into the directory you created in step 1.

7. Start your Tomcat web server.

8. Click here.

If all went well, a new web browser window should have opened, and should look like this:

If this did not happen, see the Troubleshooting section.

Testing the installation

The JSP pages that you are accessing test your JDBC database connection by allowing you to:

• access data from the database and display that data

• add new data to the database

This topic does not teach you how to add data to the database (though the JSP files you downloaded include an example of inserting data into a database). For instructions on how to insert data into a database, see Collecting information from forms.

The JSP pages you downloaded and unzipped have comments in the source code, to tell you what is going on. Feel free to browse the contents of the files, but don't feel overwhelmed by the code in them. To add JSP to your site, you will use a lot less Java code than is in these particular files.

Copy and paste content from these pages into your own JSP pages, if you like. Experimentation is a great way to get started learning how to make things work in JSP.

Troubleshooting

If you followed the set of steps on this page, and did not get the results expected, verify that:

• your Tomcat web browser is running. Click here to verify that the server is accepting connections. If you get a "Page cannot be displayed" error, then your Tomcat web server is not running. Check the Tomcat FAQ for help getting the server running.

• you did not misspell the directory you created: <Tomcat installation directory>\examples\jsp\dbtutorial

• you copied the contents of the .zip file into the directory you created.

• your autoexec.bat file has an entry to set the TOMCAT_HOME and JAVA_HOME system variables. Verify that they both point to the correct directory.

• your CLASSPATH setting includes: <Java SDK installation directory>\jre\bin.

• you created a data source for the Access database you downloaded. If you have one, use a query tool to run the following query against that database:

  SELECT * FROM dbtutorial

  If the query fails, then you may have not set up the datasource correctly. If this is the case, delete the data source you created, and create it again.

If you check all of these things, and still do not get the expected results, please fill in the following with as much detail as you can. I will review your situation as time allows, and will attempt to help you get set up on your machine. Items with a red asterisk (*) are required fields.