如何在 Java 中调用 PostgreSQL 程序？

How to call PostgreSQL procedure in Java?

提问by Kliver Max

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

如何在 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

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

或者，我认为如果我从这个查询在 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'

and call it

并称之为

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

But how to get results from this procedure in Java?

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

UPDATE

更新

I tried this SP

我试过这个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'

and call

并打电话

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

and got an error:

并得到一个错误：

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

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

org.apache.jasper.JasperException：在第 25 行in lineproc.executeQuery()处理 JSP 页面 /commit_changes.jsp 时发生异常；

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

But query

但查询

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

returns 1.

返回1。

Where is mistake?

错在哪里？

采纳答案by Erwin Brandstetter

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;

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

但是，从技术上讲，您在问题中的内容也可以使用 - 只要数据类型匹配。可以？是filedata.num类型的列integer吗？这就是我从示例中收集到的信息。关于你的另一个问题，我假设numeric缺乏信息。至少其中之一会失败。

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 函数的错误消息。在这种情况下，正确配置后，您的 PostgreSQL 日志将包含详细的错误消息。

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

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

SELECT get_geom_difference(1);

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

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

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

调用一个带一个参数并返回一个标量值的简单函数似乎很简单。PostgreSQL JDBC 手册的第 6.1 章有一个完整的示例，它似乎与您的问题完全一致（不过，我的专长是 Postgres 而不是 JDBC）。

回答by Craig Ringer

There are quite a few different CallableStatementconstructors, but only two of them let you get results back.

有很多不同的CallableStatement构造函数，但只有其中两个可以让您返回结果。

A ResultSetis returned by CallableStatement.executeQuery(). There's a good complete example in the link above.

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

I don't know if getting a scalar result back from a CallableStatementis legal. I'd expect PgJDBCto translate it to a rowset of one row, though, so it shouldwork.

我不知道从 a 返回标量结果CallableStatement是否合法。不过，我希望PgJDBC将其转换为一行的行集，因此它应该可以工作。

回答by diego matos - keke

works 100% java 7 and postgres pgadmin 2016, Use createNativeQuery In your transaction write this and change myschema.mymethodThatReturnASelect

100% java 7 和 postgres pgadmin 2016 工作，使用 createNativeQuery 在你的交易中写这个并改变myschema.mymethodThatReturnASelect

for the scheme and the name of your function.

用于方案和函数名称。

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

...etc...etc.

……等等……等等。

回答by Damian Leszczyński - Vash

Your query example is typical. So what you will need is

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

Java Database Connectivity (JDBC)

Java 数据库连接 (JDBC)

and everything needed to serv it, is in package java.sql

服务它所需的一切都在包java.sql 中

So at this point I recoemnd you to read some tutorial and if you have some particular problem write about it on SO.

所以在这一点上，我建议你阅读一些教程，如果你有一些特殊的问题，就写在 SO 上。

回答by npinti

You will need JDBC to do that. You should be able to find all JDBC related information here.

您将需要 JDBC 来做到这一点。您应该能够在此处找到所有与 JDBC 相关的信息。

Take a look herefor a more detailed tutorial on how to connect your Java application to your PostgreSQL.

看看这里了解如何在Java应用程序连接到你的PostgreSQL更详细的教程。