July 31, 2023
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.
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.
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:
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:
/**
* 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.
App app = new App();
app.getFilms("%er",2006);
In this tutorial, we have shown you how to call PostgreSQL stored functions using JDBC API.