SoFunction
Updated on 2025-04-11

Implementation steps for elegantly handling multi-table data cleaning in SpringBoot+MyBatis

Problem background

In actual business, we often encounter data redundancy problems. For example, a company table (sys_companyThere are multiple records of the same company name in ) but only one is valid (del_flag=0), the rest need to be deleted. Before deletion, you need to add foreign key fields of the associated table (such as contract draft table, invoice table, etc.) (such aspurchaser_id) Replace with the ID that keeps the record. This type of problem usually involves dynamic updates of multiple tables and multiple fields. How to implement them efficiently and safely?

Solution

We will do it through the following steps:

  1. Configurable driver: Use configuration classes to declare tables and fields that need to be processed to avoid hard coding.
  2. Dynamic SQL updates: Batch update and deletion are realized through MyBatis XML.
  3. Transaction consistency: Ensure that all operations are performed atomically.

Implementation steps

1. Define entity classes

CompanyRetainedInfo: Encapsulate the company information that needs to be retained

import ;

@Data
public class CompanyRetainedInfo {
    private String companyName;   // Company Name    private Long retainedId;      // The company ID to be retained (record of del_flag=0)    private String retainedName;  // The company name to be retained (consistent with companyName)}

• effect: Map query results, pass the ID and name of the reserved record.
• Lombok：@DataAutomatically generate Getter/Setter andtoString()method.

2. Define configuration class

TableConfig: Declare the table and foreign key relationship to be processed

public class TableConfig {
    private String tableName;    // Table name (such as contract_draft)    private String idColumn;     // Foreign key ID field (such as purchaseser_id)    private String nameColumn;  // Name field (such as purchaseser_name, probably null)
    // Constructor + Getter/Setter    public TableConfig(String tableName, String idColumn, String nameColumn) {
         = tableName;
         = idColumn;
         = nameColumn;
    }
}

3. Write the MyBatis Mapper interface

CompanyCleanMapper: Define the data operation interface (No annotation, pure XML mapping）

@Mapper
public interface CompanyCleanMapper {
    // Query the company information to be retained (del_flag=0)    List<CompanyRetainedInfo> selectRetainedCompanies();

    // Check the ID list to be deleted based on the company name (del_flag!=0)    List<Long> selectIdsToDelete(String companyName);

    // Update foreign key references to the associated table    void updateForeignKeys(
        @Param("config") TableConfig config,
        @Param("retainedId") Long retainedId,
        @Param("retainedName") String retainedName,
        @Param("ids") List<Long> ids
    );

    // Delete redundant company records    void deleteCompanies(@Param("ids") List<Long> ids);
}

4. Implement XML mapping files

: Define dynamic SQL logic

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" 
    "/dtd/">
<mapper namespace="">

    <!-- Inquiry of the company to be retained -->
    <select  resultType="CompanyRetainedInfo">
        SELECT 
            company_name AS companyName, 
            id AS retainedId, 
            company_name AS retainedName
        FROM sys_company
        WHERE del_flag = 0
          AND company_name IN (
            SELECT company_name
            FROM sys_company
            GROUP BY company_name
            HAVING COUNT(*) > 1 AND SUM(del_flag = 0) = 1
          )
    </select>

    <!-- Query what to deleteIDList -->
    <select  resultType="long">
        SELECT id
        FROM sys_company
        WHERE company_name = #{companyName}
          AND del_flag != 0
    </select>

    <!-- Dynamically update foreign key references -->
    <update >
        UPDATE ${}
        SET
            <choose>
                <when test=" != null">
                    <!-- Updated at the same timeIDand name fields -->
                    ${} = #{retainedId},
                    ${} = #{retainedName}
                </when>
                <otherwise>
                    <!-- Update onlyIDFields -->
                    ${} = #{retainedId}
                </otherwise>
            </choose>
        WHERE
            ${} IN
            <foreach item="id" collection="ids" open="(" separator="," close=")">
                #{id}
            </foreach>
    </update>

    <!-- Delete company records in batches -->
    <delete >
        DELETE FROM sys_company
        WHERE id IN
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

</mapper>

5. Service layer implementation

CompanyCleanService: Configurable driver batch processing

@Service
@RequiredArgsConstructor
public class CompanyCleanService {
    private final CompanyCleanMapper companyCleanMapper;

    // Configure tables and fields to be processed    private static final List<TableConfig> TABLE_CONFIGS = (
        new TableConfig("contract_draft", "purchaser_id", "purchaser_name"),
        new TableConfig("invoice", "company_id", "company_name")
        // Add other tables as needed...    );

    @Transactional
    public void cleanDuplicateCompanies() {
        // 1. Inquiry of all companies that need to be retained        List<CompanyRetainedInfo> retainedCompanies = ();
        
        for (CompanyRetainedInfo info : retainedCompanies) {
            // 2. Query the list of IDs to be deleted            List<Long> idsToDelete = (());
            
            if (!()) {
                // 3. Update foreign key references to all associated tables                TABLE_CONFIGS.forEach(config -> 
                    (
                        config, 
                        (), 
                        (), 
                        idsToDelete
                    )
                );
                
                // 4. Delete redundant company records                (idsToDelete);
            }
        }
    }
}

Key Design Notes

  1. Entity class and data mapping
    • CompanyRetainedInfoBy alias (AS retainedId) Directly map query results to avoid additional conversions.
    • companyNameandretainedNameThe field values ​​are the same, but the latter is preserved for explicit semantics.

  2. Advantages of XML Dynamic SQL
    • <choose>: Dynamically decide whether to update the name field based on the configuration.
    • <foreach>: Automatically expand the ID list asIN (id1, id2...), supports batch operation.
    • ${}Placeholder: The table name and field name of the secure reference configuration (non-user input, no injection risk).

  3. Transaction and performance optimization
    • @Transactional: Ensure the atomicity of "update foreign keys" and "delete company" operations.
    • Indexing suggestions:rightsys_company.company_nameAdd indexes to the foreign key fields of the associated table.

Summarize

passEntity encapsulation、Configurable table relationshipsandMyBatis Dynamic SQL, We implement a set of scalable multi-table data cleaning solutions. The core of this model is:

  1. Abstract change part: Converge the differences between tables and fields into the configuration class.
  2. Reuse unchanged logic: Batch updates and deletion operations are driven by unified services.
  3. Minimize invasiveness: Adding new tables only requires modifying the configuration, no need to change the core logic.

This solution is suitable for redundant data processing scenarios such as user centers and product systems where foreign key relationships exist. Readers can adjust configuration and SQL logic based on actual needs.

The above is the detailed content of the implementation steps for elegantly handling multi-table data cleaning in SpringBoot+MyBatis. For more information about SpringBoot MyBatis multi-table data cleaning, please pay attention to my other related articles!