Associated Save Mode

Basic Concepts

In the previous article, we introduced how to control the save mode of aggregate root objects.

This article will discuss how to control the save mode of associated objects. Associated objects support the following save modes:

Association Save Mode	Applicable Scope	Accept Wild Objects	Description
APPEND	All associations	Yes	Unconditionally perform INSERT operation on associated objects
APPEND_IF_ABSENT	All associations	No	First check if the associated object exists in the database If the id property of the associated object is specified, check existence by id Otherwise, check existence by key Execute subsequent operations based on the check result If the associated object already exists, ignore the operation and do nothing Otherwise, insert the associated object note Jimmer will try to utilize the database's native UPSERT capability to combine these two steps. If not possible, it will inform the user why
UPDATE	All associations	Yes	If the id property of the associated object is specified, update the associated object by id Otherwise, update the associated object by key
MERGE	All associations	No	First check if the associated object exists in the database If the id property of the associated object is specified, check existence by id Otherwise, check existence by key Execute subsequent operations based on the check result If the associated object already exists, update it Otherwise, insert the associated object note Jimmer will try to utilize the database's native UPSERT capability to combine these two steps. If not possible, it will inform the user why
REPLACE	Post associations	No	Based on MERGE, perform dissociate operation on no longer needed associated objects
VIOLENTLY_REPLACE	Post associations	Yes	Make Jimmer no longer search for changes in associations (or association collections) by id or key, but execute the following two operations First delete all old associations and related objects of the current object Then reinsert all associated objects and rebuild associations with the current object This is a very aggressive association update approach with the following pros and cons Pros: Accepts wild associated objects, no longer requires associated objects to either specify id or key Cons: If there are many associated objects, in most cases, this is a low-performance solution If associated objects have deeper associations, the deletion in step 1 may cause too deep cascade deletion, leading to loss of too much data

Methods with Specified Association Mode

You can set the AssociatedSaveMode for save commands in two ways

• Set parameters for the save method (this is the simpler choice)

• Call configuration methods on the save command (this is the more powerful choice)

info

Jimmer's API design only allows users to choose one of these methods, preventing confusion from having both methods appear simultaneously

Setting Save Method Parameters

Many save methods support this, for example

For save, saveEntities and saveInputs, the default AssociatedSaveMode is REPLACE. Now, let's modify it to MERGE.

• save

  Java

  Book book = ...omitted...;
  sqlClient.save(book, AssociatedSaveMode.MERGE);

  Kotlin

  val book = Book { ...omitted... }
  sqlCient.save(book, AssociatedSaveMode.MERGE);

• saveEntities

  Java

  List<Book> books = ...omitted...;
  sqlClient.saveEntities(books, AssociatedSaveMode.MERGE);

  Kotlin

  val books = listOf<Book>(...omitted...)
  sqlCient.saveEntities(books, AssociatedSaveMode.MERGE);

• saveInputs

  Java

  List<BookInput> inputs = ...omitted...;
  sqlClient.saveInputs(inputs, AssociatedSaveMode.MERGE);

  Kotlin

  val inputs = listOf<BookInput>(...omitted...)
  sqlCient.saveInputs(inputs, AssociatedSaveMode.MERGE);

  Here, the BookInput class is not an entity type, but rather an automatically generated Input DTO type by Jimmer.

  This related content hasn't been introduced before, please ignore it for now.

Besides save, saveEntities and saveInputs, Jimmer has other shortcut methods, such as

• insert, insertEntities, insertInputs
• insertIfAbsent, mergeEntitiesIfAbsent, mergeInputsIfAbsent
• update, updateEntities, updateInputs
• merge, mergeEntities, mergeInputs

Taking insert, insertEntities and insertInputs as examples, the default AssociatedSaveMode is APPEND. Now, let's modify it to MERGE.

• insert

  Java

  Book book = ...omitted...;
  sqlClient.insert(book, AssociatedSaveMode.MERGE);

  Kotlin

  val book = Book { ...omitted... }
  sqlCient.insert(book, AssociatedSaveMode.MERGE);

• insertEntities

  Java

  List<Book> books = ...omitted...;
  sqlClient.insertEntities(books, AssociatedSaveMode.MERGE);

  Kotlin

  val books = listOf<Book>(...omitted...)
  sqlCient.insertEntities(books, AssociatedSaveMode.MERGE);

• insertInputs

  Java

  List<BookInput> inputs = ...omitted...;
  sqlClient.insertInputs(inputs, AssociatedSaveMode.MERGE);

  Kotlin

  val inputs = listOf<BookInput>(...omitted...)
  sqlCient.insertInputs(inputs, AssociatedSaveMode.MERGE);

