JDBC CallableStatement with Oracle Stored Procedure Example Tutorial

In this Article We will learn how to use JDBC CallableStatement along with Stored Procedures, Cursors, STRUCT etc. The CallableStatement interface used to execute SQL stored procedures. The JDBC API provides a stored procedure SQL escape syntax that allows stored procedures to be called in a standard way for all RDBMSs. For this Example we have used Oracle Database. We will start by using CallableStatement with IN and OUT parameters. Later on we will look into Oracle specific STRUCT and Cursor examples.
 
 
 
 
 

1. Stored Procedure With IN, OUT Parameters

Stored Procedures are group of statements that we compile in the database for some task. Stored procedures are beneficial when we are dealing with multiple tables with complex scenario and rather than sending multiple queries to the database, we can send required data to the stored procedure and have the logic executed in the database server itself.

1.1 Table and Stored Procedure

So let us start by creating the table in Oracle:

CREATE TABLE STUDENT (
SID NUMBER(3,0) PRIMARY KEY,
FNAME VARCHAR2(10) NOT NULL,
LNAME VARCHAR2(10),
DEPT VARCHAR2(10) DEFAULT 'N/A',
year number(1,0),
email varchar2(30)
);

It is a simple table to store students’s data in a college or university. I have also used a stored procedure to insert data into this table.

insert_proc.sql:

CREATE OR REPLACE PROCEDURE insertStudent(fname in varchar2,lname in varchar2,dept in varchar2,year in number,email in varchar2,sid out number)
AS
max_id NUMBER(3);
new_id NUMBER(3);
BEGIN

SELECT NVL(MAX(SID),0) INTO max_id FROM STUDENT;
new_id:=max_id+1;

INSERT INTO STUDENT VALUES(new_id,fname,lname,dept,year,email);
sid:=new_id;
commit;

EXCEPTION
  WHEN OTHERS THEN
  NEW_ID:=-1;
  ROLLBACK;
END;
/

This Stored Procedure will receive all Student data except SID(Student ID) as the parameters and insert them to the table while creating the SID itself. Then it will return the ID.

1.2 Using Through Java

To establish the connection between Java & Oracle you have to copy the ojdbc Jar file from the oracle server directory to your project library or add it to the build path.

StoredProc.java:

package CallableStatementExample;

import java.sql.*;
import java.util.Scanner;

import oracle.jdbc.*;

public class StoredProc {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Scanner sc=new Scanner(System.in);
		Connection cn=null;
		CallableStatement cst=null;
		int id=0,yr;
		String fname,lname,email,dept;
		try{
			DriverManager.registerDriver(new OracleDriver());
			cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
			
			cst=cn.prepareCall("{call insertStudent(?,?,?,?,?,?)}");
			System.out.println("Enter Student's First Name: ");
			fname=sc.nextLine();
			System.out.println("Enter Student's Last Name: ");
			lname=sc.nextLine();
			System.out.println("Enter Student's Email: ");
			email=sc.nextLine();
			System.out.println("Enter Student's Year: ");
			yr=Integer.parseInt(sc.nextLine());
			System.out.println("Enter Student's Dept: ");
			dept=sc.nextLine();
			
			cst.setString(1, fname);
			cst.setString(2,lname);
			cst.setString(3,dept);
			cst.setInt(4, yr);
			cst.setString(5, email);
			
			 cst.registerOutParameter(6, java.sql.Types.DECIMAL);
			
			int r=cst.executeUpdate();
			if(r>0)
				id=cst.getInt(6);
			
			if(id>0)
				System.out.println("Details Inserted. SID: "+id);
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally{
			try{
				if(cst!=null)
					cst.close();
				
				if(cn!=null)
					cn.close();
			}catch(Exception e){}
		}
	}

}

Output

Enter Student's First Name: 
Esha
Enter Student's Last Name: 
Chkty
Enter Student's Email: 
esha.chk
Enter Student's Year: 
2
Enter Student's Dept: 
CST
Details Inserted. SID: 3

1.3 Explanation

In the above example we have read user inputs, then we assigned that to the CallableStatement input parameters using setString() and setInt() methods as applicable.

We have registered the out parameter by the registerOutParameter() method. This method registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType. All OUT parameters must be registered before a stored procedure is executed.

The JDBC type specified by sqlType for an OUT parameter determines the Java type that must be used in the get method to read the value of that parameter. If the JDBC type expected to be returned to this output parameter is specific to this particular database, sqlType should be java.sql.Types.OTHER. The method getObject(int) retrieves the value.

2.CallableStatement with Stored Procedure Oracle CURSOR

What if we use an Oracle cursor to retrieve and manipulate the data and then we return the cursor it self rather than the values separately!! The JDBC CallableStatement also provides a solution for such problem.

Here we will retrieve the records stored in a table using Oracle Cursor, Stored Procedure & JDBC CallableStatement.

cursr_proc.sql:

