- Database Accesses Embedded in Java (JDBC)
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
|
/*******************************************************************
* *
* This program shows how to list the courses taken by students. *
* *
*******************************************************************/
// Import the following packages and class to use JDBC.
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class ListCourses {
public static void main( String args[ ] ) throws SQLException {
String user = "C##user_id";
String password = "password";
String database = "20.185.147.112:1521/xe";
// 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 {
Statement stmt = conn.createStatement( );
String query = "SELECT s.name, c.title FROM ";
query += "student1 s, course1 c, take1 t WHERE ( ";
for ( int i=0; i<args.length; i++ ) {
if ( i != 0 ) query += " OR ";
query += "UPPER(s.name)";
if ( args[i].trim( ).equals( "POKE MON" ) ||
args[i].trim( ).equals( "SPONGE BOB" ) )
query += " = '" + args[i].trim( ) + "' ";
else
query += " LIKE '%" + args[i].trim( ) + "%' ";
}
query += " ) AND s.sid=t.sid AND t.cid=c.cid ";
System.out.println( query );
ResultSet rset = stmt.executeQuery( query );
// Iterate through the result and print the data.
if ( !rset.next( ) )
System.out.println( "No classes are found!" );
else
do {
System.out.print ( rset.getString(1) + ": " );
System.out.println( rset.getString(2) );
} while ( rset.next( ) );
// Close the ResultSet and Statement.
rset.close( );
stmt.close( );
}
catch ( SQLException ex ) {
System.out.println( ex );
}
// Close the Connection.
conn.close( );
}
}
|
- Testing the Exercise Thoroughly