What's the easiest way to parse multi-line SQL statements in Java?

Question

I'm pretty new to Java, but I'm looking for Java code that can take multi-line SQL statements, say something like this from a flat file:

CREATE OR REPLACE TRIGGER REQ.TR_INPT_STAY_DETAI_SQ_BI
  BEFORE INSERT ON REQ.INPT_STAY_DETAIL
  FOR EACH ROW
BEGIN
  SELECT REQ.SQ_INPT_DETAIL.nextval INTO :new.INPT_STAY_DETAIL_PID from DUAL;
END;

And convert those multi-line statements into one line SQL statements like this (maybe wrapped in this display), to be executed as a single JDBC statement against an Oracle database:

CREATE OR REPLACE TRIGGER REQ.TR_INPT_STAY_DETAI_SQ_BI BEFORE INSERT ON REQ.INPT_STAY_DETAIL FOR EACH ROW BEGIN SELECT REQ.SQ_INPT_DETAIL.nextval INTO :new.INPT_STAY_DETAIL_PID from DUAL; END;

I've tried to do this with multiple BufferedReaders, and/or using the mark() method, but I can't seem to get it to work.

The trouble seems to be the semi-colon at both the end of the long statement, and with the END; statement.

Your thoughts on the easiest way to do this?

---

I think I made some progress, but if the same SQL input file also has simpler SQL commands, like:

DROP TABLE TABLE.NAME_HERE;

They'll fail on my first condition here:

if ( !thisLine.startsWith("END;") && !thisLine.equals("/") ) {
    sqlBuf.append(thisLine).append(" ");
    statementReady = false;
}
else if (thisLine.startsWith("END;")) {
    sqlBuf.append(thisLine).append(" ");
    statementReady = true;
}
else if (thisLine.equals("/")) {
    statementReady = true;
}
else { }

I check the statemendReady variable later on before I run the .execute.

Answer 1

One approach might be to use ANTLR. It has oracle SQL grammer which will certainly help with many corner cases around the SQL syntax.

Answer 2

Just read in each line, and append them using StringBuilder, but, don't append any line that has End; as you won't need that for your query.

You should also ignore Go if the file has that.

Then you can just turn the StringBuilder into a String and you have it.

Just remember to put a space between each line.

For more on reading several lines from a file you can look at: http://www.roseindia.net/java/beginners/java-read-file-line-by-line.shtml

The important part is:

StringBuilder buf = new StringBuilder();
while ((strLine = br.readLine()) != null)   {
       if (!strLine.startsWith("End"))
          buf.append(strLine).append(" ");
    }

Answer 3

Can you pre-process the file so that you don't have as many newlines by doing using a search and replace on the newline character and add newlines at the end of statements, then each line you read will be a single sql statement?

(Unix tools like sed can make this very easy)

Otherwise, James is right (just beat me to it).