/*******************************************************************
*                                                                  *
*  ListRoutes.java                                                 *
*  This program lists the routes including the bus stop keywords.  *
*                                                                  *
*******************************************************************/

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

public class  ListRoutes {
  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 {
      // Create, compose, and execute a statement.
      Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE );
      String query;
      query  = "SELECT DISTINCT rs.rid FROM route_stop rs, stop s";
      query += " WHERE ( ";
      if ( args.length == 0 )
        query += "UPPER(s.name) LIKE '%%'";
      else
        for ( int i = 0; i < args.length; i++ ) {
          query += "UPPER(s.name) LIKE '%" + args[i] + "%'";
          if ( i != args.length-1 )  query += " OR ";
        }
      query += " ) AND s.sid = rs.sid ORDER BY rs.rid";
      System.out.println( query );
      ResultSet rset = stmt.executeQuery( query );

      if ( !rset.next( ) )
        System.out.print( "Nothing is found!" );
      else {
        System.out.print( "The following routes contain the stops: " );
        rset.beforeFirst( );
        // Iterate through the result and print the data.
        while ( rset.next( ) )
          System.out.print( "Route " + rset.getInt(1) );
      }
      // Close the ResultSet and Statement.
      rset.close( );
      stmt.close( );
    }
    catch ( SQLException ex ) {
      System.out.println( ex );
    }
    // Close the Connection.
    conn.close( );
  }
}