Calling Save Instruction Configuration Methods

Besides setting save method parameters, we can also modify AssociatedSaveMode by calling configuration methods on the save command.

• Precisely set AssociatedSaveMode for specific associations

  Java

  Book book = ...omitted...;
  sqlClient
      .saveCommand(book)
      .setAssociatedMode(
          BookProps.AUTHORS, 
          AssociatedSaveMode.MERGE
      )
      .execute();

  Kotlin

  val book = Book { ...omitted... }
  sqlCient.save(book) {
      setAssociatedMode(
          Book::authors,
          AssociatedSaveMode.MERGE
      )
  }

  • Java

    Calls the saveCommand method instead of save method, indicating creation of a save command without immediate execution.

    After configuration with setAssociatedMode, finally calls the execute method to actually execute.

  • Kotlin

    The syntax is relatively concise, still calling the save method that can directly execute save commands, just with a lambda for additional configuration.

• Blindly set AssociatedSaveMode for all associations

  Java

  Book book = ...omitted...;
  sqlClient
      .saveCommand(book)
      .setAssociatedModeAll(
          AssociatedSaveMode.MERGE
      )
      .execute();

  Kotlin

  val book = Book { ...omitted... }
  sqlCient.save(book) {
      setAssociatedModeAll(
          AssociatedSaveMode.MERGE
      )
  }

  • Java

    Calls the saveCommand method instead of save method, indicating creation of a save command without immediate execution.

    After configuration with setAssociatedModeAll, finally calls the execute method to actually execute.

  • Kotlin

    The syntax is relatively concise, still calling the save method that can directly execute save commands, just with a lambda for additional configuration.

info

Obviously, for specific associations, precise configuration has higher priority than blind configuration.

1. APPEND

Unconditionally perform INSERT operations on associated objects

Java

List<BookStore> stores = Arrays.asList(
    Immutables.createBookStore(draft -> {
        draft.setName("MANNING");
        draft.addIntoBooks(book -> {
            book.setName("SQL in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("49.9"));
        });
        draft.addIntoBooks(book -> {
            book.setName("LINQ in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("39.9"));
        });
    }),
    Immutables.createBookStore(draft -> {
        draft.setName("AMAZON");
        draft.addIntoBooks(book -> {
            book.setName("C++ Primer");
            book.setEdition(5);
            book.setPrice(new BigDecimal("44.02"));
        });
        draft.addIntoBooks(book -> {
            book.setName("Programming RUST");
            book.setEdition(1);
            book.setPrice(new BigDecimal("71.99"));
        });
    })
);

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.APPEND
);

Kotlin

val stores = listOf(
    BookStore {
        name = "MANNING"
        books().addBy {
            name = "SQL in Action"
            edition = 1
            price = BigDecimal("49.9")
        }
        books().addBy {
            name = "LINQ in Action"
            edition = 1
            price = BigDecimal("39.9")
        }
    },
    BookStore {
        name = "AMAZON"
        books().addBy {
            name = "C++ Primer"
            edition = 5
            price = BigDecimal("44.02")
        }
        books().addBy {
            name = "Programming RUST"
            edition = 1
            price = BigDecimal("71.99")
        }
    }
)

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.APPEND
)

To demonstrate simpler SQL generation, we assume that the targetTransferable feature of sqlClient is enabled, which was previously covered, see here

Two SQL statements will be generated:

1. Perform UPSERT (insert or update) operations on two root objects

   Saving the root object is not the focus of this article, collapsed by default

   H2

   merge into BOOK_STORE(
       NAME
   ) key(NAME) values(
       ?
   )
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   Mysql

   caution

   默认情况下，MySQL的批量操作不会被采用，而采用多条SQL。具体细节请参考MySQL的问题

   1. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* MANNING */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)
      

   2. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* AMAZON */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)

   Postgres

   insert into BOOK_STORE(
       NAME
   ) values(
       ?
   ) on conflict(
       NAME
   ) do update set
       /* fake update to return all ids */ NAME = execluded.NAME
   returning ID
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   • Assume MANNING exists with an id of 2
   • Assume AMAZON exists, after insertion, the database automatically assigns a new id of 100

