How to invoke PostgreSQL function using spring jdbctemplate instead of direct insert query in Java?

Question

I am new to PostgreSQL. I need to call postgresql function from spring jdbctemplate for storing Employee table details. Below is my code in which I am using insert query to store the Employee details. I need to replace insert query with the Postgresql function-"UpdateEmployee".

@Autowired
JdbcTemplate postgressqljdbctemplate;


@Override
public void update(Employee employee) {
String SQL = "insert into employee(Id, name, age, salary) values (?,?,?,?)";
postgressqljdbctemplate.update(SQL, new Object[] { employee.getId(), employee.getName(),
employee.getAge(), employee.getSalary()});
}

Profusive

Answer 1

Ok, the first thing you should do is design that function for insert/update data. Postgres supports many languages for that, but the most popular one is plpgsql.

The function itself might look like:

CREATE OR REPLACE FUNCTION update_employee(p_id INT, p_name VARCHAR(255), p_age INT, p_salary DECIMAL)
  RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_id IS NULL
  THEN
    INSERT INTO employee (name, age, salary) VALUES (p_name, p_age, p_salary) RETURNING id INTO p_id;
  ELSE
    UPDATE employee
    SET name = p_name, age = p_age, salary = p_salary
    WHERE id = p_id;
  END IF;
  RETURN p_id;
END;
$$;

Now if you call this function with null as ID it will insert the data, otherwise data will be found by specified id and updated.

In both cases you'll get the ID of modified record back.

SELECT update_employee(NULL, 'John', 42, 100000);  -- insert (returns 1)
SELECT update_employee(1, 'John Smith', 42, 200000); -- update previous record

Probably it would be better to separate insert function from update, but it is just a sample.

So, with that you can call the function from spring using for example SimpleJdbcCall:

final SimpleJdbcCall updateEmployeeCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("update_employee");
final Map<String, Object> params = new HashMap<>();
params.put("p_id", null);
params.put("p_name", "John");
params.put("p_age", 28);
params.put("p_salary", 150000);

final Map<String, Object> result = updateEmployeeCall.execute(params);
System.out.println(result.get("returnvalue"));

Drayman

This solution doesn't work if you use Postgresql 12 version and you decalre function. It's work for procedure, You can see PgDatabaseMetaData.class, line 627, your complited sql for getProcedures include p.prokind=p - only for procedure, but not for function – Roose

Answer 2

if you use spring data jdbc, you will call function like this:

@Repository 
public interface ExchangeRateRepository extends CrudRepository<ExchangeRate, Integer> {
@Modifying
@Query(value = "call s_pay_dev.p_load_rates(:from::varchar, :to::varchar, :date::date, :time::time, :rate::numeric)")
void saveRateToDB(@Param("from") String from, @Param("to") String to,
                  @Param("date") String date, @Param("time") String time,
                  @Param("rate") Float rate);}

Jook