/PostgreSQL JDBC/Calling PostgreSQL Stored Functions

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 theinitcap() function, you follow these steps:

First,establish a database connection.
Second, create aCallableStatement object by calling theprepareCall() method of theConnection object.
RegisterOUT 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 namedUtil and defines a static methodproperCase() that calls theinitcap() function in PostgreSQL:

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

The following illustrates how to use theproperCase() method of theUtil class:

public class Main {    publicstatic void main(String[] args) {        var greeting= Util.properCase("hello joe");        System.out.println(greeting);    }}

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 plpgsqlas $$declare    var_r record;beginfor var_rin(select id, name, pricefrom productswhere name ilike p_pattern    )loopp_id := var_r.id;p_name := var_r.name;p_price := var_r.price;        return next;end loop;end; $$

Third, exit the psql:

exit

Calling a stored function

The following defines thefindByName() method in theProductDB class that calls thefind_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;    }// ...}

The following uses thefindByName() method of theProductDB 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);        }    }}

Output:

Product{id=5, name='Bluetooth Headphones', price=199.0}Product{id=6, name='Phone Stand', price=24.99}

Summary

Use theCallableStatement to call a built-in function from PostgreSQL.

PreviousPostgreSQL JDBC: Delete Data from Table NextPostgreSQL JDBC: Managing Transactions

Last updated onJanuary 31, 2024

Was this page helpful?

Thank you for your feedback!

Movatterモバイル変換