2. Perform INSERT operations on 4 associated objects

   Assuming after the previous SQL saves the root objects:

   • The id of MANNING is 2
   • The id of AMAZON is 100

   The following SQL is generated:

   Most Databases

   insert into BOOK(
       NAME, EDITION, PRICE, STORE_ID
   ) values(?, ?, ?, ?)
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

   Mysql

   caution

   By default, MySQL batch operations are not used. For specific details, please refer to MySQL Issues

   1. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* SQL in Action */, 
          ? /* 1 */, 
          ? /* 49.9 */, 
          ? /* 2 */
      )
      

   2. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* LINQ in Action */, 
          ? /* 1 */, 
          ? /* 39.9 */, 
          ? /* 2 */
      )
      

   3. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* C++ Primer */, 
          ? /* 5 */, 
          ? /* 44.02 */, 
          ? /* 100 */
      )
      

   4. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* Programming RUST */, 
          ? /* 1 */, 
          ? /* 71.99 */, 
          ? /* 100 */
      )

2. APPEND_IF_ABSENT

1. First check if the associated object exists in the database

• If the id property of the associated object is specified, check existence by id
• Otherwise, check existence by key

2. Execute subsequent operations based on the check result

• If the associated object already exists, ignore the operation and do nothing
• Otherwise, insert the associated object

note

Jimmer will try to utilize the database's native UPSERT capabilities to combine these two steps. If this is not possible, it will inform the user of the reason

Java

List<BookStore> stores = Arrays.asList(
    Immutables.createBookStore(draft -> {
        draft.setName("MANNING");
        draft.addIntoBooks(book -> {
            book.setName("SQL in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("49.9"));
        });
        draft.addIntoBooks(book -> {
            book.setName("LINQ in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("39.9"));
        });
    }),
    Immutables.createBookStore(draft -> {
        draft.setName("AMAZON");
        draft.addIntoBooks(book -> {
            book.setName("C++ Primer");
            book.setEdition(5);
            book.setPrice(new BigDecimal("44.02"));
        });
        draft.addIntoBooks(book -> {
            book.setName("Programming RUST");
            book.setEdition(1);
            book.setPrice(new BigDecimal("71.99"));
        });
    })
);

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.APPEND_IF_ABSENT
);

Kotlin

val stores = listOf(
    BookStore {
        name = "MANNING"
        books().addBy {
            name = "SQL in Action"
            edition = 1
            price = BigDecimal("49.9")
        }
        books().addBy {
            name = "LINQ in Action"
            edition = 1
            price = BigDecimal("39.9")
        }
    },
    BookStore {
        name = "AMAZON"
        books().addBy {
            name = "C++ Primer"
            edition = 5
            price = BigDecimal("44.02")
        }
        books().addBy {
            name = "Programming RUST"
            edition = 1
            price = BigDecimal("71.99")
        }
    }
)

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.APPEND_IF_ABSENT
)

To demonstrate simpler SQL generation, we assume that the targetTransferable feature of sqlClient is enabled, which was previously covered, see here

Two SQL statements will be generated:

1. Perform UPSERT (insert or update) operations on two root objects

   Saving the root object is not the focus of this article, collapsed by default

   H2

   merge into BOOK_STORE(
       NAME
   ) key(NAME) values(
       ?
   )
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   Mysql

   caution

   默认情况下，MySQL的批量操作不会被采用，而采用多条SQL。具体细节请参考MySQL的问题

   1. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* MANNING */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)
      

   2. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* AMAZON */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)

   Postgres

   insert into BOOK_STORE(
       NAME
   ) values(
       ?
   ) on conflict(
       NAME
   ) do update set
       /* fake update to return all ids */ NAME = execluded.NAME
   returning ID
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   • Assume MANNING exists with an id of 2
   • Assume AMAZON exists, after insertion, the database automatically assigns a new id of 100

2. Perform INSERT_IF_ABSENT operations on 4 associated objects

   Assuming:

   • The key for Book objects is Book.name and Book.edition, and the Book entity is annotated with @KeyUniqueConstraint (For MySQL, @KeyUniqueConstraint(noMoreUniqueConstraints = true) is needed)

   • After the previous SQL saves the root objects:

     • The id of MANNING is 2
     • The id of AMAZON is 100

   The following SQL is generated:

   H2

   merge into BOOK tb_1_ 
   using(values(?, ?, ?, ?)) tb_2_(
       NAME, EDITION, PRICE, STORE_ID
   ) 
   on 
       tb_1_.NAME = tb_2_.NAME
   and
       tb_1_.EDITION = tb_2_.EDITION 
   when not matched then 
       insert(
           NAME, EDITION, PRICE, STORE_ID
       ) values(
           tb_2_.NAME, tb_2_.EDITION, tb_2_.PRICE, tb_2_.STORE_ID
       )
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

   Mysql

   insert 
   ignore
   into BOOK(
       NAME, EDITION, PRICE, STORE_ID
   ) values(
       ?, ?, ?, ?
   )
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

   Postgres

   insert into BOOK(
       NAME, EDITION, PRICE, STORE_ID
   ) values(
       ?, ?, ?, ?
   ) on conflict(
       NAME, EDITION
   ) do nothing
   returning ID
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

