如何用Java检测数据库更新及触发无关联模块2自动执行

Great question! Let's break this down into two clear parts and walk through practical solutions that fit your decoupled module architecture (where Module 1 and 2 only share the database as a dependency).

1. Trigger Module 2 Automatically When Database Data Updates #

Since your modules have no direct link, we need event-driven or indirect notification mechanisms that keep them decoupled. Here are the most reliable approaches:

- Database Triggers + Message Queue #

This is a tried-and-true decoupled pattern:

1. Create a database trigger on your target table that activates on INSERT/UPDATE/DELETE events.
2. The trigger sends a lightweight notification (e.g., updated record ID, change type) to a message queue like Kafka, RabbitMQ, or Redis Pub/Sub.
3. Module 2 runs a listener that subscribes to the queue, and triggers its business logic the moment a new message arrives.

Example simplified MySQL trigger (focused on sending a notification):

DELIMITER //
CREATE TRIGGER after_record_modification
AFTER INSERT OR UPDATE ON your_target_table
FOR EACH ROW
BEGIN
  -- Call a stored procedure or UDF to send a message to your queue
  CALL send_change_notification(NEW.id, CASE WHEN NEW.id = OLD.id THEN 'UPDATE' ELSE 'INSERT' END);
END //
DELIMITER ;

Pro tip: Avoid heavy logic inside triggers—keep it focused on notification to prevent blocking database operations.

- Change Data Capture (CDC) Tools #

CDC tools like Debezium, Canal, or Maxwell’s Daemon capture database binlog (MySQL) or WAL (PostgreSQL) events without requiring triggers. They convert these low-level changes into structured messages and push them to your queue or directly to Module 2.

• This is more scalable and less intrusive than triggers, as it doesn’t add logic to your database.
• Module 2 can subscribe to the CDC stream and react instantly to changes.

- Polling (Simpler, Lower Real-Time Requirements) #

If strict real-time isn’t necessary, Module 2 can periodically check the database for changes:

• Add an updated_at timestamp column or version integer column to your table.
• Module 2 tracks the last updated_at/version it processed, then queries for records where updated_at > last_check_time or version > last_version.
• This is trivial to implement but has latency tied to your poll interval.

2. Detect Database Updates in Java #

Here are concrete Java implementations aligned with the above strategies:

- Polling with Timestamp/Version #

This is the easiest method if you don’t need instant notifications:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

public class DbUpdateChecker {
    private static LocalDateTime lastCheckTime = LocalDateTime.MIN;

    public static void checkForUpdates() {
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_db", "db_user", "db_pass")) {
            String query = "SELECT MAX(updated_at) AS latest_update FROM your_target_table";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query)) {
                if (rs.next()) {
                    LocalDateTime latestUpdate = rs.getTimestamp("latest_update").toLocalDateTime();
                    if (latestUpdate.isAfter(lastCheckTime)) {
                        System.out.println("Database updated! Triggering Module 2 logic...");
                        // Insert Module 2's business logic here
                        lastCheckTime = latestUpdate;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // Check for updates every 30 seconds
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(DbUpdateChecker::checkForUpdates, 0, 30, TimeUnit.SECONDS);
    }
}

- PostgreSQL LISTEN/NOTIFY (Real-Time Native Notification) #

For PostgreSQL, you can use the built-in LISTEN/NOTIFY mechanism to get direct real-time updates in Java:

1. First, create a trigger that sends a notification on data changes:

CREATE TRIGGER notify_record_change
AFTER INSERT OR UPDATE ON your_target_table
FOR EACH ROW EXECUTE FUNCTION pg_notify('record_updates', NEW.id::text);

2. Then, implement the Java listener:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgresNotificationListener {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/your_db", "db_user", "db_pass")) {
            // Subscribe to the 'record_updates' channel
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("LISTEN record_updates");
            }

            // Listen for notifications indefinitely
            while (true) {
                conn.createStatement().execute("SELECT 1"); // Keep connection alive
                java.sql.SQLWarning warning = conn.getWarnings();
                if (warning != null && warning.getMessage().startsWith("NOTIFY")) {
                    System.out.println("Database updated! Notification: " + warning.getMessage());
                    // Trigger Module 2's logic here
                    conn.clearWarnings();
                }
                Thread.sleep(1000);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

- Debezium CDC Client (Real-Time, Cross-Database) #

Use Debezium’s Java client to consume database change events directly, no polling required:

import io.debezium.engine.DebeziumEngine;
import io.debezium.engine.RecordChangeEvent;
import io.debezium.engine.format.Json;

import java.util.Properties;

public class DebeziumUpdateDetector {
    public static void main(String[] args) {
        Properties props = new Properties();
        props.setProperty("name", "module2-connector");
        props.setProperty("connector.class", "io.debezium.connector.mysql.MySqlConnector");
        props.setProperty("offset.storage", "org.apache.kafka.connect.storage.FileOffsetBackingStore");
        props.setProperty("offset.storage.file.filename", "/tmp/module2-offsets.dat");
        props.setProperty("database.hostname", "localhost");
        props.setProperty("database.port", "3306");
        props.setProperty("database.user", "db_user");
        props.setProperty("database.password", "db_pass");
        props.setProperty("database.server.id", "1002");
        props.setProperty("database.server.name", "your-db-server");
        props.setProperty("database.include.list", "your_db");
        props.setProperty("table.include.list", "your_db.your_target_table");

        DebeziumEngine<RecordChangeEvent<String>> engine = DebeziumEngine.create(Json.class)
            .using(props)
            .notifying(record -> {
                System.out.println("Detected database change: " + record.value());
                // Trigger Module 2's business logic here
            })
            .build();

        // Run the engine in a background thread
        new Thread(engine).start();
    }
}

内容的提问来源于stack exchange，提问作者Think Smart