/*
 * @author  Savitha
 * @version 1.0
 *
 * Development Environment        :  Oracle JDeveloper 10g
 * Name of the Application        :  IntervalDayToSecond.java
 * Creation/Modification History  :
 *
 *    Savitha     19-Jan-2004      Created.
 */
package oracle.otnsamples.jdbc;

// Import Java SQL classes.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// Import Oracle Specific JDBC classes.
import oracle.sql.INTERVALDS;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;

/**
 * This class demonstrates the usage of INTERVAL DAY TO SECOND datatype. This is
 * an Oracle Database 10g JDBC feature. JDBC applications can now use the Oracle 
 * datatype - INTERVAL DAY TO SECOND datatype for improved time management.
 */
public class IntervalDayToSecond {

 /**
  * Class constructor to instantiate the class and get handles to the inner
  * methods.
  */
  public IntervalDayToSecond() {
  }

  /**
   * Main method to run the class.
   * Does not take in any input arguments.
   * @param args
   */
  public static void main(String[] args) {
  
    // Instantiate the class.
    IntervalDayToSecond intervalDayToSecond = new IntervalDayToSecond();
    
    try {

      // Call the method that demonstrates INTERVAL DAY TO SECOND datatype usage
      // in a Java class.
      intervalDayToSecond.getFlightDuration(); 

    } catch (Exception ex) {
        System.out.println("Some Exception in Main method: "+ex.toString());
    } 
  }
  
 /**
  * Method that demonstrates the usage of INTERVAL DAY TO SECOND datatype in
  * JDBC applications. The method tries to 'select' and 'insert' into the 
  * database table: flight_duration which has a column of INTERVAL DAY TO SECOND 
  * datatype. New Oracle Database 10g JDBC APIs getINTERVALDS(), setINTERVALDS() 
  * are used to access database's INTERVAL DAY TO SECOND datatype.
  */
  private void getFlightDuration() {
  
    // Database Connection object 
    Connection conn = null;

    // Create an OraclePreparedStatement object.
    OraclePreparedStatement pstmt = null;
    
    // Create an OracleResultSet object.
    OracleResultSet res = null;
    try {

      // Get database connection.
      conn = this.getConnection();
      
      // Create the SQL statement for selecting the records.
      String sqlString ="SELECT name,route,duration FROM flight_duration";

      // Create the PreparedStatement object with the SQL query String.
      pstmt = (OraclePreparedStatement)conn.prepareStatement(sqlString);

      // Execute the PreparedStatement.
      res = (OracleResultSet)pstmt.executeQuery();

      System.out.println("Selected records before insert are as follows\n");
      System.out.println("Flight Name      Route       Duration\n");
      
      String name = null;
      String dest = null;
      String dur  = null;
      // Loop through the resultset and get the Interval day to second value.
      while (res.next()) {
        name = res.getString(1);
        dest = res.getString(2);
        dur  = res.getINTERVALDS(3).toString();
        System.out.println(name+"      "+dest+"      "+dur);
      }  
      
      // Close the ResultSet and PreparedStatement objects so that they can be
      // re-used.
      if (res != null) res.close();
      if (pstmt != null) pstmt.close();

      // 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 destination.
      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();
      
      System.out.println("Selected records after insert are as follows\n");
      System.out.println("Flight Name      Route       Duration\n");
      
      // Loop through the resultset and get the Interval day to second value.
      while (res.next()) {
        name = res.getString(1);
        dest = res.getString(2);
        dur  = res.getINTERVALDS(3).toString();
        System.out.println(name+"      "+dest+"       "+dur);
      }  
      
      // Handle exceptions.      
    } catch (SQLException sqlEx) {
        System.out.println("SQL Exception: "+sqlEx.toString());
    } catch (Exception ex) {
        System.out.println("Exception: "+ex.toString());
    } finally {
       try  {
         // Finally close ResultSet, PreparedStatement and Connection objects.
         if (res != null) res.close();
         if (pstmt != null) pstmt.close();
         if ((conn!=null) || (!conn.isClosed())) conn.close();
       } catch(SQLException sqlEx) {
           System.out.println("Exception while closing database connection: "
                               +sqlEx.toString());  
       }
    }
  }
  
  private Connection getConnection() throws ClassNotFoundException, Exception{
  
     // Database Connection object 
    Connection conn = null;
  
    // Set database URL details.
    String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    try {
      
      // Instantiate the driver class.
      Class.forName("oracle.jdbc.OracleDriver");
      
      // Get the database connection.
      conn = DriverManager.getConnection(url,"scott","tiger");
    }catch (Exception ex) {
        System.out.println("Exception while getting database connection: "
                           +ex.toString() );
    }
    // Return the connection object.
    return conn;
  }
}