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.

did you find a better fix for this than the one mentioned. Ideally, it should order it by id or something or give random list on its own. — Aditya, Oct 04 '16 at 16:54
I could not try the solution given in the answer below, because it has arrived 4 months later :) (no complains, thanks a lot still). We have just filled those null values with a custom sql script. Have you tried the solution below? If so, please write your experience here. — ilhami visne, Apr 03 '17 at 15:42

score 3 · Answer 1 · answered Sep 25 '16 at 18:15

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.

Based on the above description, it may not be trivial for everyone : the numbering has to start from 0 for every A instance. In other words: in the database table for B, for every set of rows that points back to the same A instance, the numbering has to start from 0. — Istvan Devai, Jan 18 '19 at 12:40

Killesk · Answer 2 · 2022-08-27T13:06:40.047

Steps I took

I manually added the order column
Generated the values then updated the table
Updated table with generated values
Dropped Index
Created new index that includes image_order

I'm using postgres.

ALTER TABLE IF EXISTS test.blogposts_images
    ADD COLUMN images_order integer NOT NULL DEFAULT 0;


DO $$
DECLARE blogpostImage RECORD;
DECLARE currentBlogPostID INTEGER = -1;
DECLARE i INTEGER = -1;
BEGIN FOR blogpostImage IN
    SELECT * FROM test.blogposts_images order by blogpost_id, image_id
    LOOP
  
    if blogpostImage.blogpost_id <> currentBlogPostID then
        currentBlogPostID = blogpostImage.blogpost_id;
        i = 0;
    else
        i = i + 1;
    end if;
    RAISE NOTICE 'blogpostId[%] imageId[%] position[%]', blogpostImage.blogpost_id, blogpostImage.image_Id, i;
    UPDATE test.blogposts_images set images_order = i where blogpost_id = blogpostImage.blogpost_id and image_id = blogpostImage.image_Id;
   END LOOP;
END $$


ALTER TABLE test.blogposts_images ALTER COLUMN images_order DROP DEFAULT;

ALTER TABLE test.blogposts_images DROP CONSTRAINT idx_16386_primary;
ALTER TABLE test.blogposts_images ADD PRIMARY KEY (blogpost_id, image_id, images_order);

score 0 · Answer 3 · answered May 17 '21 at 17:06

I had a similar issue : hibernate did not seem to generate values in the order column, when I updated the list of children items on an existing parent. I fixed it by explicitly adding the order column on the child, like so (using your example) :

@Entity
public class B{  
  @Column(name = "B_ORDER")
  private int order;

  ...
}

If I failed to do so, creating A records with B children would work fine, but updating A records by adding B children would fail with null index column for collection: ... because hibernate wouldn't generate values for the B_ORDER column.

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

3 Answers3