Oracle Java Demo

Selecting Data

Java programs can be written to interface Oracle, which requires the use of a JDBC driver. Oracle has 2 - either user the JDBC OCI or JDBC Thin driver. The Thin drivers talk directly to Oracle, so should be faster. OCI Drivers also require SQL*Net to be installed on the Client machine.

The following is a simple example to login to the database list some columns from the Emp table.

Note, the HTML generated is basic and needs updating to make it compliant.

import java.sql.*;

class EmpTest
{
public static void main (String args [])
       throws SQLException, ClassNotFoundException
{
    // Load the Oracle JDBC driver
    Class.forName ("oracle.jdbc.driver.OracleDriver");

    /*
    Connect to the database. The format is:
Connection conn =
    DriverManager.getConnection ("jdbc:oracle:thin:@yourDB_URL:yourDB_PortNo:yourDB_Name",
             "yourOracleUsername", "yourOraclePassword");
    */
Connection conn =
    DriverManager.getConnection ("jdbc:oracle:thin:@oradb-srv.wlv.ac.uk:1522:orcle12c",
             "ops$1234567", "myOraclePassword");

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

    // Select the EMPNO & ENAME columns from the EMP table
    ResultSet rset = stmt.executeQuery
               ("SELECT empno, ename FROM Emp ORDER BY ENAME");

    System.out.println("Content-type: text/html\n\n");
System.out.println("<html>");
System.out.println("<head><title>Simple Java Program</title></head>");
    System.out.println("<body><table border=1>");
    // Iterate through the result and print the employee names
    while (rset.next ()) {
    System.out.println("<tr><td>"+rset.getString (1)+"</td>");
    System.out.println("<td>"+rset.getString (2)+"</td></tr>");
        }
    System.out.println("</table></body></html>");
// Close the connections to free up resources
rset.close();
    conn.close();
    }
}

Even if you do not understand the Java code, notice that the SQL statement - the beauty of JDBC is that normal SQL statements can be embedded in the code. Java can be used for any flashy output required, whilst SQL is used to interface to the database. Provided standard SQL is used, this program could be used with any relational database that has a JDBC driver. The only thing that would have to be changed is the connection string.

A script file needs to be created to run the code, which invokes java with the appropriate classpath. Note, in version 12c you must use the appropriate thin client driver otherwise you will get the error:


ORA-28040: No matching authentication protocol errors

For example>

/usr/bin/java -classpath .:<my ORACLE_HOME>/jdbc/lib/ojdbc7.jar EmpTest

If using mi-linux or csl-student you need to set up a cgi script in your public_html directory that contains the following:

/usr/bin/java -classpath .:/home/oracle/lib/ojdbc7.jar MyJavaFileName

For example, the above code has been saved in a file called EmpTest.java and that has been compiled to a class file. It is run from a getEmp.cgi file with the following code:

/usr/bin/java -classpath .:/home/oracle/lib/ojdbc7.jar EmpTest

This code can be run by adding this in your HTML code: <a href="getEmp.cgi">Give some meaningful name to click on</a>.

The output above is in a very simple table, this could be changed to improve the layout of the table and also to use the metadata to include the table headings. Emplist.java lists all the columns from the table, using the metadata class, ResultSetMetaData, to output the column headings.

To see the difference in output click here