JDBC

To connect to a database using ODBC, we need the following:

1. A database. Download the CIS135.mdb from the website.
2. A database library, DAO for Microsoft Access
3. An ODBC Data Source. Refer to "Setting Up An ODBC DataSource" from the web site.
4. A Database Driver
5. A Driver Manager
6. Your program

It is also possible to connect to a database using what are called native drivers. Oracle comes with Java class files that create a native connection, thus by-passing ODBC entirely. This would take the place of 2 and 4 above. #3 would be replaced by the URL of the database.

ODBC

ODBC stands for Open Database Connectivity. It is a protocol for connecting to databases. A protocol is simply a set of rules to be followed.

DSN

DSN stands for Data Source Name. It is used to specify the database library and the location of the database for ODBC-compliant databases.

Connection

The java.sql package comes with a Connection class. This is used to open the connection to the database. Minimally, the Connection needs the URL and a database driver class. Since we are doing ODBC, our URL is the DSN and the driver class is the JDBC-ODBC bridge class.

private final String url = "jdbc:odbc:CIS135";
private final String cls = "sun.jdbc.odbc.JdbcOdbcDriver";

A connection is created by first loading the driver class, then creating the Connection from the DriverManager. The DriverManager is a class that comes with the java.sql package.

Class.forName(cls);
con = DriverManager.getConnection(url);

Statement

The java.sql package comes with a Statement class. This is used for executing queries. A Statement is created from the Connection:

stm = con.createStatement();

There are two basic types of queries:

1. those that return data (SELECT)
2. those that modify data (INSERT, UPDATE, DELETE)

For type 1, use the executeQuery() method of the Statement. This methods takes a SELECT query as an argument and returns a ResultSet.

For type 2, use the executeUpdate() method of the Statement, This method takes an INSERT/UPDATE/DELETE query as an argument and returns an integer specifying the number of rows affected by the query. 

Another type of statement object is the PreparedStatement. This is used for parameterized queries. This is useful when you have a query that will run several times, but with different parameter values.

ResultSet

The java.sql package comes with a ResultSet class. This is used for storing records returned as a result of a SELECT query.

We get a ResultSet from the Statement when we use the executeQuery() method:

rs = stm.executeQuery(sql);

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();
	}
	catch (SQLException sqle) {}
	finally {
		rs = null;
	}