Call Stored Procedure in PostgreSQL Flexible Server from Java via JDBC

[Gennadyk]

Stored procedure is a module or a program that is stored in the database, it adds ability to create and reuse user defined programs in supported SQL/PL languages.  in all the versions , including up to PostgreSQL 10, it was not possible to create a procedure in PostgreSQL. In In PostgreSQL 11 , PROCEDURE was added as a new schema object which is a similar object to FUNCTION, but without a return value. 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 execute and reuse multiple times by the database application.  

Java setup

In order to call the stored procedure rom Java, you need to have the Java development environment setup on your machine. You can follow Oracle doc, which explains how to setup JAVA dev environment on Windows or this blog to setup Java on mac. 

Next step is to download Postgres JDBC driver.  Place it in a known location so you can specify the in your Java CLASSPATH. 

Create Stored Procedure

Lets create a simple hello procedure in the database server.  This will call from Java later:

CREATE PROCEDURE hello(IN pname TEXT) 
AS $$
BEGIN
    RAISE NOTICE 'Hello World: %', pname ;
END ;
$$
LANGUAGE plpgsql ;

To test it we can call it from PostgreSQL:

CALL hello (' Mark! ');  

You should see following as a result:

NOTICE: Hello World: Mark! 

Create Java Program to call Stored Procedure

Lets now create simple Java program that can call stored procedure we created before. 

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 = "gennadyk";
    private final String password = "p@ssw0rd";
    /**
     * 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;
    }
    /**
     *  args the command line arguments
     */
public static void main(String[] args)
    {
        HelloWorld app = new HelloWorld();
        Connection con = app.connect();
        try
        {
      
           PreparedStatement stmt = con.prepareStatement("call hello(?)");
           pstmt.setString(1, "John");
           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 important points to understand about the above program:

• 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 hello(?)”, where ?  is placeholder for parameter to be provided. Using setString we are adding parameter "John" in first position. We could add user input to the program, allowing user to provide their input string\name and using this input as parameter.

For more information on accessing Azure Database for PostgreSQL - Flexible Server with JDBC see following:

• Use Java and JDBC with Azure Database for PostgreSQL - Flexible Server 
• PostgreSQL JDBC Driver Documentation
• Connecting to PostgreSQL database
• PostgreSQL Documentation: CREATE PROCEDURE

To learn more about our Flexible Server managed service, see the Azure Database for PostgreSQL service page. We’re always eager to hear customer feedback, so please reach out to us at Ask Azure DB for PostgreSQL.