Java Database Connectivity (JDBC)


Quick overview of a database:

To connect to a database, we need the following:

  1. A database. Download the Address.mdb from the website.
  2. 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.
  3. A Database Driver - we'll be using the ODBC-JDBC Bridge Driver
  4. A Driver Manager
  5. Our program

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

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?

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