Execute an SQL Script in JDBC

An article by Binary Tides    1 Comment

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.

Popularity: 1% [?]