Slide 7.9: A SELECT example

A SELECT Example

The following sample program shows how to retrieve and list all the names (first_name, last_name) from the employee_tbl2 table, which is partially defined as

   CREATE TABLE  employee_tbl2 (
     employee_id   NUMBER(6),
     first_name    VARCHAR2(20),
     last_name     VARCHAR2(25)  CONSTRAINT  emp_last_name_nn  NOT NULL,
     email         VARCHAR2(25)  CONSTRAINT  emp_email_nn      NOT NULL,
     phone_number  VARCHAR2(20) );

   ALTER TABLE  employee_tbl2  ADD (
     CONSTRAINT  emp_emp_id_pk  PRIMARY KEY( employee_id ));

Name (args[0]) =

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

class  Select {
  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( );

    // Create a Statement
    Statement stmt = conn.createStatement( );

    // Select first_name, last_name, and email columns from the employee_tbl2 table
    String  query  = "SELECT first_name, last_name, email FROM employee_tbl2";
            query += "  WHERE last_name LIKE '%" + args[0].trim( ) + "%'";
    System.out.println( "<font color='#3366CC'><h4>" + query + "</h4></font>" );
    ResultSet rset = stmt.executeQuery( query );

    // Iterate through the result and print the employee names.
    while ( rset.next( ) )
      System.out.println (
        rset.getString( 1 ) + " " + rset.getString( 2 ) + " 🠞 " +
        rset.getString( 3 ) );

    // Close the RseultSet.
    rset.close( );

    // Close the Statement.
    stmt.close( );

    // Close the Connection.
    conn.close( );
  }
}