java jdbc sql

package com.xtoon.boot.web.controller.sys;

import org.junit.Test;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.datasource.init.ScriptUtils;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @ClassName DbTest
 * @Description TODO
 * @Author xing.qsh
 * @Date 2022/3/30
 * @Version TODO
 * @History TODO
 **/
public class DbTest {

    @Test
    public void jdbcTest() throws SQLException, ClassNotFoundException {
        // 佳联与知识
        String[] sql = {"ALTER table user add hah8 VARCHAR(22) null COMMENT 'hha';",
        "ALTER table user add hah9 VARCHAR(22) null COMMENT 'hha2';"} ;
        String url = "jdbc:mysql://localhost:3306/xing-test?useUnicode=true&characterEncoding=UTF-8";
        // 1 加载注册驱动
        Class.forName("com.mysql.jdbc.Driver") ;
        // 2 获取连接对象
        Connection conn = DriverManager.getConnection(url,"root","my-123456")  ;
        // 3 创建语句对象
        Statement st =  conn.createStatement() ;
        // 4 执行SQL 语句
        st.addBatch(sql[0]);
        st.addBatch(sql[1]);

        st.executeBatch();
        // 5 释放资源
        // 释放资源
        st.close();
        conn.close();
    }

    @Test
    public void contextLoads() throws SQLException, IOException, ClassNotFoundException {
        Resource classPathResource = new ClassPathResource("/Users/xing.qsh/xing_learn/DDD/xtoon-boot/xtoon-api/src/test/java/com/xtoon/boot/web/controller/sys/test.sql");
        // 佳联与知识
        String sql = "ALTER table user add hah33 VARCHAR(22) null COMMENT 'hha'" ;
        String url = "jdbc:mysql://localhost:3306/xing-test?useUnicode=true&characterEncoding=UTF-8";
        // 1 加载注册驱动
        Class.forName("com.mysql.jdbc.Driver") ;
        Connection conn = DriverManager.getConnection(url,"root","my-123456")  ;
        //ScriptUtils.executeSqlScript(conn, classPathResource);
        // 分批处理SQL脚本
        batchExecuteSql(conn,classPathResource, 5);
    }

    /**
     * SQL脚本分解执行
     * @param resource SQL脚本资源
     * @param batchNumber  每多少条SQL执行一次
     * @throws SQLException
     * @throws IOException
     */
    public void batchExecuteSql(Connection connection ,Resource resource, int batchNumber) throws SQLException, IOException {
        //Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        Reader bufferedReader = null;
        try {
            //获取字符缓冲流
            File file = new File("/Users/xing.qsh/xing_learn/DDD/xtoon-boot/xtoon-api/src/test/java/com/xtoon/boot/web/controller/sys/test.sql");
            bufferedReader = new InputStreamReader(new FileInputStream(file));
            int l;
            int i = 0;
            StringBuilder sql = new StringBuilder();
            while ((l = bufferedReader.read()) != -1) {
                char read = (char) l;
                sql.append(read);
                if (read == ';') { // 一个完整的SQL语句
                    i ++;
                    statement.addBatch(sql.toString());
                    if (i % batchNumber == 0) {
                        System.out.println("每" + batchNumber + "条语句批量执行开始......");
                        statement.executeBatch();
                        statement.clearBatch();
                        System.out.println("每" + batchNumber + "条语句批量执行结束......");
                    }
                    //清除StringBuilder中的SQL语句
                    sql.delete(0, sql.length());
                }
            }
            if (i % batchNumber != 0) {
                System.out.println("执行最后不足" + batchNumber + "条语句开始！！！");
                statement.executeBatch();
                statement.clearBatch();
                System.out.println("执行最后不足" + batchNumber + "条语句结束！！！");
            }
        } finally {
            if (bufferedReader != null) {
                bufferedReader.close();
            }
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
    }
}