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();
}
}
}
}
java jdbc sql
最新推荐文章于 2024-08-24 22:01:16 发布