Why is JPA ignoring my @OrderColumn in a basic use case?

Question

Inspection Entity:

@Entity
@Table(name="INSPECTION")
public class Inspection implements Serializable
{
    ...
    @OneToMany(cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE}, orphanRemoval=true)
    @OrderColumn(name="LIST_INDEX", nullable=false)
    @JoinColumn(name="INSPECTION_ID")
    private List<RecommendationInstance> recommendations;
    ...
}

RecommendationInstance Entity

@Entity
@Table(name = "RECOMMENDATION_INSTANCE")
public class RecommendationInstance implements Serializable
{
    @SequenceGenerator(name="RECOMMENDATION_INST_SEQ_GEN", sequenceName="RECOMMENDATION_INST_SEQ", allocationSize=1, initialValue=100)
    @Id @GeneratedValue(generator="RECOMMENDATION_INST_SEQ_GEN", strategy=GenerationType.SEQUENCE)
    private Long id;
    @Column(name="INSPECTION_ID")
    private Long inspectionId;
    @ManyToOne
    @JoinColumn(name="RECOMMENDATION_ID")
    private Recommendation recommendation;
    @Column(name="DESCRIPTION")
    private String description;
    ...
}

And the table is created as follows:

  CREATE TABLE "RECOMMENDATION_INSTANCE" 
   (    "ID" NUMBER(19,0) NOT NULL,
    "INSPECTION_ID" NUMBER(19,0) NOT NULL,
    "RECOMMENDATION_ID" NUMBER(19,0) NOT NULL,
    "DESCRIPTION" VARCHAR2(4000 BYTE) NOT NULL,
    "LIST_INDEX" NUMBER(4,0) NOT NULL
   ) ;

When a new RecommendationInstance is created and I attempt to save the InspectionEntity I get the following error:

Caused by: org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: NOT NULL check constraint; SYS_CT_10161 table: "RECOMMENDATION_INSTANCE" column: "LIST_INDEX"
Error Code: -10
Call: INSERT INTO RECOMMENDATION_INSTANCE (ID, DESCRIPTION, INSPECTION_ID, RECOMMENDATION_ID) VALUES (?, ?, ?, ?)
    bind => [102, Sprinkler System DESCRIPTION, 110, 40]

Am I missing some relationship here? It looks as though the list_index is being ignored completely.

To give further information, if needed, I did have this working using a join table. However I am doing a refactor since the join table is not needed. This moved the LIST_INDEX column from the join table to the RecommendationInstance table.

Looks like you don't have any list_index property in your RecommendationInstance entity, unless its in the ... part. So no wonder the persistence provider 'ignores' it. — Gimby, May 08 '14 at 15:15
The documentation at: http://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/OrderColumns states: The OrderColumn is maintained by the mapping and should not be an attribute of the target object. — Noremac, May 08 '14 at 15:17
I see (and also in the API documentation, not only the EclipseLink docs), pardon my ignorance. I have no further suggestions other than "you're using an up to date version of EclipseLink right?", so I'll just slither back into the shadows. — Gimby, May 08 '14 at 15:23
Yep, version 2.5.1. If you get any other ideas be sure to come back out of the shadows :) — Noremac, May 08 '14 at 15:53
The order columns is set only if you save the RecommendationInstance within your list, not if stored standalone. — kostja, May 09 '14 at 06:18
Hm well if you look at the example in the EclipseLink documentation, you'll see that it is a broken example; apparently the intent was that this was through a join table where the order column is in the join table, yet the entities show no sign of that being mapped as such. You could try if you can get it to work with a join table. — Gimby, May 09 '14 at 07:27

score 0 · Answer 1 · answered May 08 '14 at 15:55

0

I have done this before but using the @OrderBy annotation, for instance, an piece of code I wrote recently:

@OneToMany(mappedBy = "product")
@OrderBy("createdDateTime ASC")
private Collection<SkuUpc> skuUpcs;

Where SkuUpc has a fied

@Column(name = "created_dt")
private Date createdDateTime = new Timestamp(new Date().getTime());

answered May 08 '14 at 15:55

Edwin Dalorzo

76,803
25
144
205

Unfortunately it is a user-defined order, so there is no systematic ordering such as ASC that I can apply. I need to preserve the order given in the list when created/updated. – Noremac May 08 '14 at 15:58

score 0 · Accepted Answer · edited May 23 '17 at 12:21

0

I found that when I removed the NOT NULL constraint then everything worked (duh), but I decided I can deal with that for now. Looking at the logs, JPA first inserts the row without the list_index (thus the constraint violation) then immediately after runs an update to set the list_index.

This answer really creates a more specific question as to why it doesn't set the list_index upon insertion of the row, even when I specify nullable=false

I asked the more specific question here: Why does JPA update the OrderColumn instead of setting it on creation?

edited May 23 '17 at 12:21

Community

1
1

answered May 09 '14 at 14:26

Noremac

3,445
5
34
62

1

It is because your RecommendationInstance entity is a separate and independent entity that does not have a mapping for the order column. Eclipselink is designed to create inserts only from the entity itself. There is a feature request to have eclipselink store statements and add to them as it processes other mappings, I just couldn't find the link on my phone. – Chris May 09 '14 at 20:36
Would you mind putting that answer on the referenced question? – Noremac May 09 '14 at 21:55

Why is JPA ignoring my @OrderColumn in a basic use case?

2 Answers2

Linked