Key

Concept

info

@org.babyfish.jimmer.sql.Key is used together with Save Command in mutation section.

Initially, people used business fields directly as the primary key of tables. This is the most straightforward and easy to use approach, but it has the following problems:

• Unstable primary key

  Since the primary key itself is a business field, it can be modified, which leads to an unstable primary key.

  Although the foreign keys that reference it in the database can use on update cascade to keep consistency, an unstable primary key will cause many problems for systems outside the database, such as caches.

  Even if a unified solution to the primary key instability problem can be abstracted at the technical level, it is not recommended, because it makes the system difficult to understand.

• High cost of multi-table joins

  Since the primary key itself is a business field, its type is likely to be a relatively long string type rather than a numeric type, and it may even be a composite primary key composed of multiple columns, which leads to high cost of multi-table joins.

To solve the above problems, people learned to use data without business meaning as the primary key, such as

• Auto numbering by database
• Sequence value by database
• UUID
• Snowflake ID

To enable idempotent saves for save commands, Jimmer introduces two concepts: Id and Key

• @Id: Primary key

• @Key: Business primary key

  • If Id itself is a business attribute (not recommended), no need to specify Key, because Id itself already represents the uniqueness constraint at the business level.

  • If Id has no business meaning (recommended), then Key represents what the uniqueness constraint is at the business level.

See the following example:

Java

@Entity  
public interface TreeNode {

    @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    long id();

    @Key
    String name();

    @Key  
    @ManyToOne
    @Nullable
    TreeNode parent();

    @OneToMany(mappedBy = "parent")
    List<TreeNode> childNodes();
}

Kotlin

@Entity
interface TreeNode {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    val id: Long

    @Key
    val name: String

    @Key   
    @ManyToOne
    val parent: TreeNode?

    @OneToMany(mappedBy = "parent")
    val childNodes: List<TreeNode>
}

Here, the Id of the TreeNode entity uses auto numbering and has no business meaning. Therefore, to better match save commands, key is specified here, consisting of two properties: name and parent.

The corresponding DDL is:

create table TREE_NODE(
    ID bigint not null,
    NAME varchar(20) not null,
    PARENT_ID bigint
);

alter table TREE_NODE
    add constraint PK_TREE_NODE
        primary key(ID);
        
alter table TREE_NODE
    add constraint UQ_TREE_NODE
        /* highlight-next-line */
        unique(NAME, PARENT_ID); 

The name and parent properties of the entity type decorated with @Key correspond to the UNIQUE constraint (or UNIQUE INDEX) composed of the NAME and PARENT_ID columns in the database.

This uniqueness constraint can be analogized to file systems. File systems allow directories or files with the same name, but do not allow the same name if the parent directory is specified.

info

Through this example, we see:

• Key can consist of multiple properties

• Foreign keys can be part of Key

Let's take another look at another example where the properties that make up Key are all properties based on foreign keys:

Java

@Entity
public interface OrderItem {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    long id();

    @Key   
    @ManyToOne
    Order order();

    @Key
    @ManyToOne
    Product product();

    int quantity();

    // Snapshot of `product.price`
    BigDecimal unitPrice(); 
}

Kotlin

@Entity
interface OrderItem {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long

    @Key
    @ManyToOne
    val order: Order

    @Key 
    @ManyToOne
    val product: Product

    val quantity: Int

    // Snapshot of `product.price`
    val unitPrice: BigDecimal
}

This article only introduces the configuration of Key. For how to use it further, please refer to Save Command.

Multi-version support

Jimmer supports logical deletion, which hides deleted data instead of actually deleting it.

The unique constraint defined by @Key is only for non-hidden data, not all data in the table, so unique constraint cannot be simply defined by the columns of @key.

• When the logical deletion flag is datetime

  Java

  Book.java

  @Entity
  public interface Book {
  
      @Key
      String name();
  
      @LogicalDeleted("now")
      @Nullable
      LocalDateTime deletedTime();
  
      ...other code omitted...
  }

  Kotlin

  Book.kt

  @Entity
  interface Book {
  
      @Key
      val name: String
  
      @LogicalDeleted("now")
      val deletedTime: LocalDateTime?
  
      ...other code omitted...
  }

  In this case, combine the columns corresponding to @Key (for this example, NAME) and the logical deletion flag column (for this example, DELETED_TIME) to define the uniqueness constraint, e.g.:

  alter table BOOK
      add constraint UQ_BOOK
          unique(NAME, DELETED_TIME);

• When the logical deletion flag is other type

  Java

  Book.java

  @Entity
  public interface Book {
  
      @Key
      String name();
  
      @LogicalDeleted("true")
      boolean deleted();
  
      ...other code omitted...
  }

  Kotlin

  Book.kt

  @Entity
  interface Book {
  
      @Key
      val name: String
  
      @LogicalDeleted("true")
      val deleted: Boolean
  
      ...other code omitted...
  }

  In this case, combining the @Key columns and the logical deletion flag column is no longer a viable approach. Using a conditional unique index is the only option.

  caution

  Unfortunately, not all databases support conditional indexes, and the syntax for creating conditional index varies between databases.

  Here, PostgreSQL is used as an example:

  create unique index BOOK_KEY_INDEX 
      on BOOK(NAME)
      // highlight-next-line
      where DELETED = false;

Dynamic Overrides

Confiugration specified by the annotation @Key is called static configuration.

Sometimes, different businesses may have different requirements for @Key. Therefore, @Key configurations can be dynamically overridden at runtime.

Java

sqlClient
    .getEntities()
    .saveCommand(book)
    .setKeyProps(BookProps.NAME, BookProps.EDITION)
    .execute();

Kotlin

sqlClient.save(book) {
    .setKeyProps(Book::name, Book::edition)
}