Hibernate executeUpdate() on PostgreSQL function

Question

I am using hibernate with a function in PostgreSQL to update a record in the DB.

Following is the PostgreSQL function,

    CREATE OR REPLACE FUNCTION updatesegment(segid bigint, segname text, segdesc text, segmeta text)
      RETURNS void AS
    $BODY$
      BEGIN
        UPDATE segment
        SET
           name = segname,
           description = segdesc,
           segmentmetadata = segmeta
        WHERE
           id = segid;
      END;
    $BODY$
    LANGUAGE plpgsql;

In the java code I am invoking the function as below.

public int updateSegment(Long segmentId, String segName, String segDesc, String segMeta) {

    SQLQuery query = (SQLQuery)sessionFactory.getCurrentSession().createSQLQuery("SELECT updatesegment(:segmentId, :segName, :segDesc, :segMeta)")
            .setParameter("segmentId", segmentId)
            .setParameter("segName", segName)
            .setParameter("segDesc", segDesc)
            .setParameter("segMeta", segMeta);

    int rows = query.executeUpdate();

    return rows;
}

When I call the updateSegment() java function above with the correct input parameters, the record in the DB gets updated as expected. But query.executeUpdate() is always returning 0 where as it should be returning 1.

What am I doing wrong and why isn't executeUpdate() returning 1?

Thanks in advance...!!!

So you're calling executeUpdate(), but passing it a SELECT query? Seems like maybe that's your problem... — Tim
– Tim, Commented Aug 8, 2013 at 18:07
I need the SELECT to invoke the plpgsql function.. otherwise I cannot invoke the function in the DB.. and inside the plpgsql function there is an update statement.. — Bathiya Priyadarshana
– Bathiya Priyadarshana, Commented Aug 8, 2013 at 18:09

dic19 · Accepted Answer · 2013-08-08 19:02:48Z

2

In order to get affected rows in your function, you'll need to do some changes:

CREATE OR REPLACE FUNCTION updatesegment(segid bigint, segname text, segdesc text, segmeta text)
  RETURNS integer AS
$BODY$
  DECLARE
    rowsAffected integer := 0;
  BEGIN
    UPDATE segment
    SET
       name = segname,
       description = segdesc,
       segmentmetadata = segmeta
    WHERE
       id = segid;
    GET DIAGNOSTICS rowsAffected = ROW_COUNT; --here you get the affected rows
  END;
$BODY$
LANGUAGE plpgsql;

Then you should also modify your java code accordingly to execute a SELECT query invoking your function and retrieve the integer it will return.

Source: PostreSQL Documentation

edited Aug 8, 2013 at 19:02

answered Aug 8, 2013 at 18:49

dic19

18k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim · Accepted Answer · 2013-08-08 18:26:24Z

2

So you're calling executeUpdate(), but passing it a SELECT query? Even though that SELECT query is going to call your function, which updates rows, the function updates the rows, not the SELECT statement. So executeUpdate 1) isn't going to know to look for updated rows (it's a SELECT, why would it be updating rows?), and 2) wouldn't be able to get the count even if it knew to try.

Try running your SELECT statement directly in the console, and see if it tells you that it updated 1 row... If that doesn't tell you about rows being updated, how would Hibernate know?

As @dic19 says, If you want to get that value back, have your function return that value, and use list() instead of executeUpdate() and then pull that value from the resulting List.

edited Aug 8, 2013 at 18:26

answered Aug 8, 2013 at 18:11

Tim

2,02715 silver badges26 bronze badges

2 Comments

Bathiya Priyadarshana Over a year ago

How can I keep the update statement inside the function and still get the rowcount affected by the function inside java.. That's my requirement.. thanks..

dic19 Over a year ago

@BathiyaPriyadarshana Currently your function is returning void. Make it return the number of updated rows. Then your SELECT invoked from java will get the updated rows number.

Craig Ringer · Accepted Answer · 2013-08-09 02:03:50Z

SQL executed inside PL/PgSQL functions doesn't affect the rowcount.

If it did, how would you expect a function that DELETEd from two tables then UPDATEd a third work? Would it return the sum of all three rowcounts? The rowcount of the last operation? What?

What about if you DELETE one record from a table and INSERT another? Is the rowcount zero, one, or two? All would make sense.

If you want to use the query rowcounts, don't wrap your queries in PL/PgSQL functions.

If you must wrap them in functions you will need to fetch the row count into a variable with GET DIAGNOSTICS and return that, eg:

CREATE OR REPLACE FUNCTION updatesegment(segid bigint, segname text, segdesc text, segmeta text, OUT nrows integer)
  RETURNS integer AS
$BODY$
  BEGIN
    UPDATE segment
    SET
       name = segname,
       description = segdesc,
       segmentmetadata = segmeta
    WHERE
       id = segid;
    -- store the row count in the nrows OUT parameter
    GET DIAGNOSTICS nrows = ROW_COUNT;
  END;
$BODY$
LANGUAGE plpgsql;

JDBC doesn't know that this is a rowcount, it's just a function return value. So you must use it with execute not executeUpdate and then get the result row to determine the affected row count. This is kind of horrible; unless you need SECURITY DEFINER I struggle to imagine why you'd be doing this via a function.

There's currently no way to set the rowcount result of a function.

Collectives™ on Stack Overflow

Hibernate executeUpdate() on PostgreSQL function

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related