Dissociation Operations

An important concept is involved when saving associated objects: dissociation operations.

Concept

Consider the following example:

Existing data structure in database	Data structure user wants to save
`+-Food \| \| \| +-----Meat(Ignore child nodes) \| \-----Bread(Ignore child nodes)`	`+-Food \| +-----Drinks(Ignore child nodes) \| \| \| \-----Bread(Ignore child nodes)`

For Bread, it exists in both old and new data structures, corresponding to update operation
For Drinks, it does not exist in the old data structure, but exists in the new data, corresponding to INSERT operation
For Meat, it exists in the old data structure, but does not exist in the new data, the corresponding operation is called dissociation operation.

Dissociation applies to two types of data:

Middle table data
Child table data

Next, we discuss these two dissociation operations.

info

To simplify the generated SQL as much as possible, let us focus on the association itself and dissociation operations in this article:

All saved objects specify only the id property (short association)
The save mode of the aggregated root object is explicitly specified as UPDATE_ONLY (This article uses the syntax of calling the update method)

Dissociating Middle Table Data

There is a many-to-many association between the BOOK table and the AUTHOR table, with middle table BOOK_AUTHOR_MAPPING.

The Book.authors and Author.books properties both map this many-to-many association and are mirrors of each other. Using either can demonstrate the effect. Here we choose Book.authors.

First query the database to see which Author objects id=3 Book corresponds to:

select *  
from book_author_mapping
where book_id = 3;

The query result is:

book_id	author_id
3	1
3	2

Now update this Book object:

Java
Kotlin

sqlClient.update(
    Immutables.createBook(draft -> {
        draft.setId(3L);
        draft.addIntoAuthors(author -> author.setId(2L));
        draft.addIntoAuthors(author -> author.setId(5L));
    })
);

sqlClient.update(
    Book {
        id = 3L
        authors().addBy { id = 2L } 
        authors().addBy { id = 3L }
    }
)

The comparison of old and new data structures is:

Existing data structure in database	Data structure user wants to save
`+-Book(id=3) \| +-----Author(id=1) \| \-----Author(id=2)`	`+-Book(id=3) \| \| \| +-----Author(id=2) \| \-----Author(id=5)`

This means:

The association between Book-3 and Author-2 remains unchanged
The association between Book-3 and Author-5 needs to be created
The association between Book-3 and Author-1 needs to be deleted, which is the dissociation operation

Finally 3 SQL statements are generated:

Query which Author objects Book-3 corresponds to

select 
    AUTHOR_ID  
from BOOK_AUTHOR_MAPPING 
where
    BOOK_ID = ? /* 3 */

Dissociation operation, detach association between Book-3 and Author-1

/* highlight-next-line */
delete from BOOK_AUTHOR_MAPPING  
where
    (BOOK_ID, AUTHOR_ID) in (
        (? /* 3 */, ? /* 1 */)
    )

info

This is the deassociation operation we want on the middle table

Create association between Book-3 and Author-5

insert into BOOK_AUTHOR_MAPPING(
    BOOK_ID, AUTHOR_ID 
)
values
    (? /* 3 */, ? /* 5 */)

Dissociating Child Table Data

This operation is for associations directly based on foreign keys.

The BOOK table has a foreign key field STORE_ID pointing to the BOOK_STORE table, forming a many-to-one association. This many-to-one association is mapped to the Book.store property, and the reverse one-to-many association is mapped to the BookStore.books property.

If a parent object in the database (BookStore in this example) holds some child objects (Book in this example), but the overwritten parent object is no longer expected to continue holding some child objects, it will cause those child objects to be dissociated.

Dissociation Modes

There are 5 modes for dissociating child objects

