0

I came around an obstacle . The following statement does not execute because the String query contains multiple statements .

String query="create volatile table test1 as (etc . ); select TOP 10 * from test1; ";

        String driver = "com.xxx";
        String conUrl="jdbc:ccc";
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(conUrl,user,password);

        PreparedStatement stmt=conn.prepareStatement(query);

The last line throws an error Data definition not valid unless solitary

It is very cumbersome to split my query into multiple PreparedStatements

Is there any other way to execute a query containing multiple statements as one ?

Aleksei Nikolaevich
  • 325
  • 3
  • 15
  • 40
  • Whats wrong with executing them separately? – David Feb 05 '14 at 23:38
  • if a user types in a query `create ...where x='good ; ' and ..." ; select ... ;` How can i determine that the first query does not end after the word `good` . i need to write additional complex regex . – Aleksei Nikolaevich Feb 05 '14 at 23:42
  • You can try `conn.createStatement().execute(sql)` which will sometimes accept multiple statements. But in general, if you use JDBC, you need to know where statements start and end. – vanOekel Feb 05 '14 at 23:52

2 Answers2

2

You could use JDBC batch processing (addBatch, executeBatch), which allows you to "stack" statements and send them all to the db engine to be executed at once.

Here a starting point: http://viralpatel.net/blogs/batch-insert-in-java-jdbc/

But you would still need to split it up into separate statements, and add them one at a time. Java: splitting a comma-separated string but ignoring commas in quotes

And, as @CHEBURASHKA has pointed out, it only returns the number of rows affected by each statement - no good if you want to ask for actual data from the tables.

Community
  • 1
  • 1
Turophile
  • 3,367
  • 1
  • 13
  • 21
  • +1 for attempt. However this is not correct . Take a look at this answer http://stackoverflow.com/questions/21118698/retrieve-resultset-using-callablestatement-after-executebatch – CHEBURASHKA Feb 06 '14 at 01:51
  • You are right, depending upon what is in the SQL you are executing, and if there are results you need to see. I'll put it in the answer. – Turophile Feb 06 '14 at 03:47
0

It shouldn't be cumbersome, you've already done it by having your query containing the ; character, which naturally terminates a SQL query.

So you have to do:

Stream.of(query.split(";")).forEach(sqlStatement -> System.out.println(sqlStatement))

Just replace the println for whatever mechanism you use to execute the sql query.

hrs
  • 379
  • 4
  • 12