Connecting to Oracle from Java with JDBC

Requirements

• Java SDK, version 1.2 or greater
• Oracle9i Thin Driver (classes12.zip)
  • Available from Oracle.com or from here.

Good sources for more detailed information

• JavaDoc for java.sql and javax.sql.
• Java Enterprise In a Nutshell, published by O'Reilly, ISBN 1-56592-483-5
• SQL In a Nutshell, published by O'Reilly, ISBN 1-56592-744-3

Installing the Oracle9i Thin Driver

1. Include classes12.zip in your CLASSPATH Environment Variable
2. Alternatively, extract the files from it into a directory already in the CLASSPATH

Connecting to Oracle with JDBC

1. Create a new Java program with a main() function.
2. Import the java.sql.* package.
3. Load the Oracle JDBC thin driver.
   • Insert the following code into main():
     

     try { Class.forName("oracle.jdbc.OracleDriver"); } catch( ClassNotFoundException e ) { System.err.println( "Could not load class oracle.jdbc.OracleDriver" ); return; }

   • JDBC drivers manage the custom networking that must be done to interface to a database.
   • The thin driver does as little as possible and depends on client code running on the server.
4. Open a connection to Oracle
   • Use this code to create a connection object:
     

     Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@csse-sp-4.rose-hulman.edu:1521:cs333", "student", "student" );

   • Check if conn == null. If it is, the connection failed.
5. Execute a query and retrieve a ResultSet
   • Use this code to execute a query:
     

     ResultSet rset = sc.getResultSet( "select FIRST_NAME,LAST_NAME from PERSON" );

   • A ResultSet lets you scroll through rows and read columns of query results.
6. Read the results
   • You can only read one row at a time from a ResultSet. The current row is changed with the next() function.
   • To access a column, use one of the getXXX() functions. These get a value as any of several Java variable types. For instance, getFloat() retrieves the value as a floating point number. You should be careful that the field you are accessing can be converted to this type, or an exception will be thrown.
   • The getXXX() functions have a single parameter: either the column number (as an int) or the column name (as a String)
7. Close the connection
   • When you are done with a connection, call the close() function of the Connection class.

A Simple Example

1. A simple example may be downloaded from here.
   • Compile and run it. It should list 15 rows.
   • If you would like to log in as yourself instead of student, the sample database table used for this example is available here.