Back in Nov 2021, I wrote about calling Stored procedure from Java and demonstrated how we can call a simple stored procedure from Java or a one that contains a IN parameter. I have done some more playing around with calling Stored procedure from Java so this blog is about calling a stored procedure with multiple IN parameters and multiple INOUT parameters. As I had explained earlier in my blog of the “Stored Procedure” series that the stored procedures which don’t have a return value and if you want to return a value then you need to use a INOUT parameter. Typically OUT parameters are used for returning a value but PostgreSQL doesn’t support OUT parameter for Stored Procedures, it is currently being worked on by the PostgreSQL community.

First a small intro of Stored procedure before we dive into the main agenda of this blog 

“Stored procedure or a Stored function is a module or a program that is stored in the database, it extends the database functionality by creating and reusing user defined programs in supported SQL/PL languages. They can be created in multiple languages (details to follow) and once compiled they become a schema object which can be executed or referenced by multiple applications.  The stored procedures/functions are very useful component of a database application as they underpin the complex application logic and database operations that needs to executed and reused multiple times by the database application. Without this feature it would become very complex to carry out database operations that need to repeated, it will be done using several complex SQL queries with round trips in a single function within the database.”

Calling Procedure with multiple IN parameters

First we are going create a stored procedure that accepts multiple IN parameters and we will then call this from Java using the PG JDBC driver.

CREATE OR REPLACE PROCEDURE updStudent
(
IN std_id INT,
IN std_class VARCHAR,
std_tot_marks INT
)
LANGUAGE plpgsql AS
$$
BEGIN
	-- updating student's mask
	UPDATE student
	SET
		total_mark = std_tot_marks,
		st_class= std_class
	WHERE st_id = std_id;

	-- Commit transaction
	Commit;
END;
$$;

Now lets write a Java program that is used for calling this storied procedure, please refer to my previous blog on how the Connection to PostgreSQL is established using the PG JDBC driver and DriverManager class. I have’t included it in this blog in order to avoid repetition.

public static void main(String[] args)
    {
        CallProc app = new CallProc();
        Connection con = app.connect();


        try
        {
           PreparedStatement stmt = con.prepareStatement("call updstudent(?,?,?)");
           stmt.setInt(1,10);
           stmt.setString(2,"6B");
           stmt.setInt(3,70);
           stmt.execute();
           stmt.close();
        }
        catch(Exception err)
        {
           System.out.println("An error has occurred.");
           System.out.println("See full details below.");
           err.printStackTrace();
        }
    } 

Now lets go ahead and call this Java program :

$ java CallProc
Connected to the PostgreSQL server successfully.
Student record updated successfully. 

Query the table to see if the record was successfully updated :

postgres=# select * from student;
 st_id | st_name | st_class | total_mark 
-------+---------+----------+------------
    20 | Aqeel   | 4B       |        200
    30 | Amjad   | 5B       |        500
    10 | Ahsan   | 6B       |         70
(3 rows) 

Calling Procedure with multiple INOUT parameters

Now let’s create a procedure with INOUT parameter so we can call it from Java.

CREATE OR REPLACE PROCEDURE getStd_Details
(
IN std_id INT,
INOUT std_class VARCHAR default '',
INOUT std_name VARCHAR default '',
INOUT std_tot_marks INT default 0
)
LANGUAGE plpgsql AS
$$
BEGIN
            -- Get student class and marks
           select st_class,st_name,total_mark
           INTO
           std_class, std_name, std_tot_marks
           From student
           Where st_id = std_id;
END;
$$; 

Here is the JAVA program that callsl this stored procedure :

public static void main(String[] args)
    {
        CallProc_out app = new CallProc_out();
        Connection con = app.connect();


        try
        {
           /* CallableStatement stmt = con.prepareCall("{call helloworld()}"); */
           PreparedStatement stmt = con.prepareStatement("call getStd_Details(?,?,?,?)");
           stmt.setInt(1,20);
           stmt.setString(2,"");
           stmt.setString(3,"");
           stmt.setInt(4,0);
           ResultSet rs= stmt.executeQuery();
           while (rs.next())
           {
                System.out.println("Student Name : " + rs.getString(1));
                System.out.println("Student Class : " + rs.getString(2));
                System.out.println("Student Total Marks : " + rs.getInt(3));

           }
           rs.close();
           stmt.close();
        } 

Here is the result of executing this JAVA program :

$ java CallProc_out
Connected to the PostgreSQL server successfully.
Student Name : 4B
Student Class : Aqeel
Student Total Marks : 200 

Please note that CallableStatement class is typically used for calling a stored procedure with OUT or INOUT parameters. However the current PG JDBC driver has a limitation that stored procedure can’t be called using CallableStatement, a meaningful error is returned when someone tries to do that. So in the JAVA program above, I have used the PreparedStatement class for executing the procedure with INOUT parameters and using the ResultSet class traversing through the resultset to get the values of the OUT parameter. 

Conclusion

In this blog, I have demonstrated the executing of stored procedure containing multiple IN and INOUT parameter from JAVA. This pretty much concludes my blog of the “Stored Procedure” series, I will be back soon writing about another interesting feature in PostgreSQL.

Ahsan Hadi

Ahsan Hadi