jGuru: Exercises: JDBC 2.0 Fundamentals

Print-friendly Version

by

[Short Course| About This Short Course]

Exercise Outline

• About Exercises
  • The Anatomy of an Exercise
  • Exercise Design Goals

• JDBC 2.0 Fundamentals Exercises
  • Creating and Populating a Table
  • Data Retrieval
  • Generalizing Connection Information--Batch
  • Generalizing Connection Information--Interactive
  • Using executeUpdate()
  • Selecting Data and Presenting Information
  • Using Prepared Statements
  • Handling SQLExceptions and SQLWarnings
  • Determining Available Scalar Functions
  • Using Transactions
  • Using Batch Updates
  • Paging with Scrollable ResultSets
  • Storing an Image in a Blob
  • Retrieving and Displaying an Image from a Blob

Welcome to the jGuru exercises for the JDBC 2.0 Fundamentals Short Course.

These exercises demonstrate selected areas of JDBC, the Java programming language API for accessing tabular and relational data. On completion, you should understand the basic, and some intermediate, steps for creating, accessing, and changing database tables and columns according to the JDBC standard. You will also be aware of and able to use several capabilities that are new with JDBC 2.0.

For those new to database engines, be sure that the database you are working with is up and running prior to actually executing any of the exercise programs. The DBMS and the programs work together.

All programs have been tested and run properly (with exceptions as noted in the specific exercises) on Cloudscape, UDB2/NT, and DB2/400. The test machine ran NT Server 4.0, SP 6a. For the Cloudscape and UDB2/NT tests, the databases were local to the machine, that is, Cloudscape and DB2 run on the same box as the programs. For AS/400 testing, the application ran on the NT box and DB2/400, of course, being an integrated portion of OS/400, ran on the AS/400. The AS/400 was accessed over a 56Kb Internet connection, using the driver in JTOpen 2.0, which is the open source version of the AS/400 Toolbox for Java.

The JDK used was Java 2 Standard Edition 1.3. Following is the DBMS, driver, and version information:

• Cloudscape, version: 3.0.4
  Driver: Cloudscape Embedded JDBC Driver, version 3.0.
  


• DB2/NT, version: 07.01.0000.
  Driver: DB2CLI.DLL, 07.01.0000.
  


• DB2 UDB for AS/400, version: 04.04.0000 V4R4m0.
  Driver: AS/400 Toolbox for Java JDBC Driver, 4.0.

There was a deliberate attempt to provide both more exercises and more "meat" in the exercises than might be expected in a course of this nature. However, this is a short course, targeted at 8 to 12 hours, and you should be aware of several factors that affected the composition of the exercises and regard them accordingly. As in any course, the exercises are aimed at highlighting a specific area under discussion. To the greatest extent possible, the exercises run with little dependence on other programs.

As a result, first and foremost, these programs should not be considered production quality. The programs are not optimized as regards the Java programming language, JDBC, or SQL code. For instructional purposes, the programs are monolithic and demonstrate little of the OOPS paradigm.

Finally, those exercises that use Swing produce a deprecation warning on compilation, which has to do with using JPasswordField.getText(). This reflects the laziness, and, to a certain extent, the opinion, of the author.

About Exercises

A jGuru exercise is a flexible exercise that provides varying levels of help according to the student's needs. Some students may complete the exercise using only the information and the task list in the exercise body; some may want a few hints (Help); while others may want a step-by-step guide to successful completion (Solution). Since complete solutions are provided in addition to help, students can skip an exercise and still complete later exercises that required the skipped one(s).

The Anatomy of an Exercise

Each exercise includes a list of any prerequisite exercises, a list of skeleton code to start with, links to necessary API pages, and a text description of the exercise's educational goal. In addition, buttons link you to the following information:

• Help: Gives you help or hints on the current exercise, and an annotated solution. For ease of use, the task information is duplicated on the help page with the actual help information indented beneath it.
• Solution: The <applet> tag and Java source resulting in the expected behavior.
• API Documentation: A link directly to any necessary online API documentation.

Exercise Design Goals

There are three fundamental exercise types that you may encounter:

"Blank screen"

You are confronted with a "blank screen" and you create the entire desired functionality yourself.

Extension

You extend the functionality of an existing, correctly-working program.

Repair

You repair undesirable behavior in an existing program.

To make learning easier, exercises, where possible, address only the specific technique being taught in that exercise. Irrelevant, unrelated, and overly complex materials are avoided.

Where possible, exercises execute on the Web. However, exercises that must access Java features or library elements that could cause security violations are not executed on the web.

JDBC 2.0 Fundamentals Exercises

The fictional scenario for the exercises is the 4J Cafe. Hal Clarke is responsible for information technology for the 4J enterprise. Hal's current contract expires in 2001 and he's determined to have it renewed until at least 2010, when he plans to retire to a small island in the Indian Ocean. To further this end, Hal has engaged the services of Slide and Telly for design, development, and implementation.