3. UPDATE

• If the id property of the associated object is specified, update the associated object by id
• Otherwise, update the associated object by key

Java

List<BookStore> stores = Arrays.asList(
    Immutables.createBookStore(draft -> {
        draft.setName("MANNING");
        draft.addIntoBooks(book -> {
            book.setName("SQL in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("49.9"));
        });
        draft.addIntoBooks(book -> {
            book.setName("LINQ in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("39.9"));
        });
    }),
    Immutables.createBookStore(draft -> {
        draft.setName("AMAZON");
        draft.addIntoBooks(book -> {
            book.setName("C++ Primer");
            book.setEdition(5);
            book.setPrice(new BigDecimal("44.02"));
        });
        draft.addIntoBooks(book -> {
            book.setName("Programming RUST");
            book.setEdition(1);
            book.setPrice(new BigDecimal("71.99"));
        });
    })
);

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.UPDATE
);

Kotlin

val stores = listOf(
    BookStore {
        name = "MANNING"
        books().addBy {
            name = "SQL in Action"
            edition = 1
            price = BigDecimal("49.9")
        }
        books().addBy {
            name = "LINQ in Action"
            edition = 1
            price = BigDecimal("39.9")
        }
    },
    BookStore {
        name = "AMAZON"
        books().addBy {
            name = "C++ Primer"
            edition = 5
            price = BigDecimal("44.02")
        }
        books().addBy {
            name = "Programming RUST"
            edition = 1
            price = BigDecimal("71.99")
        }
    }
)

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.UPDATE
)

To demonstrate simpler SQL generation, we assume that the targetTransferable feature of sqlClient is enabled, which was previously covered, see here

Two SQL statements will be generated:

1. Perform UPSERT (insert or update) operations on two root objects

   Saving the root object is not the focus of this article, collapsed by default

   H2

   merge into BOOK_STORE(
       NAME
   ) key(NAME) values(
       ?
   )
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   Mysql

   caution

   默认情况下，MySQL的批量操作不会被采用，而采用多条SQL。具体细节请参考MySQL的问题

   1. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* MANNING */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)
      

   2. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* AMAZON */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)

   Postgres

   insert into BOOK_STORE(
       NAME
   ) values(
       ?
   ) on conflict(
       NAME
   ) do update set
       /* fake update to return all ids */ NAME = execluded.NAME
   returning ID
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   • Assume MANNING exists with an id of 2
   • Assume AMAZON exists, after insertion, the database automatically assigns a new id of 100

2. Perform INSERT operations on 4 associated objects

   Assuming after the previous SQL saves the root objects:

   • The id of MANNING is 2
   • The id of AMAZON is 100

   The following SQL is generated:

   Most Databases

   update BOOK
   set
       PRICE = ?,
       STORE_ID = ?
   where
       NAME = ?
   and
       EDITION = ?
   /* batch-0: [49.9, 2, SQL in Action, 1] */
   /* batch-1: [39.9, 2, LINQ in Action, 1] */
   /* batch-2: [44.02, 100, C++ Primer, 5] */
   /* batch-3: [71.99, 100, Programming RUST, 1] */

   Mysql

   caution

   By default, MySQL batch operations are not used. For specific details, please refer to MySQL Issues

   1. update BOOK
      set
          PRICE = ? /* 49.9 */,
          STORE_ID = ? /* 2 */
      where
          NAME = ? /* SQL in Action */
      and
          EDITION = ? /* 1 */

   2. update BOOK
      set
          PRICE = ? /* 39.9 */,
          STORE_ID = ? /* 2 */
      where
          NAME = ? /* LINQ in Action */
      and
          EDITION = ? /* 1 */

   3. update BOOK
      set
          PRICE = ? /* 44.02 */,
          STORE_ID = ? /* 100 */
      where
          NAME = ? /* C++ Primer */
      and
          EDITION = ? /* 5 */

   4. update BOOK
      set
          PRICE = ? /* 71.99 */,
          STORE_ID = ? /* 100 */
      where
          NAME = ? /* Programming RUST */
      and
          EDITION = ? /* 1 */

