• September 28, 2014
  • JDBC
  • Comments Off on JDBC CallableStatement Stored procedure OUT parameter example

JDBC CallableStatement Stored procedure OUT parameter example

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.javawithease.util.JDBCUtil;
 
/**
 * This class is used to get a record from DB table
 * using CallableStatement.
 * @author javawithease
 */
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 jawithease
 */
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.

 


Comments are closed.