Name	Description
NONE (default)	Depend on the global configuration jimmer.default-dissociate-action-checking. If jimmer.default-dissociate-action-checking is true (default) or the foreign key underlying the current association is real (there is a corresponding foreign key constraint in the database, please refer to Real and Fake Foreign Keys), it is treated as CHECK. If jimmer.default-dissociate-action-checking is false and the foreign key underlying the current association is fake (there is no corresponding foreign key constraint in the database, please refer to Real and Fake Foreign Keys), it is treated as LAX.
LAX	This option is only valid for pseudo foreign keys (please refer to Real and Fake Foreign Keys), otherwise it will be ignored, the same as CHECK. Dissociation operations are supported even if there are child objects. Even if the parent object is deleted (dissociation mode is also adopted by delete commands), dangling pseudo foreign keys of child objects are allowed (even if pseudo foreign keys are left dangling, the query system can still work normally).
CHECK	If there are child objects, disassociation is not supported, the operation is prevented by throwing an exception.
SET_NULL	Set the foreign key of the disassociated child object to null. The prerequisite is that the many-to-one associated property of the child object is nullable; otherwise, attempting this configuration will lead to an exception.
DELETE	Delete the disassociated child objects.

info

Although child object dissociation is caused by a one-to-many association (i.e. parent object abandoning some child objects, the one-to-many association is BookStore.books in this example), the dissociation mode is configured for the reverse many-to-one association (Book.store in this example). The purpose of this design is to maintain similarity with configuring cascade behaviors of foreign keys in database DDL.

For Jimmer, a one-to-many association must be bidirectional, so once a one-to-many association is known, its mirroring many-to-one association must also be known. So there is no problem with this design.

There are two ways to configure the dissociation mode:

Static configuration on entity with annotation, static configuration is global.
Dynamic configuration in code, dynamic configuration only affects the current save command, and it can override static configuration.

Take SET_NULL as an example:

Static configuration (default configuration, for the vast majority of business uses):

Java
Kotlin

Book.java
@Entity
public interface Book {

    @OnDissociate(DissociateAction.SET_NULL)  
    @Nullable
    @ManyToOne
    BookStore store();

    ...other code omitted...
}

Book.kt
@Entity
interface Book {

    @OnDissociate(DissociateAction.SET_NULL)
    @ManyToOne  
    val store: BookStore?

    ...other code omitted...       
}

Runtime override (only for a single save command, used by very few business cases with special needs):

Java
Kotlin

sqlClient
    .getEntities()
    .saveCommand(book) 
    .setDissociateAction(BookProps.STORE, DissociateAction.SET_NULL)
    .execute();

sqlClient.save(book) {
    .setDissociateAction(Book::store, DissociateAction.SET_NULL) 
}

Example

First, query the database to see which Book objects id=2 BookStore holds:

select *
from BOOK  
where STORE_ID = 2

The query result is:

ID	NAME	EDITION	PRICE	STORE_ID
12	GraphQL in Action	3	80.00	2
11	GraphQL in Action	2	81.00	2
10	GraphQL in Action	1	80.00	2

As you can see, BookStore-2 holds Book-10, Book-11, and Book-12.

Now, update this BookStore object:

Java
Kotlin

sqlClient.update(
    Immutables.createBookStore(draft -> {
        draft.setId(2L);
        draft.addIntoBooks(book -> book.setId(9L));
        draft.addIntoBooks(book -> book.setId(10L)); 
    })
);

sqlClient.update(
    BookStore {
        id = 2L
        books().addBy { id = 9L }
        books().addBy { id = 10L }
    }
)

The comparison of old and new data structures is:

Existing data structure in database	Data structure user wants to save
`+-BookStore(id=2) \| +-----Book(id=10) \| +-----Book(id=11) \| \-----Book(id=12)`	`+-BookStore(id=2) \| +-----Book(id=10) \| \| \| \| \| \-----Book(id=9)`

This means:

The association between BookStore-2 and Book-10 remains unchanged
The association between BookStore-2 and Book-9 needs to be created
BookStore-2 needs to dissociate Book-11 and Book-12.

However, the SQL operations determined by the dissociation mode of the configuration of Book.store property is unknown.

