使用生成的主键插入Derby表时，Eclipselink JPA出错 [英] Eclipselink JPA gets error when inserting into Derby table with generated primary key

问题描述

当持久化到具有生成的主键的表中时，EclipseLink似乎错误地将空主键值传递给Derby.在这种情况下，Derby返回错误试图修改标识列. Derby需要一个不包含id值的SQL语句.我的问题是如何强制EclipseLink发送正确的SQL?详细信息如下...

EclipseLink seems to be incorrectly passing a null primary key value to Derby when persisting into a table with generated primary key. Derby returns error of Attempt to modify an identity column in this case. Derby needs a SQL statement that excludes the id value. My question is how do I coerce EclipseLink to send the right SQL? Details follow...

我正在使用Eclipselink映射到最新的Netbeans 7.3beta2下的Derby数据库(v10.8.1.2).

I am using Eclipselink to map to a Derby database (v10.8.1.2) under the latest Netbeans 7.3beta2.

数据库表主键是自动生成的:

The database table primary key is auto generated:

CREATE TABLE STUDENT_BATCH ( 
    ID bigint PRIMARY KEY
        GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    FILENAME varchar(200) NOT NULL,
    SCHOOLBOARD varchar(100) NOT NULL,
    CREATE_TS timestamp NOT NULL,
    CONTACT_INFO varchar(200),
    NOTES varchar(2000),
    BOARD_NAME varchar(100)
)
;

相应的jpa类指定ID是使用身份策略生成的:

The corresponding jpa class specifies that the id is generated using strategy of identity:

@Entity
@Table(name = "STUDENT_BATCH")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "StudentBatch.findAll", query = "SELECT s FROM StudentBatch s")})
public class StudentBatch implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Long id;
    @Basic(optional = false)
    @Column(name = "FILENAME")
    private String filename;
    @Basic(optional = false)
    @Column(name = "SCHOOLBOARD")
    private String schoolboard;
    @Basic(optional = false)
    @Column(name = "CREATE_TS")
    @Temporal(TemporalType.TIMESTAMP)
    private Date createTs;
    @Column(name = "CONTACT_INFO")
    private String contactInfo;
    @Column(name = "NOTES")
    private String notes;
    @Column(name = "BOARD_NAME")
    private String boardName;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "studentBatchId")
    private Collection<StudentRecord> studentRecordCollection;

    methods etc follow...

当我要坚持上课时，我没有指定ID，所以Derby将提供该ID.

When I go to persist the class, I do not specify an id so that Derby will provide the id.

                StudentBatch sb = new StudentBatch();
                sb.setBoardName(meta.get("BOARD NAME"));
                sb.setContactInfo(meta.get("CONTACT INFO"));
                sb.setCreateTs(new Date());
                sb.setFilename(event.getFile().getFileName());
                sb.setNotes(meta.get("NOTES"));
                sb.setSchoolboard(meta.get("SCHOOL BOARD"));
                _logger.debug("persisting batch");
                em.persist(sb);
                _logger.debug("flushing");
                em.flush();
                _logger.debug("flushed");

但是，Eclipselink将id作为null传递给derby，而Derby给出错误试图修改标识列:

Eclipselink, however, passes the id to derby as null and Derby gives error Attempt to modify an identity column:

INFO: DEBUG  11710 27 Nov 2012 18:17:10,558 [http-thread-pool-8080(4)] (FileUploadController.java:75) - persisting batch
INFO: DEBUG  11713 27 Nov 2012 18:17:10,561 [http-thread-pool-8080(4)] (FileUploadController.java:77) - flushing
WARNING: Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'ID'. 
Error Code: -1
Call: INSERT INTO STUDENT_BATCH (ID, BOARD_NAME, CONTACT_INFO, CREATE_TS, FILENAME, NOTES, SCHOOLBOARD) VALUES (?, ?, ?, ?, ?, ?, ?)
bind => [7 parameters bound]
Query: InsertObjectQuery(ca.ontario.mhltc.studentupload.model.StudentBatch[ id=null ])
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

这对我来说很有意义，因为如果我尝试在具有指定id字段的sql命令行上执行插入操作，我也会收到错误消息.

This makes some sense to me since if I try to execute an insert on sql command line with the id field specified I get an error too.

INSERT INTO STUDENT_BATCH (id, BOARD_NAME, CONTACT_INFO, CREATE_TS, FILENAME, NOTES, SCHOOLBOARD)
VALUES (null, 'abc', 'def', current_timestamp, 'aaa', 'aabb', '1234');

给我这个:

Error code -1, SQL state 42Z23: Attempt to modify an identity column 'ID'. 
Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

但是，此插入语句成功完成:

however, this insert statement is successful:

INSERT INTO STUDENT_BATCH (BOARD_NAME, CONTACT_INFO, CREATE_TS, FILENAME, NOTES, SCHOOLBOARD)
VALUES ('abc', 'def', current_timestamp, 'aaa', 'aabb', '1234');

返回

Executed successfully in 0.002 s, 1 rows affected.
Line 1, column 1

Execution finished after 0.002 s, 0 error(s) occurred.

在我看来，Eclipselink应该了解Derby如何处理标识列，并且根本不应该在insert语句上传递id列.是否有解决方法，还是我应该转储Derby并使用其他数据库?

Seems to me that Eclipselink should know about how Derby handles identity columns and should not pass the id column at all on the insert statement. Is there a workaround for this or I should I just dump Derby and use some other database?

推荐答案

EclipseLink在INSERT中没有包含Derby IDENTITY的ID，因此您的工作很奇怪.

EclipseLink does not include the Id in the INSERT for Derby IDENTITY, so you have something odd going on.

您以前是否使用过另一种生成器策略，并且没有正确地重新编译/部署代码?

Did you previously use another generator strategy and not recompile/deploy your code correctly?

还尝试使用persistence.xml中的"eclipselink.target-database" ="Derby"设置平台.

Also try setting your platform using the"eclipselink.target-database"="Derby" in your persistence.xml.

这篇关于使用生成的主键插入Derby表时，Eclipselink JPA出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！