Pages Navigation Menu

Coding is much easier than you think

JDBC CallableStatement – Stored Procedure IN parameter example

JDBC CallableStatement – Stored Procedure IN parameter example

 
In this tutorial we will learn how to call Oracle stored procedure via JDBC CallableStatement, and how to pass IN parameters from Java to stored procedure.
 
Stored Procedure
 
Lets say we have a stored procedure insertEMPLOYEE in Oracle database, with IN parameters as shown below.
 

CREATE OR REPLACE PROCEDURE insertEMPLOYEE(
           userid IN EMPLOYEE.USER_ID%TYPE,
           username IN EMPLOYEE.USERNAME%TYPE,
           createdby IN EMPLOYEE.CREATED_BY%TYPE)
IS
BEGIN 
  INSERT INTO EMPLOYEE ("USER_ID", "USERNAME", "CREATED_BY") 
  VALUES (userid, username, createdby);
 
  COMMIT;
 
END;
/

 
Now we shall call this stored procedure from our java program to insert a user.
 
JDBC CallableStatement example for IN parameter.
 
File: JDBCCallableStatementINParameter.java
 
When below code is executed, a new record will be inserted into database via stored procedure “insertEMPLOYEE”.
 

package com.simplecode.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class JDBCCallableStatementINParameter 
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
			+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";
 
public static void main(String[] argc) 
{
	try 
	{
		callStoredPackINParameter();
	}
	catch (SQLException sqlE) 
	{
		System.err.println(sqlE.getErrorCode());
	}
}

private static void callStoredPackINParameter() throws SQLException 
{
	Connection con = null;
	CallableStatement callableStmt = null;
	String insertStoredPack = "{call insertEMPLOYEE(?,?,?)}";

	try 
	{
		con = getConnection();
		callableStmt = con.prepareCall(insertStoredPack);
		callableStmt.setInt(1, 100);
		callableStmt.setString(2, "Franklin");
		callableStmt.setString(3, "Admin");

		// execute insertEMPLOYEE store procedure
		callableStmt.executeUpdate();
		System.out.println("Inserted records into EMPLOYEE table");
	}
	
	catch (SQLException sqlE) 
	{
		System.err.println(sqlE.getErrorCode());
	}
	finally 
	{
		if (callableStmt != null) 
		{
			callableStmt.close();
		}

		if (con != null) 
		{
			con.close();
		}
	}
}

private static Connection getConnection() 
{

	Connection con = null;
	try 
	{
		Class.forName(dbDriver);
	}
	catch (ClassNotFoundException cnf) 
	{
		System.err.println(cnf.getStackTrace());
	}

	try 
	{
	con = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return con;
	}
	catch (SQLException sqlE) 
	{
		System.err.println(sqlE.getErrorCode());
	}
	return con;
}
}

 


 

About Mohaideen Jamil


Am currently working as a Struts 2 Developer in a reputed IT Organisations. I can help you with teaching Core java and Struts 2. Follow me on Facebook or Google Plus. If you like my tutorials, consider making a donation to this charity, thanks.