How to execute a query within a loop in java

Question

I have tried the below code to store all the data of array list(table).But doesn't give any error or the required output.

public class Dbops {
    String url = "jdbc:mysql://localhost:3306/ITStuffDB";
    String username = "root";
    String password = "";
    ResultSet rs = null;

    public boolean addData(ArrayList<ArrayList<String>> table){

        try {
            System.out.println(table.size());
            for (int i = 0; i < table.size(); i++) {
                Connection con1 = (Connection) DriverManager.getConnection(url, username, password);
                String query = "INSERT INTO Data (Col1,Col2,Col3) VALUES (?,?,?)";
                PreparedStatement pst1 = (PreparedStatement) con1.prepareStatement(query); 

                pst1.setString(1, table.get(i).get(0));
                pst1.setString(2, table.get(i).get(1)); 
                pst1.setString(3, table.get(i).get(2));                 
                pst1.executeUpdate();
                pst1.close();
                con1.close();
            }return true;

        } catch (Exception e) {
            return false;
        }
    }
}

How can i handle this correctly?

You are catching all exceptions, so of course you won't see any error. If you don't catch the exception (or at least print the stack trace), you may get an idea of what went wrong. — Eran
– Eran, Commented Nov 16, 2015 at 12:21

ThomasThiebaud · Accepted Answer · 2015-11-16 12:46:52Z

The comment is right, at least print the exception to know the problem.

Moreover, this is not a good idea to recreate the connexion and the statement each time. Have a look at the executeBatch() function

try {
    Connection con1 = (Connection) DriverManager.getConnection(url, username, password);
    String query = "INSERT INTO Data (Col1,Col2,Col3) VALUES (?,?,?)";
    PreparedStatement pst1 = (PreparedStatement) con1.prepareStatement(query); 
    for (int i = 0; i < table.size(); i++) {
        pst1.clearParameters();            
        pst1.setString(1, table.get(i).get(0));
        pst1.setString(2, table.get(i).get(1)); 
        pst1.setString(3, table.get(i).get(2));                 
        pst1.addBatch();
    }
    pst1.executeBatch();
    return true;
} catch (SQLException e) {
    return false;
} finally {
    //close everything
}

Danail Alexiev · Accepted Answer · 2015-11-16 12:38:54Z

2

The first comment is totally right.

Debugging can also help you trace what is your program. You should try and debug this method.

Also, as a recommendation - the whole idea of a PreparedStatement is to compile it once and then reuse it whenever possible. I would move the creation of the statement outside of the loop.

answered Nov 16, 2015 at 12:38

Danail Alexiev

7,8103 gold badges26 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to execute a query within a loop in java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related