JDBC CallableStatement – Stored Procedure IN parameter example

Code snippets to show you how to call a Oracle stored procedure via JDBC CallableStatement, and how to pass IN parameters from Java to stored procedure.


//insertDBUSER is stored procedure
String insertStoreProc = "{call insertDBUSER(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(insertStoreProc);
callableStatement.setInt(1, 1000);
callableStatement.setString(2, "mkyong");
callableStatement.setString(3, "system");
callableStatement.setDate(4, getCurrentDate());
callableStatement.executeUpdate();

JDBC CallableStatement Example

See a full JDBC CallableStatement example.

1. Stored Procedure

A stored procedure in Oracle database. Later, calls it via JDBC.


CREATE OR REPLACE PROCEDURE insertDBUSER(
	   p_userid IN DBUSER.USER_ID%TYPE,
	   p_username IN DBUSER.USERNAME%TYPE,
	   p_createdby IN DBUSER.CREATED_BY%TYPE,
	   p_date IN DBUSER.CREATED_DATE%TYPE)
IS
BEGIN

  INSERT INTO DBUSER ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE") 
  VALUES (p_userid, p_username,p_createdby, p_date);

  COMMIT;

END;
/

2. Calls Stored Procedure via CallableStatement

JDBC example to call stored procedure via CallableStatement.

File : JDBCCallableStatementINParameterExample.java


package com.mkyong.jdbc;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class JDBCCallableStatementINParameterExample {

	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			callOracleStoredProcINParameter();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void callOracleStoredProcINParameter() throws SQLException {

		Connection dbConnection = null;
		CallableStatement callableStatement = null;

		String insertStoreProc = "{call insertDBUSER(?,?,?,?)}";

		try {
			dbConnection = getDBConnection();
			callableStatement = dbConnection.prepareCall(insertStoreProc);

			callableStatement.setInt(1, 1000);
			callableStatement.setString(2, "mkyong");
			callableStatement.setString(3, "system");
			callableStatement.setDate(4, getCurrentDate());

			// execute insertDBUSER store procedure
			callableStatement.executeUpdate();

			System.out.println("Record is inserted into DBUSER table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

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

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

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
				DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static java.sql.Date getCurrentDate() {
		java.util.Date today = new java.util.Date();
		return new java.sql.Date(today.getTime());
	}

}

Done. When above JDBC example is executed, a new record will be inserted into database via stored procedure “insertDBUSER“.

Reference

  1. http://download.oracle.com/javase/6/docs/api/java/sql/CallableStatement.html
  2. http://docsrv.sco.com/JDK_guide/jdbc/getstart/callablestatement.doc.html
  3. http://onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Chirag
Guest
Chirag

Can we pass REF CURSOR as IN parameter, like we can say passing ARRAY to procedure

ImPrakash
Guest
ImPrakash

HI,
Can we call the stored procedure from Android
If Yes, How to get it done?

Oduntan Olalekan
Guest
Oduntan Olalekan
I really appreciate your idea given here. It really helped me to solved an issues having been tackling with for 3 days due to (?,?,?) whereas I used ? for only input 3 parameter [callableStatement.setString(1, sol); callableStatement.setString(2, fromDate); callableStatement.setString(3, toDate);] without considering callableStatement.registerOutParameter. check the sample of the code below: ……. try(CallableStatement callableStatement = con.prepareCall(“{call custom.newly_opened_account(?,?,?,?,?,?,?,?,?,?,?,?)}”)) { callableStatement.setString(1, sol); callableStatement.setString(2, fromDate); callableStatement.setString(3, toDate); callableStatement.registerOutParameter(4,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(5,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(6,java.sql.Types.DATE); callableStatement.registerOutParameter(7,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(8,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(9,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(10,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(11,java.sql.Types.VARCHAR); callableStatement.registerOutParameter(12,java.sql.Types.VARCHAR); ……blah….blah
Victor Moraes
Guest
Victor Moraes

What about OUT parameters?

Vinoth S
Guest
Vinoth S

How to insert list of objects which has in/Out parameters in procedure

Manu
Guest
Manu

Is it possible to use java8 DateTime API with spring-jdbc stored procedure? For example, can I pass object of java8 LocalDateTime as input parameter, instead of java.util.Date

Luke schamberger
Guest
Luke schamberger

How can I find the reference to my own jdbc package, This is a great help, but I am very new to the field, and I am having to learn as I go. Thanks ahead of time.

becas
Guest
becas

how to do i call combo-box value using callable statement please refer me in detail please

becas
Guest
becas

can we

dev
Guest
dev

This is great. Helped saving lots of time.

trackback
Llamar Stores Procedures desde Netbeans

[…] […]

Amit
Guest
Amit

Nice article. One question – Can a stored procedure be executed using java.sql.Statement object instead of a CallableStatement? The stored procedure does not take any parameters

Padam Dhariwal
Guest
Padam Dhariwal
Hi Sir, I am struck at my project on the below details. I have learnt Struts 2.0 from reading your articles. I need you help in this if you can provide I will be thankful to you. we are using Store Procedure for in order to insert and update. I have seen your example in this Article, but struck on inserting store procedure having Table of Objects. Please see the below Store Procedure and my comments in Blue and Bold I need to call this GBDF_SAVE_VAR_EQRAGG store procedure in order to insert data. Please give your suggestion how to insert… Read more »
Karthik Marupeddi
Guest
Karthik Marupeddi

Hi MK can you please show us example with Spring Storeprocdure API.

Thank you.

ajay kumar sharma
Guest
ajay kumar sharma

i am ajay kumar sharma