Trigger

Jimmer supports triggers that allow users to listen for database changes.

tip

Triggers can notify changes not only to objects, but also to associations.

Trigger Types

Trigger Classification

• BinLog Trigger

  This is the default trigger type. It does not affect the SQL generated by Jimmer itself, has higher performance, is triggered after transaction commit, can listen to database changes caused by any reason, including data changes not caused by Jimmer API.

  However, it requires the database to support binlog/CDC.

• Transaction Trigger

  This trigger does not requires the database to support binlog/CDC, it is triggered before transaction commit.

  However, It can only listener to the data changes caused by current Jimmer application, and will generate additional query statements during the modification process to simulate the trigger, which has some impact on modification performance.

  info

  Its working mechanism is similar to Alibaba Seata's AT mode

The differences between the two triggers are as follows:

	BinLog Trigger	Transaction Trigger
Trigger Time	After transaction commit	Before transaction commit
Performance	High	Low
Database changes listened	Changes caused by any reason	Only changes caused by calling current application's Jimmer API
Database Requirements	Support and enable binlog/CDC	No requirements
Working Principle	Use third-party technology to push database binlog changes to message queue, Jimmer application listens to message queue	Any Jimmer modification API automatically implants additional SQL queries to find data changes, similar to Alibaba Seata's AT mode

Apart from the differences in this table, the notification data provided by the two triggers to the user is exactly the same.

Recommended Usage

• BinLog Trigger

  The BinLog trigger is triggered after transaction commit, facing the immutable facts.

  That is, the BinLog trigger has no impact on the original transaction, and is allowed to perform time-consuming operations. So it is suitable to execute multiple tasks in its handling logic, especially these tasks:

  • Cache invalidation
  • Heterogeneous data source synchronization
  • Send messages to other microservices asynchronously

• Transaction Trigger

  The Transaction trigger is triggered before transaction commit, and its handling logic is injected directly into the current transaction.

  If an exception occurs in its event handling logic, it will cause the current transaction to fail; if its handling logic cannot complete quickly, it will cause the current transaction to hold resources for a long time.

  Therefore, the Transaction trigger is suitable for appending more modification behaviors in the current transaction without compromising atomicity.

  It is suitable for implementing business logic with strong generality through additional modifications when the database changes.

Setting Trigger Type

Concept

Before discussing setting the trigger type, let's see how developers use triggers:

• sqlClient.getTriggers() or sqlClient.getTriggers(false): Returns BinLog trigger first, if not exists, returns Transaction trigger.

• sqlClient.getTriggers(true): Explicitly returns Transaction trigger, throws exception if not exists.

To affect the trigger type that can be obtained by sqlClient.getTriggers() afterwards, you need to specify TriggerType when building SqlClient.

TriggerType has three possible values:

• BINLOG_ONLY:

  Only supports BinLog trigger, this is the default configuration.

  • sqlClient.getTriggers() and sqlClient.getTriggers(false) return BinLog trigger object
  • sqlClient.getTriggers(true) will throw an exception, cannot return Transaction trigger object

• TRANSACTION_ONLY:

  Only supports Transaction trigger. No matter what the parameter of sqlClient.getTriggers is, it will return the same Transaction trigger object.

• BOTH:

  Supports both BinLog trigger and Transaction trigger.

  • sqlClient.getTriggers() and sqlClient.getTriggers(false) return BinLog trigger object
  • sqlClient.getTriggers(true) returns Transaction trigger object

Here is a table to compare the three cases:

Trigger Type	getTriggers(false)	getTriggers(true)
BINLOG_ONLY	Dedicated binlog triggers object	Throw exception
TRANSACTION_ONLY	Shared transaction triggers object
BOTH	Dedicated binlog triggers object	Dedicated transaction triggers object

Q & A

• Q: Why is BINLOG_ONLY the default mode?

  A: Transaction triggers implant additional queries in all save operations to simulate triggers, which impacts performance, so it is disabled by default.

