CODEWITHSUNDEEP
javasqljdbcarraylist
Mitchell
Mitchell

Reputation: 67

java PreparedStatement Arraylist SQL

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();

Upvotes: 5

Views: 1747

Answers (3)

RealSkeptic
RealSkeptic

Reputation: 34638

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.

Upvotes: 1

MadProgrammer
MadProgrammer

Reputation: 347314

Instead of trying to set each parameter and then executing the update, which leaves the query in an invalidate state (as not all the parameters are set), you should, for each element, set each parameter and then execute the update.

A better solution would be then to use a batch update process, for example

String query = "insert into test (list, title, about)" + "VALUES (?, ?, ?)";
try (PreparedStatement ps = con.prepareStatement(query)) {
    pars.htmlParse("hello");
    for (int index = 0; index < pars.getListId().size(); index++) {
        ps.setString(1, pars.getListId().get(index));
        ps.setString(2, pars.getTitleId().get(index));
        ps.setString(3, pars.getAboutAr().get(index));
        ps.addBatch();
    }
    ps.executeBatch();
} catch (SQLException exp) {
    exp.printStackTrace();
}

Upvotes: 0

Jan
Jan

Reputation: 13858

You need to modify your code to each have a param 1, 2 and 3 before you executeUpdate().

I assume your lists have equal amount of entries (will break otherwise)

    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();
    }

Now you have all 3 set per insert.

Upvotes: 0

Related Questions