Programming Exercise I Construction (Cont.)
  1. Database Accesses Embedded in Java (JDBC)
    2.  The JDBC program ListCourses.java lists the book titles from the inventory table if each of them contains any of the names in command-line arguments args[i] such as 
       /usr/bin/java  -Djava.security.egd=file:/dev/./urandom  \
     ListCourses  'SPONGE BOB' 'MON'
    The JDBC program then issues the following SQL command: 
       SELECT s.name, c.title FROM student1 s, course1 c, take1 t
     WHERE ( UPPER(s.name) = 'SPONGE BOB' OR
       UPPER(s.name) LIKE '%MON%' ) AND
       s.sid=t.sid AND t.cid=c.cid
    The method trim of the class String returns a copy of the string, with leading and trailing whitespace omitted. Without using the Java option: 
       -Djava.security.egd=file:/dev/./urandom
    the JDBC program ListCourses.java would be very slow and inconsistent because of an issue with the Oracle JDBC driver which uses a blocking random number generator by default on Linux (check the Stackoverflow).

    ~/public_html/cgi-bin/demo/10/ListCourses.java
    view source
    print?
    01/*******************************************************************
    02*                                                                  *
    03*  This program shows how to list the courses taken by students.   *
    04*                                                                  *
    05*******************************************************************/
    06 
    07// Import the following packages and class to use JDBC.
    08import  java.sql.*;
    09import  java.io.*;
    10import  oracle.jdbc.*;
    11import  oracle.jdbc.pool.OracleDataSource;
    12 
    13class  ListCourses {
    14  public static void  main( String args[ ] ) throws SQLException {
    15    String user     = "C##user_id";
    16    String password = "password";
    17    String database = "20.185.147.112:1521/xe";
    18 
    19    // Open an OracleDataSource and get a connection.
    20    OracleDataSource ods = new OracleDataSource( );
    21    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    22    ods.setUser    ( user );
    23    ods.setPassword( password );
    24    Connection conn = ods.getConnection( );
    25 
    26    try {
    27      Statement stmt = conn.createStatement( );
    28      String query  = "SELECT s.name, c.title FROM ";
    29             query += "student1 s, course1 c, take1 t WHERE ( ";
    30      for ( int i=0;  i<args.length;  i++ ) {
    31        if ( i != 0 )  query += " OR ";
    32        query += "UPPER(s.name)";
    33        if ( args[i].trim( ).equals( "POKE MON" ) ||
    34             args[i].trim( ).equals( "SPONGE BOB" ) )
    35          query += " = '" + args[i].trim( ) + "' ";
    36        else
    37          query += " LIKE '%" + args[i].trim( ) + "%' ";
    38      }
    39      query += " ) AND s.sid=t.sid AND t.cid=c.cid ";
    40      System.out.println( query );
    41 
    42      ResultSet rset = stmt.executeQuery( query );
    43      // Iterate through the result and print the data.
    44      if ( !rset.next( ) )
    45        System.out.println( "No classes are found!" );
    46      else
    47        do {
    48          System.out.print  ( rset.getString(1) + ": " );
    49          System.out.println( rset.getString(2) );
    50        } while ( rset.next( ) );
    51 
    52      // Close the ResultSet and Statement.
    53      rset.close( );
    54      stmt.close( );
    55    }
    56    catch ( SQLException ex ) {
    57      System.out.println( ex );
    58    }
    59    // Close the Connection.
    60    conn.close( );
    61  }
    62}

  2. Testing the Exercise Thoroughly
    3.



      Grandpa, why don’t you have any life insurance?    
      So you can all be really sad when I die.