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

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开发者_StackOverflow社区 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.

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(" ");
    }

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

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

Syntax of Oracle SQL statement is quite complicated, you can't get SQL statement or PLSQL stored procedure from a script file easily with this string match algorithm. What you need is a fully working SQL Parser which can parse the whole SQL script correctly, Here is an article shows you how to do this with the help of general sql parser.