Java JDBC How to Handle Update Query with RETURNING Statement

Question

I have a simple sql query which increments version by 1 and returns the current value in postgresql such as:

UPDATE table SET version = version + 1 where id = 'XXX' RETURNING version

However I am unable to use it in jdbcTemplate.update statement:

int version = jdbcTemplate.update(sqlString, new Object[] {id});

throws exception:

A result was returned when none was expected

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [UPDATE table SET version = version + 1 where id = ? RETURNING version]; A result was returned when none was expected.; nested exception is org.postgresql.util.PSQLException: A result was returned when none was expected.

What is the correct way to use RETURNING statement?

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [UPDATE table SET version = version + 1 where id = ? RETURNING version]; A result was returned when none was expected.; nested exception is org.postgresql.util.PSQLException: A result was returned when none was expected. — kundante
– kundante, Commented Jun 5, 2018 at 8:26

kundante · Accepted Answer · 2018-06-05 11:23:12Z

1

I have found the solution. Instead of jdbcTemplate.update I used jdbcTemplate.query and cast number to int to obtain the value of version. Thank you all.

    Number number = jdbcTemplate.queryForObject(sqlString, new Object[] {id}, Integer.class);
    int version;

    version = number != null ? number.intValue() : 0;

answered Jun 5, 2018 at 11:23

kundante

2,1502 gold badges17 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Java JDBC How to Handle Update Query with RETURNING Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related