如何在Java中使用Spring JdbcTemplate调用PostgreSQL函数而不是直接插入查询？

Question

我是PostgreSQL的新手。我需要从Spring JdbcTemplate中调用PostgreSQL函数，以存储Employee表的详细信息。以下是我的代码，我在其中使用插入查询来存储Employee的详细信息。 我需要使用PostgreSQL函数“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()});
}

- User123

Answer 1

好的，首先你应该设计一个用于插入/更新数据的函数。Postgres支持许多语言，但最流行的是plpgsql。

函数本身可能如下所示：

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;
$$;

现在，如果你以null为ID调用此函数，它将插入数据，否则将通过指定的ID查找并更新数据。无论哪种情况，你都会收到修改记录的ID。

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

也许将插入功能与更新分开会更好，但这只是一个示例。

因此，您可以使用例如SimpleJdbcCall从Spring调用该函数：

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"));

- Bohdan Levchenko

---

如果您使用的是Postgresql 12版本并且声明了函数，则此解决方案不可行。 它适用于存储过程， 您可以查看PgDatabaseMetaData.class， 第627行， 您完成的getProcedures SQL包括p.prokind = p - 只适用于存储过程，而不适用于函数。 - Dmitry

Answer 2

如果你使用Spring Data JDBC，你会像这样调用函数：

@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);}

- ivvasch