15-8. Creating and Calling a Java Database Function
Problem
You want to create a database function using the Java language.
Solution
Create a function written in Java, and then create a call specification for the function. Ensure that the call specification allows for the same number of parameters to pair up with the Java function and allows for a returning result. For this recipe, you will add a function to the Employees Java class that will accept an employee ID and return that employee's job title. The following code is the Java source for the function named getEmpJobTitle:
public static String getEmpJobTitle(int empId){
String jobTitle = null;
try {
Connection conn = DriverManager.
getConnection("jdbc:default:connection:");
String sql = "SELECT JOB_TITLE " +
"FROM EMPLOYEES EMP, " +
"JOBS JOBS " +
"WHERE EMP.EMPLOYEE_ID = ? " +
"AND JOBS.JOB_ID = EMP.JOB_ID";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, empId);
ResultSet rset = pstmt.executeQuery();
while(rset.next()) {
jobTitle = rset.getString(1);
}
pstmt.close();
rset.close();
} catch (SQLException ex){
System.err.println("ERROR: " + ex);
jobTitle = "N/A";
}
if (jobTitle == null){
jobTitle = "N/A";
}
return jobTitle;
}
Next is the call specification for the function:
CREATE OR REPLACE FUNCTION get_emp_job_title(emp_id IN NUMBER)
RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'Employees.getEmpJobTitle(int) return java.lang.String';
The function can now be called just like a PL/SQL function would. The following lines of code show a SQL SELECT statement that calls the function passing an employee ID number of 200.
SQL> select get_emp_job_title(200) from dual;
GET_EMP_JOB_TITLE(200)
--------------------------------------------------------------------------------
Administration Assistant
How It Works
The difference between a stored procedure and a stored function is that a function always returns a value. In the Java language, a method may or may not return a value. The difference between a PL/SQL call specification for a Java stored procedure and a PL/SQL call specifcation for a Java function is that the PL/SQL call specification will specify a return value if it is being used to invoke an underlying function. In the solution to this recipe, the example PL/SQL call specification returns a VARCHAR2 data type because the Java function that is being called will return a Java String.