Questions about Managing Bidirectional Relationships with Ebean

[raphaelNguyen]

Hi all. I am hoping for some inputs regarding best practices when modeling and working with relationships in ebean. Thank you for taking the time to look into this.

Please consider the following case.

• I have a One-to-Many/Many-to-One relationship between Order table (One) and OrderItem table (Many) where each OrderItem record has a foreign key to an Order record.
• In the application, I need to
  • Update/delete Order.
  • Update/delete OrderItem, including moving an item from one Order to another.
  • Inspecting the in-memory state of an Order, i.e. what OrderItems are in the Order at any time. I would like the state to reflect even the changes to OrderItems that has not yet been saved back to databases.

From my understanding, the mapped classes should look roughly as seen below.

• Can you please advise if this looks correct?
• How does one keep Order.items in sync in memory when updating OrderItems without writing back to database and refreshing the beans? Is there any additional mappings I have to do to achieve this?

// setter getter ommitted for brevity

@Entity
public class Order {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private String id;

  @OneToMany(
     mappedBy = "order",
     fetch = FetchType.LAZY
  )
  private List<OrderItem> items;

  // other fields ommitted for brevity
}

@Entity
public class OrderItem {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private String id;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(
    name = "orderitemid",
   referencedColumnName = "id",
   nullable = false
  )
  private Order order;

  // other fields ommitted for brevity
}

[rbygrave]

@GeneratedValue(strategy = GenerationType.IDENTITY)

This is saying that you want to use IDENTITY regardless of whether it is the best choice for the database being used. The recommendation is to not do that but instead not specify a strategy (leaving it to AUTO) or not have any @GeneratedValue (and Ebean will assume the appropriate Sequence or Identity or UUID generation based on the id type.

@id String id;

Using String is not correct here for generated DB identity or sequence. It should instead be a long,Long or int,Integer. Prefer long and use int when you know that int is sufficiently large enough.

fetch = FetchType.LAZY

Remove these everywhere. The defaults for fetch are actually correct for Ebean, the recommendation is to not explicitly specify fetch and just leave the defaults.

@JoinColumn

The recommendation is to not specify @JoinColumn with the expectation that the column names match the naming convention. We only use @JoinColumn when we have an existing DB schema and the columns do not match the naming convention.

@JoinColumn(nullable = false)

Move the nullable = false to the ManyToOne so it becomes @ManyToOne(nullable = false)

---

With that, we note the above that on the @OneToMany there is:

• No cascade=PERSIST (or ALL)
• No orphanRemoval=true

I'd suggest that not using orphanRemoval is correct here because of the OrderItem, including moving an item from one Order to another.

Use of cascade=PERSIST comes down to the how the Order is inserted and updated. If the OrderItem's are mostly inserted and updated at the same time as the Order then it makes sense to use cascade=PERSIST | ALL.

---

How does one keep Order.items in sync in memory when updating OrderItems without writing back to database and refreshing the beans? Is there any additional mappings I have to do to achieve this?

I do not like this way of thinking at all.

When we fetch an Order back from the database, what we actually have is a SNAPSHOT of the Order AS AT a given time [where time is more transaction time and depends on transaction isolation level but typically it is the time that the query was started].

When we modify our Order setting some properties etc we are modifying our snapshot / version / copy of the Order in memory. When we save back to the database we are applying our changes to the database. We use optimistic locking to tell us when something else modified the database in the meantime. We use pessimistic locking to prevent something else from modifying the rows we want.

Our application side in-memory Order and OrderItem are always only going to represent a snapshot / copy of the data as at a given time [but generally this is all fine and ok].

The mental model I'd prefer is something like, there are 2 cases: A) Read only queries. B) Transaction where we update and can also read

A) Read only queries

• We are getting a snapshot [copy] of the Order, OrderItem as at now [as at query start time]
• We can hold onto this but understand this is a snapshot copy and could be out of date

B) Transaction where we update and can also read

• We only concern ourselves to the scope of the transaction
• We fetch Order, OrderItem, mutate them as needed
• We save() them back (with either optimistic locking or pessimistic locking)

We only need our in-memory state of Order, OrderItem to reflect the changes that we want to see when we save() them. We otherwise do not really care about their in-memory state beyond that.

---

Update/delete OrderItem, including moving an item from one Order to another.

This is the classic example where we ideally do: Fetch the OrderItem, change its Order, save the OrderItem ... to produce the update order_item set order_id.= ? where id = ? and version = ?

Rather than Fetch Order A with all its OrderItems, Fetch Order B with all its OrderItems, remove the OrderItem from the Order A items, add it to the Order B items, save Order A, save Order B. This approach is a bad approach because it does a whole lot of unnecessary work. We should not take this approach just because we want our in-memory model to be in sync with the database etc.

[raphaelNguyen]

Thanks @rbygrave for your prompt response. You've given me lots of food for thoughts. I will consider my design and possibly come back with other questions.