4. MERGE

1. First check if the associated object exists in the database

• If the id property of the associated object is specified, check existence by id
• Otherwise, check existence by key

2. Execute subsequent operations based on the check result

• If the associated object already exists, update it
• Otherwise, insert it

note

Jimmer will try to utilize the database's native UPSERT capabilities to combine these two steps. If this is not possible, it will inform the user of the reason

Java

List<BookStore> stores = Arrays.asList(
    Immutables.createBookStore(draft -> {
        draft.setName("MANNING");
        draft.addIntoBooks(book -> {
            book.setName("SQL in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("49.9"));
        });
        draft.addIntoBooks(book -> {
            book.setName("LINQ in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("39.9"));
        });
    }),
    Immutables.createBookStore(draft -> {
        draft.setName("AMAZON");
        draft.addIntoBooks(book -> {
            book.setName("C++ Primer");
            book.setEdition(5);
            book.setPrice(new BigDecimal("44.02"));
        });
        draft.addIntoBooks(book -> {
            book.setName("Programming RUST");
            book.setEdition(1);
            book.setPrice(new BigDecimal("71.99"));
        });
    })
);

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.MERGE
);

Kotlin

val stores = listOf(
    BookStore {
        name = "MANNING"
        books().addBy {
            name = "SQL in Action"
            edition = 1
            price = BigDecimal("49.9")
        }
        books().addBy {
            name = "LINQ in Action"
            edition = 1
            price = BigDecimal("39.9")
        }
    },
    BookStore {
        name = "AMAZON"
        books().addBy {
            name = "C++ Primer"
            edition = 5
            price = BigDecimal("44.02")
        }
        books().addBy {
            name = "Programming RUST"
            edition = 1
            price = BigDecimal("71.99")
        }
    }
)

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.MERGE
)

To demonstrate simpler SQL generation, we assume that the targetTransferable feature of sqlClient is enabled, which was previously covered, see here

Two SQL statements will be generated:

1. Perform UPSERT (insert or update) operations on two root objects

   Saving the root object is not the focus of this article, collapsed by default

   H2

   merge into BOOK_STORE(
       NAME
   ) key(NAME) values(
       ?
   )
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   Mysql

   caution

   默认情况下，MySQL的批量操作不会被采用，而采用多条SQL。具体细节请参考MySQL的问题

   1. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* MANNING */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)
      

   2. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* AMAZON */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)

   Postgres

   insert into BOOK_STORE(
       NAME
   ) values(
       ?
   ) on conflict(
       NAME
   ) do update set
       /* fake update to return all ids */ NAME = execluded.NAME
   returning ID
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   • Assume MANNING exists with an id of 2
   • Assume AMAZON exists, after insertion, the database automatically assigns a new id of 100

2. Perform MERGE operations on 4 associated objects

   Assuming:

   • The key for Book objects is Book.name and Book.edition, and the Book entity is annotated with @KeyUniqueConstraint (For MySQL, @KeyUniqueConstraint(noMoreUniqueConstraints = true) is needed)

   • After the previous SQL saves the root objects:

     • The id of MANNING is 2
     • The id of AMAZON is 100

   The following SQL is generated:

   H2

   merge into BOOK(
       NAME, EDITION, PRICE, STORE_ID
   ) key(NAME, EDITION) values(
       ?, ?, ?, ?
   )
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

   Mysql

   caution

   By default, MySQL batch operations are not used. For specific details, please refer to MySQL Issues

   1. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* SQL in Action */, 
          ? /* 1 */, 
          ? /* 49.9 */, 
          ? /* 2 */
      ) on duplicate key 
      update
          /* fake update to return all ids */ ID = last_insert_id(ID),
          PRICE = values(PRICE),
          STORE_ID = values(STORE_ID)

   2. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* LINQ in Action */, 
          ? /* 1 */, 
          ? /* 39.9 */, 
          ? /* 2 */
      ) on duplicate key 
      update
          /* fake update to return all ids */ ID = last_insert_id(ID),
          PRICE = values(PRICE),
          STORE_ID = values(STORE_ID)

   3. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* C++ Primer */, 
          ? /* 5 */, 
          ? /* 44.02 */, 
          ? /* 100 */
      ) on duplicate key 
      update
          /* fake update to return all ids */ ID = last_insert_id(ID),
          PRICE = values(PRICE),
          STORE_ID = values(STORE_ID)

   4. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* Programming RUST */, 
          ? /* 1 */, 
          ? /* 71.99 */, 
          ? /* 100 */
      ) on duplicate key 
      update
          /* fake update to return all ids */ ID = last_insert_id(ID),
          PRICE = values(PRICE),
          STORE_ID = values(STORE_ID)

   Postgres

   insert into BOOK(
       NAME, EDITION, PRICE, STORE_ID
   ) values(
       ?, ?, ?, ?
   ) on conflict(
       NAME, EDITION
   ) do update set
       PRICE = excluded.PRICE, 
       STORE_ID = excluded.STORE_ID
   returning ID
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

