i have a bunch of sql scripts that should upgrade the database when the java web application starts up.
i tried using the ibatis scriptrunner, but it fails gloriously when defining triggers, where the ";" character does not mark an end of statement.
now i have written my own version of a script runner, which basically does the job, but destroys possible formatting and comments, especially in "create or replace view".
public class ScriptRunner {
private final DataSource ds;
public ScriptRunner(DataSource ds) {
this.ds = ds;
}
public void run(InputStream sqlStream) throws SQLException, IOException {
sqlStream.reset();
final Statement statement = ds.getConnection().createStatement();
List<String> sqlFragments = createSqlfragments(sqlStream);
for (String toRun : sqlFragments) {
if (toRun.length() > 0) {
statement.execute(toRun);
}
}
}
private static List<String> createSqlfragments(InputStream sqlStream) throws IOException {
BufferedReader br = new BufferedReader(new InputStreamReader(sqlStream));
List<String> ret = new ArrayList<String>();
String line;
StringBuilder script = new StringBuilder();
while ((line = br.readLine()) != null) {
if (line.equals("/")) {
ret.add(removeMultilineComments(script));
script = new StringBuilder();
} else {
//strip comments
final int indexComment = line.indexOf("--");
String lineWithoutComments = (indexComment != -1) ? line.substring(0, indexComment) : line;
script.append(lineWithoutComments).append(" ");
}
}
if (script.length() > 0) {
ret.add(removeMultilineComments(script));
}
return ret;
}
private static String removeMultilineComments(StringBuilder script) {
return script.toString().replaceAll("/\\*(.*?)\\*/", "").trim();
}
is there a clean way to acieve this? is there something in hibernate i have not seen? or can i pass an inputstream to sqlplus somehow? besides my worries about the formatting, i doubt that this code is error-free, since i have limited knowledge about the pl/sql syntax.