基于Spring与MyBatis实现Oracle数据库列更新监听需求

Hey there! Let’s walk through building a solution to monitor specific column updates in an Oracle table, catch those changes in your Java app, and trigger corresponding business methods—mirroring how Java’s WatchService tracks directory changes. We’ll use Spring and MyBatis, leaning into the core ideas from the classic DB listener pattern.

Two Main Approaches #

We’ve got two practical paths here, depending on your real-time needs:

• Polling-based monitoring: Simple to implement, great for low-to-medium frequency updates
• Oracle Native Database Change Notification (DCN): Near-real-time, ideal for latency-sensitive use cases

Let’s start with the polling approach—it’s the easiest to get up and running.

1. Polling-Based Monitoring (Beginner-Friendly) #

This works by periodically checking the database for changes, using a change log table and Spring scheduled tasks.

Step 1: Set Up Database Change Tracking #

First, add a change log table to record updates to your target column. This avoids scanning the entire table every time.

-- Create a change log table
CREATE TABLE column_change_log (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    target_table VARCHAR2(100) NOT NULL,
    changed_column VARCHAR2(100) NOT NULL,
    old_value VARCHAR2(2000),
    new_value VARCHAR2(2000),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_processed CHAR(1) DEFAULT 'N' CHECK (is_processed IN ('Y', 'N'))
);

Next, add a trigger to the target table that inserts a log entry when your specific column is updated:

-- Trigger to log updates to your target column
CREATE OR REPLACE TRIGGER trg_monitor_target_column
AFTER UPDATE OF your_target_column ON your_table_name
FOR EACH ROW
BEGIN
    INSERT INTO column_change_log (target_table, changed_column, old_value, new_value)
    VALUES ('your_table_name', 'your_target_column', :OLD.your_target_column, :NEW.your_target_column);
END;
/

Step 2: MyBatis Mapper for Change Logs #

Create a mapper to fetch unprocessed change logs and mark them as handled:

@Mapper
public interface ColumnChangeLogMapper {
    // Get all unprocessed change records
    List<ColumnChangeLog> getUnprocessedChanges();
    
    // Mark a log entry as processed
    void markChangeAsProcessed(@Param("logId") Long logId);
}

Corresponding MyBatis XML (ColumnChangeLogMapper.xml):

<mapper namespace="com.yourpackage.mapper.ColumnChangeLogMapper">
    <select id="getUnprocessedChanges" resultType="com.yourpackage.entity.ColumnChangeLog">
        SELECT id, target_table, changed_column, old_value, new_value, change_time
        FROM column_change_log
        WHERE is_processed = 'N'
        ORDER BY change_time ASC
    </select>

    <update id="markChangeAsProcessed">
        UPDATE column_change_log
        SET is_processed = 'Y'
        WHERE id = #{logId}
    </update>
</mapper>

Step 3: Spring Listener & Scheduled Task #

First, define a listener interface to decouple change detection from business logic:

public interface ColumnUpdateListener {
    void onColumnUpdated(ColumnChangeLog changeLog);
}

Implement the listener with your business logic (run methods based on the new column value):

@Component
public class TargetColumnUpdateListener implements ColumnUpdateListener {
    @Override
    public void onColumnUpdated(ColumnChangeLog changeLog) {
        String newValue = changeLog.getNewValue();
        
        // Execute methods based on the new column value
        switch (newValue) {
            case "ACTIVE":
                activateBusinessProcess();
                break;
            case "INACTIVE":
                deactivateBusinessProcess();
                break;
            default:
                handleDefaultCase();
        }
    }

    private void activateBusinessProcess() {
        // Your business logic here
        System.out.println("Triggering activation process...");
    }

    private void deactivateBusinessProcess() {
        // Your business logic here
        System.out.println("Triggering deactivation process...");
    }

    private void handleDefaultCase() {
        // Fallback logic
        System.out.println("Handling unknown column value...");
    }
}

Finally, create a scheduled task to poll the database and trigger the listener:

@Component
public class ColumnChangeMonitor {
    @Autowired
    private ColumnChangeLogMapper changeLogMapper;
    @Autowired
    private ColumnUpdateListener updateListener;

