JDBC : Stored Procedure Example


A SQL stored procedure (SP) is a collection SQL statements , SQL command logic, which is compiled and stored on the database and execute on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

See Also :

Types of parameters of Stored Procedure:

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute procedures by JDBC instead of writing of procedure.

Table and Procedure :  Here is small procedure to getStudentName based on student id.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('1', 'Saurabh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('2', 'Rajesh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('3', 'Ramesh Gupta');

Create Stored procedure
USE `facingissuesonitdb`;
DROP procedure IF EXISTS `getStudentName`;

DELIMITER $$
USE `facingissuesonitdb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentName`(in student_id integer, out first_name varchar(25))
BEGIN
select name into  first_name from student where id=student_id;
END$$

DELIMITER ;

Java code to execute stored procedure by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import connection.MyConnection;

public class StoredProcedureTest {

	private static final String PROCEDURE_QUERY = "{call getStudentName(?,?)}";

	public static void main(String[] args) {
	 try {
             //to get connection code check blog for Connection according to your need.
	     Connection conn = MyConnection.getConnectionMain("MySql");
	     CallableStatement csmt = conn.prepareCall(PROCEDURE_QUERY);
	     csmt.setInt(1, 2);
	     // Because second parameter is OUT so register it
	    csmt.registerOutParameter(2, java.sql.Types.VARCHAR);
	    csmt.execute();
	     String firstName = csmt.getString(2);// for retrieving out parameter
											     // after execution
	     System.out.println(firstName);
	    } catch (SQLException ex) {
			ex.printStackTrace();
	   }
	}
}

Output


MySql Connection created successfully !!
MySql Connection Creation end !!
Rajesh Gupta

More on JDBC

Follow below links to know more on JDBC and solving JDBC issues :

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s