The layers for connecting to a database using the DriverManager class:

ODBC	Native
Java Application JDBC API JDBC Driver Manager JDBC-ODBC Driver ODBC DB Client Library Database	Java Application JDBC API JDBC Driver Manager Native Driver Database

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

• Connection
• Statement
• ResultSet

For an ODBC connection, it is necessary to have a DSN set up first. Once that's done, we set up the following variables:

Type	Name	Value for MS Access	Value for Oracle
String	cls	sun.jdbc.odbc.JdbcOdbcDriver	oracle.jdbc.driver.OracleDriver
String	url	jdbc:odbc:DSNName	jdbc:oracle:thin:@servername:serverport:SID
String	usr	N/A	USERNAME
String	pwd	N/A	PASSWORD

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
		con = DriverManager.getConnection(url, usr, pwd); // creates the connection for Oracle
		stm = con.createStatement(); // creates the statement
	}
	catch (ClassNotFoundException cnfe) {}
	catch (SQLException sqle) {}
			

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

1. executeQuery(String sql) - for SELECT statements, returns a ResultSet
2. 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.

Home Next