Associated Id Checking

Basic Concepts

For Short Associations Only

Association id checking is a feature that only applies to

, and is meaningless for .

As we learned before, save commands can persist arbitrary data shapes, and any object can further hold associated objects.

If the id of an associated object is specified, but the object it represents does not exist in the database, how does Jimmer handle this scenario?

First, for

, Jimmer will first create the non-existing associated object, then establish the association between the current object and the new associated object. For example:

Java

sqlClient.update(
    Objects.createBook(draft -> {
        draft.setId(3L);
        draft.addIntoAuthors(author -> author.setId(1L)); // ❶
        draft.addIntoAuthors(author -> author.setId(2L)); // ❷
        draft.addIntoAuthors(author -> { // ❸
            author.setId(1000L); 
            author.setFirstName("Svetlana");
            author.setLastName("Isakova");
            author.setGender(Gender.FEMALE);
        });
    })
);

Kotlin

sqlClient.update(
    new(Book::class).by {
        id = 3L
        authors().addBy { id = 1L } // ❶
        authors().addBy { id = 2L } // ❷
        authors().addBy { // ❸
            id = 1000L 
            firstName = "Svetlana"
            lastName = "Isakova"
            gender = Gender.FEMALE
        }
    }
)

This example mixes long associations and short associations.

• ❶ ❷ They are

  , specifying invalid ids will lead to errors.

• ❸ This is a

  , even if an invalid id is specified, Jimmer will automatically create the associated object.

The generated SQL is:

// Check if associated object exists
select
    tb_1_.ID,
    tb_1_.FIRST_NAME,
    tb_1_.LAST_NAME
from AUTHOR tb_1_
where
    tb_1_.ID = ? /* 1000 */

// Associated object does not exist, create it
// highlight-next-line  
insert into AUTHOR(ID, FIRST_NAME, LAST_NAME, GENDER)
values
    (? /* 1000 */, ? /* Svetlana */, ? /* Isakova */, ? /* F */)

// Query current `Book` and `Author` mapping
select
    AUTHOR_ID
from BOOK_AUTHOR_MAPPING 
where
    BOOK_ID = ? /* 3 */

// Map current `Book` with the newly created `Author`  
insert into BOOK_AUTHOR_MAPPING(BOOK_ID, AUTHOR_ID)
values
    (? /* 3 */, ? /* 1000 */)

info

Therefore, association id checking is a topic that only makes sense for

. In the following examples of this article, all discussions are about .

Concept Definition: Target Foreign Key

To discuss the id checking problem of short associations, we first define a concept for associated properties: the target foreign key.

• For associations based on join tables, the foreign key in the join table that points to the target entity table is the target foreign key.

  For examples:

  • The target foreign key for Book.authors is the AUTHOR_ID field in the BOOK_AUTHOR_MAPPING table.

  • The target foreign key for Author.books is the BOOK_ID field in the BOOK_AUTHOR_MAPPING table.

• For associations based on foreign keys, whether the foreign key is fake or not (please refer to Fake Foreign Keys), the foreign key of the association itself is the target foreign key.

  For example:

  The target foreign key for Book.store is the STORE_ID field in the BOOK table.

• If neither of the above two cases apply, the association is considered to not have a target foreign key.

  Properties without a target foreign key are the ones with mappedBy specified for one-to-one or one-to-many mappings. That is, properties decorated with @OneToOne(mappedBy = "...") or @OneToMany(mappedBy="...").

  For example:

  There is no target foreign key for BookStore.books.

Summary

Association	Column of target foreign key	Table of target foreign key
Book.authors	AUTHOR_ID	BOOK_AUTHOR_MAPPING
Author.books	BOOK_ID	BOOK_AUTHOR_MAPPING
Book.store	STORE_ID	BOOK
BookStore.books	NA	NA

Checking Mechanism

Users can configure whether to check the ids of short associated objects.

Here, let's not discuss how to configure for now, but look at whether enabling this configuration has any impact on Jimmer's behavior.

• Properties without target foreign keys

  Take BookStore.books as an example. The code to save a short association is:

  Java

  sqlClient.update(
          Objects.createBookStore(draft -> {
              draft.setId(2L);
              draft.addIntoBooks(book -> book.setId(8L));
              draft.addIntoBooks(book -> book.setId(9L));
              draft.addIntoBooks(book -> book.setId(1000L));
              draft.addIntoBooks(book -> book.setId(1001L));
          })
  );

  Kotlin

  sqlClient.update(
      new(BookStore::class).by {
          id = 2L
          books().addBy { id = 8L }
          books().addBy { id = 9L }  
          books().addBy { id = 1000L }
          books().addBy { id = 1001L }
      }
  );

  • Without checking

    Properties without a target foreign key will automatically ignore associated objects with illegal ids. For example:

    update book set store_id = 2 where id in(1, 2, 1000, 10001)

    If 1000 and 10001 are ids that do not exist in the database, this update statement will only affect the two existing records, and the two non-existing records will be naturally ignored.

  • With checking

    Jimmer will execute the following query to check all short associated ids:

    select
        tb_1_.ID 
    from BOOK tb_1_
    where
        tb_1_.ID in (
            ? /* 1 */, ? /* 2 */, ? /* 1000 */, ? /* 1001 */
        )

    If ids 1000 and 1001 do not exist in the database, the following exception will be thrown:

    Save error caused by the path: "<root>.books": Illegal ids: [1000, 1001]

