15-1. Creating a Java Database Class
Problem
You want to write a Java class that will query the database and return a result.
Solution
Create a Java class that uses the Java Database Connectivity (JDBC) API to query the Oracle Database. For example, the Java class in the following example will query the EMPLOYEES table for all employees who belong to the IT department. The example entails a complete Java class that is named Employees. This class contains a method named getItEmps() that will become a Java stored procedure. The Employees class shown here will be stored into a file named Employees.java.
import java.sql.*;
import oracle.jdbc.*;
public class Employees {
public static void getItEmps(){
String firstName = null;
String lastName = null;
String email = null;
try {
Connection conn = DriverManager.
getConnection("jdbc:default:connection:");
String sql = "SELECT FIRST_NAME, LAST_NAME, EMAIL " +
"FROM EMPLOYEES " +
"WHERE DEPARTMENT_ID = 60";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rset = pstmt.executeQuery();
while(rset.next()) {
firstName = rset.getString(1);
lastName = rset.getString(2);
email = rset.getString(3);
System.out.println(firstName + " " + lastName + " " +
email);
}
pstmt.close();
rset.close();
} catch (SQLException ex){
System.err.println("ERROR: " + ex);
}
};
The following lines from SQL*Plus show how to execute this Java in the database, followed by the output from the program. Prior to executing the code, you must load it into the database and compile it. You will learn more about doing this in the next recipe. To learn more about executing Java in the database, please see Recipe 15-5. For now, it is important to see the output that will result from a successful call to this Java program.
SQL> exec get_it_emps;
Alexander Hunold AHUNOLD
Bruce Ernst BERNST
David Austin DAUSTIN
Valli Pataballa VPATABAL
Diana Lorentz DLORENTZ
PL/SQL procedure successfully completed.
The Java class in this example performs a simple query and then prints the result. Although this class does not demonstrate the full potential of using Java, it is a good segue into Java database development.
How It Works
Java is a mature language that can be used in conjunction with PL/SQL. Sometimes it makes sense to code portions of your application in Java, while in other instances it may make sense to code the entire application in Java. Both PL/SQL and Java can coexist in the same application, and you must use PL/SQL to access Java via the database.
This recipe demonstrates how to create a simple Java class that queries the database for EMPLOYEE records. The JDBC APIs provide a way for Java programs to methodically perform the tasks you will typically want to complete whenever you access a database, whether it's querying data, updating records, or deleting rows.
A Java class that you will use to access an Oracle Database as a stored procedure must adhere to a few standards. The class must be public, and each of its methods must be pubic and static. Failure to follow these standards will render the class methods inaccessible for use as stored procedures.
The first step taken in the solution to this recipe is to obtain a connection to the database. In a Java class that lives outside the database, obtaining a connection is a performance-intensive operation, and you must pass a user name and password along with the database host name. However, obtaining a connection using stored procedures is a bit different since they reside within the database itself. The only requirement is that you pass jdbc:default:connection to the getConnection() method.
Next, the SQL query (sql) is formed as a String, and a PreparedStatement object (pstmt) is then created from it using the prepareStatement method. The prepared statement is what actually queries the database. The next line of code in the solution issues the query by calling the executeQuery() method on the prepared statement object, which returns a result set. The result set is what you need to use in order to access the rows that have been returned via the query. Use a simple while loop to traverse the rows, and obtain each of the values from the result set within each iteration of the loop by indicating the position of the column you want to retrieve. For instance, to obtain the FIRST_NAME, you will call rset.getString(1) because FIRST_NAME is the first column that is listed within the query.
Lastly, the class in the solution closes the prepared statement and result set objects. Not doing so may cause issues such as memory leaks, although Java has a very efficient garbage collection system, so it should take care of this for you. Again, closing the objects is a form of good practice to ensure that resources can be reallocated.
The Oracle Java virtual machine (JVM) also supports the use of SQLJ for database access. Use of SQLJ is beyond the scope of this book, but if you are interested in learning about it JVM, then please refer to the Oracle Java Developer Guide, which can be found at http://download.oracle.com/docs/cd/E11882_01/java.112/e10588/toc.htm.