Adding the JPA @OrderColumn afterwards causes null index column for collection error

Question

Let's say, we have an @OneToMany relation for entities A and B. Each A can have many B.

@Entity
public class A{
  @Id
  private long id;
  ...
  @OneToMany(mappedBy="ref")
  private List<B> items;
  ...
}

@Entity
public class B{
  @Id
  private long id;

  @ManyToOne
  @JoinColumn(name = "A_ID")
  private A ref;
}

This application is in production for some time and the order of the Bs saved into db was not relevant till now.

However, now the exact order of the Bs is required. For this, I have added the @OrderColumn like below.

@OneToMany
@OrderColumn(name="B_ORDER")
private List<B> items;

It works fine and the B table has now the B_ORDER column with order values.

Now the production db needs to be updated. A new column B_ORDER has been added to the table B. However, for existing rows, this column has no value (null). When you try to retrieve a list of Bs for already existing record, I get the following exception:

org.hibernate.HibernateException: null index column for collection

It is Ok to have a list of Bs in any order for old records. @OrderColumn has the property "nullable" which is by default true. But it does not help.

Is there a JPA way for this or do I have to fill those null values in db myself?

Using JPA 2.1 with Hibernate 4.3.7.

Answer 1

The problem that you have is that the JPA provider, in your case Hibernate, need a contiguous (non-sparse) ordering of the values of the order column.

However, when you add a new column to a new database, the values will be null unless you have defined a default value of 0.

You might think that adding a default value will do the trick. Sorry, that is not the case. If you do so, your collection will always show max one item.

To solve that problem, you have to add each collection a sequential, non-sparse numbering.

In practice you should alter your entity temporary so that you can actually access the respective field:

@Entity  
public class B{  
@Id  
private long id;  

@ManyToOne  
@JoinColumn(name = "A_ID")  
private A ref; 

@Column(name = "B_ORDER")
private Integer order;
}

Now you can load all items. What you need to do next is to write a temporal method that is loading all items from the database an numbers each item per collection starting from 0 up to the length of the collection minus 1.

The above suggestion is just one way that I find convenient. There are many ways to solve it. You could even do it manually if you have enough time.

What you should take away from this answer is that the items of the collection need to be numbered starting with 0. Otherwise Hibernate wont load the items into your collection. You need to fulfill the constrains described here.