JDBC/Oracle Tutorial 2

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.

Links

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

Last updated: November 05, 2010 11:12 PM