JDBC/Oracle Tutorial 1

JDBC 1.0

1. Load the driver and register it with the driver manager
2. Connect to a database
3. Create a statement
4. Execute a query and retrieve the results, or make changes to the database
5. Disconnect from the database

Steps 1 and 2 are the only DBMS-specific steps.  The rest is DBMS independent with one exception:  the mappings between the DBMS and Java datatypes is somewhat DBMS-specific.  However, this is not a major issue because the driver usually handles the datatype conversions.  Therefore, to make your program work with DB2 instead Oracle, you usually only have to change the code associated with steps 1 and 2.  As you can see, JDBC is very powerful and flexible.

This tutorial will cover only JDBC 1.0, which is part of the JDK 1.1x release.  Some of the features of JDBC 2.0, which is part of the JDK 1.2x release, will be covered in the next tutorial.  Once you have learned JDBC, you should be able to learn ODBC, which is the C/C++ counterpart to JDBC, quite easily.  The basic steps in using ODBC are similar.
 

Getting Started

If you use Java from the command line, make sure that the Unix environment variable CLASSPATH includes the path /home/o/oracle/jdbc/lib/classes12.zip .

If this path is not in your classpath you can added (for this session) by executing

> export CLASSPATH=/home/o/oracle/jdbc/lib/classes12.zip

If you use Eclipse (in the undergrad lab machines), you should add  /home/o/oracle/jdbc/lib/classes12.zip as an external library into the project. Check the brief tutorial "Connecting To Oracle from Eclipse"

To run the examples we use here from home, read the brief section on "Working from Home or Wireless" and check the brief tutorial "Connecting To Oracle from Eclipse".
 

Loading and Registering Drivers

Here is the code that loads the driver and registers it with the JDBC driver manager:
 

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Here is another way of doing the above:
 

Class.forName("oracle.jdbc.driver.OracleDriver");

If there are errors loading or registering the driver, the first method throws an SQLException and the second throws a ClassNotFoundException.  If you are unfamiliar with exceptions, click here to learn what they are and how to handle them.

The purpose of a driver manager is to provide a unified interface to a set of drivers (recall that JDBC allows for simultaneous access to multiple database management systems).  It acts as a "facade" to the drivers, which are themselves interfaces, by ensuring that the correct driver function is called.  You will discover that no other functions in this tutorial other than those for loading the driver and connecting to a database include the name of the driver or DBMS as an argument.  The driver manager automatically handles the mappings from JDBC functions to driver functions.
 

Connecting to a Database

public static Connection getConnection(String url, String userid, String password) throws SQLException

The url is the DBMS-specific part.  For the Oracle thin driver, it is of the form:  "jdbc:oracle:thin:@host_name:port_number:sid", where host_name is the host name of the database server, port_number is the port number on which a "listener" is listening for connection requests, and sid is the system identifier that identifies the database server.  The url that we are using is "jdbc:oracle:thin:@dbhost.ugrad.cs.ubc.ca:1521:ug", so our connection code is
 

Connection con = DriverManager.getConnection(
  "jdbc:oracle:thin:@dbhost.ugrad.cs.ubc.ca:1522:ug", "username", "password");

