PreparedStatement, addBatch() and setting parameters

Hi,

I am trying PreparedStatement's addBatch(String sql) method. I am trying to execute two(2) Insert statement.
But I am a little confuse how would I set the values for parameters in two insert statement. Trying to google
it but I did not see any example that uses more than one insert statement in one preparedstatement.

Here is what I am trying to do.

Code:

---

    private boolean insertOrder()
    {  boolean insertSuccessful = true;
        Connection conn = null;
        try
        {  String url="jdbc:mysql://aComputer:3306/myDatabase";
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, "user", "password");
            conn.setAutoCommit(false);
        }
        catch(Exception ex){  ex.printStackTrace();    }

        try
        {
            String sql = "INSERT INTO Table1(Field1, Field2, Field3, Field4) "
                    + "VALUES(?, ?, ?, ?)";

            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, strForField1);
            pst.setString(2, strForField2);
            pst.setInt(3, intForField3);
            pst.setString(4, strForField4);

            sql = "INSERT INTO Table2(FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG) "
                    + "VALUES(?, ?, ?, ?, ?, ?, ?)";
            pst.addBatch(sql);
            pst.setString(1, strForFieldA);
            pst.setString(2, strForFieldB);
            pst.setString(3, strForFieldC);
            pst.setString(4, strForFieldD);
            pst.setString(5, strForFieldE);
            pst.setString(6, strForFieldF);
            pst.setString(7, strForFieldG);

            pst.execute();

            pst.clearParameters();
            pst.close();

            System.out.println("DONE!")
        }
        catch(Exception ex){  ex.printStackTrace();    }

        try{ if(conn != null){conn.close();}    }
        catch(Exception ex){  ex.printStackTrace();    }

        return insertSuccessful;
    }

---

Thanks for the help,
geje

A PreparedStatement is a single statement that is often used multiple times with different bound variables. You can't (I'm fairly sure) change the actual statement.

You can, using the addBatch(String) method that you're currently using, add concatenated statements to the batch, but you won't be able to bind the variables to them.

When executing a batch you use exceuteBatch() by the way.

Uhm, the easy answer is to use two PreparedStatements. You can have two PreparedStatements open over the same Connection.

They won't batch though, will they?
Which is what I thought the OP was talking about.

You "batch" each one individually (i.e. addBatch and executeBatch on each one independent of the other).

Yes...but there's no point batching if they're not being executed in sequence, and you're only doing it once in the first place.
That is sqlA is batched, then sqlB is batched, repeat in a loop for however many jobs you have...then execute.
You can't do that with two separate PreparedStatements.

The solution there is to use a stored procedure, of course, but the OP had several misconceptions that I thought I'd better save that up for later.

No, I can guarantee that the rows in statement2 depend on rows in statement1 but that the reverse is not true. So, so long as you executeBatch on statement1 first you can then executeBatch on statement2. Doing this sort of thing in a loop is a snap and no problem, what-so-ever to coordinate.

Edit: Ach, nevermind. It has to due with a grand total of two statements. If those are not getting executed in bulk than batches don't come into question in the first place. If they are being executed in bulk then the statement above stands. And a stored procedure is not worth it either, in either case. Unless the DB is a real dog the two statements won't make any real difference unless performed in bulk, and, if performed in bulk, probably adds more overhead than using two preparedstatements as outlined here.

They can't actually be related in any meaningful way, can they?
This must be a case of Dim Monday for me...

Since they're batched, nothing from the first statement can impact on the second in any case since you can't feed the results (say a foreign key id) into the second. So I'm now confused as to what the OP is actually trying to achieve here.

Oh, and thoroughly stood in a corrected way...:)

masijade is correct, statement2 depends on statement1.
Tolls is also correct, I am trying to do this in batch.

This will NOT be executed in bulk, however I would like to know what will be the right solution if ever I will need to execute insert or update statements in bulk.

Quote:

---

You can, using the addBatch(String) method that you're currently using, add concatenated statements to the batch, but you won't be able to bind the variables to them.

---

I thought that I should not concatenate strings to give values to the parameters...

Quote:

---

When executing a batch you use exceuteBatch() by the way.

---

thanks. I will check my codes. :)

Quote:

---

The solution there is to use a stored procedure, of course, but the OP had several misconceptions that I thought I'd better save that up for later.

---

I will not deny it. I am always trying to apply what I have read and learned in forums and tutorials. Actually, I apply database normalization which results me to have 2 tables (Table1 and Table2) so I am thinking that this should be executed in batch.

Thanks, :)

No. You need a transaction. Whether done in one call, or two, is irrelevant (once again, unless done in bulk), as long as the data is not committed until both statements have executed. The amount of extra time needed to execute a second statement call, as oppossed to doing two statements in a single call, is negligible. Unless, once again, being done in bulk. What you need to do is to make sure that both statements are executed before the data is committed. So, as mentioned, a transaction.

Make sure autocommit is off, then execute both statements, then call commit on the connection.

To Tolls and masijade,
Thank you for the time you gave and for the explanation, I really appreciated your help.