[FIXED] JPA - Adding and Removing elements in List with ElementCollection

Issue

I keep a list of Strings in SQL as @ElementCollection, my main usage is similar to a Queue:

1. fetch it from the database
2. run some calculations
3. remove the first element and insert a new element to the end of the list:

this.measurements.remove(0); this.measurements.add(measurement);

4. save it to the database

The problem with the current implementation is that removing the first element updates the index of all elements in the list (items_order column) which is very bad for performance... Is there any other way to do this? perhaps is it possible to have a running index (items_order column) so order will be kept but not position in list?

Example:
left to ':' is the index of the element and to the right is the element itself.
[0:"a", 1:"b", 2:"c"]

After Calculation the following will be inserted to the database:

[0:"b", 1:"c", 2:"d"] - all element indexes have been updated (bad performance)

Possible solution which i'm not sure if possible to implement:

[1:"b", 2:"c", 3:"d"] - all elements keep the same order id (still ordered)

My entity:

@Entity
public class Store {
  @ElementCollection
  @OrderColumn
  private List<String> items;

  // setter getter
}

Solution

What I ended up doing was creating an entity of my own instead of using annotations to create the fields table, this is the Store entity:

@Entity
public class Store {
  @OneToMany(cascade=CascadeType.ALL, orphanRemoval=true)
  @OrderBy("index ASC")
  private List<Item> itemsList;

  @Column
  private int index = 0;

  public List<Item> getItemsList() {
      return itemsList;
  }

  public void setItemsList(List<Item> itemsList) {
      this.itemsList = itemsList;
  }

  public void addToItemList(String item) {
      final Item newItem = new Item();
      newItem.setStoreId(this.id);
      newItem.setValue(item);
      newItem.setIndex(index++);
      this.itemsList.add(newItem);
  }
}

this is the Item entity which the store will hold a list of:

@Entity
@IdClass(Ids.class)
public class Item {

  @Id
  private long storeId;

  @Id
  private int index;

  @Column
  private String value;

  public long getStoreId() {
      return storeId;
  }

  public void setStoreId(long storeId) {
      this.storeId = storeId;
  }

  public int getIndex() {
      return index;
  }

  public void setIndex(int index) {
      this.index = index;
  }

  public String getValue() {
      return value;
  }

  public void setValue(String value) {
      this.value = value;
  }
}

class Ids implements Serializable { // each id will be composed of storeId + index
    long storeId;
    int index;
}

Answered By - Uri Loya