• Q: In TRANSACTION_ONLY mode, why do the two different trigger APIs share the same object?

  A: Jimmer's built-in cache consistency strategy must be driven by sqlClient.getTriggers(false), which developers cannot change.

  The purpose is to allow cache consistency maintenance work to not affect the modification transaction, and only start execution after transaction commit. Thus, the original transaction will not be elongated and can end quickly to release lock resources.

  However, not all database products support binlog/CDC. In this case, getTriggers(false) returns the transaction trigger object, masquerading as the BinLog trigger object, taking over cache consistency maintenance work that should have been handled by the BinLog trigger.

  That is to say, TRANSACTION_ONLY is designed for databases that do not support binlog/CDC, this is the only reason to use this mode.

• Q: In BOTH mode, there are two different trigger API objects, does this mean there are two chances to handle any modification?

  A: Yes, and this is an important feature.

  Unlike Jimmer's built-in cache consistency mechanism which must be driven by sqlClient.getTriggers(false), the user's business code does not have this limitation. Developers can freely decide whether an event handling logic should be registered to sqlClient.getTriggers(false) or sqlClient.getTriggers(true), or registered to both at the same time.

  • If the developer's event handling logic contains some additional modifications that must participate in the atomic scope of the current transaction, sqlClient.getTriggers(true) should be chosen.

  • If the developer's event handling logic does not need to participate in the current transaction, sqlClient.getTriggers(false) should be chosen to allow the current transaction to end as soon as possible and release lock resources as soon as possible.

  • If the developer's event handling logic contains both of the above cases, it should be split into two and registered to the two triggers respectively.

    caution

    If the developer registers the same event callback for two types of triggers, then this callback will indeed be executed twice each time an event is notified.

    At this point, distinguishing between the two calls is very important. The parameter of the callback method is an object that can obtain the JDBC connection object. Whether its value is null can be used as a criterion to distinguish between the two:

    • Non-null: First callback, caused by transaction trigger
    • Null: Second callback, caused by binlog trigger

• Q: For databases that do not support binlog/CDC, isn't it impossible to do cache cleanup after transaction commit?

  A: Not exactly, it can be achieved if the developer is willing to optimize.

  Admittedly, such databases cannot support binlog triggers, and using transaction triggers to get data change notifications within the transaction lifetime is the only feasible method.

  However, it is not necessary to perform cache cleanup immediately after receiving the notification, because the cache cleanup work on remote caches like redis has network communication costs and risks of communication failure. Doing so will elongate or even fail the local transaction.

  Jimmer's cache system supports custom CacheOperator. By customizing CacheOperator, users can override the cache deletion behavior, record the cache deletion tasks but not execute them immediately, and perform the actual cache cleanup after the transaction is committed.

  • Unreliable approach

    1. Customize CacheOperator and do not immediately clear the cache, but use ThreadLocal to record the keys of the caches to be deleted.
    2. Collectively clean up the cache in Spring's AfterCommit event.

  • Reliable approach

    1. Customize CacheOperator and do not immediately clear the cache, but use a local event table in the same database to record the keys of the caches to be deleted.
    2. In Spring's AfterCommit event, take data from the local event table, clean up the cache, and if successful, delete the data in the local event table.
    3. Use a polling service to cover failures in step 2.
    tip

    Fortunately, for trigger type TRANSACTION_ONLY, Jimmer's Spring Boot Starter has already implemented this. Please refer to Cache Consistency/Transaction Trigger

Using Jimmer Spring Boot Starter

If using the Jimmer Spring Boot Starter, setting the trigger type is very simple.

Just add a configuration in application.properties or application.yml. Its name is jimmer.trigger-type, and its value is BINLOG_ONLY | TRANSACTION_ONLY | BOTH.

Not Using Jimmer Spring Boot Starter

Java

JSqlClient sqlClient = JSqlClient
    .newBuilder()
    .setTriggerType(TriggerType.BOTH)
    ...other config omitted...
    .build();

Kotlin

javax.sql.DataSource dataSource = ...;

val sqlClient = newKSqlClient {
    setTriggerType(TriggerType.BOTH)
    ...other config omitted...
}

BinLog Trigger Development Work

Unlike Transaction triggers, BinLog triggers require third-party technologies to push database binlog changes to the message queue, and applications need to listen to the message queue.

Therefore, just specifying TriggerType as BINLOG_ONLY (default behavior) or BOTH when building the SqlClient object is not enough.

