Using 'Interval Day To Second' Datatype from JDBC applications

Date: 22-Jan-2004

Objective

Table of Contents

Introduction
Prerequisites
Software Requirements
Handling the Oracle Datatype, 'Interval Day To Second' in JDBC
Running the Sample Java Application
Useful References

Introduction

JDBC (Java Database Connectivity) is a standard Java interface for connecting from Java to relational databases. The JDBC standard was defined by Sun Microsystems, allowing independent providers to implement and extend the standard with their own.

In addition to supporting the standard JDBC API, Oracle drivers have extensions to support Oracle-specific datatypes and to enhance performance. This document introduces the new Oracle JDBC 10g API for handling the Oracle datatype, 'Interval Day To Second' in a JDBC class. For more information about Oracle JDBC, refer the Useful References section in this document.

Prerequisites

To work your way through this HowTo, it is necessary to have a basic understanding of the fundamental concepts of JDBC, and how to use the JDBC APIs to connect to the database for the basic CRUD(Create, Read, Update, and Delete)- the essential database operations. In addition, you need to know the basic Oracle database concepts along with different datatypes available with the database.

Software Requirements

• Oracle10g Database Release or later. This can be downloaded from here.
• Oracle Database 10g JDBC Driver. This can be downloaded from here.
• JDK1.4.x or above. This can be downloaded from here.
  

Note: JDBC drivers classes are available with Oracle Client or Database installation and need not be downloaded separately.

Handling the Oracle datatype, 'Interval Day To Second' in JDBC

The INTERVAL DAY TO SECOND datatype provides greater granularity, allowing you to express time intervals in terms of days, hours, minutes, seconds, and even fractions of a second. With JDBC 3.0, the JDBC applications are now capable of handling this Interval Day To Second Datatype for better management and control of time related data. Oracle supports this datatype from Oracle database 10g JDBC.

The datatype INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision) stores a period of time in days, hours, minutes, and seconds, where

• day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.
• fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6.
  

Sample scenario:

Let us assume a database table 'flight_duration' which has to store the flight duration in terms of days, hours, minutes and seconds. Following is the SQL statement to create 'flight_duration' table in the database.

SQL> CREATE TABLE flight_duration(name      VARCHAR2(20), 
                                  route     VARCHAR2(50), 
                                  duration  INTERVAL DAY TO SECOND);

We shall use the Oracle Database 10g JDBC APIs like getINTERVALDS(), setINTERVALDS() to insert and access this 'duration' column.

Following is the code snippet that demonstrates the usage of the JDBC APIs for a 'INSERT' and 'SELECT' operations with 'flight_duration' table. Please refer the IntervalDayToSecond.java file for the full runnable Java class.

	  .........
      import oracle.sql.INTERVALDS;
      .........
      // Create the SQL statement for inserting a record.
      sqlString = "INSERT INTO flight_duration VALUES(?,?,?)";

      // Create the PreparedStatement object with the SQL query String.    
      pstmt = (OraclePreparedStatement)conn.prepareStatement(sqlString);
 
      // Bind the first two parameters with flight name and route values.
      pstmt.setString(1,"AC-2001");
      pstmt.setString(2,"Singapore to SFO");

      // Bind the second parameter with INTERVALDS object.
      // Format for specifying day and time for INTERVALDS() is:
      // Days Hour:Mons:Seconds.fractionalseconds
      pstmt.setINTERVALDS(3, new INTERVALDS ("2 03:15:15.0"));
      
      // Execute the PreparedStatement.
      boolean bool = pstmt.execute();

      // Create the SQL statement for selecting the records after insert.
      sqlString ="SELECT name,route,duration FROM flight_duration";

      // Execute the prepared statement.
      pstmt = (OraclePreparedStatement)conn.prepareStatement(sqlString);

      // Execute the PreparedStatement.
      res = (OracleResultSet)pstmt.executeQuery();
      
      ............
      
      // Loop through the resultset and get the Interval day to second value.
      while (res.next()) {
        ............
        dur  = res.getINTERVALDS(3).toString();
      }
	  ....................
                  

Running the Sample Java Application

• Copy the Java class file IntervalDayToSecond.java from here to a directory say for example, IntervalDayToSecond
• From the IntervalDayToSecond directory, include the JDBC driver class in the CLASSPATH.
  • Use ojdbc14.jar with JDK 1.4.x.
• Also include, the current directory(.) to the CLASSPATH.
• Make sure you have created the database table 'flight_duration' which has a column 'duration' of the datatype Interval Day To Second. Refer the sample scenario section for more details.
• Edit the file IntervalDayToSecond.java to set your database details. Change the following lines in the file:
  
  // Set database URL details.
String url = "jdbc:oracle:thin:@localhost:1521:orcl";

// Get the database connection.
conn = DriverManager.getConnection(url,"scott","tiger");

Mention the username and password under which the table was created. Save the file.
  
  
• Compile the Java file using:
  javac -d . IntervalDayToSecond.java
• Run the class using:
  java oracle.otnsamples.jdbc.IntervalDayToSecond
  
  This will display the output as follows:
  
  Selected records before insert are as follows
  Flight Name Route Duration
  
  Selected records after insert are as follows
  Flight Name   Destination             Duration
  AC-2001       Singapore to SFO   2 3:15:15.
  

Useful References

• Oracle9iJDBC Developer's Guide and Reference
• JDBC API Documentation by Sun
• JDBC How To documents on OTN
• Basic JDBC samples on OTN
• JDBC WhitePaper
• JDBC Technology Page