Logical Deletion

info

Readers cannot find anything related to global filters in this doc, because the filter required for logical deletion is built-in and hidden by Jimmer.

Mapping

Logical deletion, also known as soft deletion, means data is not actually deleted from the database. Instead, data is hidden to give the illusion of deletion. This leaves room for recovering from accidental operations.

The mapping related to logical deletion is introduced in great detail in Mapping / Advanced Mapping / Logical Deletion, so all the details are not repeated here, just a brief recap:

Java

Book.java

@Entity
public interface Book {

    @LogicalDeleted("true") 
    boolean isDeleted();
    
    ...other code omitted...
}

Kotlin

Book.kt

@Entity
interface Book {

    @LogicalDeleted("true")
    val isDeleted: Boolean

    ...other code omitted...
}

Usage

Filter root entity

Java

BookTable table = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .createQuery(table)
    .select(table)
    .execute(); 

Kotlin

val books = sqlClient
    .createQuery(Book::class) {
        select(table)
    }
    .execute()

The generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.DELETED,
    tb_1_.STORE_ID
from BOOK tb_1_
where
    /* highlight-next-line */  
    tb_1_.DELETED <> ? /* true */

Filter associated objects

Java

AuthorTable author = Tables.AUTHOR_TABLE;

List<Author> authors = sqlClient
    .createQuery(author) 
    .select(
        author.fetch(
            Fetchers.AUTHOR_FETCHER
                .allScalarFields()
                .books(
                    Fetchers.BOOK_FETCHER
                        .allScalarFields()
                )
        )
    )
    .execute();

Kotlin

val authors = sqlClient
    .createQuery(Author::class) {
        select(
            table.fetchBy {
                allScalarFields()
                books {
                    allScalarFields()
                }
            }
        )
    }
    .execute() 

Without caching enabled, this generates two SQLs:

• Query root entity

  select
      tb_1_.ID,
      tb_1_.FIRST_NAME,
      tb_1_.LAST_NAME,
      tb_1_.GENDER
  from AUTHOR tb_1_

• Query associated objects, apply logical deletion filter

  select
      tb_2_.AUTHOR_ID,
      tb_1_.ID,
      tb_1_.NAME,
      tb_1_.EDITION,
      tb_1_.PRICE 
  from BOOK tb_1_
  inner join BOOK_AUTHOR_MAPPING tb_2_
      on tb_1_.ID = tb_2_.BOOK_ID
  where
          tb_2_.AUTHOR_ID in (
              ? /* 1 */, ? /* 2 */, ? /* 3 */, ? /* 4 */, ? /* 5 */  
          )
      and
          /* highlight-next-line */
          tb_1_.DELETED <> ? /* true */

Ignore Logical Deletion Filter

Java

BookTable table = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .filters(cfg -> { ❶
        cfg.setBehavior(LogicalDeletedBehavior.IGNORED); ❷
    })
    .createQuery(table)
    .select(table)
    .execute();

Kotlin

val books = sqlClient
    .filters { ❶
        cfg.setBehavior(LogicalDeletedBehavior.IGNORED)
    } 
    .createQuery(table)
    .select(table)
    .execute()

• ❶ Adjust filter config without affecting the current sqlClient, create a new temporary sqlClient

• ❷ Ignore soft deletion flag

This time, the generated SQL no longer contains:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.DELETED,
    tb_1_.STORE_ID
from BOOK tb_1_

Reverse Logical Deletion Filter

Java

BookTable table = Tables.BOOK_TABLE;

List<Book> books = sqlClient
    .filters(cfg -> { ❶
        cfg.setBehavior(LogicalDeletedBehavior.REVERSED); ❷
    })
    .createQuery(table)
    .select(table)
    .execute(); 

Kotlin

val books = sqlClient
    .filters { ❶
        cfg.setBehavior(LogicalDeletedBehavior.REVERSED) 
    }
    .createQuery(table)
    .select(table) 
    .execute()

• ❶ Adjust filter config without affecting the current sqlClient, create a new temporary sqlClient

• ❷ Reverse the soft deletion flag, i.e. query deleted data

Executing again, the generated SQL is:

select
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE, 
    tb_1_.DELETED,
    tb_1_.STORE_ID
from BOOK tb_1_
where
    /* highlight-next-line */
    tb_1_.DELETED = ? /* true */ 

This time the filter condition is tb_1_.DELETED = true, which is the exact opposite of the default filtering rule - it queries already deleted data.