S&T is owned by Duane Mounds and Chrissie Fore and operates from a home office located in Statesboro, Ohio. Duane and Chrissie support a number of remote clients via email, telephone, internet connections, and the occasional flight. S&T prides itself on professional quality, 24x7 availability, cost benefits produced by leveraging the advantages of their cyberspace workplace and testing, testing, testing.

Exercises

1. Creating and Populating a Table

   This initial example helps you to understand the JDBC code and steps involved in creating and populating a table in a database.

   Educational goals:

   • Get a first look at JDBC code that creates a table and inserts data into the table.
   • Understand how the general conceptual steps of loading a JDBC driver, connecting to a database, and creating a Statement to work with the database are implemented.

2. Data Retrieval

   This second example helps you to further understand the JDBC code and steps involved in accessing and retrieving data from a table in a database.

   Educational goals:

   • Get a first look at JDBC code for obtaining data from a table.
   • Reinforce how the basic steps of a JDBC application are implemented.

3. Generalizing Connection Information--Batch

   The first course examples hardcoded connection information. This helped to keep things simple at the price of program changes and recompilation for different databases--hardly the Java ideal! This exercise provides a more general method of obtaining the information without user intervention.

   Educational goal(s):

   • Remind the reader that portability must be reflected in design, as well as the programming language.
   • Provide one method of generalized storage and input of program-required information that can be reused in other programs of this type.
   • Reinforce how to connect to a database and provide a first glimpse of the metadata available for a database and a ResultSet.

4. Generalizing Connection Information--Interactive

   This exercise reimplements the previous application in a form appropriate for interaction with an end user and includes the ability to dynamically display table data.

   Educational goal(s):

   • Remind the reader that portability must be reflected in design as well as the programming language.
   • Provide one method of generalized storage and input of program-required information that can be reused in other programs of this type.
   • Demonstrate how to connect to a database in an interactive application.

5. Using executeUpdate()

   This exercise demonstrates the flexibility of executeUpdate() and introduces a generalized maintenance application that is enhanced and modified in future exercises.

   Educational goals:

   • Understand how executeUpdate() can be used for DML statements.
   • Provide some ideas for generalized data maintenance processing.

6. Selecting Data and Presenting Information

   This exercise focuses on data selection. It introduces the DISTINCT SQL keyword and gives an example of how the data itself can be used to enable dynamic reporting. The exercise works with a third party charting package that you can use in your own applications.

   Educational goals:

   • Provide a deeper understanding of the SELECT statement.
   • Provide an example for using data in reporting and different presentation methods.

7. Using Prepared Statements

   This exercise adds a new column to JJJJData and updates multiple columns with a PreparedStatement to give some ideas as to when prepared statements can be useful.

   Educational goal(s):

   • Reinforce the course material and demonstrate PreparedStatement usage.
   • Illustrate usage of the ALTER TABLE SQL statement.

8. Handling SQLExceptions and SQLWarnings

   This exercise expands EURun.java, the demonstration program built in Using executeUpdate(), to include complete handling for all SQLExceptions and SQLWarnings.

   Educational goals:

   • Demonstrate error handling as discussed in the text.
   • Provide a tool to test the results of problem statements.

9. Determining Available Scalar Functions

   This exercise retrieves available scalar functions in a DMBS and demonstrates the invocation of selected scalar functions.

   Educational goals:

   • Understand the retrieval of available scalar functions.
   • Demonstrate escape syntax used in scalar function invocation.

10. Using Transactions

    This exercise expands EURun2.java, the exercise program built in Handling SQLExceptions and SQLWarnings, to include processing transactions.

    Educational goals:

    • Demonstrate transactions as discussed in the text.
    • Provide a transaction-oriented tool for DML statements and problem testing.

11. Using Batch Updates

    This exercise converts the exercise program built in Using Transactions to include batch update functionality and BatchUpdateException handling.

    Educational goals:

    • Demonstrate the Batch Update Facility.
    • Demonstrate BatchUpdateException handling.

12. Paging with Scrollable ResultSets

    This exercise implements a completely new version of the table viewer in Generalizing Connection Information--Interactive. The application presents data formatted and in a set page size using a scrollable ResultSet. A separate window displays metadata about the table and the ResultSet.

    Educational goal(s):

    • Reinforce the course text and demonstrate how to obtain and use a scrollable ResultSet.
    • Provide some dynamic formatting techniques.
    • Demonstrate additional Metadata capabilities.

13. Storing an Image in a Blob

    This example presents a solution to storing image data in a Blob column, using PreparedStatement's setBinaryStream method.

    Educational goals:

    • Increase understanding of one type of binary data a Blob column can contain.
    • Understand how to initially load a Blob with binary data.

14. Retrieving and Displaying an Image from a Blob

    This exercise demonstrates a method to retrieve and display images stored in a Blob column.

    Educational goals:

    • Understand Blob data retrieval.
    • Provide one method of displaying images stored in a Blob column.

Copyright 1996-2000 jGuru.com. All Rights Reserved.