16-1. Accessing a PL/SQL Stored Procedure via JDBC
Problem
You are writing a Java application that uses JDBC to access data, but you also want to call some PL/SQL stored procedures from within your Java application.
Solution
Use the JDBC API to connect to the database, and then execute prepareCall(), passing a string to it that consists of a PL/SQL code block that calls the stored procedure. For example, consider a stand-alone Java class that contains a method named increaseWage(). This method uses JDBC to obtain a database connection, create a CallableStatement, and then invoke the PL/SQL stored procedure that passes in the required variables.
import java.sql.*;
import oracle.jdbc.*;
public class EmployeeFacade {
public void increaseWage()
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Obtain a connection
conn = DriverManager.getConnection("jdbc:oracle:thin:@hostname:port_number:mydb",
"user",
"password");
int emp_id = 199;
double increase_pct = .02;
int upper_bound = 10000;
CallableStatement pstmt =
conn.prepareCall("begin increase_wage(?,?,?); end;");
pstmt.setInt(1, emp_id);
pstmt.setDouble(2, increase_pct);
pstmt.setInt(3, upper_bound);
pstmt.executeUpdate();
pstmt.close();
conn.commit();
conn.close();
System.out.println("Increase successful");
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
public static void main(String[] args){
EmployeeFacade facade = new EmployeeFacade();
try {
facade.increaseWage();
} catch (SQLException e){
System.err.println("A database exception has occurred.");
}
}
}
Running this code within an integrated development environment such as NetBeans would result in the following output:
run:
Increase successful
BUILD SUCCESSFUL (total time: 4 seconds)
The EmployeeFacade class contains a main() method that is used to initiate the execution of the increaseWage() method. The increaseWage() method initializes three variables that are passed to the increase_wage PL/SQL stored procedure using a CallableStatement.
How It Works
It is possible to invoke a PL/SQL stored procedure from a JDBC call just as if you were issuing a call from PL/SQL. You can do so by passing a PL/SQL code block that contains the procedure call as a string to the JDBC connection. In the solution to the example we've chosen for this recipe, a Java class named EmployeeFacade contains a method that makes a JDBC call to invoke a stored procedure. If you are unfamiliar with Java and database connectivity, you can see that using JDBC is very methodical. There are several steps that need to be taken in order to obtain a connection to the database, followed by the steps to perform the database transaction and lastly to commit the changes and close all of the JDBC constructs.
Any Java work that is done using the JDBC API must include an exception handler for the java.sql.SQLException. As the increaseWage() method demonstrates, the SQLException is handled using a Java try-catch block. Prior to the try-catch block, a couple of variables are created that the rest of the method will use. One of the variables is the java.sql.Connection, which is to be used to make a connection to the database, execute the call, and then finally close the connection. In the next couple of lines, a try-catch block is started, and a connection is obtained to the Oracle Database using the DriverManager class. The getConnection() method accepts a JDBC URL pertaining to a database as well as a user name and password.
Note It is important to maintain a close watch on JDBC connections. They can be costly for performance, and only a limited number of connections is usually available for use. For this reason, a connection should always be obtained, used, and then closed.
If a connection is successfully made to the database, then a CallableStatement is created that performs all the work against the database. If you wanted to issue a query, then you would use a PreparedStatement instead because CallableStatements are most useful for making PL/SQL calls. A string containing a PL/SQL code block is used to invoke the call to the PL/SQL stored procedure. The call is a bit different from native PL/SQL because it includes Java bind variables that represent the parameters that need to be passed into the procedure. A bind variable is represented by a question mark (?) character, and subsequent setter methods will be used to set values for each bind variable. After the CallableStatement's prepareCall() method is invoked, variables are passed to the procedure using a series of setXXX() methods on the CallableStatement. The set methods correlate with the type of data that is being passed to the stored procedure, and they provide a positional parameter that maps the variable to the bind variable position in the call. For instance, the first setInt(1, emp_id) method contains an integer variable, emp_id, and it will be passed to the bind variable in the first position within the call.
After all the variables have been set, the executeUpdate() method is called in order to execute the call to the procedure. If successful, program execution will continue. However, if unsuccessful for some reason, then a java.sql.SQLException will be thrown that will cause the execution of the Java program to be passed to the catch block. Finally, if the transaction was a success, then the connection commits the transaction, and the CallableStatement is closed, followed by the closing of the connection. You will notice that the throws SQLException clause has been placed within the method declaration. When any Java method contains a throws clause within the declaration, then you must code an exception handler for any Java code that calls the method. In this solution, the throws clause has been put into place to handle any exceptions that may be raised when closing the connection within the exception-handling catch block. For more information on Java exception handling, please see the online documentation available at http://download.oracle.com/javase/tutorial/essential/exceptions/handling.html.
The JDBC API can be used to call PL/SQL stored procedures by passing a PL/SQL code block in the form of a Java String to a CallableStatement object. The majority of the code using JDBC is spent creating and closing the database connections as well as the CallableStatements. If you are unfamiliar with JDBC, then you can learn more about it at www.oracle.com/technetwork/java/overview-141217.html. It can be used for creating small Java programs or enterprise-level Java applications. The JDBC API has been around since the early days of Java, so it is quite mature and allows you to access the database and your PL/SQL programs directly.