Summary: in this tutorial, you will learn how to call PostgreSQL stored functions using JDBC.
PostgreSQL allows you to centralize the business logic at the database layer using the user-defined stored functions. It only makes sense if you can these stored functions in the application layer such as from a Java application.
Fortunately, PostgreSQL JDBC driver fully supports PostgreSQL stored functions.
We will show you how to call a built-in stored function as well as a user-defined stored function using JDBC
Calling the built-in stored function
We will call a built-in string function initcap() that capitalizes each word in a string.
To call the initcap() function, you follow these steps:
- First, establish a database connection.
- Second, create a CallableStatement object and register the OUT parameters.
- Third, execute function call and get the returned result.
The following connect() method of the App class establishes a database connection and returns a Connection object.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | public class App { private final String url = "jdbc:postgresql://localhost/dvdrental"; private final String user = "postgres"; private final String password = "postgres"; /** * Connect to the PostgreSQL database * * @return a Connection object * @throws java.sql.SQLException */ public Connection connect() throws SQLException { return DriverManager.getConnection(url, user, password); } //... } |
The following callBuiltInSF() method accepts a string and returns its proper case form.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | public class App { // ... /** * Call a built-in stored function demo * * @param s * @return */ public String properCase(String s) { String result = s; try (Connection conn = this.connect(); CallableStatement properCase = conn.prepareCall("{ ? = call initcap( ? ) }")) { properCase.registerOutParameter(1, Types.VARCHAR); properCase.setString(2, s); properCase.execute(); result = properCase.getString(1); } catch (SQLException e) { System.out.println(e.getMessage()); } return result; } /** * @param args the command line arguments */ public static void main(String[] args) { App app = new App(); System.out.println(app.properCase("this is the actor list:")); } } |
The program issues the following result:
1 | This Is The Actor List: |
Calling the user-defined stored function
We will use the get_film stored function that we developed in the PL/pgSQL Function That Returns A Table tutorial.
See the following getFilms() method:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | /** * Call the get_film stored function * @param pattern * @param releaseYear */ public void getFilms(String pattern, int releaseYear) { String SQL = "SELECT * FROM get_film (?, ?)"; try (Connection conn = this.connect(); PreparedStatement pstmt = conn.prepareStatement(SQL)) { pstmt.setString(1,pattern); pstmt.setInt(2,releaseYear); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println(String.format("%s %d", rs.getString("film_title"), rs.getInt("film_release_year"))); } } catch (SQLException e) { System.out.println(e.getMessage()); } } |
The getFilms method accepts two parameters:
- Pattern to search for the film.
- Release Year of the film
How it works.
- First, connect to the dvdrental database.
- Next, create a PreparedStatement object with the query that calls the get_film stored function.
- Then, pass the parameter to the statement using setString and setInt methods.
- After that, execute the statement
- Finally, process the result set and print out the film data.
Let’s execute the program that displays the films that are released in 2006 and ended with the "er" string.
1 2 | App app = new App(); app.getFilms("%er",2006); |
In this tutorial, we have shown you how to call PostgreSQL stored functions using JDBC API.