在Spring Boot應(yīng)用中，有時(shí)候我們需要批量執(zhí)行存儲(chǔ)在數(shù)據(jù)庫(kù)中的 SQL 腳本。本文將介紹一個(gè)實(shí)際的案例，演示如何通過(guò) Spring Boot、MyBatis 和數(shù)據(jù)庫(kù)來(lái)實(shí)現(xiàn)這一目標(biāo)。

0、數(shù)據(jù)庫(kù)層

CREATE TABLE batchUpdate (

    id INT AUTO_INCREMENT PRIMARY KEY,
    update_type VARCHAR(255) NOT NULL,
    success_flag BOOLEAN NOT NULL,
    failure_count INT NOT NULL,
    execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sql_script TEXT NOT NULL
);

  


-- 第一條數(shù)據(jù)

INSERT INTO `batch_update` (`update_type`, `success_flag`, `failure_count`, `update_count`, `sql_script`)

VALUES

('update_type_1', 0, 0, 0,

'UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_1'';

UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_1'';

UPDATE batch_update SET failure_count = failure_count + 1, update_count = update_count + 1 WHERE update_type = ''update_type_1'';');

  


-- 第二條數(shù)據(jù)

INSERT INTO `batch_update` (`update_type`, `success_flag`, `failure_count`, `update_count`, `sql_script`)

VALUES

('update_type_2', 0, 0, 0,

'UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_2'';

UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_2'';

UPDATE batch_update SET failure_count = failure_count + 1, update_count = update_count + 1 WHERE update_type = ''update_type_2'';');

  


-- 第三條數(shù)據(jù)

INSERT INTO `batch_update` (`update_type`, `success_flag`, `failure_count`, `update_count`, `sql_script`)

VALUES

('update_type_1', 0, 0, 0,

'UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_1'';

UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_1'';

UPDATE batch_update SET failure_count = failure_count + 1, update_count = update_count + 1 WHERE update_type = ''update_type_1'';');

  


-- 第四條數(shù)據(jù)

INSERT INTO `batch_update` (`update_type`, `success_flag`, `failure_count`, `update_count`, `sql_script`)

VALUES

('update_type_2', 0, 0, 0,

'UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_2'';

UPDATE batch_update SET success_flag = 1, update_count = update_count + 1 WHERE update_type = ''update_type_2'';

UPDATE batch_update SET failure_count = failure_count + 1, update_count = update_count + 1 WHERE update_type = ''update_type_2'';');

1. 控制器層（Controller）

@RestController
@RequestMapping("/batchUpdate")
@AllArgsConstructor
public class BatchUpdateController {

    private BatchUpdateService batchUpdateService;

    @PostMapping("/executeScript/{updateType}")
    public String executeScript(@PathVariable String updateType) {
        List<BatchUpdateEntity> batchUpdateEntities = batchUpdateService.findByUpdateType(updateType);

        if (batchUpdateEntities.isEmpty()) {
            return "Update type not found.";
        }

        for (BatchUpdateEntity batchUpdateEntity : batchUpdateEntities) {
            batchUpdateService.executeSqlScriptBatch(batchUpdateEntity);
        }

        return "SQL scripts executed successfully.";
    }
}

2. 服務(wù)層（Service）

javaCopy code
@Service
@AllArgsConstructor
public class BatchUpdateService {

    private BatchUpdateMapper batchUpdateMapper;

    public List<BatchUpdateEntity> findByUpdateType(String updateType) {
        return batchUpdateMapper.findByUpdateType(updateType);
    }

    public void executeSqlScriptBatch(BatchUpdateEntity batchUpdateEntity) {
        String sqlScript = batchUpdateEntity.getSqlScript();
        List<String> sqlScripts = Arrays.stream(sqlScript.split(";"))
                .map(String::trim)
                .filter(s -> !s.isEmpty())
                .toList();
        System.out.println(sqlScripts.size());
        sqlScripts.forEach(sql -> {
            System.out.println("要執(zhí)行的sql:" + sql);
            batchUpdateMapper.executeSqlScript(sql);
            System.out.println("已執(zhí)行的sql:" + sql);
        });
    }
}

3. 數(shù)據(jù)訪問(wèn)層（Mapper）

@Mapper
public interface BatchUpdateMapper {

    List<BatchUpdateEntity> findByUpdateType(@Param("updateType") String updateType);
    void executeSqlScript(@Param("sql") String sql);
}

4. MyBatis 配置文件（BatchUpdateMapper.xml）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lfsun.sqlscript.BatchUpdateMapper">

    <resultMap id="BatchUpdateEntityResultMap" type="com.lfsun.sqlscript.BatchUpdateEntity">
        <id property="id" column="id" />
        <result property="updateType" column="update_type" />
        <result property="successFlag" column="success_flag" />
        <result property="failureCount" column="failure_count" />
        <result property="executionTime" column="execution_time" />
        <result property="sqlScript" column="sql_script" />
    </resultMap>

    <select id="findByUpdateType" resultMap="BatchUpdateEntityResultMap">
        SELECT * FROM batch_update WHERE update_type = #{updateType}
    </select>
    <update id="executeSqlScript">
        ${sql}
    </update>
</mapper>

通過(guò)這個(gè)案例，可以學(xué)到如何在 Spring Boot 中通過(guò) MyBatis 實(shí)現(xiàn)批量執(zhí)行 SQL 腳本的功能。在控制器層中，我們通過(guò) @PostMapping 注解定義了一個(gè)接口，接收 updateType 作為路徑參數(shù)，然后調(diào)用服務(wù)層的方法。服務(wù)層中，我們通過(guò) MyBatis 執(zhí)行 SQL 腳本，實(shí)現(xiàn)了對(duì)數(shù)據(jù)庫(kù)的批量操作。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-795766.html

到了這里，關(guān)于Spring Boot 中批量執(zhí)行 SQL 腳本的實(shí)踐的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！