Internet Programming I

Assignment 7

Java and databases

Due: October 31, 2006

1. Create a database called Store with a table called Products.

2. Register the database with the Windows operating system. To do this, first click on Settings/Control Panel/Administrative Tools. On that screen, click on Data Sources (ODBC). Select Add/Microsoft Access Driver (*.mdb), and from there Browse to find the location of your database. Click on OK and exit. You must close your database before trying to register it.

3. Next open JCreator and create a Java program called DisplayProducts. Make sure that the name of your file is the same name as the name of the class. For this example, the file name must be DisplayProducts.java.

// This is a Java application that gets data from a database and displays it on the screen.

import java.sql.*;

public class DisplayProducts

{

public static void main (String [] args)

{

try

{

// Get a connection to the database.

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

Connection con = DriverManager.getConnection ("jdbc:odbc:store");

// Create a statement and a query.

Statement stmt = con.createStatement ();

String query = "Select * From Products";

// Execute the query and retrieve a ResultSet.

ResultSet rs = stmt.executeQuery (query);

System.out.println ("Products List");

// Display the data in the ResultSet.

while (rs.next ())

{

System.out.println ();

System.out.println ("ID: " + rs.getString ("ID"));

System.out.println ("Name: " + rs.getString ("Name"));

System.out.println ("Quantity: " + rs.getInt ("Quantity"));

System.out.println ("Price: " + rs.getDouble ("Price"));

}

con.close (); // Close the connection to the database.

} catch (SQLException e) {System.out.println ("SQL Exception");}

catch (ClassNotFoundException e) {System.out.println ("Driver not found");}

} // main

} // DisplayProducts

4. Some things to note:

• Java is case sensitive while SQL is not. Make sure that you use upper case and lower case letters the same as those in the example.
• Many of the lines in the example are boiler plate. That means that they can be copied with only a few changes from one program to the next. The only ones you will have to change in your programs are those that specifically describe names in your database.
• The order that you access the fields in the table should be the same as the order in the database.
• When the type of a field is Text, you must use rs.getString ("ID") to get the data. But when the type is either Number or Double, you must use rs.getInt ("Quantity") or rs.getDouble ("Price").

5. Save the program, compile and run it. The result for this example is shown below.

6. Now write a program called DisplayCustomers that will display all the customers in the Customers table. You do not have to register the database again. However, if you are working in a computer lab with your database on a USB drive, you will have to register it each time. The lines in the program that you must change are shown in bold in the example. The result should look something like the following.

7. Now use the database that you created for assignment 5 and write programs that will display the tables in it. Zip up your database and the programs and send them to me at cwolf@pace.edu.