Calling a Stored Procedure in PostgrSQL from JAVA and its Current Limitations

Enterprise PostgreSQL Solutions

Comments are off

Calling a Stored Procedure in PostgrSQL from JAVA and its Current Limitations

This is my third blog about Stored Procedure support in PostgreSQL, the previous two blogs are reachable from the HighGo CA blogs site https://www.highgo.ca/author/ahsan-h/. The first blog was introduction and usage of Stored Procedures and its difference with Stored Functions, the second blog focussed on creating and using Procedures with Definer and Invoker rights. The purpose of this blog is to demonstrate how to execute a Stored procedure from Java. Java is an important platform for developing enterprise level applications and it is really important to have the capability to call Stored procedure from Java.

Lets start with short intro of Stored Procedures :

“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.” 

Setup and Configuration

In order to call the stored procedure from Java, you need to have the JAVA development environment setup on your machine. There are plenty of blogs that will show you how to do that, I will just list down the key steps that need to be followed for creating the environment.

Start with installing the JDK on your machine, you can do that by getting the installer from the official site or install it using the package manager. I was doing this on Mac so I did the installation with brew using this command :

Brew install java

After installing JDK on your machine, you need to setup the JAVA_HOME environment variable. The JAVA_HOME is the folder that has the bin directory of JDK, you need to export the JAVA_HOME env variable according your environment, I did the command below after installing openjdk on my machine.

export JAVA_HOME=/usr/local/opt/openjdk

Run the following commands to check that you have Java environment properly set-up on your machine :

Ahsans-MacBook-Pro:java ahsanhadi$ which java
/usr/bin/java
Ahsans-MacBook-Pro:java ahsanhadi$ java -version
openjdk version "15.0.1" 2020-10-20
OpenJDK Runtime Environment (build 15.0.1+9)
OpenJDK 64-Bit Server VM (build 15.0.1+9, mixed mode, sharing)
Ahsans-MacBook-Pro:java ahsanhadi$ 

You also need to setup the CLASSPATH environment variable, this will tell java where to find all the classes that are being used by your java program

export CLASSPATH=/Users/ahsanhadi/Projects/java/postgresql-42.2.18.jar:/Users/ahsanhadi/Projects/java/storedproc.jar

In the CLASSPATH above, I have provided the patch for the PostgreSQL JDBC driver as-well as the JAR file the contains the classes developed for calling Stored procedure from Java. You will see these in later part of the blog.

Download the PostgreSQL JDBC driver from the office site : https://jdbc.postgresql.org/ and place it in a known location so you can specify the in the CLASSPATH as shown above.

The above steps are required in order to get the setup ready in JAVA side of things, let’s move to the database side of things.

Calling Stored Procedure from Java program

Lets start with creating a simple helloworld procedure in the database server and call that from Java.

CREATE OR REPLACE PROCEDURE helloworld()
LANGUAGE plpgsql 
AS 
$$
BEGIN
	raise info ‘Hello World’;
END;
$$;

Now lets write the JAVA program that will call this stored procedure :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class HelloWorld
{
    private final String url = "jdbc:postgresql://localhost/postgres";
    private final String user = "ahsanhadi";
    private final String password = "highgo";

    /**
     * Connect to the PostgreSQL database
     *
     * @return a Connection object
     */

    public Connection connect()
    {
        Connection conn = null;
        try
        {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the PostgreSQL server successfully.");
        } catch (SQLException e)
        {
            System.out.println(e.getMessage());
        }

        return conn;
    }

    /**
     * @param args the command line arguments
     */

public static void main(String[] args)
    {
        HelloWorld app = new HelloWorld();
        Connection con = app.connect();
        try
        {
           /* CallableStatement stmt = con.prepareCall("{call helloworld()}"); */

           PreparedStatement stmt = con.prepareStatement("call helloworld()");
           stmt.execute();
           System.out.println("Stored Procedure executed successfully");
        }
        catch(Exception err)
        {
           System.out.println("An error has occurred.");
           System.out.println("See full details below.");
           err.printStackTrace();
        }
    }
}

