Delete Command

Basic Concepts

The delete command deletes objects by id or id collection.

API Category	Language	Delete by Id	Delete by Id Collection
Low Level API	Java	Full API JSqlClient.getEntities().delete JSqlClient.getEntities().deleteCommand Shortcut API JSqlClient.deleteById	Full API JSqlClient.getEntities().deleteAll JSqlClient.getEntities().deleteAllCommand Shortcut API JSqlClient.deleteByIds
Low Level API	Kotlin	Full API KSqlClient.entities.delete Shortcut API KSqlClient.deleteById	Full API KSqlClient.entities.deleteAll Shortcut API KSqlClient.deleteByIds
Spring Data API	Java	JRepository.deleteById	JRepository.deleteByIds JRepository.deleteAllById
Spring Data API	Kotlin	KRepository.deleteById	KRepository.deleteByIds KRepository.deleteAllById

Necessary Explanations:

In the Java low-level API there are two methods ending in Command: deleteCommand and deleteAllCommand.
- These two methods create commands but do not execute them immediately. After the user does more configuration on the command, calling execute executes it.
  
  Take deleteCommand as an example:
```
DeleteResult result = sqlClient
    .getEntities()
    .deleteCommand(BookStore.class, 1L) ❶
    .setDissociateAction(BookProps.STORE, DissociateAction.SET_NULL) ❷
    .execute(); ❸
```
  - ❶ Create the command, but do not execute it.
  - ❷ Configure the command, can be chained with multiple configurations (here only one configuration is demoed)
  info
  The effect of this configuration will be explained later, readers can ignore it for now.
  - ❸ Finally execute it.
  Kotlin does not need such a design, because its delete method supports an optional Lambda parameter for configuration. Simply execute with the necessary configuration:
```
val result = sqlClient
    .entities
    .delete(BookStore::class, 1L) {
        setDissociateAction(Book::store, DissociateAction.SET_NULL)
    }
```
In the Spring Data API we can find two methods with the same functionality but different names: deleteByIds and deleteAllById.
- deleteByIds: A method consistent in style with the Jimmer low-level shortcut API.
- deleteAllById: A required method when inheriting org.springframework.data.repository.CrudRepository, can be understood as an alias of deleteByIds.
The delete command supports two types of operations:
- Logical delete: Does not actually delete data, just marks the logical delete field of the object as "deleted".
- Physical delete: Actually deletes data.
All the above APIs support an optional parameter of type DeleteMode through Java method overloading or Kotlin default parameters. This parameter type is an enum with three possible values:
- AUTO: Does not explicitly specify whether the current operation is logical delete or physical delete
  - If the deleted entity type has a logical delete field, perform logical delete.
  - Otherwise, perform physical delete.
- LOGICAL: Explicitly specifies that the current operation is logical delete
  - If the deleted entity type has a logical delete field, perform logical delete.
  - Otherwise, throw an exception indicating that the current entity does not support logical delete.
- PHYSICAL: Explicitly specifies that the current operation is physical delete
  
  Regardless of whether the deleted entity type has a logical delete field, perform physical delete.

Logical Delete

Assuming the entity has a logical delete field, for example:

Java
Kotlin

Book.java
@Entity
public interface Book {

    @LogicalDeleted("true")
    boolean isDeleted();

    ...other code omitted...
}

Book.kt
@Entity
interface Book {

    @LogicalDeleted("true")
    val isDeleted: Boolean

    ...other code omitted...
}

Then the following three behaviors:

sqlClient.deleteById(Book.class, 1L)
sqlClient.deleteById(Book.class, 1L, DeleteMode.AUTO)
sqlClient.deleteById(Book.class, 1L, DeleteMode.LOGICAL)

have the same function, they all represent logical delete, and generate the following sql:

update BOOK  
set DELETED = ? /* true */
where ID in(?/* 1L*/)

Physical Delete

If the entity does not have a logical delete field, or the delete mode is explicitly specified as DeleteMode.PHYSICAL, physical delete is performed.

Basic Usage

Java
Kotlin

DeleteResult result = sqlClient
    .getEntities()
    .deleteAll(Book.class, Arrays.asList(1L, 2L, 3L, 4L));

System.out.println(
    
    "Affected row count: " +
    result.getTotalAffectedRowCount() + 

    "\nAffected row count of table 'BOOK': " +
    result.getAffectedRowCount(AffectedTable.of(Book.class)) +
    
    "\nAffected row count of middle table 'BOOK_AUTHOR_MAPPING': " +
    result.getAffectedRowCount(AffectedTable.of(BookProps.AUTHORS))
);

val result = sqlClient
    .entities
    .deleteAll(Book::class, listOf(1L, 2L, 3L, 4L))
