Java MySQL preparedStatement Batch

Question

I am trying to use the preparedStatement Batch but I am having a problem.

The following code does not give me errors, but it inserts in the table only last key of the map and I do not know why.

It will be surely a very stupid error, but this is the first time I use the addBatch() method..

        Class.forName("com.mysql.jdbc.Driver");
        this.connect = DriverManager.getConnection("jdbc:mysql://localhost/" + this.database + "?user=" + this.user + "&password=" + this.password);
        String s;
        for (String key : this.map.keySet())
        {
            s = ("insert into " + this.database + ".user (nickname) values (?)");
            this.preparedStatement = this.connect.prepareStatement(s);
            this.preparedStatement.setString(1, key);
            this.preparedStatement.addBatch();
        }

        this.preparedStatement.executeBatch();

Thanks in advance!

There's no good reason for 'connect' and 'preparedStatement' to be member variables. All that's accomplishing is introducing thread-unsafety, and scope for other errors. Make them local variables. — user207421
– user207421, Commented Feb 5, 2013 at 23:12
@EJP yes, I have taken part of code from an example.. I will correct it when I will be more confident with it. Thanks for the suggestion! — Ikki
– Ikki, Commented Feb 5, 2013 at 23:21

PermGenError · Accepted Answer · 2013-02-05 23:03:14Z

6

Prepare your Statement and query outside of the loop:

      s = ("insert into " + this.database + ".user (nickname) values (?)");
      this.preparedStatement = this.connect.prepareStatement(s);
      for (String key : this.map.keySet())
        {
            this.preparedStatement.setString(1, key);
            this.preparedStatement.addBatch();
        }
        this.preparedStatement.executeBatch();

answered Feb 5, 2013 at 23:03

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

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

1 Comment

Ikki Over a year ago

Thanks! Now it works! Now i would have another question about batch process, I will open another question! Take a look in few minutes please! :)

Miguel Prz · Accepted Answer · 2013-02-05 23:03:25Z

3

You are using the addBatch() method wrong. In your code you are doing the prepareStatement in each iteration of for loop, and this replaces the prepared query each time.

You should only be calling prepareStatement once per batch. You should place the prepared statement before the loop (only one call)

answered Feb 5, 2013 at 23:03

Miguel Prz

13.8k32 silver badges42 bronze badges

Collectives™ on Stack Overflow

Java MySQL preparedStatement Batch

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related