java PreparedStatement Arraylist SQL

Question

Can not insert new values to my DB from ArrayList; i want to add all ArrayList to DB columns list, title, about values from ArrayLists pars.getListId(), getTitleId(), pars.getAboutAr().

String query = "insert into test (list, title, about)" + "VALUES (?, ?, ?)";

    try {
        pars.htmlParse("hello");
        PreparedStatement preparedStmt = db.getConnection().prepareStatement(query);
        for (int i = 0; i < pars.getListId().size(); i++) {
            preparedStmt.setString(1, pars.getListId().get(i));
            preparedStmt.executeUpdate();
        }
        for (int i = 0; i < pars.getTitleId().size(); i++) {
            preparedStmt.setString(2, pars.getTitleId().get(i));
            preparedStmt.executeUpdate();
        }
        for (int i = 0; i < pars.getAboutAr().size(); i++) {
            preparedStmt.setString(3, pars.getAboutAr().get(i));
            preparedStmt.executeUpdate();
        }
        preparedStmt.execute();


        } catch (IOException e1) {
        e1.printStackTrace();

Answer 1

You have to set all three columns before you call executeUpdate even once.

You are doing:

        preparedStmt.setString(1, pars.getListId().get(i));
        preparedStmt.executeUpdate();

So you only set the first parameter in the prepared statement, and then you call executeUpdate. But you need to have all three.

You should have a loop that inserts all three together:

    for (int i = 0; i < pars.getListId().size(); i++) {
        preparedStmt.setString(1, pars.getListId().get(i));
        preparedStmt.setString(2, pars.getTitleId().get(i));
        preparedStmt.setString(3, pars.getAboutAr().get(i));
        preparedStmt.executeUpdate();
    }

This sets all three columns in the statement before executing it. So you will get a proper insert statement with all values set.

One problem here is that it might happen that pars.getListId() has 5 items, for example, while pars.getTitleId() and pars.getAboutAr() have 4 items. Then, when you run the loop above, when i gets to the value 4 you'll get an exception, because pars.getTitleId() doesn't have a value 4 (because it has only 4 items).

This can have several solutions.

1. If you know that the result of your parsing is always equal-length lists, then it's not a problem.

2. You can insert only the values up to the shortest list. You can have a calculation like this:

   int minSize = Collections.min( Arrays.asList( pars.getListId().size(),
                                                 pars.getTitleId().size(),
                                                 pars.getAboutAr().size() ) );
   

   And then use

   for (int i = 0; i < minSize; i++) {
       preparedStmt.setString(1, pars.getListId().get(i));
       preparedStmt.setString(2, pars.getTitleId().get(i));
       preparedStmt.setString(3, pars.getAboutAr().get(i));
       preparedStmt.executeUpdate();
   }
   

   And then it will give you as many rows as the shortest list.

3. You can go with the longest list (do max instead of min in the calculation above) and set nulls for the missing values (that is, check if the list is shorter than i requires. If so, use setNull instead of setString.

4. Personally, I would not build pars the way you have, with three separate lists. Instead, I would create a class, perhaps named Par or whatever makes sense, which has id, title and about fields. Then I would create a single list of Par objects in my pars instead of three separate lists. And then you will not have a problem with different lengths, and the data structure will be more like what you have in the database.