Different dissociation mode configurations will lead to different execution logic. Next, we discuss the three cases of NONE, SET_NULL and DELETE.

NONE (default):

NONE means dissociation is not supported, the operation is prevented by throwing an exception which causes transaction rollback. So the above code will throw an exception.

The exception type is org.babyfish.jimmer.sql.runtime.SaveException.

The exception message is:

Save error caused by the path: "<root>.books": Cannot dissociate child objects because the dissociation action of the many-to-one property "com.yourcompany.yourproject.model.Book.store" is not configured as "set null" or "cascade". There are two ways to resolve this issue: Decorate the many-to-one property "com.yourcompany.yourproject.model.Book.store" by @org.babyfish.jimmer.sql.OnDissociate whose argument is DissociateAction.SET_NULL or DissociateAction.DELETE , or use save command's runtime configuration to override it
SET_NULL:

In this mode, two SQL statements are generated:
1. Associate BookStore-2 with Book-9 and Book-10. For Book-10 this is effectively no change.
```
update BOOK  
set
    STORE_ID = ? /* 2 */ 
where
    ID in (
        ? /* 9 */, ? /* 10 */
    )
```
2. Set foreign key of all books belonging to BookStore-2 whose id is not 9 or 10 (Book-11 and ``Book-11` in this example) to empty.
```
update BOOK
set
    STORE_ID = null
where 
    STORE_ID = ? /* 2 */
    and 
    ID not in ( /* Note `not` */
        ? /* 9 */, ? /* 10 */
    )
```
  info
  Note the not in SQL, this is the deassociation operation on the child table for the SET_NULL mode.
DELETE:

In this mode, 4 SQL statements are generated:
1. Associate BookStore-2 with Book-9 and Book-10. For Book-10 this is effectively no change.
```
update BOOK
set
    STORE_ID = ? /* 2 */
where
    ID in (
        ? /* 9 */, ? /* 10 */  
    )
```
2. Query all books belonging to BookStore-2 whose id is not 9 or 10. For this example, the query result is Book-11 and ``Book-12`.
```
select
    ID
from BOOK
where
    STORE_ID = ? /* 2 */
    and
    ID not in (
        ? /* 9 */, ? /* 10 */
    ) 
```
  note
  Note the not in the SQL
3. Before deleting Book-11 and Book-12, first clean up their many-to-many associations with Author
```
delete from BOOK_AUTHOR_MAPPING
where
    BOOK_ID in (
        ? /* 11 */, ? /* 12 */
    )
```
  info
  If Book type has more other associations, Jimmer will clean them all up properly. It just means more SQL statements like this step.
4. Finally, safely delete Book-11 and Book-12 to complete the dissociation operation
```
delete from BOOK
where
    ID in (
        ? /* 11 */, ? /* 12 */
    )
```
tip
Step 3 and step 4 are a whole, they jointly form the deassociation operation.
In fact, this is the Delete Command that will be introduced in subsequent documentation.

info

This article simplifies the examples a lot, such as:

Explicitly specifying processing mode of aggregated root object as UPDATE_ONLY (calling update instead of save)
All associated objects are short associated objects with only id. In fact, you can arbitrarily change the format of associated objects, for example:
- Do not specify id property, but specify key property ( expressed in another way)
- Specify key property and some properties that are neither id nor key ()

The examples are so simplified in this article just to keep the SQL generated by the save command as simple as possible for readers to quickly understand the characteristics of dissociation operations.

As for associated objects of more complex formats, readers can try it themselves, or refer directly to the official examples jimmer-examples/java/save-command and jimmer-examples/kotlin/save-command-kt. These functionalities still exist, just with more generated SQL statements and more tedious hidden details.

Concept​

Dissociating Middle Table Data​

Dissociating Child Table Data​

Dissociation Modes​

Example​

Concept

Dissociating Middle Table Data

Dissociating Child Table Data

Dissociation Modes

Example