Stored Procedures - JDBC Java SQL

Stored Procedures:

• group of SQL statements that perform a particular task 
• Normally created by DBA
• Can have any combination of input, output, and input/output parameters

Benefits:

• Performance - compiled once but executable more times
• Productivity and Ease of Use - avoid redundant code, extend SQL Database functionality 
• Scalability 
• Maintainability
• Interoperability
• Replication
• Security

To call stored procedure from Java

The JDBC API provides the CallableStatement

CallableStatement myCall = myConn.prepareCall("{call some_stored_procedure()}");

...

myCall.execute();

JDBC API parameter types

• IN default
• INOUT
• OUT

Stored Procedure can return result sets

EXAMPLE: Created stored procedure on MySQL side - NAME: increase_salaries_for_department

DELIMITER $$

DROP PROCEDURE IF EXISTS `increase_salaries_for_department`$$

CREATE DEFINER=`student`@`localhost` PROCEDURE `increase_salaries_for_department`

(IN the_department VARCHAR(64), IN increase_amount DECIMAL(10,2))

BEGIN

UPDATE employees SET salary= salary + increase_amount where department=the_department;

END$$

DELIMITER ;

Java side:

                        // Get a connection to database

myConn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/newdb?autoReconnect=true&useSSL=false", "root", "Sasanka01");

                        // Values for parameters

String theDepartment = "Engineering";

int theIncreaseAmount = 10000;

// Show salaries BEFORE

System.out.println("Salaries BEFORE\n");

showSalaries(myConn, theDepartment);

// Prepare the stored procedure call

myStmt = myConn

.prepareCall("{call increase_salaries_for_department(?, ?)}");

// Set the parameters

myStmt.setString(1, theDepartment);

myStmt.setDouble(2, theIncreaseAmount);

// Call stored procedure

System.out.println("\n\nCalling stored procedure.  increase_salaries_for_department('" + theDepartment + "', " + theIncreaseAmount + ")");

                        //Execute statement

                        myStmt.execute();

System.out.println("Finished calling stored procedure");

// Show salaries AFTER

System.out.println("\n\nSalaries AFTER\n");

showSalaries(myConn, theDepartment);

INOUT parameters

Stored procedure developed for MySQL

PROCEDURE 'greet_the_department' ( INOUT department VARCHAR(64) )

BEGIN

     SET department = concat ('Hello to awesome ', department, 'team!' );

END

Java Coding

 // Prepare the stored procedure call

 myStmt = myConn.prepareCall("{call greet_the_department(?)}");

// Set the parameters

myStmt.registerOutParameter(1, Types.VARCHAR);

myStmt.setString(1, theDepartment);

// Call stored procedure

System.out.println("Calling stored procedure.  greet_the_department('" + theDepartment + "')");

myStmt.execute();

System.out.println("Finished calling stored procedure");

// Get the value of the INOUT parameter

String theResult = myStmt.getString(1);

System.out.println("\nThe result = " + theResult);

OUT Parameters

Stored procedure developed for MySQL

PROCEDURE `get_count_for_department`(IN the_department VARCHAR(64), OUT the_count INT)

BEGIN

SELECT COUNT(*) INTO the_count FROM employees where department=the_department;

END;

Java Coding

 // Prepare the stored procedure call

 myStmt = myConn.prepareCall("{call get_count_for_department(?, ?)}");

// Set the parameters

myStmt.setString(1, "Engineering);

myStmt.registerOutParameter(2, Types.INTEGER);

//Call stored procedure

myStmt = execute();

//Get the value of the OUT parameter

int theCount = myStmt.getInt(2)