There are many choices for the message queue, such as Kafka and RabbitMQ; there are also many choices for third-party technologies to push database binlog incrementally to the message queue, such as MaxWell, Debezium, Canal and DataBus.

Jimmer does not restrict such choices. But to simplify the discussion, this article assumes Kafka is used as the message queue, and Maxwell (For MySQL) and Debezium (For postgres) are used as the push technologies.

caution

Because Debezium itself is a kafka-connector, using Debezium inevitably leads to Kafka being the message queue.

Create External Environment

Before development, the environment needs to be installed first, including the database, Kafka, and Maxwell or Debezium.

• Maxwell

  1. Enter the local directory corresponding to jimmer-examples/env-with-cache/maxwell after git clone.

  2. Execute

     bash ./install.sh

• Debezium

  1. Enter the local directory corresponding to jimmer-examples/env-with-cache/debezium after git clone.

  2. Execute

     bash ./install.sh

Listen to Message Queue

Whether choosing different databases (MySQL or Postgres), or choosing different push technologies (Maxwell or Debezium), there will be differences in the listening code.

But in any case, the user code is quite similar, divided into the following 4 steps:

1. Listen to the message queue and get the message body string.

2. Use ObjectMapper.readTree for weakly typed parsing of the message text.

   info

   So-called weakly typed parsing means the resulting type is JsonNode, unrelated to business system types.

3. Observe the content of the JsonNode and extract:

   • Table name, referred to as tableName

   • The sub-JsonNode of the old data before modification, referred to as oldJsonNode

     For insert operations, oldJsonNode is null

   • The sub-JsonNode of the new data after modification, referred to as newJsonNode

     For delete operations, newJsonNode is null

   info

   The differences in listening code caused by different choices of database and push technology are reflected in this step. However, it is not difficult after observing the message content for inserts, updates, and deletes.

4. Call JSqlClient.getBinLog().accept or KSqlClient.binLog.accept with tableName, oldJsonNode and newJsonNode.

The following examples demonstrate MySQL + Maxwell and Postgres + Debezium respectively.

• MySQL + Maxwell

  For MySQL + Maxwell, the message format is typically like:

  {
      "database":"jimmer_demo",
      "table":"book",
      "type":"update",
      "ts":1688592724, 
      "xid":11790,
      "commit":true,
      "data":{
          "id":1,
          "name":"Learning GraphQL",
          "edition":1,
          "price":50,
          "store_id":1,
          "tenant":"a",
          "created_time":"2023-07-05 20:21:00",
          "modified_time":"2023-07-05 20:21:00"
      },
      "old":{
          "store_id":2
      }
  }

  After a little observation (it is recommended to look at the messages for insert, update, and delete), it is not difficult to implement the following message listening code:

  Java

  MaxwellListener.java

  @Component
  public class MaxwellListener {
  
      private static final ObjectMapper MAPPER = new ObjectMapper();
  
      private final Caches caches;
  
      public MaxwellListener(JSqlClient sqlClient) {
          this.caches = sqlClient.getCaches();
      }
  
      @KafkaListener(topics = "maxwell")
      public void onMaxwellEvent(
              String json,
              Acknowledgment acknowledgment
      ) throws JsonProcessingException {
          JsonNode node = MAPPER.readTree(json);
          String tableName = node.get("table").asText();
          String type = node.get("type").asText();
          JsonNode data = node.get("data");
          switch (type) {
              case "insert":
                  binLog.accept(tableName, null, data);
                  break;
              case "update":
                  binLog.accept(tableName, node.get("old"), data);
                  break;
              case "delete":
                  binLog.accept(tableName, data, null);
                  break;
          }
          acknowledgment.acknowledge();
      }
  }

  Kotlin

  MaxwellListener.kt

  @Component
  class MaxwellListener(sqlClient: KSqlClient) {
  
      private val caches: KCaches = sqlClient.caches
  
      @KafkaListener(topics = ["maxwell"])
      fun onMaxwellEvent(
          json: String,
          acknowledgment: Acknowledgment
      ) {
          val node = MAPPER.readTree(json)
          val tableName = node["table"].asText()
          val type = node["type"].asText()
          val data = node["data"]
          when (type) {
              "insert" ->
                  binLog.accept(tableName, null, data)
              "update" ->
                  binLog.accept(tableName, node["old"], data) 
              "delete" ->
                  binLog.accept(tableName, data, null)
          }
          acknowledgment.acknowledge()
      }
  
      companion object {
          private val MAPPER = ObjectMapper()
      }
  }

