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.
   
2. Click ” Query —> Specify Values for Template Parameters ” menu.
   
3. Input Stored Procedure related information in popup dialog. Please note the parameter type and default values.
   
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.
   
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.
   

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.

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..

[download id=”2991″]