General approach for SQL script execution in Java

Question

The functionality that discussed within this question is to execute the given SQL script. The content of the script is intended to be defined by the user during application runtime. The script can be whether one INSERT statement or a sequence of complex PL/SQL statements. Since the input available during runtime (eventually as String instance) it should be executed through Java.

Current approach is wrapping user input with a PreparedStatement and to execute it. This solution works for the existing test cases. The main concern is to provide the full functionality of the used database that might be not covered by tests, i.e. solution that is closest to passing the same user SQL script into database vendor's provided console.

I'm wondering are there any not envisaged limitations in current approach with PreparedStatement? Is there any better general approach for SQL script execution via Java?

Answer 1

Well, this is a broad design question but I think that there are several steps that could be done:

• SQL script parsing and recognition: You need to be able to detect which type of SQL script you have: PL/SQL, DML, DDL, CDL, TCL, multipart separated by ";" etc.
• Statement building: for each type of sql script you need to be able to execute the statement with java.
• Parsing the result. You need to be able to collect the returned in SELECTs and optionally parameters returned by functions or number of affected/inserted rows.
• Error handling: you need to be able to report what happened to the SQL Script when things didn't worked as expected.

Please consider:

• This seems like the programming of a SQL Client. If not please explain what do you want to do. Do not use this as the connection layer in a normal application. It will be extremely inefficient and vulnerable to SQL injections (It is much more complicated than just scaping commas)
• You may want to call functions o execute queries with external parameters.
• This does not includes the user interfaces features like Syntax highlighting. Parameters interfaces, etc...