0

My insert query returns the UUID of the record that was inserted. Below is the relevant code.

KeyHolder keyHolder = new GeneratedKeyHolder();

template.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(insertQuery);
    ps.setString(1, stateName);
    ps.setString(2, stateAb);
    ps.setObject(3, propertyDetailsObject);


    return ps;
}, keyHolder);

I'm using a long CTE query that ends with select id from tmpproperty limit 1; I have shortened things here for easier readability.

But I get the following exception

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; A result was returned when none was expected.; nested exception is org.postgresql.util.PSQLException: A result was returned when none was expected.

I found the above example online and was expecting it to work. Any idea what I should be doing differently?

Marko Previsic
  • 1,820
  • 16
  • 30
Arya
  • 8,473
  • 27
  • 105
  • 175
  • What's the query in the insertQuery? Why do you have 'select id...' in the update query? This is your error: update query should not return any value, you are doing select at the end to return last row id. This is not a valid scenario for the update query. – Pavel Molchanov Jul 05 '19 at 16:46
  • You should use INSERT...RETURNING, ex: INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id; – Pavel Molchanov Jul 05 '19 at 16:49
  • https://stackoverflow.com/questions/10597477/getting-auto-generated-key-from-row-insertion-in-spring-3-postgresql-8-4-9 – Pavel Molchanov Jul 05 '19 at 16:54

1 Answers1

0

Need to change to:

PreparedStatement ps =  connection.prepareStatement(youSQL, new String[]{"id"});

where id is the primary key column name

And remove select id from tmpproperty limit 1;, update query will be enough.

Pavel Molchanov
  • 2,299
  • 1
  • 19
  • 24
  • I'm using a CTE query, I think I need to have some kind of select after the CTE queries? Here is the full query https://pastebin.com/eGDHDfUh I always want to get the property.id, if it was just inserted, or if it already exists. The query works as expected right now. Not sure how to modify it so there would be no select. – Arya Jul 05 '19 at 17:09
  • I think your query should be executed with 'execute' or 'query' method, not 'update'. JDBCTemplate update method is designed for simple single inserts, not such complex insert that you have. – Pavel Molchanov Jul 05 '19 at 17:58