PreparedStatement for several statements

Question

Is it possible to use PreparedStatement for several statements?

E.g., I mean if String sql = "INSERT INTO OR INGNORE ... ; UPDATE ... ; INSERT INTO ..."; this

PreparedStatement pre = conn.prepareStatement(sql);
//...
pre.executeUpdate();

executes only first statement "INSERT INTO OR INGNORE ... (until semicolon). Is it possible to execute all at once?

what you are looking for is batch. you insert all statements in a batch and then execute the batch — XtremeBaumer, Dec 21 '16 at 08:02
there is mysql specific solution: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement — Vladimir, Dec 21 '16 at 08:18

score 3 · Answer 1 · answered Dec 21 '16 at 08:06

3

Is it possible to execute all at once?

this might depend on the implementation of the JDBC driver by the database vendor but in general I would not expect that to work.

answered Dec 21 '16 at 08:06

Timothy Truckle

15,071
2
27
51

Why it should depend on JDBC? Where did you found it? – Vyacheslav Dec 21 '16 at 08:07
The [API](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html) does not specify this behavior so the JDBC implementer is free to do anything she can think of. – Timothy Truckle Dec 21 '16 at 08:14
that is what batches are for – XtremeBaumer Dec 21 '16 at 14:33
@XtremeBaumer in the question the TO is refering to *different SQL within the same string* and this is not the target of *batches* in JDBC. – Timothy Truckle Dec 21 '16 at 14:36
then what is the target of a batch? and as i showed, you can execute different sql in 1 string, even tho i showed it with a string array – XtremeBaumer Dec 21 '16 at 14:39
@TimothyTruckle JDBC does specify the behavior: you execute a statement, not a collection of statements. It is just that some driver implementations find that too restrictive. – Mark Rotteveel Dec 22 '16 at 12:25

XtremeBaumer · Accepted Answer · 2016-12-21T09:40:24.723

1

String[] query = { "insert into Gericht (classification,date,name,preisExtern,preisIntern) values (?,?,?,?,?)",
            "test" };
    PreparedStatement stmt;
    for (String str : query) {
        stmt = c.prepareStatement(str);
        stmt.addBatch();
    }
    stmt.executeBatch();

here is an example on how to use batch. if its not what you want, please tell me so.

as wished:

@XtremeBaumer how about when the parameters in prepared statement change? how can you change it dynamically?

Answer:

you can't. if you want several different queries to be executed at once, you can only use fixed statement, otherwise your code will be very large, and then you can do it manually by setting the parameters and adding it to a batch. batches are good if you have 1 query that gets different parameters and you want to add all at once

edited Dec 21 '16 at 09:40

answered Dec 21 '16 at 08:04

XtremeBaumer

6,275
3
19
65

You wanna show me how to execute the same statement several times. Yes? – Vyacheslav Dec 21 '16 at 08:08
have a look now. was it really that hard to guess how to use several statements? – XtremeBaumer Dec 21 '16 at 08:12
@XtremeBaumer how about when the parameters in prepared statement change? how can you change it dynamically? – msagala25 Dec 21 '16 at 08:16
you can't. if you want several **different** queries to be executed at once, you can only use fixed statement, otherwise your code will be very large, and then you can do it manually by setting the parameters and adding it to a batch. batches are good if you have 1 query that gets different parameters and you want to add all at once – XtremeBaumer Dec 21 '16 at 08:19
Add your last comment to the answet. I will approve it in this case – Vyacheslav Dec 21 '16 at 09:38
This code is not going to work, it will only execute the last statement that was prepared (and if it works, that JDBC driver has a serious bug). – Mark Rotteveel Dec 22 '16 at 12:23
@MarkRotteveel why should it only execute the last statement? and why should the driver have a bug? – XtremeBaumer Dec 22 '16 at 14:56
Because you are preparing a new statement for each statement, calling add batch, and only calling execute batch on the last statement in the list. If that actually works (executes all those statements), that would mean that the driver collects batches over multiple statement objects, which goes against the JDBC specification (hence: a bug). `addBatch` on a prepared statement is to execute the same statement with multiple sets of parameters for that statement. – Mark Rotteveel Dec 22 '16 at 14:59

PreparedStatement for several statements

2 Answers2