How To Call and Get Value From Stored Procedure In Java

Here is simple example to get data by call stored procedure using Java. The scenario is we want to get value with some parameter.

The procedure:

CREATE OR REPLACE PROCEDURE BOHAY_SCHEME.GETBILLAMOUNT 
                      (productid IN varchar2,
                      billid IN varchar2,
                      amount OUT number
                    )
IS
BEGIN
   select bill_amount into amount 
   from t_bill
   where product_id = productid 
     and bill_id = billid; 
END;

The data on database:

+-----------+-------------+-------------+
|product_id |bill_id	  |bill_amount	|
+-----------+-------------+-------------+
|123	    |419473649323 |500000       |
+-----------+-------------+-------------+


Java code:

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.aries.main;

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

public class Main {
     
    public static void main(String[] args)throws SQLException{
        Connection connection = null;
	try {
            connection = DriverManager.getConnection(
				"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.98.10.128)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME = bohay123)))", 
                                "BOHAY_SCHEME",
				"bohayBANGET"); 
            
            System.out.println("bill amount for productid:123 and billid:419473649323 is");
            System.out.println(getBillAmountFromDB(connection, "123", "419473649323"));
	} finally{
            connection.close();
        }
    }
    
    private static String getBillAmountFromDB(Connection conn, String productId, String billId){
        CallableStatement proc = null;
        String amount = "";
        try {
            proc = conn.prepareCall("{call GETBILLAMOUNT(?,?,?)}");
            proc.setString(1, productId);
            proc.setString(2, billId);
            proc.registerOutParameter(3, Types.VARCHAR);
            proc.execute();
            
            amount = proc.getString(3);
            if(proc != null){
                proc.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return amount;        
    }
}

the output is:

run:
bill amount for productid:4151 and billid:4011401013 is
500000
BUILD SUCCESSFUL (total time: 0 seconds)

Hope this simple article can help 🙂

 

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