Spring: How to use KeyHolder with PostgreSQL

Question

Recently migrated to POSTGRESQL, I am trying to obtain the uniquely generated key on creating a new entry into the db table. The table screenstable looks like this:

CREATE TABLE screenstable
(
  id serial NOT NULL,
  screenshot bytea,
  CONSTRAINT screen_id PRIMARY KEY (id )
)

The method that inserts data into screenstable is as follows:

@Autowired NamedParameterJDBCTemplate template;
public int insertImage(ImageBean imageBean){
        String query = "insert into screenstable (screenshot) values (:image)";
        SqlParameterSource data = new BeanPropertySqlParameterSource(imageBean);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(query, data, keyHolder);
        return keyHolder.getKey().intValue();
    }

and ImageBean is

import java.util.Arrays;

public class ImageBean {
    private int id;
    private byte[] image;
    @Override
    public String toString() {
        return "ImageBean [id=" + id + ", image=" + Arrays.toString(image)
                + "]";
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public byte[] getImage() {
        return image;
    }
    public void setImage(byte[] image) {
        this.image = image;
    }
}

But running the code give the following exception

15:33:20,953 ERROR JsonParseExceptionMapper:15 - org.springframework.dao.InvalidDataAccessApiUsageException: The getKey method should only be used when a single key is returned.  The current key entry contains multiple keys: [{id=3, screenshot=[B@db59df}]
org.springframework.dao.InvalidDataAccessApiUsageException: The getKey method should only be used when a single key is returned.  The current key entry contains multiple keys: [{id=3, screenshot=[B@db59df}]
        at org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:65)
        at some.project.model.FeedbackDao.insertImage(FeedbackDao.java:20)
        at some.project.rest.FeedsRest.pluginCheck(FeedsRest.java:62)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597).....

The same code used to run fine in case of MySQL but is failing with keys when used with POSTGRES. Is the datatype serial somehow responsible for the code failing or may be I am using the primary key feature correctly?

Please advice.

Answer 1

If the framework is not informed about which column is the key, it will return all columns of the table as keys.

You can inform it by passing a new parameter to the update method, as follows:

template.update(query, data, keyHolder, new String[] { "id" });

See NamedParameterJdbcTemplate.update(sql, paramSource, generatedKeyHolder, keyColumnNames)