Executing a long SQL script on Microsoft SQL Server 2017

Question

I need to execute a SQL script (~5000 lines) with DDL and DML commands on a new instance of a Microsoft SQL Server 2017 through a Java application, already with connection pools and data sources deployed and configured.

Searching for a solution the only method I have found is to load the script to buffer, read it, and break each line into a Statement and then send each of those statements individually to the database instance, like in this example.

Is there a more elegant or simple way of doing this?

Answer 1

After researching for a solution I concluded that my first approach would be the best for this specific case. Basically, I implemented a factory class that instances a parser class for the database being used.

This parser reads each line from the SQL Script and loads it into a buffer, evaluating if it is a comment or correct line, adds it into a list, cleans empty lines and then finally builds each command between two GO statements by appending the lines and passing them to a another list that will be returned at the end.

package parsing;

import java.io.*
import java.util.*

public class ScriptParser implements Parser {

private static ArrayList<String> listOfQueries = null;

@Override
public ArrayList<String> createQueries(String path) {
    String queryLine = new String();
    StringBuilder sBuffer = new StringBuilder();
    listOfQueries = new ArrayList<>();
    List<String> dirtyQueries = new ArrayList<>();

    try {
        FileReader fr = new FileReader(new File(path));
        BufferedReader br = new BufferedReader(fr);
        while ((queryLine = br.readLine()) != null) {
            if (queryLine.contains("--") || queryLine.contains("/*")) {
                queryLine = "";
                dirtyQueries.add(queryLine);
            } else {
                dirtyQueries.add(queryLine + " ");
            }
        }
        br.close();
        for (Iterator<String> iter = dirtyQueries.listIterator(); iter.hasNext();) {
            //TODO
            String str = iter.next();
            if (str.isEmpty() || str.trim().isEmpty()) {
                iter.remove();
            } else {
                //DO NOTHING
            }
        }

        StringBuilder queryStatement = new StringBuilder();
        for (int i = 0; i < dirtyQueries.size(); i++) {
            //TODO
            if (!dirtyQueries.get(i).contains("GO") || !dirtyQueries.get(i).contains("GO ")) {
                queryStatement.append(dirtyQueries.get(i)).append(" ");
            } else {
                listOfQueries.add(queryStatement.toString());
                queryStatement = new StringBuilder();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        System.out.println(sBuffer.toString());
    }
    return listOfQueries;
  }
}    

Then, each entry in the list is looped and passed to a Statement and individually executed.