Results 1 to 11 of 11
  1. #1
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default 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.
    Java 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

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,805
    Rep Power
    19

    Default

    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.

  3. #3
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

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

  4. #4
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,805
    Rep Power
    19

    Default

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

  5. #5
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

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

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,805
    Rep Power
    19

    Default

    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.

  7. #7
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.
    Last edited by masijade; 12-06-2010 at 11:42 AM.

  8. #8
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,805
    Rep Power
    19

    Default

    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...:)

  9. #9
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

    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.


    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...


    When executing a batch you use exceuteBatch() by the way.
    thanks. I will check my codes. :)

    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, :)

  10. #10
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    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.

  11. #11
    mine0926 is offline Senior Member
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    580
    Rep Power
    5

    Default

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

Similar Threads

  1. Replies: 5
    Last Post: 11-21-2010, 02:08 PM
  2. Regarding PreparedStatement
    By adeeb in forum JDBC
    Replies: 0
    Last Post: 06-09-2008, 09:07 PM
  3. PreparedStatement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-05-2007, 03:56 PM
  4. Help me. PreparedStatement
    By Felissa in forum JDBC
    Replies: 2
    Last Post: 06-28-2007, 05:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •