CODEWITHSUNDEEP
javasqlpostgresqljdbcjdbctemplate
kundante
kundante

Reputation: 2160

Java JDBC How to Handle Update Query with RETURNING Statement

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?

Upvotes: 1

Views: 4060

Answers (1)

kundante
kundante

Reputation: 2160

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;

Upvotes: 1

Related Questions