JDBC CallableStatement Stored procedure OUT parameter

by

The JDBC CallableStatement is used to execute the store procedure and functions. Let us study JDBC CallableStatement by OUT parameter example.

Example:

getEmpNameByEmpId Procedure

JDBC CallableStatement Stored procedure IN parameter example.
CREATE OR REPLACE PROCEDURE getEmpNameByEmpId(
	   e_id IN EMPLOYEE.EMPLOYEE_ID%TYPE,
	   e_NAME OUT EMPLOYEE.NAME%TYPE)
IS
BEGIN
 
  SELECT NAME INTO e_NAME 
  FROM  EMPLOYEE WHERE EMPLOYEE_ID = e_id;
 
END;

JDBCTest.java

import java.sql.CallableStatement;
import java.sql.Connection;
import com.w3schools.util.JDBCUtil;
 
/**
 * This class is used to get a record from DB table
 * using CallableStatement.
 * @author w3schools
 */
public class JDBCTest {
	public static void main(String args[]){
		Connection conn = null;
		CallableStatement callableStatement = null;
		String proc = "{call getEmpNameByEmpId(?,?)}";
		try{			
			//get connection
			conn = JDBCUtil.getConnection();
 
			//create callableStatement
			callableStatement = conn.prepareCall(proc);
			callableStatement.setInt(1, 5);
			callableStatement.registerOutParameter(2,
					java.sql.Types.VARCHAR);
 
			//execute query
			callableStatement.executeUpdate();
 
			//get employee name
			String empName = callableStatement.getString(2);
			System.out.println("Emp Name: " + empName);
 
			//close connection
			callableStatement.close();
			conn.close();
 
		     System.out.println("Record inserted successfully.");
		}catch(Exception e){
			e.printStackTrace();
		}
	}	
}

JDBCUtil.java

import java.sql.Connection;
import java.sql.DriverManager;
 
/**
 * This is a utility class for JDBC connection.
 * @author w3schools
 */
public class JDBCUtil {
	//JDBC and database properties.
	private static final String DB_DRIVER = 
		           "oracle.jdbc.driver.OracleDriver";
	private static final String DB_URL = 
		        "jdbc:oracle:thin:@localhost:1521:XE";
	private static final String DB_USERNAME = "system";
	private static final String DB_PASSWORD = "oracle";
 
	public static Connection getConnection(){
		Connection conn = null;
		try{
			//Register the JDBC driver
			Class.forName(DB_DRIVER);
 
			//Open the connection
			conn = DriverManager.
			getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
 
			if(conn != null){
			   System.out.println("Successfully connected.");
			}else{
			   System.out.println("Failed to connect.");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}	
}

Output:

Successfully connected.
Emp Name: Shveta

Download this example.
 
Next Topic: JDBC CallableStatement Stored procedure batch update example.
Previous Topic: JDBC CallableStatement Stored procedure IN parameter example.

Related Topics:

JDBC CallableStatement Stored procedure IN parameter example.
JDBC CallableStatement Stored procedure OUT parameter example.
JDBC CallableStatement Stored procedure batch update example.