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 another DBMS instead of 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. 
 

Getting Started

export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.zip

You must put this line after  export ORACLE_HOME= /home/o/oracle  (which you should have already added to your .bashrc file).  If CLASSPATH is already defined in that file, add the following line instead of the one above:
 

export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes12.zip:$CLASSPATH

You must add this line after the line where CLASSPATH is first defined.

Type source ~/.bashrc for changes to take place without having to close the current Unix shell.
 

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:1522: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).
 

Sequences

CREATE SEQUENCE <sequence_name>

Therefore, to create a sequence called branch_counter, we specify:
 

CREATE SEQUENCE branch_counter

We normally define sequences after we define the relation which uses the sequence, but before we insert any tuples into that relation.  Sequences are normally not created in a Java program.  To start generating sequence numbers, we do the following in our INSERT statements:
 

INSERT
INTO BRANCH (branch_id, branch_name, branch_addr, branch_city,
             branch_phone)
VALUES      (branch_counter.nextval, 'West', '7291 W. 16th',
             'Coquitlam', 5559238)

Every time the NEXTVAL variable is accessed, the sequence number corresponding to branch_counter increases by 1. Therefore, the sequence numbers generated by branch_counter for branch_id are 1, 2, ...  (Note: multiple accesses to NEXTVAL within the same SQL statement result in the same value).

NEXTVAL can be used only in the following cases:

• in an INSERT statement
• in an UPDATE statement
• in a SELECT statement which must NOT:
• be part of a view
• contain DISTINCT
• contain ORDER BY
• contain GROUP BY
• contain set operators such as UNION

START WITH   <integer>
INCREMENT BY <integer>
MAXVALUE     <integer>
MINVALUE     <integer>
CYCLE | NOCYCLE
ORDER | NOORDER

The semantics of these options should be self-explanatory. To illustrate:
 

CREATE SEQUENCE branch_counter
START WITH    10
INCREMENT BY  2
MAXVALUE      20
CYCLE

results in the sequence:
 

10, 12, 14, 16, 18, 20, 10, 12 ...

Of course, if we use sequences for primary key fields, Oracle will not allow the CYCLE option to be part of the definition of the sequence.

Another useful variable is the CURRVAL variable, which returns the most recently generated value by NEXTVAL.

Here is an example of the use of CURRVAL:
 

SELECT *
FROM   BRANCH
WHERE  branch_id = branch_counter.currval

which selects the most recently generated branch record.

To alter or delete sequences, we use the commands:
 

ALTER SEQUENCE <sequence-name> [<sequence options>]

and
 

DROP SEQUENCE <sequence-name>

respectively.

For example, to alter the branch counter sequence to increment by 100, to a maximum value of 1000:
 

ALTER SEQUENCE branch_counter
INCREMENT BY 100
MAXVALUE 1000

The only option that we cannot alter after a sequence has been created is START WITH. To change the START WITH value, we would have to delete the sequence and create it again with the new value.

To delete the branch_counter sequence:

 
DROP SEQUENCE branch_counter

You can query the settings of your sequences by referencing the SEQ table, which contains fields such as SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, LAST_NUMBER, INCREMENT_BY, and C (for cycle).

Below is an example of how to use a sequence in Java.  A branch tuple is inserted and then returned in the query that follows.
 

// stmt is a Statement object
// branch_counter is a sequence
stmt.executeUpdate("INSERT INTO branch VALUES (branch_counter.nextval, 'West', " +
                   "'7291 W.16th', 'Coquitlam', 5559238)");

ResultSet rs = stmt.executeQuery("SELECT * FROM branch WHERE " +
                                 "branch_id = branch_counter.currval");

Note:  Not all DBMSs support sequences.

Miscellaneous Topics

Getting Information about a Result Set

// rs is a ResultSet object
ResultSetMetaData rsmd = rs.getMetaData();

int count = rsmd.getColumnCount();

Floating Point Numbers

float f = 3.14;

// ps is a PreparedStatement object
ps.setFloat(1, 1234.99);

In order for this to compile, you need to cast the number to the float datatype:
 

float f = (float)3.14;
ps.setFloat(1, (float)1234.99);

Dates, Times, and Timestamps

stmt.excuteUpdate("INSERT INTO abc VALUES ({d '2001-06-23'})");

You can find out the meaning of each format symbol by checking out the Java 2 API documentation for the SimpleDateFormat class.  Note that 'M' is for month and 'm' is for minute in hour.

