postgresql 从java调用postgres上的存储函数

call a stored function on postgres from java

提问by giozh

I've created the following function in my Postgres database:

我在 Postgres 数据库中创建了以下函数：

CREATE FUNCTION funct(arg1 type1, arg2 type2, ...) RETURNS void AS $$
BEGIN
--insert passed args in some tables
END

How can I call this function from java and pass args properly?

如何从 java 调用此函数并正确传递 args？

回答by Preet Sangha

Provided you understand how to use JDBC to talk to postgres (if not this tutorialshould get you up to speed), then this JDBC pageshows how to call stored functions:

如果您了解如何使用 JDBC 与 postgres 对话（如果不是本教程应该能让您快速上手），那么这个JDBC 页面将展示如何调用存储的函数：

For example:

例如：

// Setup function to call.
Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '"
        + " DECLARE "
        + "    mycurs refcursor; "
        + " BEGIN "
        + "    OPEN mycurs FOR SELECT 1 UNION SELECT 2; "
        + "    RETURN mycurs; "
        + " END;' language plpgsql");
stmt.close();

// We must be inside a transaction for cursors to work.
conn.setAutoCommit(false);

// Procedure call.
CallableStatement proc = conn.prepareCall("{ ? = call refcursorfunc() }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
    // do something with the results...
}
results.close();
proc.close();

There are other kinds of stored function discussed on the latter link also.

在后一个链接中还讨论了其他类型的存储函数。

回答by Crazy_learner

Calling postgresql function/stored procedure is little bit different than normal Sql call. Some time back I was also faced similar problem,i searched a lot, then i found solution. below I am sharing the solution.

调用 postgresql 函数/存储过程与普通 Sql 调用略有不同。前段时间我也遇到了类似的问题，我搜索了很多，然后我找到了解决方案。下面我分享解决方案。

Below is the postgresql function

下面是 postgresql 函数

Create or Replace Function myfunction(id integer) Returns Table(empid integer,empname charactervarying) AS
$BODY$
DECLARE
var_r record;

BEGIN
FOR var_r IN(
select * from emp where empno=id
)

LOOP
empid:=var_r.empno;
empname:=var_r.empname;
RETURN NEXT;
end LOOP;

END; $BODY$
LANGUAGE plsql VOLATILE

when we will run the query in postgresql console, it will give the result according to the records present in the table for id=5

当我们在 postgresql 控制台中运行查询时，它会根据表中存在的记录给出结果，id=5

select myfunction(5);

Now we have to write code to call this function from java. below is the code

现在我们必须编写代码来从 java 调用这个函数。下面是代码

public class Employee {
    public static void main(String[] args) {

    try {
    callProc();
    }Catch(SQLException e) {
    e.printStackTrace();
    }
    }

    public static void callProc () throws SQLException {



        Connection con=DBConnection.getConnection();
        Statement statement=con.createStatement();
    //Call to postgresql function   
        String query=" call myfunction(?)";
        CallableStatement ps=con.prepareCall(query);
        ps.setInt(1,5)  // it means we are setting value 5 at first index.
        ResultSet rs=ps.executeQuery();
        List<Emp> listOfEmp=new ArrayList<Emp>();

        while(rs.next()){

        Emp data=new Emp();
        data.setEmpno(rs.getInt("empid"));
        data.setEmpName(rs.getString("empid"));
        listOfEmp.add(data);

        }
        system.out.println("Total emp"+listOfEmp.size());
    }
}

In above example we have written the code to call the postgresql function from java.

在上面的例子中，我们编写了从 java 调用 postgresql 函数的代码。

for more details see this Postgresql Function with java

有关更多详细信息，请参阅此 Postgresql Function with java

回答by Jose Daniel Chacón Bogarín

I know this is a bit old, but I stumbled upon the same problem and found a solution myself. It was pretty straightforward, but I hope this works for future reference.

我知道这有点旧，但我偶然发现了同样的问题并自己找到了解决方案。这很简单，但我希望这可以供将来参考。

CallableStatement statement = connection.prepareCall(" { call function( ?, ?, ? ) } ");

statement.setInt(1, value);
statement.setInt(2, value);
statement.setInt(3, value);
statement.execute();
statement.close();

Notice that there isn't a ? =before the callin the actuall sql code and that there is no call to registerOutParametermethod.

请注意，没有一个? =前call在实际工作中的SQL代码，并且有没有调用registerOutParameter方法。

回答by diego matos - keke

Use createNativeQuery In your transaction write this and change myschema.mymethodThatReturnASelectfor the scheme and the name of your function.

使用 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);
                    c.setIdUsuario(o[1] != null ? Integer.valueOf(o[1].toString()) : -1);
                    c.setIsCreador(o[2] != null ? Boolean.valueOf(o[2].toString()) : null);
                    c.setIdPadre(o[3] != null ? Long.valueOf(o[3].toString()) : -1L);
                    c.setNombre(o[4] != null ? o[4].toString() : "");
                    c.setNombreCliente(o[5] != null ? o[5].toString() : "");
                    c.setCodigo(o[6] != null ? o[6].toString() : "");
                    c.setEstado(o[7] != null ? Long.valueOf(o[7].toString()) : -1L);
                    c.setTotalDisponible(o[8] != null ? Double.valueOf(o[8].toString()) : 0.0);
                    c.setCantidadAlmacenamiento(o[9] != null ? Double.valueOf(o[9].toString()) : 0.0);
                    c.setPorcentajeAlmacenamiento(o[10] != null ? Double.valueOf(o[10].toString()) : 0.0);
                    c.setCantidadUsuario(o[11] != null ? Long.valueOf(o[11].toString()) : 0);
                    c.setCantidadRegistros(o[12] != null ? Long.valueOf(o[12].toString()) : 0);
                    c.setMbFoto(o[13] != null ? Double.valueOf(o[13].toString()) : 0.0);
                    c.setMbVideo(o[14] != null ? Double.valueOf(o[14].toString()) : 0.0);
                    c.setMbTexto(o[15] != null ? Double.valueOf(o[15].toString()) : 0.0);
                    list.add(c);
                }
            }
        } catch (Exception e) {
            System.out.println("listarFormulario " + e.getMessage());
        }
        return list;
  }