/*******************************************************************
*                                                                  *
*  SampleJDBC.java                                                 *
*  This program shows a typical JDBC program.                      *
*                                                                  *
*******************************************************************/

// Import the following packages and class to use JDBC.
import  java.sql.*;
import  java.io.*;
import  oracle.jdbc.pool.OracleDataSource;

public class SampleJDBC {
  public static void  main( String args[ ] ) throws SQLException {
    String user     = "C##user.id";
    String password = "password";
    String database = "65.52.222.73:1521/cdb1";

    // Open an OracleDataSource and get a connection.
    OracleDataSource ods = new OracleDataSource( );
    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    ods.setUser    ( user );
    ods.setPassword( password );
    Connection conn = ods.getConnection( );

    try {
      String    cmd;
      Statement stmt = conn.createStatement( );
      ResultSet rset;

      // Insert a product.
      if ( args[0].equals( "insert" ) ) {
        cmd  = "INSERT INTO products1 ";
        cmd += "SELECT product_seq.NEXTVAL, '" + args[1].trim( );
        cmd += "', " + args[2].trim( ) + " FROM DUAL";
        System.out.println( cmd );
        stmt.execute( cmd );
      }

      // Show the coupons including the entered product.
      else if ( args[0].equals( "select" ) ) {
        cmd  = "SELECT DISTINCT c.cid, discount FROM products1 p, coupons c,";
        cmd += " coupon_product cp WHERE name LIKE '%" + args[1].trim( ) + "%'";
        cmd += " AND p.pid = cp.pid AND cp.cid = c.cid";
        System.out.println( cmd + "\n" );
        rset = stmt.executeQuery( cmd );
        while ( rset.next( ) ) {
          System.out.print  ( "Coupon ID: "   + rset.getInt  ( 1 ) );
          System.out.println( ", Discount: $" + rset.getFloat( 2 ) );
        }
      }

      // Show all table contents.
      else if ( args[0].equals( "select all" ) ) {
        cmd  = "SELECT * FROM products1";
        System.out.println( cmd );
        rset = stmt.executeQuery( cmd );
        while ( rset.next( ) ) {
          System.out.print  ( "Product ID: " + rset.getInt   ( 1 ) );
          System.out.print  ( ", Name: "     + rset.getString( 2 ) );
          System.out.println( ", Price: $"   + rset.getFloat ( 3 ) );
        }
        cmd  = "SELECT * FROM coupons";
        System.out.print( "\n" + cmd + "\n" );
        rset = stmt.executeQuery( cmd );
        while ( rset.next( ) ) {
          System.out.print  ( "Coupon ID: "   + rset.getInt  ( 1 ) );
          System.out.println( ", Discount: $" + rset.getFloat( 2 ) );
        }
        cmd  = "SELECT * FROM coupon_product";
        System.out.print( "\n" + cmd + "\n" );
        rset = stmt.executeQuery( cmd );
        while ( rset.next( ) ) {
          System.out.print  ( "Coupon ID: "    + rset.getInt( 1 ) );
          System.out.println( ", Product ID: " + rset.getInt( 2 ) );
        }
        // Close the ResultSet.
        rset.close( );
      }
      // Close the Statement.
      stmt.close( );
    }
    catch ( SQLException ex ) {
      System.out.println( ex );
    }
    // Close the connection.
    conn.close( );
  }
}