How do I register out parameter in CallableStatement?

This example show you how to register out parameter for executing a stored procedure using the CallableStatement.registerOutParameter() method call. We must register the out parameters before the query execution. The registerOutParameter() method takes two parameters, the index of the parameter and the sql data type of the out parameter.

package org.kodejava.example.sql;

import java.sql.*;

public class RegisterOutParameter {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            //
            // Creates a CallableStatement for executing the
            // stored procedure
            //
            String query = "call GET_DETAIL_BY_PRODUCT(?, ?, ?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // 
            // Sets the input parameter
            //
            cb.setString(1, "bag");

            //
            // Registers the out parameters
            //
            cb.registerOutParameter(2, Types.VARCHAR);
            cb.registerOutParameter(3, Types.DECIMAL);
            cb.registerOutParameter(4, Types.INTEGER);

            //
            // Executes the query
            //
            cb.executeQuery();

            //
            // Gets the query result output
            //
            System.out.println("Code    : " + cb.getString(2));
            System.out.println("Price   : " + cb.getBigDecimal(3));
            System.out.println("Quantity: " + cb.getInt(4));

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

Here is the MySQL stored procedure that we call in the code above.

DROP PROCEDURE IF EXISTS kodejavadb.GET_DETAIL_BY_PRODUCT;
CREATE PROCEDURE kodejavadb.`GET_DETAIL_BY_PRODUCT`(IN vproduct varchar(30),
OUT vcode varchar(5),
OUT vprice decimal,
OUT vqty int)
BEGIN
  SELECT
  code INTO vcode
  FROM kodejavadb.products
  WHERE name = vproduct;

  SELECT
  price INTO vprice
  FROM kodejavadb.products
  WHERE name = vproduct;

  SELECT
  qty INTO vqty
  FROM kodejavadb.products
  WHERE name = vproduct;
END;
Wayan Saryada

Wayan Saryada

A programmer, runner, recreational diver, currently living in the island of Bali, Indonesia. Mostly programming in Java, creating web based application with Spring Framework, JPA, etc. If you need help on Java programming you can hire me on Fiverr.
Wayan Saryada

Leave a Reply