println(
    """Affected row count:
        |${result.totalAffectedRowCount}
        |Affected row count of table 'BOOK':
        |${result.affectedRowCount(Book::class)} 
        |Affected row count of middle table 'BOOK_AUTHOR_MAPPING':
        |${result.affectedRowCount(Book::authors)}
    """.trimMargin()  
)

The finally generated SQL is:

delete from BOOK_AUTHOR_MAPPING
where BOOK_ID in(?, ?, ?, ?) 

```
delete from BOOK
where ID in(?, ?, ?, ?)
```

Dissociate Mode

From the above discussion we can see that the delete command may cause data in many-to-many association middle tables to be deleted, which is a relatively simple case.

For one-to-one or one-to-many associations directly based on foreign keys, the situation to be handled is a little more complex.

Java
Kotlin

DeleteResult result = sqlClient
    .getEntities()
    .delete(BookStore.class, 1L);

System.out.println(

    "Affected row count: " +
            result.getTotalAffectedRowCount() +

            "\nAffected row count of table 'BOOK_STORE': " +
            result.getAffectedRowCount(AffectedTable.of(BookStore.class)) +

            "\nAffected row count of table 'BOOK': " + 
            result.getAffectedRowCount(AffectedTable.of(Book.class)) +

            "\nAffected row count of middle table 'BOOK_AUTHOR_MAPPING': " +
            result.getAffectedRowCount(AffectedTable.of(BookProps.AUTHORS))
);

val result = sqlClient
    .entities
    .delete(BookStore::class, 1L)
println(  
    """Affected row count:
        |${result.totalAffectedRowCount}
        |Affected row count of table 'BOOK_STORE':
        |${result.affectedRowCount(BookStore::class)}
        |Affected row count of table 'BOOK':
        |${result.affectedRowCount(Book::class)}
        |Affected row count of middle table 'BOOK_AUTHOR_MAPPING':
        |${result.affectedRowCount(Book::authors)} 
    """.trimMargin()
)

This code deletes a BookStore object.

Because the BookStore object has a one-to-many association BookStore.books, if the object being deleted already has some associated objects in the database, Jimmer will discard those objects.

The one-to-many association BookStore.books is not a mapping based on a middle table, but a mapping based on a foreign key. How will Jimmer discard those Book objects?

Unlike JPA, Jimmer does not allow direct use of @OneToMany for association mapping. @OneToMany must use the mappedBy property. See @OneToMany to learn more.

This means that through the one-to-many association BookStore.books we can certainly find the corresponding many-to-one association Book.store.

Next, Jimmer will refer to the @OnDissociate annotation on the many-to-one association property Book.store.

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.

Dissociation Examples

If the foreign key corresponding to Book.store is configured by the @OnDissociate annotation as SET_NULL, the following SQL is executed:
```
update BOOK set STORE_ID = null where STORE_ID in(?)
```
where the parameter is the id of the deleted object. Thus, the foreign keys of these discarded objects are set to null.
Otherwise, first execute:
```
select ID from BOOK where STORE_ID in(?) 
```
where the parameter is the id of the deleted object. Thus, the ids of these discarded objects are obtained.

If the query does not return any data, skip the subsequent steps.
- If the foreign key corresponding to Book.store is configured by @OnDissociate as DELETE, use the new delete command to delete these discarded objects. This is actually the automatic recursive execution capability of the delete command.
- Otherwise, throw an exception.

The above discussed situations all require the developer to use the @OnDissociate annotation on the Book.store property.

However, you can also choose not to use the @OnDissociate annotation, but dynamically specify the dissociateAction configuration for the delete command.

Java
Kotlin

DeleteResult result = sqlClient
    .getEntities()
    .deleteCommand(BookStore.class, 1L)
    .configure(it ->
            it
                .setDissociateAction(
                    BookProps.STORE,
                    DissociateAction.SET_NULL
                )
    )
    .execute();

val result = sqlClient
    .entities
    .delete(BookStore::class, 1L) {
        setDissociateAction(
            Book::store,
            DissociateAction.SET_NULL  
        )
    }

Here, calling the command's setDissociateAction method dynamically has the same effect as using the @OnDissociate annotation statically on the Book.store property and specifying cascade delete.

info

If the last parameter of the setDissociateAction method is DissociateAction.SET_NULL, the associated property being set must be nullable, otherwise it will cause an exception.
If dissociation rules are configured dynamically for the save command and also configured statically in the entity interface through the @OnDissociate annotation, the dynamic configuration takes precedence.

Basic Concepts​

Logical Delete​

Physical Delete​

Basic Usage​

Dissociate Mode​

Dissociation Examples​