CREATE OR REPLACE PROCEDURE studenyByDept(dpt in varchar2,data out SYS_REFCURSOR)
AS
BEGIN
OPEN data FOR SELECT SID,FNAME,LNAME,DEPT,YEAR,EMAIL FROM STUDENT WHERE DEPT=dpt;
END;
/

StoredProcCursr.java:

package CallableStatementExample;

import java.sql.*;
import java.util.Scanner;

import oracle.jdbc.*;

public class StoredProc {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Scanner sc=new Scanner(System.in);
		Connection cn=null;
		CallableStatement cst=null;
		int id=0,yr;
		String fname,lname,email,dept;
		try{
			DriverManager.registerDriver(new OracleDriver());
			cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
			
			cst=cn.prepareCall("{call insertStudent(?,?,?,?,?,?)}");
			System.out.println("Enter Student's First Name: ");
			fname=sc.nextLine();
			System.out.println("Enter Student's Last Name: ");
			lname=sc.nextLine();
			System.out.println("Enter Student's Email: ");
			email=sc.nextLine();
			System.out.println("Enter Student's Year: ");
			yr=Integer.parseInt(sc.nextLine());
			System.out.println("Enter Student's Dept: ");
			dept=sc.nextLine();
			
			cst.setString(1, fname);
			cst.setString(2,lname);
			cst.setString(3,dept);
			cst.setInt(4, yr);
			cst.setString(5, email);
			
			 cst.registerOutParameter(6, java.sql.Types.DECIMAL);
			
			int r=cst.executeUpdate();
			if(r>0)
				id=cst.getInt(6);
			
			if(id>0)
				System.out.println("Details Inserted. SID: "+id);
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally{
			try{
				if(cst!=null)
					cst.close();
				
				if(cn!=null)
					cn.close();
			}catch(Exception e){}
		}
	}

}

Output

Enter Department Name to Search : 
Comp. Sc.
Student ID: 1 Name : Esha Dey Dept: Comp. Sc. Year:3 EMail: esha.dey
Student ID: 2 Name : Rivu Chk Dept: Comp. Sc. Year:4 EMail: rivuchk@jcg
Student ID: 4 Name : Ilias Tsagkills Dept: Comp. Sc. Year:4 EMail: ilias@jcg
Student ID: 5 Name : Byron Kiourtzogl Dept: Comp. Sc. Year:4 EMail: byron@jcg
Student ID: 6 Name : Nikos Maravitsas Dept: Comp. Sc. Year:4 EMail: nikos@jcg

3. Using Oracle DB Objects

Have you ever wondered that if we use Oracle DB Objects in our database (which is very common) then how to retrieve those records in our java program!! Well we have a solution for that too. Oracle DB Objects can be easily fetched using STRUCT. We will fist create an Object for address, then we will redesign the table to store the students address.

3.1 Create Oracle DB Object and Redesign the Table

Creating Object:

cr_obj.sql:

CREATE TYPE ADDRESS_OB AS OBJECT(STREET VARCHAR2(50),CITY VARCHAR2(25),STATE VARCHAR2(25),COUNTRY VARCHAR2(25));
/

Adding Column to store Student’s Address:

alter_table.sql:

ALTER TABLE STUDENT ADD(ADDRESS ADDRESS_OB);
/

3.2 Inserting from Java using Object

Rewriting the insert_proc.sql stored procedure to store the students address.

insert_proc.sql:

CREATE OR REPLACE PROCEDURE insertStudent(fname in varchar2,lname in varchar2,dept in varchar2,year in number,email in varchar2,address in address_ob,sid out number)
AS
max_id NUMBER(3);
new_id NUMBER(3);
BEGIN

SELECT NVL(MAX(SID),0) INTO max_id FROM STUDENT;
new_id:=max_id+1;

INSERT INTO STUDENT VALUES(new_id,fname,lname,dept,year,email,address);
sid:=new_id;
commit;
EXCEPTION
  WHEN OTHERS THEN
  NEW_ID:=-1;
  ROLLBACK;
END;
/

StoredProcStruct.java:

package callableStatementExample;

import java.sql.*;
import java.util.Scanner;

import oracle.jdbc.*;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;


