1. 在工作中很多时候需要执行一个SQL脚本文件到数据库中作为初始化数据;spring提供了一个工具类ScriptUtils,具体用法如下:
@SpringBootTest class ExecuteSqlScriptApplicationTests { @Autowired private DataSource dataSource; @Test void contextLoads() throws SQLException, IOException { Resource classPathResource = new ClassPathResource("init/student.sql"); ScriptUtils.executeSqlScript(dataSource.getConnection(), classPathResource); } }
2. 但是有时候我们的SQL脚本文件很大,甚至是几百mb,这样容易造成内存溢出的情况,因此我写了一个工具类,对SQL脚本进行拆解,然后批量执行。 这样每批量执行后,就清空缓存中的SQL,因此解决内存溢出问题。如下:
具体还没有用大数据量的脚本测试,等周一到公司再测试一下吧,哈哈哈。。。
@SpringBootTest class ExecuteSqlScriptApplicationTests { @Autowired private DataSource dataSource; @Test void contextLoads() throws SQLException, IOException { Resource classPathResource = new ClassPathResource("init/student.sql"); ScriptUtils.executeSqlScript(dataSource.getConnection(), classPathResource); // 分批处理SQL脚本 batchExecuteSql(classPathResource, 5); } /** * SQL脚本分解执行 * @param resource SQL脚本资源 * @param batchNumber 每多少条SQL执行一次 * @throws SQLException * @throws IOException */ public void batchExecuteSql(Resource resource, int batchNumber) throws SQLException, IOException { Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement(); BufferedReader bufferedReader = null; try { //获取字符缓冲流 bufferedReader = new BufferedReader(new InputStreamReader(resource.getInputStream())); 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(); } } } }