JDBC CallableStatement Stored Procedure Output Parameters Example

This example will show you how to use java.sql.CallableStatement to get output data from stored procedure. This example will use Microsoft Sql Server, the java code for other databases is same. If you do not know how to create stored procedure in Microsoft Sql Server, you can read article JDBC CallableStatement Stored Procedure Input Parameters Example first.

1. dbo.searchEmployBySalary

This is the stored procedure name. You should mark output parameters with OUTPUT in stored procedure definition.

jdbc callable statement stored procedure output parameters example

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jerry Zhao
-- Create date: 2017/10/02
-- Description: Search for employee info by salary.
-- =============================================
CREATE PROCEDURE searchEmployBySalary  
      -- Add the parameters for the stored procedure here 
      @salary int = 0,  
      @name varchar(max) = '' OUTPUT, 
      @email varchar(max) = '' OUTPUT
AS
BEGIN 
      -- SET NOCOUNT ON added to prevent extra result sets from 
      -- interfering with SELECT statements. 
      SET NOCOUNT ON;
      SELECT @name = name,@email = email from testdb.dbo.EmployeeInfo where salary = @salary;
END
GO

2. CallableStatementParameterExample.java

To get the output parameter data from stored procedure, you should first register the output data type with java.sql.CallableStatement as below code snippet, then you can get it’s value by it’s type.

	// Regist output parameter type.
	cStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
						
	// Execute stored procedure.
	cStmt.executeUpdate();
			
	String name = cStmt.getString(2);

Full source code example.

public class CallableStatementParameterExample {

	public static void main(String[] args) {
		
		CallableStatementParameterExample cspie = new CallableStatementParameterExample();
		/*
		cspie.storedProcedureInParameterExample("jerry", "[email protected]", 10000);
		cspie.storedProcedureInParameterExample("richard", "[email protected]", 10000);
		cspie.storedProcedureInParameterExample("kevin", "[email protected]", 8000);
		cspie.storedProcedureInParameterExample("jackie", "[email protected]", 18000);
		cspie.storedProcedureInParameterExample("michale", "[email protected]", 19000);
		cspie.storedProcedureInParameterExample("steven", "[email protected]", 10000);
		*/
		
		cspie.storedProcedureOutParameterExample(10000);
	}

	
	
	public void storedProcedureInParameterExample(String name, String email, int salary)
	{
		try
		{	
			// Get db connection first.
			Connection dbConn = this.getDBConnectionFromPropertiesFile();
			
			// Create CallableStatement object.
			String storedProcudureCall = "{call addEmployeeInfo(?,?,?)};";
			
			CallableStatement cStmt = dbConn.prepareCall(storedProcudureCall);
			
			// Set input parameters value.
			cStmt.setString(1, name);
			
			cStmt.setString(2, email);
			
			cStmt.setInt(3, salary);
			
			// Execute stored procedure.
			cStmt.executeUpdate();
			
			System.out.println("Add one employee info success. name : " + name + " , email : " + email + " , salary : " + salary);
			
			// Do not forget close Callabel Statement and db connection object.
			cStmt.close();
			
			dbConn.close();
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	public void storedProcedureOutParameterExample(int salary)
	{
		try
		{	
			// Get db connection first.
			Connection dbConn = this.getDBConnectionFromPropertiesFile();
			
			// Create CallableStatement object.
			String storedProcudureCall = "{call searchEmployBySalary(?,?,?)};";
			
			CallableStatement cStmt = dbConn.prepareCall(storedProcudureCall);
			
			// Set searched salary value.
			cStmt.setInt(1, salary);
			
			// Regist output parameter type.
			cStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			
			cStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
			
			// Execute stored procedure.
			cStmt.executeUpdate();
			
			String name = cStmt.getString(2);
			
			String email = cStmt.getString(3);
			
			System.out.println("Employee whose name : " + name + " , email : " + email + " has salary : " + salary);
			
			// Do not forget close Callabel Statement and db connection object.
			cStmt.close();
			
			dbConn.close();
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	
	// This method return database connection object by using the configuration data in a properties file.
	public Connection getDBConnectionFromPropertiesFile()
	{
		Connection ret = null;
		
		try
		{
			// Create Properties object.
			Properties props = new Properties();
			
			File dbPropsFile = new File("C:\\WorkSpace\\JDBCSettings.properties");
			
			FileReader fileReader = new FileReader(dbPropsFile);
			
			// Load jdbc related properties in above file. 
			props.load(fileReader);
			
			// Get each property value.
			String dbDriverClass = props.getProperty("db.driver.class");
			
			String dbConnUrl = props.getProperty("db.conn.url");
			
			String dbUserName = props.getProperty("db.username");
			
			String dbPassword = props.getProperty("db.password");
			
			/* Register jdbc driver class. */
			Class.forName(dbDriverClass);
			
			// Get database connection object.
			ret = DriverManager.getConnection(dbConnUrl, dbUserName, dbPassword);
			
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}finally
		{
			return ret;
		}
	}
}

Above Example Console Output.

Employee whose name : steven , email : [email protected] has salary : 10000

Reference

  1. Load JDBC Configuration From Properties File Example
  2. JDBC CallableStatement Stored Procedure Input Parameters Example
READ :   Use JDBC To Connect Microsoft SQL Server

Download “JDBC-CallableStatement-Stored-Procedure-Output-Parameters-Example.zip” JDBC-CallableStatement-Stored-Procedure-Output-Parameters-Example.zip – Downloaded 50 times – 2 KB

(Visited 2,369 times, 12 visits today)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.