public class StoredProcStruct {
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Scanner sc=new Scanner(System.in);
		Connection cn=null;
		OracleCallableStatement cst=null;
		int id=0,yr;
		String fname,lname,email,dept;
		String[] addressObj=new String[4];
		try{
			DriverManager.registerDriver(new OracleDriver());
			cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
			
			
			System.out.println("Enter Student's First Name: ");
			fname=sc.nextLine();
			System.out.println("Enter Student's Last Name: ");
			lname=sc.nextLine();
			System.out.println("Enter Student's Email: ");
			email=sc.nextLine();
			System.out.println("Enter Student's Year: ");
			yr=Integer.parseInt(sc.nextLine());
			System.out.println("Enter Student's Dept: ");
			dept=sc.nextLine();
			System.out.println("Enter Address:");
			System.out.println("Enter Street Name:");
			addressObj[0]=sc.nextLine();
			System.out.println("Enter City:");
			addressObj[1]=sc.nextLine();
			System.out.println("Enter State:");
			addressObj[2]=sc.nextLine();
			System.out.println("Enter Country:");
			addressObj[3]=sc.nextLine();
			
			//Creating StructDescriptor
			StructDescriptor addressSD=StructDescriptor.createDescriptor("ADDRESS_OB", cn);
			STRUCT addrSTRUCT=new STRUCT(addressSD,cn,addressObj);
			
			cst=(OracleCallableStatement)cn.prepareCall("{call insertStudentObj(?,?,?,?,?,?,?)}");
			
			cst.setString(1, fname);
			cst.setString(2,lname);
			cst.setString(3,dept);
			cst.setInt(4, yr);
			cst.setString(5, email);
			cst.setSTRUCT(6, addrSTRUCT);
			
			 cst.registerOutParameter(7, java.sql.Types.DECIMAL);
			
			int r=cst.executeUpdate();
			if(r>0)
				id=cst.getInt(7);
			
			if(id>0)
				System.out.println("Details Inserted. SID: "+id);
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally{
			try{
				if(cst!=null)
					cst.close();
				
				if(cn!=null)
					cn.close();
			}catch(Exception e){}
		}
	}

}

Output

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

Enter Student's First Name: 
Nandan
Enter Student's Last Name: 
Banerjee
Enter Student's Email: 
hellonandan
Enter Student's Year: 
4
Enter Student's Dept: 
Comp. Sc.
Enter Address:
Enter Street Name:
Amta
Enter City:
Howrah
Enter State:
WB
Enter Country:
India
Details Inserted. SID: 7

3.3 Explanation

First of all we are creating an String array of same length as the ADDRESS_OB database object. Then we are setting values according to the ADDRESS_OB object variables. This is very important otherwise the data will get inserted into wrong columns.

Then we are creating oracle.sql.STRUCT object with the help of oracle.sql.StructDescriptor and our Object array. Once the STRUCT object is created, we are setting it as IN parameter for the stored procedure, register the OUT parameter and executing it. This code is tightly couple with OJDBC API and will not work for other databases.

Here we used the OracleCallableStatement instead of CallableStatement to deal the STRUCT as CallableStatement does not support STRUCT but OracleCallableStatement does.

3.4 Using Struct to Fetch Row

SQL File to Read Table Data:

struct_cursr_proc.sql:

CREATE OR REPLACE PROCEDURE studentsAllwithAddress(data out SYS_REFCURSOR)
AS
BEGIN
OPEN data FOR SELECT SID,FNAME,LNAME,DEPT,YEAR,EMAIL,ADDRESS FROM STUDENT WHERE ADDRESS IS NOT NULL;
END;
/

FetchStruct.java:

package callableStatementExample;


import java.sql.*;
import java.util.Scanner;

import oracle.jdbc.*;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class FetchStruct {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Scanner sc=new Scanner(System.in);
		Connection cn=null;
		OracleCallableStatement cst=null;
		OracleResultSet rs=null;
		int id=0,yr;
		String fname,lname,email,dept;
		try{
			DriverManager.registerDriver(new OracleDriver());
			cn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr");
			
			cst=(OracleCallableStatement)cn.prepareCall("{call studentsAllwithAddress(?)}");
									
			cst.registerOutParameter(1, OracleTypes.CURSOR);
			
			cst.execute();
			rs=(OracleResultSet)cst.getObject(1);
			
			while(rs.next())
			{
				System.out.println("Student ID: "+rs.getInt("SID")+" Name : "+rs.getString("FNAME")+" "+rs.getString("LNAME")+" Dept: "+rs.getString("DEPT")+" Year:"+rs.getInt("YEAR")+" EMail: "+rs.getString("EMAIL"));
				STRUCT addr=rs.getSTRUCT("ADDRESS");
				Object[] address=addr.getAttributes();
				System.out.println("Address:");
				for(Object adr:address)
				{
					System.out.print(adr.toString());
				}
			}			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		finally{
			try{
				if(rs!=null)
					rs.close();
				if(cst!=null)
					cst.close();
				
				if(cn!=null)
					cn.close();
			}catch(Exception e){}
		}
	}

}

Output

Student ID: 6 Name : Rajdeep Samanta Dept: Commerce Year:2 EMail: rajEgmail
Address:
Andul RoadHowrahWBIndiaStudent ID: 7 Name : Nandan Banerjee Dept: Comp. Sc. Year:4 EMail: hellonandan
Address:
AmtaHowrahWBIndia

3.5 Explanation

Here we fetched the row using Cursor then Retrieved it to the OracleResultSet object to fetch the STRUCT.

4. Download

This was an example of JDBC Callable Statement with Stored Procedure IN OUT, OracleCallableStatement with Struct & OracleResultSet.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.