问题描述
最近迁移到 POSTGRESQL,我试图在创建新条目到 db 表时获取唯一生成的密钥.表 screenstable
看起来像这样:
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 )
)
将数据插入screenstable
的方法如下:
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();
}
和 ImageBean
是
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).....
同样的代码在 MySQL
的情况下运行良好,但在与 POSTGRES 一起使用时会因密钥而失败.数据类型 serial
是否以某种方式导致代码失败,或者我是否正确使用了主键功能?
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?
请指教.
推荐答案
如果框架没有被告知哪一列是键,它会返回表的所有列作为键.
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" });
见 NamedParameterJdbcTemplate.update(sql, paramSource,generatedKeyHolder,keyColumnNames)
这篇关于Spring:如何在 PostgreSQL 中使用 KeyHolder的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!