5. REPLACE

The MERGE mode performs INSERT or UPDATE operations on associated objects, so after saving, the number of associated objects either remains the same or increases, but never decreases.

The REPLACE mode does not have this characteristic because REPLACE performs dissociate operations on no-longer-needed associated objects in addition to the MERGE functionality.

The dissociate operation removes associations that the current object no longer needs, for example:

Existing Database Structure	User's Expected Data Structure
+-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 an UPDATE operation

• For Drinks, it doesn't exist in the old data structure but exists in the new data, corresponding to an INSERT operation

• For Meat, it exists in the old data structure but not in the new data, corresponding to an operation called dissociation operation.

info

The dissociate operation will be explained in detail in subsequent tutorials, so this article provides a highly simplified example

Java

List<Book> books = Arrays.asList(
    Immutables.createBook(book -> {
        book.setId(1L);
        book.addIntoAuthors(author -> author.setId(2L));
        book.addIntoAuthors(author -> author.setId(3L));
    }),
    Immutables.createBook(book -> {
        book.setId(2L);
        book.addIntoAuthors(author -> author.setId(2L));
        book.addIntoAuthors(author -> author.setId(4L));
    })
);
sqlClient.saveEntities(
    books,
    // This parameter can be removed because for the `saveEntities` method,
    // `AssociatedSaveMode.REPLACE` is the default behavior
    AssociatedSaveMode.REPLACE
);

Kotlin

val books = listOf(
    Book {
        id = 1L
        authors().addBy { id = 2L }
        authors().addBy { id = 3L }
    },
    Book {
        id = 2L
        authors().addBy { id = 2L }
        authors().addBy { id = 4L }
    }
)
sqlClient.saveEntities(
    books,
    // This parameter can be removed because for the `saveEntities` method,
    // `AssociatedSaveMode.REPLACE` is the default behavior
    AssociatedSaveMode.REPLACE
)

Assuming the existing database structure is shown in the left column of the table below:

Existing Database Structure	Desired Data Structure to Save
--+-Book(1) | +---Author(1) | \---Author(2) --+-Book(2) | +---Author(1) | \---Author(2)	--+-Book(1) | | | +---Author(2) | \---Author(3) --+-Book(2) | | | +---Author(2) | \---Author(4)

Obviously, there should be 4 behaviors:

• Dissociate operations specific to REPLACE:

  • Dissociate the association between Book-1 and Author-1
  • Dissociate the association between Book-2 and Author-1

• Capabilities inherited from MERGE:

  • Create a new association between Book-1 and Author-3
  • Create a new association between Book-2 and Author-4

Finally, two SQL statements will be generated:

1. The dissociate operation specific to REPLACE mode, removing old associations:

   H2

   delete from BOOK_AUTHOR_MAPPING
   where
       BOOK_ID = ?
   and
       not (AUTHOR_ID = any(?))
   /* batch-0: [1, [2, 3]] */
   /* batch-1: [2, [2, 4]] */

   Mysql

   delete from BOOK_AUTHOR_MAPPING
   where
       BOOK_ID in (
           ? /* 1 */, ? /* 2 */
       )
   and
       (BOOK_ID, AUTHOR_ID) not in (
           (? /* 1 */, ? /* 2 */), 
           (? /* 1 */, ? /* 3 */), 
           (? /* 2 */, ? /* 2 */), 
           (? /* 2 */, ? /* 4 */)
       )

   Postgres

   delete from BOOK_AUTHOR_MAPPING
   where
       BOOK_ID = ?
   and
       not (AUTHOR_ID = any(?))
   /* batch-0: [1, [2, 3]] */
   /* batch-1: [2, [2, 4]] */

   The purpose of this SQL is:

   • For Book-1, delete its associations with Authors other than Author-2 and Author-3

     In this case, it means dissociating Book-1 from Author-1

   • For Book-2, delete its associations with Authors other than Author-2 and Author-4

     In this case, it means dissociating Book-2 from Author-1

   We can see significant differences between different databases:

   • H2 and Postgres use simple batched SQL
   • MySQL uses a single complex SQL statement
   info

   This is because H2 and Postgres support the =any(array) syntax, while MySQL does not.

