Calling a PostgreSQL function from Java

Question

I have written a function that I would like to call in Java. But I don't think it is able to do anything with the query that I passed. Following is my code from java:

String QUERY_LOCATION = "select (license_plate) as test from carInst( (select     category_name from reservation where rid = ?) , (select lname from reservation where rid =  ?))";  
        //PreparedStatement check_location = null;
        PreparedStatement check_location = connection.prepareStatement(QUERY_LOCATION);
        check_location.setInt(1, rid);
        check_location.setInt(2, rid);
        rs = check_location.executeQuery();
        if (rs.next()) {
            System.out.print("Car found: "+rs.getString("test")+"\n");
            license_plate = rs.getString("test");
            update_reservation.setString(5, license_plate);
            
            bool = false;
        } else {
            System.out
                    .print("There is no car available\n");
        }

And following is my stored procedure written in PL/pgSQL (PostgreSQL):

CREATE OR REPLACE FUNCTION carInst(cname varchar(20), loc varchar(20) ) 
RETURNS TABLE (license_plate varchar(6) ) AS $$
BEGIN 
    DECLARE cur CURSOR 
        FOR SELECT carinstance.license_plate, carmodel.category_name, carinstance.lname FROM carinstance,carmodel 
            WHERE carinstance.mid = carmodel.mid ;                  
BEGIN 
    FOR rec IN cur LOOP
        RETURN QUERY SELECT distinct carinstance.license_plate FROM Carinstance 
            WHERE rec.category_name  = cname
                AND rec.lname = loc
                AND rec.license_plate=carinstance.license_plate;
        END LOOP;
    END; 
END;
$$ LANGUAGE plpgsql;

When I run the code in Java, the print statement prints a null value for Car found. I would really appreciate some help here.

Erwin Brandstetter · Accepted Answer · 2022-10-29 06:48:10Z

Problems

Most importantly, the query in the LOOP is nonsense. You select rows from carinstance, but all conditions are on rec. This select all rows multiple times.
One END too many. FOR has no END, only LOOP has.
Whenever you feel the temptation to work with an explicit cursor in plpgsql, stop right there. Chances are, you are doing it wrong. A FOR loop has an implicit cursor anyway.
Don't mess with mixed case identifiers without double quotes. I converted all identifiers to lower case.
You use one simple query, spread out over a cursor and another query. This can all be much simpler.

Solution

Try this simple SQL function instead:

CREATE OR REPLACE FUNCTION car_inst(_cname text, _loc text) 
  RETURNS TABLE (license_plate text)
  LANGUAGE sql AS
$func$
SELECT DISTINCT ci.license_plate
FROM   carmodel    cm
JOIN   carinstance ci USING (mid)
WHERE  cm.category_name  = $1
AND    ci.lname = $2
$func$;

Call:

SELECT license_plate AS test FROM car_inst(
         (SELECT category_name FROM reservation WHERE rid = ?)
       , (SELECT lname FROM reservation WHERE rid = ?)
       );

Or build it all into your function:

CREATE OR REPLACE FUNCTION car_inst(_cname text, _loc text) 
  RETURNS TABLE (license_plate text)
  LANGUAGE sql AS
$func$
SELECT DISTINCT ci.license_plate
FROM   carmodel    cm
JOIN   carinstance ci USING (mid)
JOIN   reservation r1 ON r1.category_name = cm.category_name
JOIN   reservation r2 ON r2.lname = ci.lname
WHERE  r1.rid = $1
AND    r2.rid = $2;
$func$;

Call:

"SELECT license_plate AS test FROM car_inst(? , ?)";

Remember: The OUT parameter license_plate is visible anywhere in the body of the function. Therefore you must table-qualify the column of the same name at all times to prevent a naming collision.

DISTINCT may or may not be redundant.

Collectives™ on Stack Overflow

Calling a PostgreSQL function from Java

1 Answer 1

Problems

Solution

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Problems

Solution

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related