How to call PostgreSQL procedure in Java?

Question

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

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'

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?

UPDATE

I tried this 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

But query

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

returns 1.

Where is mistake?

What about `SELECT get_geom_difference()` in `psql`? Do you get a result there? If so, what about if, for testing, you use conventional JDBC query syntax with a `SELECT get_geom_difference()` instead of `{call}` syntax? — Craig Ringer, Aug 09 '12 at 07:16
If you are still stuck after all these answers, start by providing information that should have been here to begin with: Version numbers of the software in use (a very old version of PostgreSQL that doesn't have the procedural language plpgsql pre-installed, could be the problem), table definition of table `filedata` (data type mismatch as mentioned in my answer could be a problem). — Erwin Brandstetter, Aug 09 '12 at 07:42

Craig Ringer · Answer 1 · 2012-08-09T07:17:57.267

2

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

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

I don't know if getting a scalar result back from a CallableStatement is legal. I'd expect PgJDBC to translate it to a rowset of one row, though, so it should work.

edited Aug 09 '12 at 07:17

answered Aug 08 '12 at 12:16

Craig Ringer

307,061
76
688
778

`CallableStatement.executeQuery()` not help me, can u look at question update? – Kliver Max Aug 09 '12 at 04:55

score 2 · Accepted Answer · edited May 23 '17 at 11:45

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.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.

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.

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

SELECT get_geom_difference(1);

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

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

score 0 · Answer 3 · answered Aug 08 '12 at 07:33

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

Java Database Connectivity (JDBC)

and everything needed to serv it, is in package 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.

score 0 · Answer 4 · answered Aug 08 '12 at 07:35

0

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

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

answered Aug 08 '12 at 07:35

npinti

51,780
5
72
96

Why would you link to Oracle docs when this question is tagged PostgreSQL. I would seem that the [PostgreSQL JDBC docs](http://jdbc.postgresql.org/documentation/91/) are more appropriate. – Erwin Brandstetter Aug 09 '12 at 07:09
1

@ErwinBrandstetter: The link I have posted is Oracle's (used to be Sun) JDBC tutorial. It should be database independent and should cover the basics. I have included a link which should then take the OP to a more direct approach to what he/she is after. – npinti Aug 09 '12 at 07:27
Makes sense now when you put it that way. :) – Erwin Brandstetter Aug 09 '12 at 07:34

score 0 · Answer 5 · answered Feb 17 '16 at 23:14

works 100% java 7 and postgres pgadmin 2016, Use createNativeQuery In your transaction write this and change 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.

How to call PostgreSQL procedure in Java?

UPDATE

5 Answers5

Linked