2. Capabilities inherited by REPLACE mode from MERGE mode to establish new associations

   H2

   merge into BOOK_AUTHOR_MAPPING tb_1_ 
   using(values(?, ?)) tb_2_(
       BOOK_ID, AUTHOR_ID
   ) 
   on 
       tb_1_.BOOK_ID = tb_2_.BOOK_ID
   and
       tb_1_.AUTHOR_ID = tb_2_.AUTHOR_ID 
   when not matched then 
       insert(BOOK_ID, AUTHOR_ID)
       values(tb_2_.BOOK_ID, tb_2_.AUTHOR_ID)
   /* batch-0: [1, 2] */
   /* batch-1: [1, 3] */
   /* batch-2: [2, 2] */
   /* batch-3: [2, 3] */

   Mysql

   caution

   By default, MySQL batch operations are not used. For specific details, please refer to MySQL Issues

   1. insert 
      ignore
      into BOOK_AUTHOR_MAPPING(BOOK_ID, AUTHOR_ID) 
      values(? /* 1 */, ? /* 2 */)

   2. insert 
      ignore
      into BOOK_AUTHOR_MAPPING(BOOK_ID, AUTHOR_ID) 
      values(? /* 1 */, ? /* 3 */)

   3. insert 
      ignore
      into BOOK_AUTHOR_MAPPING(BOOK_ID, AUTHOR_ID) 
      values(? /* 2 */, ? /* 2 */)

   4. insert 
      ignore
      into BOOK_AUTHOR_MAPPING(BOOK_ID, AUTHOR_ID) 
      values(? /* 2 */, ? /* 3 */)

   Postgres

   insert 
   into BOOK_AUTHOR_MAPPING(BOOK_ID, AUTHOR_ID) 
   values(?, ?)
   on conflict(BOOK_ID, AUTHOR_ID)
   do nothing
   /* batch-0: [1, 2] */
   /* batch-1: [1, 3] */
   /* batch-2: [2, 2] */
   /* batch-3: [2, 3] */

The REPLACE mode integrates INSERT, UPDATE, and DELETE operations into one, expecting to replace all associated relationships completely (if the association relationship is a deep long association, then it's replacing an entire subtree).

However, REPLACE mode has one limitation: it expects objects to either have an id or a key.

Because it expects to cleverly find the parts where the new and old data structures have changed through id and key, thereby minimizing the impact range of INSERT, UPDATE, and DELETE operations to achieve maximum performance.

tip

If readers are familiar with the web domain, it's not hard to see that this is the same principle as specifying the key attribute when using loop rendering in React.

In fact, the save command design was inspired by React.

6. VIOLENTLY_REPLACE

We've dissucess REPLACE, however, what should we do when dealing with wild objects, i.e., associated objects that have neither id nor key?

In this case, we can use the VIOLENTLY_REPLACE mode. Once this mode is adopted, Jimmer no longer looks for changes in associations (or association collections) based on id or key, but performs the following two operations:

1. First, delete all old associations and related objects of the current object
2. Then reinsert all associated objects and rebuild associations with the current object

Java

List<BookStore> stores = Arrays.asList(
    Immutables.createBookStore(draft -> {
        draft.setName("MANNING");
        draft.addIntoBooks(book -> {
            book.setName("SQL in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("49.9"));
        });
        draft.addIntoBooks(book -> {
            book.setName("LINQ in Action");
            book.setEdition(1);
            book.setPrice(new BigDecimal("39.9"));
        });
    }),
    Immutables.createBookStore(draft -> {
        draft.setName("AMAZON");
        draft.addIntoBooks(book -> {
            book.setName("C++ Primer");
            book.setEdition(5);
            book.setPrice(new BigDecimal("44.02"));
        });
        draft.addIntoBooks(book -> {
            book.setName("Programming RUST");
            book.setEdition(1);
            book.setPrice(new BigDecimal("71.99"));
        });
    })
);

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.VIOLENTLY_REPLACE
);

Kotlin

val stores = listOf(
    BookStore {
        name = "MANNING"
        books().addBy {
            name = "SQL in Action"
            edition = 1
            price = BigDecimal("49.9")
        }
        books().addBy {
            name = "LINQ in Action"
            edition = 1
            price = BigDecimal("39.9")
        }
    },
    BookStore {
        name = "AMAZON"
        books().addBy {
            name = "C++ Primer"
            edition = 5
            price = BigDecimal("44.02")
        }
        books().addBy {
            name = "Programming RUST"
            edition = 1
            price = BigDecimal("71.99")
        }
    }
)

sqlClient.saveEntities(
    stores,
    AssociatedSaveMode.VIOLENTLY_REPLACE
)

To demonstrate simpler SQL generation, let's assume that the targetTransferable feature of sqlClient is enabled, which was previously introduced, please refer to here

Three SQL statements will be generated:

1. Perform UPSERT (insert or update) operations on the two root objects

   Saving the root object is not the focus of this article, collapsed by default

   H2

   merge into BOOK_STORE(
       NAME
   ) key(NAME) values(
       ?
   )
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   Mysql

   caution

   默认情况下，MySQL的批量操作不会被采用，而采用多条SQL。具体细节请参考MySQL的问题

   1. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* MANNING */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)
      

   2. insert into BOOK_STORE(
          NAME
      ) values(
          ? /* AMAZON */
      ) on duplicate key update
          /* fake update to return all ids */ ID = last_insert_id(ID)

   Postgres

   insert into BOOK_STORE(
       NAME
   ) values(
       ?
   ) on conflict(
       NAME
   ) do update set
       /* fake update to return all ids */ NAME = execluded.NAME
   returning ID
   /* batch-0: [MANNING] */
   /* batch-1: [AMAZON] */

   • Assume MANNING exists with an id of 2
   • Assume AMAZON exists, after insertion, the database automatically assigns a new id of 100

