如何使用spring jdbctemplate而不是Java中的直接插入查询来调用PostgreSQL函数? [英] How to invoke PostgreSQL function using spring jdbctemplate instead of direct insert query in Java?

问题描述

我是PostgreSQL的新手.我需要从spring jdbctemplate调用postgresql函数来存储Employee表的详细信息.下面是我使用插入查询存储Employee详细信息的代码.我需要用Postgresql函数"UpdateEmployee"替换插入查询.

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

推荐答案

好，您应该做的第一件事就是设计用于插入/更新数据的函数.Postgres支持多种语言，但是最受欢迎的语言是plpgsql.

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

现在，如果使用 null 作为ID调用此函数，它将插入数据，否则将通过指定的ID查找数据并进行更新.

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.

在两种情况下，您都将获得修改后的记录的ID.

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.

因此，您可以使用例如SimpleJdbcCall从spring调用函数:

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

这篇关于如何使用spring jdbctemplate而不是Java中的直接插入查询来调用PostgreSQL函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！