    // Poll every 5 seconds (adjust based on your needs)
    @Scheduled(fixedRate = 5000)
    public void checkForColumnChanges() {
        List<ColumnChangeLog> unprocessedChanges = changeLogMapper.getUnprocessedChanges();
        
        for (ColumnChangeLog log : unprocessedChanges) {
            try {
                updateListener.onColumnUpdated(log);
                changeLogMapper.markChangeAsProcessed(log.getId());
            } catch (Exception e) {
                // Handle errors (log, retry, etc.)
                System.err.println("Failed to process change log entry: " + log.getId());
                e.printStackTrace();
            }
        }
    }
}

Don’t forget to enable scheduling in your Spring Boot app:

@SpringBootApplication
@EnableScheduling
public class YourApplication {
    public static void main(String[] args) {
        SpringApplication.run(YourApplication.class, args);
    }
}

2. Real-Time Monitoring with Oracle DCN #

If you need near-instant updates, use Oracle’s Database Change Notification (DCN) to get push notifications when the column changes.

Step 1: Grant Database Permissions #

First, give your DB user the necessary permissions:

GRANT CHANGE NOTIFICATION TO your_db_username;
GRANT CONNECT TO your_db_username;

Step 2: Spring DCN Listener #

Implement a listener that registers with Oracle and reacts to change events:

@Component
public class OracleDcnChangeListener implements ApplicationListener<ContextRefreshedEvent> {
    @Autowired
    private DataSource dataSource;
    @Autowired
    private ColumnUpdateListener updateListener;

    @Override
    public void onApplicationEvent(ContextRefreshedEvent event) {
        try (Connection conn = dataSource.getConnection()) {
            OracleConnection oracleConn = conn.unwrap(OracleConnection.class);
            
            // Configure DCN properties
            Properties dcnProps = new Properties();
            dcnProps.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
            
            // Register change notification
            DatabaseChangeRegistration dcr = oracleConn.registerDatabaseChangeNotification(dcnProps);
            
            // Add a listener to handle change events
            dcr.addListener(new DatabaseChangeListener() {
                @Override
                public void onDatabaseChangeNotification(DatabaseChangeEvent dbEvent) {
                    for (TableChangeEvent tableEvent : dbEvent.getTableChangeEvents()) {
                        // Check if the event is for your target table
                        if ("YOUR_TABLE_NAME".equalsIgnoreCase(tableEvent.getTableName())) {
                            for (RowChangeEvent rowEvent : tableEvent.getRowChangeEvents()) {
                                // Extract old/new values for your target column
                                String oldVal = (String) rowEvent.getOldValues().get("YOUR_TARGET_COLUMN");
                                String newVal = (String) rowEvent.getNewValues().get("YOUR_TARGET_COLUMN");
                                
                                // Build change log and trigger your listener
                                ColumnChangeLog changeLog = new ColumnChangeLog();
                                changeLog.setTargetTable("YOUR_TABLE_NAME");
                                changeLog.setChangedColumn("YOUR_TARGET_COLUMN");
                                changeLog.setOldValue(oldVal);
                                changeLog.setNewValue(newVal);
                                changeLog.setChangeTime(new Timestamp(System.currentTimeMillis()));
                                
                                updateListener.onColumnUpdated(changeLog);
                            }
                        }
                    }
                }
            });

            // Register the target table/column for monitoring
            try (Statement stmt = conn.createStatement()) {
                ((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
                // Execute a dummy query to register the table with DCN
                stmt.executeQuery("SELECT YOUR_TARGET_COLUMN FROM YOUR_TABLE_NAME WHERE 1=0");
            }
        } catch (SQLException e) {
            System.err.println("Failed to set up DCN listener");
            e.printStackTrace();
        }
    }
}

Key Considerations #

• Polling Frequency: Adjust the fixedRate in the scheduled task based on your latency needs—don’t poll too often or you’ll stress the database.
• Idempotency: Ensure your business methods can handle duplicate triggers (e.g., if a log entry is processed twice).
• DCN Limitations: DCN requires Oracle 11g+, and you’ll need to handle connection reconnections if the DB drops the session.
• Batch Processing: For large volumes of changes, batch processing logs will be more efficient than processing one at a time.

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