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.






Last Updated On : 4th April 2008

Subscribe to get updates delivered to your inbox

About Silver Moon

Php developer, blogger and Linux enthusiast. He can be reached at [email protected]. Or find him on

Leave a comment