Code to Execute an SQL Script in JDBC.
The SQL Script should have comments starting with - or -- only on new lines and each command should end with a ; .

Reading a sql file and putting all of it in a string variable and feeding to the execute command would result in an exception. All instructions must be executed individually. A proper sql script should have all instructions starting on a newline and all comments on a newline. Start with an empty string. Start reading the lines ; if a line has a - as the first character then continue else add it to the query string. Now if the last character of the query string is a ; then a command is complete so execute the query and make the query string empty and loop up. Thats it

Executing an sql script in jdbc with the following code should work ...

//Now read line bye line
String thisLine, sqlQuery;
try {
    sqlQuery = "";
    while ((thisLine = d.readLine()) != null) 
    {  
        //Skip comments and empty lines
        if(thisLine.length() > 0 && thisLine.charAt(0) == '-' || thisLine.length() == 0 ) 
            continue;
        sqlQuery = sqlQuery + " " + thisLine;
        //If one command complete
        if(sqlQuery.charAt(sqlQuery.length() - 1) == ';') {
            sqlQuery = sqlQuery.replace(';' , ' '); //Remove the ; since jdbc complains
            try {
                stmt.execute(sqlQuery);
            }
            catch(SQLException ex) {
                JOptionPane.showMessageDialog(null, "Error Creating the SQL Database : " + ex.getMessage());
            }
            catch(Exception ex) {
                JOptionPane.showMessageDialog(null, "Error Creating the SQL Database : " + ex.getMessage());
            }
            sqlQuery = "";
        }   
    }
}
catch(IOException ex) {
}
catch(Exception ex) {
    JOptionPane.showMessageDialog(null, "Error Creating the SQL Database : " + ex.getMessage());
}

where d could be BufferedReader and c could be a Statement object.

Wonderful; just what I was looking for! Funny that this is not a built-in in JDBC. Should be a Statement.runScript("script.sql") method or something.

This article has been dead for over six months. Start a new discussion instead.