How to update @OneToMany collections?

[Miltiadis Orfanos]

Hello, I believe we've hit a breaking change when updating from 11.39.1 to 12.16.0. We are unsure how to proceed.

Below is an example reflecting our use case. We are attempting to update the `roles` assigned to a `user`.

Small note: We weren't using `orphanRemoval=true` in 11.39 but I read several threads mentioning a breaking change. I think it's required. We're running on sql8.

```

@Entity

class User {

  @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)

  private List<Role> roles;

  public List<Role> getRoles() {

    return this.roles;

  }

  public void setRoles(List<Role> roles) {

    this.roles = roles;

  }

}

@Entity

class Role {

  @ManyToOne

  private User user;

  public User getUser() {

    return this.user;

  }

  public void setUser(User user) {

    this.user = user;

  }

}

public void updateRoles() {

  // fetch existing user

  User user = database.find(User.class, id);

  // create new roles

  List<Role> roles = user.getRoles().stream()

    .filter(...) // take a subset

    .toList();

  List<Role> rolesToAdd = Arrays.asList(buildRoles());

  rolesToAdd.addAll(roles);

  // update user

  user.setRoles(rolesToAdd);

  database.update(user);

  // later, fetch user roles

  User fetchedUser = database.find(user);

  List<Role> fetchedRoles = fetchedUser.getRoles();

}

```

We expect `fetchedRoles` to contain the same objects as `rolesToAdd`. In 11.39 (without the orphan flag), the roles are updated as expected but not in 12.16.0.

Here are the SQL logs before the update

```

Begin

delete from roles where role_id = ? and not ( id in (?) )

delete from roles where role_id = ? and not ( id in (?) )

-- bind(c31e5fef-d9c5-4a05-a396-59910c270ce5, Array[1]={ee753d46-32ad-44a2-98a4-43761aa40cb9})

-- bind(c31e5fef-d9c5-4a05-a396-59910c270ce5, Array[1]={ee753d46-32ad-44a2-98a4-43761aa40cb9})

BatchControl flush [Role:101 i:1]

insert into roles (id, ...) values (?,?,?,?,?,?,?)

insert into roles (id, ...) values (?,?,?,?,?,?,?)

-- bind(c7784826-6514-4e6b-b09c-dbc5b9252e73,...)

-- bind(c7784826-6514-4e6b-b09c-dbc5b9252e73,...)

Inserted [Role] [c7784826-6514-4e6b-b09c-dbc5b9252e73]

```

after the update

```

Begin

BatchControl flush [Role:100 i:1]

insert into roles (id, ...) values (?,?,?,?,?,?,?)

insert into roles (id, ...) values (?,?,?,?,?,?,?)

-- bind(16e9495d-e94e-4083-8db5-f8f72dc881ad,...)

-- bind(16e9495d-e94e-4083-8db5-f8f72dc881ad,...)

Inserted [Role] [16e9495d-e94e-4083-8db5-f8f72dc881ad]

```

after the update with the orphan flag

```

Begin

delete from roles where role_od=?

delete from roles where role_od=?

-- bind(c31e5fef-d9c5-4a05-a396-59910c270ce5)

-- bind(c31e5fef-d9c5-4a05-a396-59910c270ce5)

BatchControl flush [Role:100 i:1]

insert into roles (id, ...) values (?,?,?,?,?,?,?)

insert into roles (id, ...) values (?,?,?,?,?,?,?)

-- bind(56683052-4f04-4c28-8269-ce9c29a2651b,password,1q2w,false,null,null,c31e5fef-d9c5-4a05-a396-59910c270ce5)

-- bind(56683052-4f04-4c28-8269-ce9c29a2651b,password,1q2w,false,null,null,c31e5fef-d9c5-4a05-a396-59910c270ce5)

Inserted [Role] [56683052-4f04-4c28-8269-ce9c29a2651b]

```

Do we need to update our application code or is this a bug?

Thanks,

[Miltiadis Orfanos]

FWIW updating the setter as shown below "works" but the SQL query executed is different from any of those shown above.

changes

```

public void setRoles(List<Role> roles) {

    this.roles.clear();

    this.roles.addAll(roles);

  }

```

SQL logs

```

Begin

BatchControl flush [Role:0 d:2, Role:100 i:3]

delete from roles where id=?

-- bind(52d0a164-cb6e-4b0d-8060-a4f76594aef1)

-- bind(ee753d46-32ad-44a2-98a4-43761aa40cb9)

Deleted [Role] [52d0a164-cb6e-4b0d-8060-a4f76594aef1]

Deleted [Role] [ee753d46-32ad-44a2-98a4-43761aa40cb9]

insert into roles (id, ...) values (?,?,?,?,?,?,?)

-- bind(3ad64255-720d-4e92-97a0-701de3c4cff5,...)

-- bind(f7375ac7-31b9-43a7-91eb-bcd2aa842852,...)

-- bind(2c1ce0b3-8c7c-42e9-a2af-0f4cbe8dd072,...)

Inserted [Role] [3ad64255-720d-4e92-97a0-701de3c4cff5]

Inserted [Role] [f7375ac7-31b9-43a7-91eb-bcd2aa842852]

Inserted [Role] [2c1ce0b3-8c7c-42e9-a2af-0f4cbe8dd072]

```

[Miltiadis Orfanos]

The logs in my last message should be the following, sorry for the confusion

```

16:26:22.842 [main] TRACE io.ebean.TXN - txn[1003] Begin
16:26:22.856 [main] DEBUG io.ebean.SUM - txn[1003] BatchControl flush [Role:0 d:1, Role:100 i:1]
16:26:22.861 [main] DEBUG io.ebean.SQL - txn[1003] delete from roles where id=?
16:26:22.861 [main] DEBUG io.ebean.SQL - txn[1003]  -- bind(52d0a164-cb6e-4b0d-8060-a4f76594aef1)
16:26:22.869 [main] DEBUG io.ebean.SUM - txn[1003] Deleted [Role] [52d0a164-cb6e-4b0d-8060-a4f76594aef1]
16:26:22.878 [main] DEBUG io.ebean.SQL - txn[1003] insert into roles (id, ...) values (?,?,?,?,?,?,?)
16:26:22.878 [main] DEBUG io.ebean.SQL - txn[1003]  -- bind(b1decec5-e3ff-44d4-bc8f-5137870a3d3a,...)
16:26:22.886 [main] DEBUG io.ebean.SUM - txn[1003] Inserted [Role] [b1decec5-e3ff-44d4-bc8f-5137870a3d3a]

```

[Rob Bygrave]

Do you want to fork example-minimal  - https://github.com/ebean-orm-examples/example-minimal ... and add a failing test case? Then I could pull that fork and look.

It looks from the sql log that only 1 insert occurs so that does not look right.  Have you seen if this issue reproduces in the latest version - 13.6.0? If we have a failing test we can check it against both 12.16.0 and 13.6.0.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/14287dfb-86de-44e7-981f-46516e536483n%40googlegroups.com.

[Miltiadis Orfanos]

Hello, I was able to reproduce the error with some changes to the config (no docker, mysql8).

Please take a look https://github.com/ebean-orm-examples/example-minimal/pull/8

Thanks,

[Rob Bygrave]

Nice work with the test case - thanks !!

Just adding in a link to the PR with Fix and test case - https://github.com/ebean-orm/ebean/pull/2690

Will be fixed in 13.6.1.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/845c399a-097d-4577-8f2a-e7ed00220259n%40googlegroups.com.