Callable Statement in JDBC

This statement is used to call stored procedure of database, store procedure means precompile block of code of an application that will be directly executed hence procedure performance is better as compare to normal SQL Query.

Syntax to create a Stored Procedure?

Create Procedure Procedurename()  as

         begin

               Statement1

              Statement2

         end;

     /

 Procedurename()

The procedure will link with the table, which means without the table, the procedure can not be created.

Now I am explaining an example of the Select Procedure on MySQL Database?

DELIMITER //

CREATE PROCEDURE GetAllProducts()

BEGIN

SELECT *  FROM tbl_student;

END //

DELIMITER ;

How to execute

call GetAppProducts()

How to use Callable Statement using Java Code?

package jdbc;

import java.sql.*;

public class JDBCEXAMPLE {

public static void main(String[] args) {

try

{

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/schooldb","root","");

      CallableStatement st = conn.prepareCall("{call GetAllProducts()}");

  ResultSet x = st.executeQuery();

  while(x.next()){

System.out.println(x.getString(1) + " "+ x.getString(2));

}

}

catch(Exception ex)

{

System.out.println(ex.getMessage());

}

}

}

............................................................................................................

Now Create a procedure for Data Insertion on MYSQL:-

DELIMITER //

CREATE PROCEDURE InsertStudent(a int,b varchar(50),c int)

BEGIN

insert into student values(a,b,c);

END //

DELIMITER ;

Code for JDBC:-

package jdbc;

import java.sql.*;

public class JDBCEXAMPLE {

public static void main(String[] args) {

try

{

Class.forName("com.mysql.jdbc.Driver");

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/schooldb","root","");

      CallableStatement st = conn.prepareCall("{call InsertStudent(?,?,?,?)}");

      st.setInt(1, 1212);

      st.setString(2,"ravi");

      st.setString(3,"CS");

      st.setInt(4,45000);

  int x = st.executeUpdate();

      if(x!=0)

      {

    System.out.print("data inserted successfully");

      } 

}

catch(Exception ex)

{

System.out.println(ex.getMessage());

}

}

}