如何用Java调用PostgreSQL程序？ [英] How to call PostgreSQL procedure in Java?

问题描述

如何在Java中进行这样的查询并获得结果：

How to make query like this in Java and get the results:

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中根据此查询创建过程会更好。

Or, I think will be better if I create procedure in Postgres from this query.

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中获取此过程的结果？

But how to get results from this procedure in Java?

我试过这个SP

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行行 proc.executeQuery（发生异常） ）;

org.apache.jasper.JasperException: An exception occurred processing JSP page /commit_changes.jsp at line 25in lineproc.executeQuery();

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

root cause javax.servlet.ServletException: org.postgresql.util.PSQLException: No results were returned by the query

但查询

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

返回 1 。

哪里出错？

推荐答案

你可以在很大程度上简化这项功能。 （为了问题，保持简单化的功能。）

You can largely simplify the function. (Keeping simplistic function for the sake of the question.)

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 ？这就是我从这个例子中收集的内容。在您的其他问题上，由于缺乏信息，我假设数字。其中至少有一个会失败。

Though, technically, what you have in the question would work, too - provided the data type matches. Does it? Is the column filedata.num of type integer? That's what I gather from the example. On your other question I was assumingnumeric for lack of information. At least one of them will fail.

如果函数的返回类型与返回的值不匹配，则会从PostgreSQL函数中获得错误。正确配置后，PostgreSQL日志在这种情况下会有详细的错误消息。

If the return type of the function doesn't match the returned value you get an error from the PostgreSQL function. Properly configured, your PostgreSQL log would have detailed error messages in this case.

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

What do you see, when you create the above function in PostgreSQL and then call:

SELECT get_geom_difference(1);

来自 psql 。 （最好在同一个会话中排除数据库，端口，服务器或用户的混合。）

from psql. (Preferably in the same session to rule out a mixup of databases, ports, servers or users.)

调用一个简单的函数获取一个参数并返回一个标量值似乎很漂亮直截了当 PostgreSQL JDBC手册的第6.1章有一个完整的例子似乎完全一致你的问题是什么（我的专长是Postgres而不是JDBC）。

Calling a simple function taking one parameter and returning one scalar value seems pretty straight forward. Chapter 6.1 of the PostgreSQL JDBC manual has a full example which seems to agree perfectly with what you have in your question (My expertise is with Postgres rather than JDBC, though).

这篇关于如何用Java调用PostgreSQL程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！