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