Here are some key points to note about the above JAVA program :

  • At the start, we are importing some library classes from the JDBC driver that are being used by the program.
  • Create instance of String object at the start of the program which will be required for establishing connection with the database server. Please adjust the database server URL, username and password according to your configuration
  • The connect() method is returning Connection object which is required for making connection with the database server using the given credentials. It uses the DriverManager class and getConnection() method for returning the connection handle of the PG database server.
  • In the main method of the HelloWorld class, we are making the connection by using the connect(). 
  • Using prepareStatement() method of Connection class for making the query command and then using PreparedStatement object to execute the command.
  • The command used for executing stored procedure is “call HelloWorld()”, it is using empty parenthesis because the procedure doesn’t have any parameters.
  • Please note that CallableStatement class is used for executing stored procedure from java in Oracle, this is currently not supporting with PG JDBC driver.

Now lets try and execute the HelloWorld java program that we have created :

Ahsans-MacBook-Pro:java ahsanhadi$ java HelloWorld
Connected to the PostgreSQL server successfully.
Stored Procedure executed successfully
Ahsans-MacBook-Pro:java ahsanhadi$ 

Now lets try to call a procedure that contains an IN parameter :

Create or Replace Procedure call_updstudent (IN st_id INT)
LANGUAGE plpgsql
AS $$
Declare
   tot_marks INT;
Begin
       -- updating student
      call updStudent(3,'5B',true, tot_marks);
      raise info 'total marks : %',tot_marks;
END;
$$;

The above procedure takes a IN parameter and call another procedure to perform some DML operations. The calling procedure also returns a INOUT parameter but currently we don’t have a way to get the return value from a stored procedure in PG JDBC, this is currently being implemented.

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

/**
 *
 * @author postgresqltutorial.com
 */

public class CallProc
{

    private final String url = "jdbc:postgresql://localhost/postgres";
    private final String user = "ahsanhadi";
    private final String password = "highgo";

    /**
     * Connect to the PostgreSQL database
     *
     * @return a Connection object
     */

    public Connection connect()
    {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("Connected to the PostgreSQL server successfully.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return conn;
    }

    /**
     * @param args the command line arguments
     */

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

        try
        {
           /* CallableStatement stmt = con.prepareCall("{call helloworld()}"); */

           PreparedStatement stmt = con.prepareStatement("call call_updstudent(?)");
           stmt.setInt(1,3);
           stmt.execute();
           stmt.close();

           CallableStatement pstmt = con.prepareCall("{? = call getStudentDetail(?)}");
           pstmt.registerOutParameter(1, Types.INTEGER);
           pstmt.setInt(2,3);
           pstmt.execute();
           System.out.println("Total Marks : " + pstmt.getInt(1) );

        }
        catch(Exception err)
        {
           System.out.println("An error has occurred.");
           System.out.println("See full details below.");
           err.printStackTrace();
        }

    }
}


I have added a stored function (shown below) that is also getting called in the above JAVA program. The stored procedure is updating the total marks for the student, the function is returning the marks for the student. So I am call the function after executing stored procedure in order to make sure that the procedure was indeed called and performed the DML.

CREATE OR REPLACE FUNCTION getStudentDetail(IN st_id INT)
RETURNS INT
AS $get_Student$
Declare  
     st_marks INT;     
 BEGIN              
 -- Get student class and marks
       select total_marks 
       INTO  
       st_marks
       From Student
       Where std_id = st_id; 
    
       return st_marks; 
END;
$get_Student$
Language plpgsql;  

Running the JAVA program above results in the following output :

Ahsans-MacBook-Pro:java ahsanhadi$ java CallProc
Connected to the PostgreSQL server successfully.
Total Marks : 40

PG JDBC driver limitations

As mentioned earlier, the CallableStatement class should be used for executing stored procedure from JAVA. The prepareCall method of Connection class accept the “Call ProcName()” syntax and returns a CallaableStatement object that is used for executing the stored procedure and returning values for INOUT parameters.

Currently the PG JDBC CallableStatement class doesn’t support calling the stored procedure, if you try to call the procedure using CallableStatement, it will return the following error

org.postgresql.util.PSQLException: ERROR: helloworld() is a procedure
  Hint: To call a procedure, use CALL. 

Basically the JDBC driver is sending this as select statement to database server and server is returning a meaningful error. The PG JDBC community is currently working on fixing this behaviour.

Conclusion

This is my 3rd blog in the Stored Procedure support in PostgreSQL, this blog should help you in understanding how stored procedures are executed from JAVA and help you write your own JAVA programs for doing the same. I will post an update for this once PG JDBC driver addresses the current limitation stated above.