Postgresql java stored procedures

How to properly call postgresql functions (stored procedures) within spring/hibernate/jpa?

When working with a PostgreSQL database in a Spring/Hibernate/JPA application, there may be instances where it is necessary to call a stored function within the database. However, properly calling these functions can be tricky, as the syntax and approach may differ from standard SQL queries.

Method 1: Using JPA’s @NamedStoredProcedureQuery

CREATE OR REPLACE FUNCTION get_employee_details(IN emp_id INTEGER, OUT emp_name VARCHAR, OUT emp_salary NUMERIC) AS $$ BEGIN SELECT name, salary INTO emp_name, emp_salary FROM employee WHERE id = emp_id; END; $$ LANGUAGE plpgsql;

public class EmployeeDetails  private String name; private BigDecimal salary; // getters and setters >

1. Define the stored procedure in your JPA entity class using the @NamedStoredProcedureQuery annotation:

@Entity @NamedStoredProcedureQuery( name = "getEmployeeDetails", procedureName = "get_employee_details", parameters =  @StoredProcedureParameter(mode = ParameterMode.IN, name = "emp_id", type = Integer.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_name", type = String.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "emp_salary", type = BigDecimal.class) >, resultClasses = EmployeeDetails.class> ) public class Employee  @Id private Integer id; private String name; private BigDecimal salary; // getters and setters >

StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("getEmployeeDetails"); query.setParameter("emp_id", 123); query.execute(); EmployeeDetails employeeDetails = (EmployeeDetails) query.getSingleResult();

That’s it! You have successfully called a PostgreSQL stored procedure using JPA’s @NamedStoredProcedureQuery in Spring/Hibernate/JPA.

Method 2: Using Hibernate’s Session.createStoredProcedureCall()

To call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA, you can use Hibernate’s Session.createStoredProcedureCall() method. This method creates a StoredProcedureCall object that represents a stored procedure call. Here are the steps to use this method:

Create a StoredProcedureCall object with the name of the stored procedure and the class of the result set:

StoredProcedureCall storedProcedureCall = session.createStoredProcedureCall("function_name", ResultClass.class);

storedProcedureCall.registerParameter(1, String.class, ParameterMode.IN).bindValue("param_value"); storedProcedureCall.registerParameter(2, Integer.class, ParameterMode.OUT);

ListResultClass> resultList = storedProcedureCall.getResultList();

Here is an example code that demonstrates how to call a stored procedure that takes one input parameter and returns a result set:

StoredProcedureCall storedProcedureCall = session.createStoredProcedureCall("get_users_by_name", User.class); storedProcedureCall.registerParameter(1, String.class, ParameterMode.IN).bindValue("John"); ListUser> resultList = storedProcedureCall.getResultList();

In this example, the stored procedure get_users_by_name takes one input parameter of type VARCHAR and returns a result set of User objects. The input parameter is bound to the value «John» . The result set is returned as a list of User objects.

Here is another example code that demonstrates how to call a stored procedure that takes two input parameters and returns a single value:

StoredProcedureCall storedProcedureCall = session.createStoredProcedureCall("get_user_count", Long.class); storedProcedureCall.registerParameter(1, String.class, ParameterMode.IN).bindValue("John"); storedProcedureCall.registerParameter(2, Integer.class, ParameterMode.IN).bindValue(30); Long result = (Long) storedProcedureCall.getSingleResult();

In this example, the stored procedure get_user_count takes two input parameters of type VARCHAR and INTEGER and returns a single value of type BIGINT . The input parameters are bound to the values «John» and 30 respectively. The single value is returned as a Long object.

Method 3: Using Spring’s JdbcTemplate

To call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA using Spring’s JdbcTemplate, follow these steps:

bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> property name="dataSource" ref="dataSource"/> bean>

@Autowired private JdbcTemplate jdbcTemplate;

jdbcTemplate.call(new CallableStatementCreator()  @Override public CallableStatement createCallableStatement(Connection connection) throws SQLException  String storedProc = ""; CallableStatement callableStatement = connection.prepareCall(storedProc); callableStatement.setInt(1, arg1); callableStatement.setString(2, arg2); return callableStatement; > >, Arrays.asList(new SqlParameter(Types.INTEGER), new SqlParameter(Types.VARCHAR)));

In the above code, replace my_stored_proc with the name of your stored procedure, and arg1 and arg2 with the input parameters for your stored procedure. The Arrays.asList(new SqlParameter(Types.INTEGER), new SqlParameter(Types.VARCHAR)) argument specifies the output parameter types for the stored procedure.

1. To retrieve the output parameters from the stored procedure, use the getXXX methods on the CallableStatement object:

int outputParam1 = callableStatement.getInt(3); String outputParam2 = callableStatement.getString(4);

In the above code, replace 3 and 4 with the indices of the output parameters in the stored procedure.

That’s it! With these steps, you can properly call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA using Spring’s JdbcTemplate.

Method 4: Using Spring’s @Procedure annotation

To call PostgreSQL functions (stored procedures) within Spring/Hibernate/JPA using Spring’s @Procedure annotation, follow these steps:

CREATE OR REPLACE FUNCTION get_user_details(user_id INT) RETURNS TABLE (id INT, name VARCHAR, email VARCHAR) AS $$ BEGIN RETURN QUERY SELECT id, name, email FROM users WHERE id = user_id; END; $$ LANGUAGE plpgsql;

1. In your Spring entity class, annotate the method that will call the stored procedure with @Procedure and define the name of the stored procedure as the value:

@Entity public class User  @Id private int id; private String name; private String email; @Procedure(name = "get_user_details") public void getUserDetails(int userId) > >

1. In your service or repository class, use the EntityManager to create a StoredProcedureQuery and set the parameters and result type:

@Repository public class UserRepository  @PersistenceContext private EntityManager entityManager; public ListUser> getUserDetails(int userId)  StoredProcedureQuery query = entityManager.createStoredProcedureQuery("get_user_details"); query.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN); query.setParameter(1, userId); query.getResultList(); ListUser> users = new ArrayList>(); for (Object[] result : results)  User user = new User(); user.setId((int) result[0]); user.setName((String) result[1]); user.setEmail((String) result[2]); users.add(user); > return users; > >

@Service public class UserService  @Autowired private UserRepository userRepository; public ListUser> getUserDetails(int userId)  return userRepository.getUserDetails(userId); > >

That’s it! Now you can call your PostgreSQL stored procedure using Spring’s @Procedure annotation.

Источник

Calling Stored Functions and Procedures

PostgreSQL® supports two types of stored objects, functions that can return a result value and — starting from v11 — procedures that can perform transaction control. Both types of stored objects are invoked using CallableStatement and the standard JDBC escape call syntax . The escapeSyntaxCallMode connection property controls how the driver transforms the call syntax to invoke functions or procedures.

The default mode, select , supports backwards compatibility for existing applications and supports function invocation only. This is required to invoke a function returning void.

For new applications, use escapeSyntaxCallMode=callIfNoReturn to map CallableStatements with return values to stored functions and CallableStatements without return values to stored procedures.

Example 6.1. Calling a built-in stored function

This example shows how to call the PostgreSQL® built-in function, upper , which simply converts the supplied string argument to uppercase.

CallableStatement upperFunc = conn.prepareCall(""); upperFunc.registerOutParameter(1, Types.VARCHAR); upperFunc.setString(2, "lowercase to uppercase"); upperFunc.execute(); String upperCased = upperFunc.getString(1); upperFunc.close(); 

Obtaining a ResultSet from a stored function

PostgreSQL’s™ stored functions can return results in two different ways. The function may return either a refcursor value or a SETOF some datatype. Depending on which of these return methods are used determines how the function should be called.

From a Function Returning SETOF type

Functions that return data as a set should not be called via the CallableStatement interface, but instead should use the normal Statement or PreparedStatement interfaces.

Example 6.2. Getting SETOF type values from a function

Statement stmt = conn.createStatement(); stmt.execute("CREATE OR REPLACE FUNCTION setoffunc() RETURNS SETOF int AS " +  "' SELECT 1 UNION SELECT 2;' LANGUAGE sql"); ResultSet rs = stmt.executeQuery("SELECT * FROM setoffunc()"); while (rs.next())   // do something  > rs.close(); stmt.close(); 

From a Function Returning a refcursor

When calling a function that returns a refcursor you must cast the return type of getObject to a ResultSet`

NOTE

One notable limitation of the current support for a ResultSet created from a refcursor is that even though it is a cursor backed ResultSet , all data will be retrieved and cached on the client. The Statement fetch size parameter described in the section called Getting results based on a cursor is ignored. This limitation is a deficiency of the JDBC driver, not the server, and it is technically possible to remove it, we just haven’t found the time.

Example 6.3. Getting refcursor Value From a Function

// Setup function to call.  Statement stmt = conn.createStatement(); stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '" +  " DECLARE " +  " mycurs refcursor; " +  " BEGIN " +  " OPEN mycurs FOR SELECT 1 UNION SELECT 2; " +  " RETURN mycurs; " +  " END;' language plpgsql"); stmt.close();  // We must be inside a transaction for cursors to work.  conn.setAutoCommit(false);  // Function call.  CallableStatement func = conn.prepareCall(""); func.registerOutParameter(1, Types.OTHER); func.execute(); ResultSet results = (ResultSet) func.getObject(1); while (results.next())   // do something with the results.  > results.close(); func.close(); 

It is also possible to treat the refcursor return value as a cursor name directly. To do this, use the getString of ResultSet . With the underlying cursor name, you are free to directly use cursor commands on it, such as FETCH and MOVE .

Example 6.4. Treating refcursor as a cursor name

conn.setAutoCommit(false); CallableStatement func = conn.prepareCall(""); func.registerOutParameter(1, Types.OTHER); func.execute(); String cursorName = func.getString(1); func.close(); 

Example 6.5. Calling a stored procedure

This example shows how to call a PostgreSQL® procedure that uses transaction control.

// set up a connection  String url = "jdbc:postgresql://localhost/test"; Properties props = new Properties(); . other properties.  // Ensure EscapeSyntaxCallmode property set to support procedures if no return value  props.setProperty("escapeSyntaxCallMode", "callIfNoReturn"); Connection con = DriverManager.getConnection(url, props);  // Setup procedure to call.  Statement stmt = con.createStatement(); stmt.execute("CREATE TEMP TABLE temp_val ( some_val bigint )"); stmt.execute("CREATE OR REPLACE PROCEDURE commitproc(a INOUT bigint) AS '" +  " BEGIN " +  " INSERT INTO temp_val values(a); " +  " COMMIT; " +  " END;' LANGUAGE plpgsql"); stmt.close();  // As of v11, we must be outside a transaction for procedures with transactions to work.  con.setAutoCommit(true);  // Procedure call with transaction  CallableStatement proc = con.prepareCall(""); proc.setInt(1, 100); proc.execute(); proc.close(); 

Источник