• Postgres + Debezium

  For Postgres + Debezium, the message format is typically:

  {
      "before": {
          "id": 10,
          "name": "GraphQL in Action",
          "edition": 1,
          "price": "H0A=", 
          "store_id": 1,
          "tenant": "b",
          "created_time": 1688590805971294,
          "modified_time": 1688590805971294
      },
      "after": {
          ...omitted...
      },
      "source": {
          "table": "book",
          ...omitted... 
      },
      ...omitted...
  }

  We find some difficulties here, not all data can be directly recognized and converted by Jimmer's BinLog mapping mechanism:

  • Properties of BigDecimal type (NUMERIC(M[, D]) in Postgres) like Book.price are displayed as Base64 encoded (H0A= in this example).

    This Base64 string is the info processed by org.apache.kafka.connect.data.Decimal

  • Properties of LocalDateTime type (TIMESTAMP in Postgres) are displayed as numbers

  info

  Debezium's documentation will explain in detail how its various connectors process certain special data, such as how pg-connector handles decimal.

  The various connectors in Debezium also provide rich configurations, some of which can be used to change the default data handling method, such as changing how decimal data is handled, to avoid similar problems.

  However, Debezium's connectors usually serve all systems, and will not deliberately "pamper" a particular application. We cannot assume its configuration will always ensure output that Jimmer can understand directly.

  The examples that come with Jimmer deliberately do not configure the Debezium connector, letting it output kafka-connector specific data to demonstrate how Jimmer solves this problem, same as this article.

  Java

  DebeziumCustomizer.java

  package ...omitted...;
  
  import org.apache.kafka.connect.data.Decimal;
  import org.apache.kafka.connect.data.Schema;
  import org.babyfish.jimmer.sql.runtime.Customizer;
  
  ...other imports omitted...
  
  @Component
  public class DebeziumCustomizer implements Customizer {
  
      private static final Schema BOOK_PRICE_SCHEMA =
              // Postgres `BOOK.PRICE` is `NUMERIC(10, 2)`, precision is 2
              Decimal.schema(2);
  
      @Override
      public void customize(JSqlClient.Builder builder) {
          
          builder.setBinLogPropReader( ❶
                  LocalDateTime.class,
                  (prop, jsonNode) -> {
                      return Instant.ofEpochMilli(
                              jsonNode.asLong() / 1000
                      ).atZone(ZoneId.systemDefault()).toLocalDateTime();
                  }
          );
  
          builder.setBinLogPropReader(
                  BookProps.PRICE, ❷
                  (prop, jsonNode) -> {
                      byte[] bytes = Base64.getDecoder().decode(jsonNode.asText());
                      return Decimal.toLogical(BOOK_PRICE_SCHEMA, bytes);
                  }
          );
      }
  }

  Kotlin

  DebeziumCustomizer.kt

  package ...omitted...
  
  import org.apache.kafka.connect.data.Decimal
  import org.apache.kafka.connect.data.Schema
  import org.babyfish.jimmer.sql.kt.cfg.KCustomizer
  
  ...other imports omitted...
  
  @Component
  class DebeziumCustomizer : KCustomizer {
  
      override fun customize(dsl: KSqlClientDsl) {
  
          dsl.setBinLogPropReader(
              LocalDateTime::class ❶
          ) { _, jsonNode ->
              Instant.ofEpochMilli(
                  jsonNode.asLong() / 1000
              ).atZone(ZoneId.systemDefault()).toLocalDateTime()
          }
  
          dsl.setBinLogPropReader(
              Book::price ❷
          ) { _, jsonNode ->
              Decimal.toLogical(
                  BOOK_PRICE_SCHEMA,
                  Base64.getDecoder().decode(jsonNode.asText())
              )
          }
      }
  
      companion object {
          private val BOOK_PRICE_SCHEMA =
              // Postgres `BOOK.PRICE` is `NUMERIC(10, 2)`, precision is 2
              Decimal.schema(2)
      }
  }

  setBinLogPropReader allows developers to customize how to parse properties in the message that cannot be directly recognized, with two usages:

  • ❶ Given the return type, specify how a type of properties should be parsed

  • ❷ Precisely define how a certain property should be parsed

  After solving these problems, the message listening code is easy to implement:

  Java

  DebeziumListener.java

  @Component
  public class DebeziumListener {
  
      private static final ObjectMapper MAPPER = new ObjectMapper();
  
      private final BinLog binLog;
  
      public DebeziumListener(JSqlClient sqlClient) {
          this.binLog = sqlClient.getBinLog();
      }
  
      @KafkaListener(topicPattern = "debezium\\..*") 
      public void onDebeziumEvent(
              @Payload(required = false) String json,
              Acknowledgment acknowledgment
      ) throws JsonProcessingException {
          if (json != null) { // Debezium sends empty msg after delete msg
              JsonNode node = MAPPER.readTree(json);
              String tableName = node.get("source").get("table").asText();
              binLog.accept(
                      tableName,
                      node.get("before"),
                      node.get("after")
              );
          }
          acknowledgment.acknowledge();
      }
  }

  Kotlin

  DebeziumListener.kt

  @Component
  class DebeziumListener(sqlClient: KSqlClient) {
  
      private val binLog: BinLog = sqlClient.binLog
  
      @KafkaListener(topicPattern = """debezium\..*""")
      fun onDebeziumEvent(
          @Payload(required = false) json: String?,
          acknowledgment: Acknowledgment
      ) {
          if (json !== null) { 
              val node: JsonNode = MAPPER.readTree(json)
              val tableName: String = node["source"]["table"].asText()
              binLog.accept(
                  tableName,
                  node["before"],
                  node["after"]
              )
          }
          acknowledgment.acknowledge()
      }
  
      companion object {
          private val MAPPER = ObjectMapper()
      }
  }

