jpa - Eclipselink JPA 在使用生成的主键插入 Derby 表时出错

Question

在持久化到具有生成的主键的表中时，EclipseLink 似乎错误地将空主键值传递给 Derby。在这种情况下，Derby 返回错误 Attempt to modify an identity column。 Derby 需要排除 id 值的 SQL 语句。我的问题是如何强制 EclipseLink 发送正确的 SQL？详情如下...

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

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

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类指定使用identity策略生成id:

@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。

                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 给出错误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 命令行上执行插入，我也会收到错误消息。

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.

但是，这个插入语句是成功的:

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 如何处理标识列，并且根本不应该在插入语句中传递 id 列。是否有解决方法，或者我应该转储 Derby 并使用其他数据库？

Answer 1

EclipseLink 不在 Derby IDENTITY 的 INSERT 中包含 Id，因此您遇到了一些奇怪的事情。

您之前是否使用过另一种生成器策略而没有正确地重新编译/部署您的代码？

也可以尝试在您的 persistence.xml 中使用“eclipselink.target-database”="Derby"来设置您的平台。