Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

General approach for SQL script execution in Java

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?

like image 599
Pavel Avatar asked Jan 22 '16 09:01

Pavel


People also ask

Which method is used to execute the SQL query in Java?

To execute a SQL statement with the execute method, call it by passing it a valid SQL statement as a String object, or as a string literal, as shown in the following example: boolean isResultSet = false; Statement stmt = null; try { stmt = conn. createStatement( ); isResultSet = stmt.

Which method will return Boolean When we try to execute SQL query from a JDBC program?

Description: The method used for all types of SQL statements, and that is, returns a Boolean value of TRUE or FALSE. Return type: This method return a Boolean value. TRUE indicates that query returned a Result Set object and FALSE indicate returned an int value or returned nothing.


1 Answers

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...
like image 111
borjab Avatar answered Oct 02 '22 23:10

borjab