In addition, time literals must be of the form {t 'H:mm:ss'}.  'H' is for hour in day (0-23) and 'h' is for hour in am/pm (1-12).  For example, the following query returns tuples that have inspectionTime equal to 05:12 and 45 seconds:
 

stmt.executeQuery("SELECT xyz FROM abc WHERE inspectionTime = {t '05:12:45'}");

The format for timestamp literals is {ts 'yyyy-MM-dd H:mm:ss.[f...]} e.g., {ts '2001-05-18 08:15:00'}.  The "f...", which represents fractions of a second, is optional (you won't find the symbol 'f' in the documentation for the SimpleDateFormat class).

Everything between the {} braces is mapped by the driver into DBMS-specific syntax.  So on input to Oracle, the driver will convert dates, times, and timestamps in ISO format to their equivalent Oracle representations.  For example, {d, '2001-06-23'} will get converted to "23 JUN 2001", {t, '13:30:45'} will get converted to "13:30:45" assuming the Oracle server is configured to use a 24 hour clock, and {ts '2001-05-18 08:15:00'} will get converted to "MAY 18 2001 08:15:00".

For information on formatting dates, times, and timestamps refer to the Java 2 API documentation for the SimpleDateFormat class and the DateFormat abstract class.  These classes are used to parse and format dates.  Parsing converts a date string to a date object.  Formatting converts a date object to a date string.  The Calendar and GregorianCalendar classes are also worth checking out.  They are used to manipulate and obtain information on date fields (e.g., year, month, day).  You can also check out the Formatting tutorial at Sun.

If you check the Java 2 API documentation, you will notice that there are two classes to represent dates.  One is java.util.Date and the other is java.sql.Date.  A java.util.Date represents the date and time with millisecond precision.  A java.sql.Date is basically a JDBC-compliant java.util.Date with the time component zeroed.

Here are some examples:
 

Example 1.

In this example, the date string "18/08/01" is converted into a valid JDBC date, and then it is inserted into the, initially, empty table "abc".  The date is then fetched and displayed in its original format.  The formatting and parsing methods in the SimpleDateFormat class accept/return java.util.Date, but the setDate() method in the PreparedStatement class and the getDate() method in the ResultSet class accept/return java.sql.Date.  Therefore, you should make sure that you use the correct date type.  The getTime() and setTime() methods of both java.util.Date and java.sql.Date are used to convert between the two dates.
 

import java.sql.*;
import java.util.*;

// for SimpleDateFormat
import java.text.*;
. . .

public static void main(String args[])
              throws SQLException, ParseException
{
  . . .
  // con is a Connection object
  con.setAutoCommit(false);

  String stringDate = new String("18/08/01");

  SimpleDateFormat fm = new SimpleDateFormat("dd/MM/yy");

  // parse() interprets a string according to the
  // SimpleDateFormat's format pattern and then converts
  // the string to a date object
  java.util.Date utilDate = fm.parse(stringDate);

  // The getTime() method returns the the number of
  // milliseconds between January 1, 1970, 00:00:00 GMT
  // and the given date. Dates are represented
  // with millisecond precision.
  // The constructor for java.sql.Date zeroes the time
  // component.
  java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());

  PreparedStatement ps = con.prepareStatement(
                          "INSERT INTO abc VALUES (?)");

  ps.setDate(1, sqlDate);

  ps.executeUpdate();
  con.commit();

  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("SELECT * FROM abc");

  while(rs.next())
  {
     sqlDate = rs.getDate(1);
 
     // we need to convert the date to java.util.Date
     // so that we can use format()
     utilDate.setTime(sqlDate.getTime());
 
     // convert the fetched JDBC date to the original format 
     // format() returns a string
     System.out.println(fm.format(utilDate));
  }
  . . .
}

Example 2.

This example returns today's date if today's date + 60 days is less than or equal to today's date + 2 months.  The Oracle keyword "sysdate" represents the current date.  The table "dual" is a dummy table provided by Oracle.  Note that you can add and subtract constants to and from dates.  The constants are treated as days.  To add and subtract months and years, use the add() method in the GregorianCalendar class and the constants in the Calendar class.  Note, also, that the standard comparison operators such as <, =, <>, != (<> is the same as !=), <=, etc. can be used with dates.
 

import java.sql.*;

// for GregorianCalendar
import java.util.*;
. . .

// con is a Connection object
PreparedStatement ps = con.prepareStatement("SELECT sysdate " + 
                       "FROM dual where (sysdate + 60) <= ?");
