Sometimes the it is not possible to retrieve the whole manipulated data with a single query from database. So many queries clubbed together to form a single block. This block is known as procedures in database. So if you want to call these blocks from your java programs so you need to use a special statement i.e Callable Statement. The below example will demonstrate the parameter passing, calling mechanism and also getting the results from the Database procedures.

Suppose, there is a procedure "Test" for the adding the two numbers. Here is code for the procedure :-

TG DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`Test` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Test`(IN num1 INT, IN num2 INT ,OUT param1 INT)

BEGIN

set param1 := num1 + num2;

END $$ DELIMITER ;

The following code will call the procedure “Test”.

package com.visualbuilder;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Types;

public class CallableStatementExample {

  public static void main(String[] args) {

    try {

        /** Loading the driver*/

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

        /** Getting Connection*/

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

        /** Creating Statement*/

                CallableStatement call = con.prepareCall("call test.Test(?,?,?)");

        call.setInt(1,2);

        call.setInt(2,2);

        call.registerOutParameter(3,Types.INTEGER);

        call.execute();

                System.out.println("The addition is "+call.getInt(3));

        con.close();

                } catch (Exception e) {

                         e.printStackTrace();

                }

        }

}

Output is:-

The addition is 4

• Printer Friendly
• Email to a friend
• Add to my Favourites
• Download PDF version
• Report Bad Submissions
• Submit Feedback