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 databases 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 the Programmability folder under the database name, Right-click the Stored Procedure folder, click the ” New Stored Procedure ” menu in the popup menu list.
2. Click the ” Query —> Specify Values for Template Parameters ” menu item on the top toolbar.
3. Input the Stored Procedure information (Author, Procedure_Name, @Param1, @Param2 …) in the popup Specify Values for Template Parameters 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
   -- 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 the above script then you can find the stored procedure created in the Microsoft SQL Server left side Object Explorer panel tree. The stored procedure saved path is database-name/Programmability/Stored Procedures/dbo.stored-procedure-name.
6. If you can not find some existing objects such as created tables etc, you can click the ” Edit —> IntelliSense —> Refresh Local Cache ” menu to refresh the not taken effect database objects.

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

1. Below java code will get database connection from data saved at local properties file. You can read the article Load JDBC Configuration From Properties File Example to learn more. And then invoke the above Microsoft SQL Server stored procedure, the stored procedure will insert the data into the testdb.dbo.EmployeeInfo table.

   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);
   	}
   
   	
   	// This method will call the microsoft sql server stored procedure to insert data into database table.
   	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 the 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. Below is the above source code execution result.

   Add one employee info success. name : jerry, email : [email protected], salary : 10000
   Add one employee info success. name : richard, email : [email protected], salary : 10000
   Add one employee info success. name : kevin, email : [email protected], salary : 8000
   Add one employee info success. name : jackie, email : [email protected], salary : 18000
   Add one employee info success. name : michale, email : [email protected], salary : 19000
   Add one employee info success. name : steven, email : [email protected], salary : 10000

3. Now run the SQL command select top 1000 [name],[email],[salary] from [testdb].[db0].[EmployeeInfo] in SQL Server query window, you can see the below result.

   name        email              salary
   jerry    [email protected]          10000
   richard  [email protected]     10000
   kevin    [email protected]         8000
   jackie   [email protected]    18000
   michale  [email protected]    19000
   steven   [email protected]         10000

4. Create Stored Procedure Reference.

1. For Microsoft SQL Server Database.
2. For Oracle Database.
3. For MySql Database.
4. MySql Stored Procedure Tutorial.
5. For PostgreSQL Database.