如何在Java中调用PostgreSQL存储过程？

Question

如何在Java中执行以下查询并获取结果：

SELECT filedata.num,st_area(ST_Difference(ST_TRANSFORM(filedata.the_geom,70066),filedata_temp.the_geom))
FROM filedata, filedata_temp
Where filedata.num=filedata_temp.num

或者，我认为如果我从这个查询中创建一个Postgres存储过程会更好。

CREATE OR REPLACE FUNCTION get_geom_difference()
RETURNS void AS
$$
BEGIN
SELECT filedata.num,st_area(ST_Difference(ST_TRANSFORM(filedata.the_geom,70066),filedata_temp.the_geom))
FROM filedata, filedata_temp
Where filedata.num=filedata_temp.num

end;
$$
LANGUAGE 'plpgsql'

并将其称为

Connection ce_proc= null;
ce_proc = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgis","postgres","123456");
java.sql.CallableStatement proc =  ce_proc.prepareCall("{get_geom_difference()}");
proc.execute();
proc.close();
ce_proc.close();

但是如何在Java中从这个过程中获取结果呢？

更新

我尝试了这个存储过程

DROP FUNCTION get_geom_difference();

CREATE OR REPLACE FUNCTION get_geom_difference()
RETURNS integer AS
$$
DECLARE

tt integer;
BEGIN
SELECT filedata.num INTO tt
FROM filedata
Where filedata.num=1;
RETURN tt;

END;
$$
LANGUAGE 'plpgsql'

并调用

Class.forName("org.postgresql.Driver");
Connection connect= null;
connect = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgis","postgres","123456");
java.sql.CallableStatement proc =  connect.prepareCall("{?=call get_geom_difference()}");
proc.registerOutParameter(1, java.sql.Types.INTEGER);
proc.executeQuery(); 
ResultSet results = (ResultSet) proc.getObject(1);

并且遇到了错误：

org.apache.jasper.JasperException：处理JSP页面/commit_changes.jsp时出现异常，位于第25行in lineproc.executeQuery();

根本原因javax.servlet.ServletException： org.postgresql.util.PSQLException：查询未返回任何结果

但是查询

SELECT filedata.num 
FROM filedata
Where filedata.num=1;

返回 1。

哪里有错误？

- Kliver Max

---

在 psql 中使用 SELECT get_geom_difference() 有什么结果吗？如果有的话，那么如果您使用传统的 JDBC 查询语法来测试，而不是 {call} 语法，会发生什么情况？ - Craig Ringer

如果您在阅读所有答案后仍然无法解决问题，可以从提供一些最初就应该有的信息开始：软件使用的版本号（使用非常老的 PostgreSQL 版本而没有预先安装过程序语言 plpgsql 可能是问题所在），filedata 表的表定义（如我回答中提到的数据类型不匹配可能是问题所在）。 - Erwin Brandstetter

Answer 1

您可以大大简化该函数。（为了问题的简单性而保持简单的功能。）

CREATE OR REPLACE FUNCTION get_geom_difference()
   RETURNS integer AS
$BODY$
   SELECT num
   FROM   filedata
   WHERE  num = 1 
   LIMIT  1;  -- needed if there can be more than one rows with num = 1
$BODY$    LANGUAGE SQL;

尽管技术上，你在问题中的做法也可以行得通——只要数据类型匹配。它匹配吗？列filedata.num是integer类型吗？这是我从例子中了解到的。在你的另一个问题中，由于缺少信息，我假设为numeric。其中至少一个会失败。

如果函数的返回类型与返回值不匹配，那么PostgreSQL函数将会报错。适当配置后，PostgreSQL日志将会详细记录错误信息。

在PostgreSQL中创建上述函数并调用时，你看到了什么？

SELECT get_geom_difference(1);

从 psql 中执行（最好在同一会话中，以排除数据库、端口、服务器或用户混淆的可能性）。

调用一个简单的函数，该函数接受一个参数并返回一个标量值似乎非常直接。PostgreSQL JDBC 手册第6.1章 提供了一个完整的示例，与您在问题中提到的内容完全一致（尽管我的专业是 Postgres 而不是 JDBC）。

- Erwin Brandstetter

Answer 2

有相当多不同的CallableStatement构造函数，但只有其中两个让您获取结果。

CallableStatement.executeQuery()返回一个ResultSet。上面的链接中有一个很好的完整示例。

我不知道从CallableStatement获取标量结果是否合法。 我期望PgJDBC将其转换为一个包含一行的行集，所以它应该有效。

- Craig Ringer

---

CallableStatement.executeQuery() 对我没有帮助，您能看一下问题更新吗？ - Kliver Max

Answer 3

在Java 7和Postgres pgAdmin 2016中100%工作，使用createNativeQuery在您的事务中编写以下内容，并更改myschema.mymethodThatReturnASelect为您函数的模式和名称。

@Override
    public List<ViewFormulario> listarFormulario(Long idUsuario) {
        List<ViewFormulario> list =null;
        try {
            Query q = em.createNativeQuery("SELECT * FROM myschema.mymethodThatReturnASelect(?);");
            q.setParameter(1, idUsuario);

             List<Object[]> listObject = (List<Object[]>) q.getResultList();
            if (listObject != null && !listObject.isEmpty()) {
                list = new ArrayList<>();
                for (Object o[] : listObject) {
                    ViewFormulario c = new ViewFormulario();
                    c.setIdProyecto(o[0] != null ? Long.valueOf(o[0].toString()) : -1L);

...等等...等等。

- diego matos - keke

Answer 4

你的查询示例是典型的。所以你需要的是

Java数据库连接（JDBC）

而且，你需要服务它的所有内容都在包java.sql中。

所以此时我建议你先阅读一些教程，如果你有特殊问题，请在SO上写下来。

- Damian Leszczyński - Vash

Answer 5

你需要使用JDBC来完成这个任务。你可以在这里找到所有与JDBC相关的信息。

如果想要更详细地了解如何将Java应用程序连接到PostgreSQL，请查看这里的教程。

- npinti

---

为什么在这个问题标记为PostgreSQL的情况下，你会链接到Oracle文档？似乎PostgreSQL JDBC文档更加合适。 - Erwin Brandstetter

@ErwinBrandstetter：我发布的链接是Oracle（曾经是Sun）的JDBC教程。它应该是与数据库无关的，并且应该涵盖基础知识。我还包含了一个链接，应该可以带领OP更直接地找到他/她所需要的内容。 - npinti

这样说的话现在有意义了。 :) - Erwin Brandstetter