03
Oct

Calling stored procedure from Java program

In RDBMS, a stored procedure is set of sql statements which is complied and stored in a database to perform a special task.

A stored procedure can have any combination of input, output and input/output parameters.

The “CallableStatement#prepareCall()” method is used to call a stored procedure from a java program. The following example demonstrates how to call a MySQL stored procedure from a java program using JDBC “CallableStatement”
.

Sample Database

Consider the “PRODUCT” table having the following records.

+----+-----------+-------+ | ID | NAME      | PRICE | +----+-----------+-------+ |  1 | Pencil    | 15.15 | |  2 | Pen       | 20.00 | |  3 | Color Box | 12.45 | +----+-----------+-------+

Stored Procedure Creation

The following is a stored procedure with IN, OUT and INOUT parameters.

CREATE PROCEDURE  PRODUCT_PROC (         IN pid int,     OUT pname varchar(50),     INOUT pprice decimal(10,2) ) BEGIN         declare tempPrice decimal(10,2);          -- Select data         select name,price into pname,tempPrice from product where id = pid;          -- Update new price     update product set price=pprice where  id = pid;          -- Return old price     set pprice= tempPrice;      END

Calling stored procedure from Java program

Here is an example of calling the “PRODUCT_PROC” stored procedure from java program.

Note – The “CallableStatement#registerOutParameter()” method must be registered for OUT and INOUT parameters.

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Types;

/**

* @author imssbora

*/

public class StoredProcedureExample {

public static void main(String[] args) {

String jdbcUrl = “jdbc:mysql://10.9.121.6:3306/DBName”;

String username = “username”;

String password = “pwd”;

String sql = “{call PRODUCT_PROC(?,?,?)}”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

CallableStatement stmt=conn.prepareCall(sql);) {

//Set IN parameter

stmt.setInt(1, 1);

//Set OUT parameter

stmt.registerOutParameter(2, Types.VARCHAR);

//Set INOUT parameter

stmt.setDouble(3, 15.15);

stmt.registerOutParameter(3, Types.DOUBLE);

//Execute stored procedure

stmt.execute();

// Get Out and InOut parameters

System.out.println(“Product Name = “+stmt.getString(2));

System.out.println(“Product Old Price = “+stmt.getDouble(3));

} catch (SQLException e) {

e.printStackTrace();

}

}

}

Output:

Product Name = Pencil

Product Old Price = 14.15