Java

Java RSS

Execute an SQL Script in JDBC

•

Join Date: Mar 2008

Posts: 21

Reputation: theausum has a little shameless behaviour in the past

Solved Threads: 1

theausum

Offline

Newbie Poster

Execute an SQL Script in JDBC

Oct 28th, 2008

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 ...

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
//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());
}
//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.