CIS 133

Java Database Connectivity (JDBC)

Quick overview of a database:

A database is a collection of tables.
A table is used to store information of a specific category- Addresses, for example.
A table is a collection of records, each record represents one address.
A record is used to store the details of a specific instance of a category- a particular address.
A record is a collection of fields, each field represents a piece of information about the address.

To connect to a database, we need the following:

A database. Download the Address.mdb from the website.
An ODBC Data Source. Refer to "Setting Up An ODBC DataSource" from the web site. ODBC stands for Open Database Connectivity. The Data Source is how we connect to the database.
A Database Driver - we'll be using the ODBC-JDBC Bridge Driver
A Driver Manager
Our program

The 'java.sql' package is required. From this package, we will use three components:

Connection - maintains the connection to the database, data is transferred over the connection
Statement - used for executing requests on the database
ResultSet - stores the information retrieved from the database

We will use the following class level variables:

	private String cls = "sun.jdbc.odbc.JdbcOdbcDriver"
	private String url = "jdbc:odbc:CIS133"

With the variables set, we're ready to connect:

	try {
		Class.forName(cls); // loads the driver into memory
		con = DriverManager.getConnection(url); // creates the connection for MS ACCESS
		stm = con.createStatement(); // creates the statement
	}
	catch (ClassNotFoundException cnfe) {}
	catch (SQLException sqle) {}

Now we can execute queries. There are two methods on the Statement component:

executeQuery(String sql) - for SELECT statements, returns a ResultSet
executeUpdate(String sql) - for INSERT, UPDATE, and DELETE statements, returns an int for the number of rows affected

Retrieving a ResultSet works like this:

	ResultSet rs = null;
	try {
		rs = stm.executeQuery(sql);
	}
	catch (SQLException sqle) {}

Once we have the ResultSet, we'll want to read data from the records. When a ResultSet is returned, we are not automatically pointing to the first record. It is necessary to call the next() method on the ResultSet. This method returns a boolean: true if it could advance to the next record, false if it could not.

To loop through all records in a ResultSet, we could use the following while:

	ResultSet rs = null;
	try {
		rs = stm.executeQuery(sql);
		if ( rs != null ) {
			while ( rs.next() ) {
			}
		}
	}
	catch (SQLException sqle) {}

The ResultSet object has two get methods for each data type:

	getString(int columnIndex);
	getString(String columnName);

	getLong(int columnIndex);
	getLong(String columnName);

	getInt(int columnIndex);
	getInt(String columnName);

These are just a few. Refer to the JavaDocs for more. Each method returns the data type asked for. You can pull the information by referencing either the column index or the column name. Which one should you use?

Getting by column index works when you will be guaranteed to get the ResultSet fields in the same order every time, but the fields names may change.
Getting by column name works when you will be guaranteed the fields names won't change, but you never know what order the fields will be in. For readability of code, it is advisable to use this method.

Let's look at an example:

	String sql = "SELECT PersonID, PersonName FROM Persons";
	ResultSet rs = null;
	try {
		rs = stm.executeQuery(sql);
		if ( rs != null ) {
			while ( rs.next() ) {
				System.out.println("Person ID  : " + rs.getLong("PersonID");
				System.out.println("Person Name: " + rs.getString("PersonName"));
				System.out.println("");
			}
		}
		rs.close();
		stm.close();
		con.close();
	}
	catch (SQLException sqle) {}
	finally {
		rs = null;
		stm = null;
		con = null;
	}

Notice in this last example that we call the close method on all the objects, and added a finally clause to set the objects to null.

For the homework, you will be given a Java class that manages the connection and returns the ResultSet for you: Addresses.java. Refer to the documentation on what you can do with it.

Use the Addresses component in your program to retrieve a ResultSet of addresses from the database.

	ResultSet rs = null;
	Addresses a = new Addresses();
	try {
		rs = a.getAddresses();
		if ( rs != null ) {
			while ( rs.next() ) {
			}
		}
		rs.close();
		stm.close();
		con.close();
	}
	catch (SQLException sqle) {}
	finally {
		rs = null;
		stm = null;
		con = null;
	}

The fields returned are:

AddressID
FirstName
LastName
Address1
Address2
City
State
ZipCode