Usage Examples

If using BinLog trigger, please enable it first as described above.

Register Handler Logic

• Using Jimmer Spring Boot Starter

  If using the Jimmer Spring Boot Starter, trigger events will be sent as Spring events.

  So use @org.springframework.context.event.EventListener to handle Spring events:

  Java

  DatabaseListener.java

  @Component
  public class DatabaseListener {
  
      @EventListener
      public void onEntityChanged(EntityEvent<?> e) {
          if (e.getImmutableType().getJavaClass() == Book.class) {
              System.out.println("The object `Book` is changed");
              System.out.println("\told: " + e.getOldEntity());
              System.out.println("\tnew: " + e.getNewEntity());
          }
      }
  
      @EventListener
      public void onAssociationChanged(AssociationEvent e) {
          if (e.isChanged(BookProps.STORE)) {
              System.out.println("The many-to-one association `Book.store` is changed");
              System.out.println("\tbook id: " + e.getSourceId());
              System.out.println("\tdetached book store id: " + e.getDetachedTargetId());
              System.out.println("\tattached book store id: " + e.getAttachedTargetId());
          } else if (e.isChanged(BookStoreProps.BOOKS)) {
              System.out.println("The one-to-many association `BookStore.books` is changed");
              System.out.println("\tbook store id: " + e.getSourceId());
              System.out.println("\tdetached book id: " + e.getDetachedTargetId());
              System.out.println("\tattached book id: " + e.getAttachedTargetId());
          }
      }
  }

  Kotlin

  DatabaseListener.kt

  @Component
  class DatabaseListener {
  
      @EventListener
      fun onEntityChanged(e: EntityEvent<*>) {
          if (e.ImmutableType.javaClass == Book::class.java) {
              println("The object `Book` is changed")
              println("\told: ${e.oldEntity}")
              println("\tnew: ${e.newEntity}")
          }
      }
  
      @EventListener
      fun onAssociationChanged(e: AssociationEvent) {
          if (e.isChanged(Book::store)) {
              println("The many-to-one association `Book.store` is changed")
              println("\tbook id: ${e.sourceId}")
              println("\tdetached book store id: ${e.detachedTargetId}")
              println("\tattached book store id: ${e.attachedTargetId}")
          } else if (e.isChanged(BookStore::books)) {
              println("The one-to-many association `BookStore.books` is changed")
              println("\tbook store id: ${e.sourceId}")
              println("\tdetached book id: ${e.detachedTargetId}")
              println("\tattached book id: ${e.attachedTargetId}")
          }
      }
  }

