How to execute query of multiple statements as one?

Question

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 ?

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
– Aleksei Nikolaevich, Commented Feb 5, 2014 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
– vanOekel, Commented Feb 5, 2014 at 23:52

Community · Accepted Answer · 2017-05-23 12:28:40Z

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.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Feb 5, 2014 at 23:46

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CHEBURASHKA Over a year ago

+1 for attempt. However this is not correct . Take a look at this answer stackoverflow.com/questions/21118698/…

Turophile Over a year ago

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.

hrs · Accepted Answer · 2018-05-03 21:57:44Z

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.

answered May 3, 2018 at 21:57

hrs

4155 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to execute query of multiple statements as one?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related