The Connection object returned by getConnection() represents one connection to a particular database.  If you want to connect to another database, you will need to create another Connection object using getConnection() with the appropriate url argument (consult the driver's documentation for the format of the url).  If the database is on another DBMS, then you will also need to load and register the appropriate driver.

To disconnect from a database, use the Connection object's close() method.

Creating and Executing Statements

// con is a Connection object
Statement stmt = con.createStatement();

The SQL statement string is not specified until the statement is executed.

Executing Inserts, Updates, and Deletes

// stmt is a statement object
int rowCount = stmt.executeUpdate("INSERT INTO branch VALUES (20, 'Richmond Main', " +
                                  "'18122 No.5 Road', 'Richmond', 5252738)");

Notes:  Do not terminate SQL statements with a semicolon.  You can reuse Statement objects to execute another statement.  To indicate string nesting, alternate between the use of double and single quotation marks.

Executing Queries

stmt.executeQuery("SELECT branch_id, branch_name FROM branch " +
                  "WHERE branch_city = 'Vancouver'");

The executeQuery() method returns a ResultSet object, which maintains a cursor.  excuteQuery() never returns null.  Cursors were invented to satisfy both the SQL and host programming languages.  SQL queries handle sets of rows at a time, while Java can handle only one row at a time.  The ResultSet class makes it easy to move from row to row and to retrieve the data in the current row (the current row is the row at which the cursor is currently pointing).  Initially, the cursor points before the first row.  The next() method is used to move the cursor to the next row and make it the current row.  The first call to next() moves the cursor to the first row. next() returns false when there are no more rows.  The getXXX() methods are used to fetch column values of Java type XXX from the current row (you will learn more about the getXXX() methods in the "Converting between Java and Oracle Datatypes" section).  For specifying the column, these methods accept a column name or a column number.  Column names are not case sensitive, and column numbers start at 1 (column numbers refer to the columns in the result set).  For a list of all the getXXX() methods, refer to the Java 2 API documentation for the ResultSet class.

Here is an example of using a ResultSet object to retrieve the results of a query:
 

int branchID;
String branchName;
String branchAddr;
String branchCity;
int branchPhone;

. . .

// con is a Connection object
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM branch");

while(rs.next())
{
  branchID = rs.getInt(1);
  branchName = rs.getString("branch_name");
  branchAddr = rs.getString(3);
  branchCity = rs.getString("branch_city");
  branchPhone = rs.getInt(5);
  . . .
}

Checking for Null Return Values

There are a few important things to consider when checking for nulls.  The SQL NULL value is mapped to Java's null.  However, only object types can represent null; primitive types, such as int and float, cannot.  These types represent null as 0.  Thus when NULL is fetched, getByte(), getShort(), getInt(), getLong(), getFloat(), and getDouble() return 0 instead of null.  Moreover, the getBoolean() method returns false if NULL is fetched.  What if the value stored in the database is actually 0 or false?  To avoid this problem, use the wasNull() method to check for null values.

You cannot insert null using Statement objects.  You have to use PreparedStatement objects instead (see "Using Prepared Statements" and "Inserting Null Values").

Closing Statements

Converting between Java and Oracle Datatypes

The table below shows the getXXX() methods that can be used for some common Oracle datatypes.  An * denotes that the getXXX() method is the preferred one for retrieving values of the given Oracle datatype.  An x denotes that the getXXX() method can be used for retrieving values of the given Oracle datatype.  Use your own judgement when deciding on which getXXX() method to use for NUMBER.  For example, if only integers are stored in a NUMBER column, use getInt().  For the DATE datatype, if a DATE column only stores times, use getTime().  If a DATE column only stores dates, use getDate(). If the column stores both dates and times, use getTimestamp().  Note that getString() can be used for all the Oracle datatypes; however, use getString() only when you really want to receive a string.
 
 

Table 1.  getXXX() Methods

	C  H A R	V  A R C H A R 2	L  O N G	N  U M B E R	I  N T E G E R	F  L O A T	D  A T E	R  A W	L  O N G R A W
getByte()	x	x	x	x	x	x
getShort()	x	x	x	x	x	x
getInt()	x	x	x	x	*	x
getLong()	x	x	x	x	x	x
getFloat()	x	x	x	x	x	x
getDouble()	x	x	x	x	x	*
getBigDecimal()	x	x	x	x	x	x
getBoolean()	x	x	x	x	x	x
getString()	*	*	x	x	x	x	x	x	x
getBytes()								*	x
getDate()	x	x	x				x
getTime()	x	x	x				x
getTimestamp()	x	x	x				x
getAsciiStream()	x	x	*					x	x
getUnicodeStream()	x	x	*					x	x
getBinaryStream()								x	*
getObject()	x	x	x	x	x	x	x	x	x

Source:  Hamilton, Graham, and Rick Cattell.  Passing parameters and receiving results.  JDBC: A Java SQL API.  10 June 2001.
 

Note:  You must import java.math.*; if you want to use the BigDecimal class.
 

Using Prepared Statements

Because PreparedStatement inherits methods from Statement, you can use executeQuery() and executeUpdate() to execute a prepared statement; however, these methods are redefined to have no parameters as you will soon see.  You can also use the close() method to close a prepared statement, and the wasNull() method to check for fetched null values.

Similar to a Statement object, a PreparedStatement is created using the Connection object returned by getConnection().  However, unlike a Statement object, the SQL statement is specified when the prepared statement is created and not when it is executed.  Here's an example of creating a prepared statement:
 

// con is a Connection object created by getConnection()
// note that there is no 'd' in "prepare" in prepareStatement()
PreparedStatement ps = con.prepareStatement("UPDATE branch SET " +
   "branch_addr = ?, branch_phone = ? WHERE branch_city = 'Vancouver'");

Each placeholder is denoted by a ?.  A ? can only be used to represent a column value.  It cannot be used to represent a database object, such as a table or column name.  To build an SQL statement containing user supplied database object names, you will have to use string routines on the SQL string.  You will see an example of this in the third JDBC tutorial.

The setXXX() methods are used to substitute values for the placeholders.  setXXX() accepts a placeholder index and a value of type XXX.  The first placeholder has an index of 1.  The table below lists the valid setXXX() methods for some of the common Oracle datatypes:
 
 

Table 2.  setXXX() Methods

Oracle Datatype	setXXX()
CHAR	setString()
VARCHAR2	setString()
LONG	setString()
NUMBER	setBigDecimal()
	setBoolean()
	setByte()
	setShort()
	setInt()
	setLong()
	setFloat()
	setDouble()
INTEGER	setInt()
FLOAT	setDouble()
RAW	setBytes()
LONGRAW	setBytes()
DATE	setDate()
	setTime()
	setTimestamp()

Source:  Datatype Mappings.  Oracle10g JDBC Developer's Guide and Reference Release 2 (10.2).  March 2010.
 

Here is an example of using a prepared statement:
 

// con is a Connection object created by getConnection()
PreparedStatement ps = con.prepareStatement("INSERT INTO branch " +
            "(branch_id, branch_name, branch_city) VALUES (?, ?, 'Vancouver')");
int bid[5] = {1, 2, 3, 4, 5};
String bname[5] = {"Main", "Westside", "MacDonald", "Mountain Ridge", "Valley Drive"};

for (int i = 0; i < 5; i++)
{
  setInt(1, bid[i]);
  setString(2, bname[i]);

  ps.executeUpdate();
}

Note:  Once the value of a placeholder has been defined using setXXX(), the value will remain in the prepared statement until it is replaced by another value, or when the clearParameters() method gets called.

Inserting Null Values

Transaction Processing

To make changes to the database permanent and thus visible to other users, use the Connection object's commit() method like this:
 

// con is a Connection object
con.commit();

By default, data manipulation language statements, such as insert, delete, and update, issue an automatic commit.  You should disable auto commit mode so that you can group statements into transactions.  To disable auto commit, use the setAutoCommit() method like this:
 

con.setAutoCommit(false);

When you disable auto commit, you must manually issue commit() after each transaction.  However, if you do not issue a commit or rollback for the last transaction and auto commit is disabled, then a commit is issued automatically for you when the connection is closed.  As a general rule, commit often. This is an analogous to the "save often" rule used when editing any file.

To enable auto commit, use setAutoCommit(true).

Note:  Data definition statements, such as create, drop, and alter, issue an automatic commit regardless of whether or not auto commit is off or on.  This means that everything after the last commit or rollback is committed (you'll encounter rollback next).

To undo changes made to the database by the most recently executed transaction, use the Connection object's rollback() method like this:

 
con.rollback();

rollback() is usually used in error handling code.
 

Error Handling

Exceptions

try
{
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
}
catch (SQLException ex)
{
  . . .
}

or
public void someFunction() throws SQLException
{
  . . .
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  . . .
}

Note:  If you don't catch or throw SQLExceptions, you won't be able to compile your code.

Within the catch{} block you can obtain information on the SQLException that was just caught by calling its methods.  The getMessage() method returns the error message.  If the error originated in the Oracle server instead of the JDBC driver, then the message is prefixed with ORA-#####, where ###### is a five digit error number.  For information on what a particular error number means, check out the Oracle Error Reference at the Oracle Documentation Library.  The error reference manual describes the cause of the error and suggests a course of action to take in order to solve the problem.  Another useful method is printStackTrace(), which prints the stack trace to the standard error stream so that you can find out which functions were called prior to the error.
 

Warnings

// rs is a ResultSet object
SQLWarning wn = rs.getWarnings();

while (wn != null)
{
  System.out.println("Message: " + wn.getMessage());

  // get the next warning
  wn = wn.getNextWarning();
}

SQLWarnings are actually rare.  In fact, the Oracle JDBC drivers generally do not support them.  The most common warning is data truncation.  When a value read is truncated, a DataTrunction warning is reported (DataTruncation is a subclass of SQLWarning).  When a value written is truncated, a DataTruncation exception (not warning) is thrown.  The methods in this class allow you to find out the number of bytes actually transferred and the number of bytes that should have been transferred (see the Java 2 API documentation for the details).
 

Sample Program

Play around with the program, and study the code.  The program uses Swing to draw a login window for entering your Oracle username and password.  It is a bad idea to hard code your password in a program because someone could read the source to obtain your password.  It is also a bad idea to read the password from the command line.  This is because you cannot turn off echoing in Java.  For example, don't use code like this:
 

BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
String password = in.readLine();

Don't worry if you don't understand the code for the login window (we could write a much simpler code using a dialog window, but I didn`t have time to change the code).  The only Swing component you need is a login window, which has already been provided for you. The rest of the I/O is done through the standard Java streams. The  optional JDBC/Swing tutorial will introduce you to Swing by examining a Swing-based JDBC program.
 
 

Working from home or wireless

1. Set up Xmanager as per the instructions located http://www.cs.ubc.ca/support/available-software-xmanager
   
2. Add the SSH tunnel by going to the connection "Properties" window: Connection->SSH->Tunneling.
   Create a local outgoing connection on port 1521.
   Destination host: dbhost.ugrad.cs.ubc.ca port 1521
   The Putty equivalent can be found at http://oldsite.precedence.co.uk/nc/putty.html
   For Unix based systems you can use the following command in the Terminal:
   

   ssh -l username -L localhost:1521:dbhost.ugrad.cs.ubc.ca:1521 remote.ugrad.cs.ubc.ca

3. Disconnect and reconnect from the remote.ugrad.cs.ubc.ca server if currently connected.
   On reconnect, your firewall software may prompt for access
4. The connection string for your java program will now change to
   

   jdbc:oracle:thin:@localhost:1521:ug

Note:

Structuring Transactions

The second method is a more object-orientated solution.  It decouples objects that use the transaction from the details of the transaction itself.  The details of each transaction is encapsulated and hidden.  Existing classes do not have to be modified when you add a new transaction.  For example, you could declare an abstract class or interface called Transaction that contains the method execute() whose implementation is empty.  You could then define concrete transaction classes that implement this method.  To execute a transaction, you would simply call the transaction's execute() method.  Parameters for the transaction could be passed in when the transaction is constructed.  Another benefit is that this method supports undo, redo, and logging.  For this to work, a transaction would have to store the current state before executing the transaction.  For multiple undos, you could construct a history list of transactions.

Links

• Oracle10g JDBC Developer's Guide and Reference
• Sun's JDBC 1.0 Tutorial
• JDBC 1.0 Specification
• Java 2 API documentation
• Sun's Swing tutorial

JDBC/Oracle Tutorial 1
Last updated 11/06/2012 07:01 PM