如何在MyBatis XML中通过单查询实现清表并插入新数据？

Absolutely! You can pull off this logic in MyBatis—either by wrapping multiple operations in a transaction (the most maintainable approach) or by executing a single multi-statement SQL block. Let’s break down both solutions, tailored to your existing StudentService and StudentMapper setup.

Approach 1: Use Service-Level Transactions (Recommended) #

This keeps transaction logic in the business layer and aligns with MyBatis’s design of mapping single SQL statements to individual Mapper methods. It’s clean, easy to debug, and safe for rollbacks.

Step 1: Extend Your Mapper Interface #

Add two new methods to StudentMapper for clearing the table and batch-inserting data:

public interface StudentMapper {
    // ... your existing methods ...

    // Delete all students (works safely within transactions)
    void deleteAll();

    // Batch insert multiple student records
    void batchInsert(@Param("students") List<Student> students);
}

Step 2: Implement the Transactional Service Method #

Add a new method to StudentServiceImpl annotated with @Transactional to ensure both operations run in a single transaction:

public class StudentServiceImpl implements StudentService {
    // Inject your StudentMapper here

    // ... your existing methods ...

    @Transactional
    public void reloadStudents(List<Student> students) {
        // First clear the entire table
        studentMapper.deleteAll();
        // Then insert the new batch of students
        studentMapper.batchInsert(students);
    }
}

Step 3: Write MyBatis XML Mappings #

Add these tags to your StudentMapper.xml file:

<!-- Delete all records from Students (supports transaction rollbacks) -->
<delete id="deleteAll">
    DELETE FROM Students
</delete>

<!-- Batch insert using MyBatis's foreach to generate a single INSERT statement -->
<insert id="batchInsert">
    INSERT INTO Students (name, age, class)
    VALUES
    <foreach collection="students" item="student" separator=",">
        (#{student.name}, #{student.age}, #{student.class})
    </foreach>
</insert>

Approach 2: Single Mapper Method with Multi-Statement SQL #

If you want to minimize database round-trips, you can combine the truncate/delete and insert into one Mapper method. Note this requires your database to allow multiple statements per query (e.g., MySQL needs allowMultiQueries=true in the JDBC URL).

Step 1: Add a Mapper Method #

public interface StudentMapper {
    // ... your existing methods ...

    // Truncate table and batch insert in one call
    void truncateAndBatchInsert(@Param("students") List<Student> students);
}

Step 2: Write the Multi-Statement XML Mapping #

Use MyBatis’s <script> tag to wrap both SQL statements:

<update id="truncateAndBatchInsert">
    <script>
        -- TRUNCATE is faster than DELETE, but note:
        -- In MySQL, TRUNCATE is a DDL operation that auto-commits transactions
        -- Use DELETE FROM Students instead if you need rollback support
        TRUNCATE TABLE Students;

        INSERT INTO Students (name, age, class)
        VALUES
        <foreach collection="students" item="student" separator=",">
            (#{student.name}, #{student.age}, #{student.class})
        </foreach>
    </script>
</update>

Critical Notes for This Approach: #

• Database Setup: For MySQL, add allowMultiQueries=true to your JDBC URL (e.g., jdbc:mysql://localhost:3306/your_db?allowMultiQueries=true).
• Transaction Behavior: If using TRUNCATE, some databases (like MySQL) treat it as a DDL command that auto-commits the transaction. Stick with DELETE FROM Students if you need rollback capability.

Key Considerations #

• Transaction Safety: Never skip wrapping these operations in a transaction—if the insert fails after the delete, you’ll end up with an empty table.
• Performance: For large datasets, split batch inserts into smaller chunks (e.g., 1000 records at a time) to avoid hitting database query size limits.
• TRUNCATE vs DELETE: Use TRUNCATE for speed when you don’t need triggers or rollbacks; use DELETE for transaction-safe operations.

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