CODEWITHSUNDEEP
javamysql
ajw
ajw

Reputation: 2702

How to execute a query within a loop in java

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?

Upvotes: 1

Views: 3043

Answers (2)

ThomasThiebaud
ThomasThiebaud

Reputation: 11969

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
}

Upvotes: 3

Danail Alexiev
Danail Alexiev

Reputation: 7772

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.

Upvotes: 2

Related Questions