10.B] Explain Callable statement object with an example program.
Answer:
The CallableStatement
interface is used to call stored procedures and functions in a database. Here’s a detailed explanation with the provided snippet and an example.
Explanation
- Purpose:
- Call stored procedures or functions from Java.
- Set input parameters and retrieve output parameters.
Code Snippet Explanation
- Prepare the CallableStatement:
String SQL = "{call getEmpName (?, ?)}"; // Stored procedure call cs = conn.prepareCall(SQL);
"{call getEmpName (?, ?)}"
specifies the stored proceduregetEmpName
with two parameters.
- Set Input Parameters:
cs.setInt(1, 100);
cs.setInt(1, 100);
sets the first parameter (employee ID) to 100.
- Register Output Parameters:
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
registers the second parameter as an output parameter of typeVARCHAR
.
- Execute the CallableStatement:
cs.execute();
- Executes the stored procedure.
- Retrieve Output Parameter:
String Name = cs.getString(2);
- Retrieves the output parameter value, which is the employee’s first name.
- Close the CallableStatement:
cs.close();
- Closes the
CallableStatement
to release resources.
Example Stored Procedure in Oracle
Here’s an example of an Oracle stored procedure that retrieves an employee’s name based on their ID:
CREATE OR REPLACE PROCEDURE getEmpName ( EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR ) AS BEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID; END;
Full Example Code
Below is a complete Java code example demonstrating how to use CallableStatement
with the given Oracle stored procedure.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.CallableStatement; import java.sql.SQLException; import java.sql.Types; public class CallableStatementExample { public static void main(String[] args) { // Database connection details String url = "jdbc:oracle:thin:@localhost:1521:xe"; String user = "username"; String password = "password"; // JDBC objects Connection conn = null; CallableStatement cs = null; try { // Load JDBC driver Class.forName("oracle.jdbc.driver.OracleDriver"); // Establish the connection conn = DriverManager.getConnection(url, user, password); // Prepare CallableStatement String sql = "{call getEmpName (?, ?)}"; cs = conn.prepareCall(sql); // Set input parameter cs.setInt(1, 100); // Register output parameter cs.registerOutParameter(2, Types.VARCHAR); // Execute the stored procedure cs.execute(); // Retrieve and display output parameter String name = cs.getString(2); System.out.println("Employee Name: " + name); } catch (ClassNotFoundException e) { System.out.println("JDBC Driver not found."); e.printStackTrace(); } catch (SQLException e) { System.out.println("SQL Exception occurred."); e.printStackTrace(); } finally { // Close resources try { if (cs != null) cs.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
Key Points:
- Prepare Call: Use
conn.prepareCall("{call procedure_name (?, ?)}")
to call the stored procedure. - Set Parameters: Use
setXXX()
methods for input parameters. - Register Output Parameters: Use
registerOutParameter(index, sqlType)
for output parameters. - Execute Procedure: Use
execute()
to run the stored procedure. - Retrieve Results: Use
getXXX()
methods to fetch output parameters.
Replace the database connection details with your actual database credentials.