PostgreSQL JDBC: Call Stored Functions

Summary: in this tutorial, you will learn how to call PostgreSQL stored functions using JDBC.

Calling a built-in stored function example

We will call a built-in string function initcap() that capitalizes the first letter of each word in a string.

To call the initcap() function, you follow these steps:

  • First, establish a database connection.
  • Second, create a CallableStatement object by calling the prepareCall() method of the Connection object.
  • Register OUT parameters if applicable.
  • Bind values to the statement if applicable.
  • Third, execute the function call and obtain the result.

The following example creates a new class named Util and defines a static method properCase() that calls the initcap() function in PostgreSQL:

import java.sql.SQLException;
import java.sql.Types;

public class Util {
    public static String properCase(String s) {
        try (var conn = DB.connect();
             var stmt = conn.prepareCall("{ ? = call initcap( ? ) }")) {
            stmt.registerOutParameter(1, Types.VARCHAR);
            stmt.setString(2, s);
            stmt.execute();
            return stmt.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}Code language: Java (java)

The following illustrates how to use the properCase() method of the Util class:

public class Main {
    public static void main(String[] args) {
        var greeting = Util.properCase("hello joe");
        System.out.println(greeting);
    }
}Code language: JavaScript (javascript)

Output:

Hello Joe

Calling a stored function example

Let’s take an example of calling a stored function in PostgreSQL from a Java program using JDBC.

Creating a stored function

First, open Command Prompt on Windows or Terminal on Unix-like systems and connect to the sales database on your PostgreSQL server:

psql -U postgres -d sales

Second, create a function that finds the products by name based on a specified pattern:

create or replace function find_products (
	p_pattern varchar
) 
returns table (
	p_id int,
	p_name varchar,
	p_price decimal
) 
language plpgsql
as $$
declare 
    var_r record;
begin
	for var_r in(
		select id, name, price 
		from products 
		where name ilike p_pattern
    ) 
	loop 
		p_id := var_r.id; 
		p_name := var_r.name;
		p_price := var_r.price;
        return next;
	end loop;
end; $$ Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, exit the psql:

exitCode language: PHP (php)

Calling a stored function

The following defines the findByName() method in the ProductDB class that calls the find_products stored function to find the products by names based on a pattern:

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProductDB {
    public static List<Product> findByName(String pattern) {
        String SQL = "SELECT * FROM find_products (?)";

        var products = new ArrayList<Product>();

        try (var conn = DB.connect();
             var pstmt = conn.prepareStatement(SQL)) {

            pstmt.setString(1, pattern);
            var rs = pstmt.executeQuery();

            while (rs.next()) {
                var product = new Product(
                        rs.getInt("p_id"),
                        rs.getString("p_name"),
                        rs.getDouble("p_price")
                );
                products.add(product);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return products;
    }

// ...
}Code language: Java (java)

The following uses the findByName() method of the ProductDB class to search for products with the name containing the string "phone":

public class Main {
    public static void main(String[] args) {

        var products = ProductDB.findByName("%phone%");

        for (var product: products) {
            System.out.println(product);
        }
    }
}Code language: Java (java)

Output:

Product{id=5, name='Bluetooth Headphones', price=199.0}
Product{id=6, name='Phone Stand', price=24.99}Code language: JavaScript (javascript)

Summary

  • Use the CallableStatement to call a built-in function from PostgreSQL.
Was this tutorial helpful ?