• Properties with target foreign keys

  Take Book.store as an example. The code to save a short association is:

  Java

  sqlClient.update(
          Objects.createBook(draft -> {
              draft.setId(10L);
              draft.applyStore(store -> store.setId(321L));
          })
  );

  Kotlin

  sqlClient.update(
      new(Book::class).by {
          id = 10L
          store { id = 321L }
      }
  );

  Assume id 321 does not exist for BookStore in the database.

  • Without checking

    • If the foreign key is fake, there is no real foreign key constraint in the database, and Jimmer will allow BOOK.STORE_ID to be modified to an illegal value.

    • If the foreign key is real, there is a real foreign key constraint in the database, and the underlying database will throw an error eventually.

  • With checking

    Regardless of whether the foreign key is fake or not, Jimmer will execute the following query to check the short associated id:

    select
        tb_1_.ID 
    from BOOK_STORE tb_1_
    where
        tb_1_.ID in (
            ? /* 321 */
        ) 

    Once the query returns no data, the following exception will be thrown:

    Save error caused by the path: "<root>.store": Illegal ids: [321]

Summary

	Real target foreign key	Fake target foreign key	No target foreign keys
Without checking	Error from database	Save wrong data	Ignore invalid operations
With checking	Error from Jimmer	Error from Jimmer	Error from Jimmer

info

As you can see, for properties with a real target foreign key, exceptions will be thrown regardless of whether Jimmer's short association id checking is enabled.

• Without checking, the database throws the error.

  • Advantage: Avoid an extra SQL query

  • Disadvantage: Less control over the exception message and type.

• With checking, Jimmer throws the error.

  • Advantage: clear exception message and type.

  • Disadvantage: An extra SQL query.

tip

It is recommended to turn on this checking mechanism for all properties if your project does not have extreme performance requirements, in order to get ideal exception information (we will introduce how to configure it later).

Configuration

Users can configure whether to check associated ids for properties. There are global configuration and command-level configuration.

Global Configuration

The global configuration provides three levels:

• NONE
• FAKE
• ALL

The behaviors are:

	Real target foreign key	Fake target foreign key	No target foreign keys
NONE	No checking	No checking	No checking
FAKE	No checking	Checking	Checking
ALL	Checking	Checking	Checking

There are two ways to configure globally:

• Via Spring Boot Starter configuration

  Modify application.yml (or application.properties) and add the following:

  jimmer:
      id-only-target-checking-level: ALL

• Via low-level API

  Java

  JSqlClient sqlClient = JSqlClient
      .newBuilder()
      .setIdOnlyTargetCheckingLevel(IdOnlyTargetCheckingLevel.ALL)
      ...other configurations omitted...
      .build();

  Kotlin

  val sqlClient = newKSqlClient {
      setIdOnlyTargetCheckingLevel(IdOnlyTargetCheckingLevel.ALL) 
      ...other configurations omitted...
  }

Command-Level Configuration

The command-level configuration can override the global configuration, but only affects the current save command.

There are three features for command-level configuration:

• Explicitly specify properties to check

  Java

  Book book = ...
  sqlClient
      .getEntities()
      .saveCommand(book)
      .setAutoIdOnlyTargetChecking(BookProps.STORE)
      .setAutoIdOnlyTargetChecking(BookProps.AUTHORS)
      .execute();

  Kotlin

  val book = ...
  sqlClient.save(book) {
      setAutoIdOnlyTargetChecking(Book::store) 
      setAutoIdOnlyTargetChecking(Book::authors)
  }
  info

  If the global configuration already enables checking, no need to adjust the save command like this.

• Specify all properties to check

  Java

  Book book = ...
  sqlClient
      .getEntities()
      .saveCommand(book)
      .setAutoIdOnlyTargetCheckingAll()
      .execute();

  Kotlin

  val book = ...
  sqlClient.save(book) {
      setAutoIdOnlyTargetCheckingAll()
  }
  info

  If the global configuration already enables checking, no need to adjust the save command like this.

• Negative configuration, explicitly specify properties to not check

  Java

  Book book = ...
  sqlClient
      .getEntities()
      .saveCommand(book)
      .setAutoIdOnlyTargetCheckingAll()
      .setAutoIdOnlyTargetChecking(BookProps.STORE, false)
      .execute();

  Kotlin

  val book = ...
  sqlClient.save(book) {
      setAutoIdOnlyTargetCheckingAll()
      setAutoIdOnlyTargetChecking(Book::authors, false)
  }