• Using Low Level API

  If not using the Jimmer Spring Boot starter, need to manually register event handler code:

  Java

  sqlClient.getTriggers().addEntityListener(Book.class, e -> {
      System.out.println("The object `Book` is changed");
      System.out.println("\told: " + e.getOldEntity());
      System.out.println("\tnew: " + e.getNewEntity());
  });
  sqlClient.getTriggers().addAssociationListener(BookProps.STORE, e -> {
      System.out.println("The many-to-one association `Book.store` is changed");
      System.out.println("\tbook id: " + e.getSourceId());
      System.out.println("\tdetached book store id: " + e.getDetachedTargetId());
      System.out.println("\tattached book store id: " + e.getAttachedTargetId());
  });
  sqlClient.getTriggers().addAssociationListener(BookStoreProps.BOOKS, e -> {
      System.out.println("The one-to-many association `BookStore.books` is changed");
      System.out.println("\tbook store id: " + e.getSourceId());
      System.out.println("\tdetached book id: " + e.getDetachedTargetId());
      System.out.println("\tattached book id: " + e.getAttachedTargetId());
  });

  Kotlin

  sqlClient.triggers.addEntityListener(Book::class) {
      println("The object `Book` is changed")
      println("\told: ${e.oldEntity}")
      println("\tnew: ${e.newEntity}")
  }
  sqlClient.triggers.addAssociationListener(Book::store) {
      println("The many-to-one association `Book.store` is changed")
      println("\tbook id: ${e.sourceId}")
      println("\tdetached book store id: ${e.detachedTargetId}")
      println("\tattached book store id: ${e.attachedTargetId}")
  }
  sqlClient.triggers.addAssociationListener(BookStore::books) {
      println("The one-to-many association `BookStore.books` is changed")
      println("\tbook store id: ${e.sourceId}")
      println("\tdetached book id: ${e.detachedTargetId}")
      println("\tattached book id: ${e.attachedTargetId}") 
  }

  Where sqlClient.getTriggers() or sqlClient.triggers is used to register handler logic to the default trigger.

  Can also replace sqlClient.getTriggers() or sqlClient.triggers with sqlClient.getTriggers(true) to register handler logic to the Transaction trigger.

Experience Triggers

Now we trigger events to experience triggers.

The BinLog trigger can listen to database changes caused by any reason, even if the database is modified by bypassing the application using any other means.

For example, you can directly execute:

update BOOK set STORE_ID = 2 where ID = 7;

However, to send events to the Transaction trigger, the database must be modified through Jimmer's API, for example:

Java

BookTable table = Tables.BOOK_TABLE;
sqlClient
    .createUpdate(book)
    .set(table.store().id(), 2L)
    .where(table.id().eq(7L))
    .execute();

Kotlin

sqlClient
    .createUpdate(Book::class) {
        set(table.store.id, 2L)
        where(table.id eq 7L)
    }
    .execute()

The output is:

The object `Book` is changed ❶
	old: {"id":7,"name":"Programming TypeScript","edition":1,"price":47.50,"store":{"id":1}}
	new: {"id":7,"name":"Programming TypeScript","edition":1,"price":47.50,"store":{"id":2}}
The many-to-one association `Book.store` is changed ❷  
	book id: 7
	detached book store id: 1
	attached book store id: 2
The one-to-many association `BookStore.books` is changed ❸
	book store id: 1
	detached book id: 7
	attached book id: null
The one-to-many association `BookStore.books` is changed ❹
	book store id: 2
	detached book id: null
	attached book id: 7

Where:

• ❶ Represents object change event

• ❷, ❸ and ❹ Represent association change events

tip

Jimmer triggers can not only simply convert table changes into object change events, but also convert foreign key changes and middle table changes into association change events.