2. Delete all old associated objects

   Assuming after saving the root objects in the previous SQL

   • The id of MANNING is 2
   • The id of AMAZON is 100
   H2

   delete from BOOK 
   where STORE_ID = any(
       ? /* [2, 100] */
   )

   Mysql

   delete from BOOK 
   where STORE_ID in (
       ? /* 2 */, ? /* 100 */
   )

   Postgres

   delete from BOOK 
   where STORE_ID = any(
       ? /* [2, 100] */
   )
   info

   If there are other tables in the database that reference the BOOK table through foreign keys, a simple DELETE statement cannot delete all associated objects.

   To simplify the discussion, let's assume this scenario doesn't exist.

3. Create all new associations and associated objects

   Assuming after saving the root objects in the previous SQL

   • The id of MANNING is 2
   • The id of AMAZON is 100
   Most Databases

   insert into BOOK(
       NAME, EDITION, PRICE, STORE_ID
   ) values(?, ?, ?, ?)
   /* batch-0: [SQL in Action, 1, 49.9, 2] */
   /* batch-1: [LINQ in Action, 1, 39.9, 2] */
   /* batch-2: [C++ Primer, 5, 44.02, 100] */
   /* batch-3: [Programming RUST, 1, 71.99, 100] */

   Mysql

   caution

   By default, MySQL batch operations are not used. For specific details, please refer to MySQL Issues

   1. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* SQL in Action */, 
          ? /* 1 */, 
          ? /* 49.9 */, 
          ? /* 2 */
      )
      

   2. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* LINQ in Action */, 
          ? /* 1 */, 
          ? /* 39.9 */, 
          ? /* 2 */
      )
      

   3. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* C++ Primer */, 
          ? /* 5 */, 
          ? /* 44.02 */, 
          ? /* 100 */
      )
      

   4. insert into BOOK(
          NAME, EDITION, PRICE, STORE_ID
      ) values(
          ? /* Programming RUST */, 
          ? /* 1 */, 
          ? /* 71.99 */, 
          ? /* 100 */
      )

Not comparing old and new data in detail, but simply deleting associated objects and rebuilding them is a very violent mode with the following disadvantages:

• If there are many associated objects, in most cases, this is a low-performance solution
• If the associated objects have deeper associations, the deletion behavior in step 1 may lead to too deep cascade deletion, resulting in the loss of too much data

caution

Therefore, it is not recommended, please use with caution

Default Mode

The save command provides not only save, saveEntities, and saveInputs methods, but also other shortcut methods for various application scenarios.

Different save commands have different default values for AssociatedSaveMode, please refer to this table:

Method	Default AssociatedSaveMode
save	REPLACE
saveEntities
saveInputs
insert	APPEND
insertEntities
insertInputs
insertIfAbsent	APPEND_IF_ABSENT
insertEntitiesIfAbsent
insertInputsIfAbsent
update	UPDATE
updateEntities
updateInputs
merge	MERGE
mergeEntities
mergeInputs