JDBC CallableStatement Stored Procedure Output Parameters Example

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

1. dbo.searchEmployBySalary

1. The dbo.searchEmployBySalary is the stored procedure name. You should mark output parameters with OUTPUT in the stored procedure definition.
2. Below is the source code of the stored procedure dbo.searchEmployBySalary.

   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

1. To get the output parameter data from a stored procedure, you should first register the output data type with java.sql.CallableStatement like the 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);
   

3. Full Source Code Example.

1. CallableStatementParameterExample.java

   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;
   		}
   	}
   }

2. Above example console output.

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

References

1. Load JDBC Configuration From Properties File Example
2. JDBC CallableStatement Stored Procedure Input Parameters Example