// creates a new calendar initialized to the current date and time
GregorianCalendar gregCalendar = new GregorianCalendar();

// add 2 months to the current date and time
gregCalendar.add(Calendar.MONTH, 2);

// gregCalendar.getTime() returns a java.util.Date
// the second getTime() returns the date in milliseconds
// recall that dates are represented with millisecond precision
java.sql.Date sqlDate = new java.sql.Date(
                        gregCalendar.getTime().getTime());
ps.setDate(1, sqlDate);
 
ResultSet rs = ps.executeQuery();

if (rs.next())
{
  sqlDate = rs.getDate(1);
  System.out.println(sqlDate.toString());
}
. . .

For information about the Oracle date datatype, such as how to use SQL*Plus (not JDBC) to SELECT or INSERT a date with both a date and time component, check out this link (note:  the date format elements listed on that page is not the same as those in the SimpleDateFormat class).  Because the Oracle date datatype includes both a date and time component, the ResultSetMetaData's getColumnType() method returns Types.TIMESTAMP instead of Types.DATE .   You will encounter getColumnType() in the sample program in tutorial 3.

Formatting Numbers

Here is an example:

This example will fetch numbers from column 'a'  (Oracle type float) in table "xyz", multiply each number by 1.07, and then format them to two decimal places.
 

import java.sql.*;

// for NumberFormat
import java.text.*;
. . .

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

// getNumberInstance() returns a general purpose number formatter
NumberFormat numFormatter = NumberFormat.getNumberInstance();
double num;

while (rs.next())
{
  num = rs.getDouble(1);
  num *= 1.07;

  // Set the maximum and minimum number of digits to 2.
  // If necessary, rounding will be performed and
  // zeros will be added. 
  numFormatter.setMinimumFractionDigits(2);
  numFormatter.setMaximumFractionDigits(2);

  // format() formats the number and returns it as a string
  System.out.println(numFormatter.format(num));
}
. . .

To place a '$' in front of each number, replace NumberFormat.getNumberInstance() with NumberFormat.getCurrencyInstance(Locale.CANADA).  You will need to import java.util.* to use the Locale class.

Using '_' and '%' in LIKE Clauses

// stmt is a Statement object
stmt.executeQuery("SELECT branch_name FROM branch WHERE branch_name " +
                  "LIKE '%&_%' {ESCAPE '&'}");

The {ESCAPE '&'} is used to indicate to the Oracle JDBC driver that & is the escape character.  You should choose an escape character that is not part of the search string.

Note:  If you want to use the backslash character (\) as an escape character, you must enter it twice (that is, \\).  For example:
 

stmt.executeQuery("SELECT branch_name FROM branch WHERE branch_name " +
                  "LIKE '%\\_%' {ESCAPE '\\'}");

This method of specifying special characters in LIKE clauses is specific to Oracle.  Don't confuse this with how special characters are specified in a Java string literal (the \ character is used as the escape character).

Apostrophes

// con is a Connection object
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO xyz VALUES ('Richard''s car')");

will insert Richard's car into table xyz.  For some reason, you cannot use the string escape character \ to specify the apostrophe.  It is likely that the string is sent directly to Oracle without being processed first by Java because apostrophes are, in fact, specified in Oracle using ''.

However, for the PreparedStatement method setString(), you need to use the string escape character \ in order to use an apostrophe.  You cannot use '' because the string is processed by Java before it is sent to Oracle (this probably has something to do with how a prepared statement is handled).  For example,

 
PreparedStatement ps = con.prepareStatement("INSERT INTO xyz VALUES (?)");
ps.setString(1,"Richard\'s car");
ps.executeUpdate();

will insert Richard's car into table xyz.

For other special characters such as ", you need to use the \ escape character.  For example,

 
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO xyz VALUES ('\"ABC\"')");

will insert "ABC" into table xyz.

Sample Program

You will need to include hb15.zip in the CLASSPATH environment variable in order to compile the program.  For example, if hb15.zip is in the same directory as the program code, the CLASSPATH variable in your .bashrc file may look like this (on the UNIX platform, path entries are separated using ":"):
 

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

Play around with the program, and study the code.  The program uses Swing (a GUI package) 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();

The  optional JDBC/Swing tutorial will introduce you to Swing by examining a Swing-based JDBC program.
 

Note:

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 1522.
   Destination host: dbhost.ugrad.cs.ubc.ca port 1522
   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:1522:dbhost.ugrad.cs.ubc.ca:1522 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:1522:ug

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 May 8 2011