JDBC CallableStatement Stored Procedure Input Parameters Example

This example will show you how to use java.sql.CallableStatement to call a Microsoft Sql Server stored procedure with input parameters. If you use other database such as oracle, Mysql etc, the java code is almost the same.

1.Create Microsoft Sql Server Stored Procedure.

  1. In Microsoft Sql Server Management Studio, open Object Explorer, Expand your database name, Expand Programmability, Right click ” Stored Procedure “, click ” New Stored Procedure ” menu.
    create stored procedure for microsoft sql server
  2. Click ” Query —> Specify Values for Template Parameters ” menu.
    specify values for microsoft sql server stored procedure template parameters
  3. Input Stored Procedure related information in popup dialog. Please note the parameter type and default values.
    microsoft sql server stored procedure input parameters edit window
  4. Click OK to save the stored procedure script source code. Edit the source code as below.
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: Jerry Zhao
    -- Create date: 2017/09/28
    -- Description: Add one employee info.
    -- =============================================
    CREATE PROCEDURE addEmployeeInfo  
       -- Add the parameters for the stored procedure here 
       @name varchar(MAX) = '',  
       @email varchar(MAX) = '', 
       @salary int = 0
    AS
    BEGIN 
       -- SET NOCOUNT ON added to prevent extra result sets from 
       -- interfering with SELECT statements. 
       SET NOCOUNT ON;
       -- Insert statements for procedure here 
       insert into testdb.dbo.EmployeeInfo(name, email, salary) values(@name, @email, @salary);
    END
    GO
  5. Execute above script then you can find the stored procedure created in left panel tree.
    execute microsoft sql server stored procedure result
  6. If you can not find some exist objects such as created tables etc, you can click ” Edit —> IntelliSense —> Refresh Local Cache ” menu to refresh the not taken effect database objects.
    intellisense refresh local cache

2. Use CallableStatement To Call Stored Procedure With Input Parameters.

Below java code will get database connection from data saved at local properties file. You can read article Load JDBC Configuration From Properties File Example to learn more.

public class CallableStatementParameterInExample {

	public static void main(String[] args) {
		
		CallableStatementParameterInExample cspie = new CallableStatementParameterInExample();
		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);
	}

	
	
	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();
		}
	}
	
	// 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;
		}
	}
}

3. Above Code Execution Result.

callable statement example input parameter console output

callable statement example input parameter database table query output

4. Create Stored Procedure Referrence

  1. For Microsoft Sql Server Database.
  2. For Oracle Database.
  3. For MySql Database.
  4. MySql Stored Procedure Tutorial.
  5. For PostgreSql Database..
READ :   JDBC PreparedStatement Example – Batch Insert, Update, Delete

Download “JDBC-CallableStatement-Microsoft-Sql-Server-Stored-Procedure-In-Parameter-Example.zip” JDBC-CallableStatement-Microsoft-Sql-Server-Stored-Procedure-In-Parameter-Example.zip – Downloaded 125 times – 2 KB

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.