Adding multiple strings into SQL database using for loop

Question

I'm currently scraping some scores from a HTML page and then inputting them into a SQL database. The scores are being parsed using Jsoup into an ArrayList. From here I'm converting the ArrayList to a String to allow it to be parsed into a VARCHAR field in the db. Although I can't seem to work out how to edit the for loop I have to insert all the values at once.

Here is my current code:

  Document doc = Jsoup.connect(URL).timeout(5000).get();

    for (Element table : doc.select("table:first-of-type")) //selects first table
    {
        for (Element row : table.select("tr:gt(0)")) { //selects first table cell
            Elements tds = row.select("td");//selects row

            List1.add(tds.get(0).text());
            List2.add(tds.get(1).text());
            List3.add(tds.get(2).text());
        }
    }
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO Scores (Home, Score, Away) VALUES (?,?,?)");

    String[] List1str = new String[List1.size()];
    List1str = List1.toArray(List1str);

    for (String s : List1str) {
        stmt.setString(1, s);
        stmt.setString(2, "test");
        stmt.setString(3, "test");
        stmt.executeUpdate();

    }

why not simply do List1Str.get(index)? why loop? Another issue, java naming convention, identifier name starts with small case letter. — kosa, Oct 25 '13 at 18:45
Try using jdbc batchUpdate. http://stackoverflow.com/questions/14264953/how-is-jdbc-batch-update-helpful — Saurin, Oct 25 '13 at 18:50
@Saurin Yes, I see what you mean about using a batch update. Although I'm a little unsure as to how I would implement it here. As I would need all the values to be added to the batch for one row then it be executed then move onto the next row. I did think about coding it by having three separate for loops but which would addBatch then at the end would executeBatch but it doesn't seem to work. Unless I'm not understanding it correctly. — Frank_B, Oct 29 '13 at 09:24

score -1 · Answer 1 · edited Mar 29 '16 at 13:39

-1

for (int i = 0; i < dtm.getRowCount(); i++) {
    for (int j = 0; j < dtm.getColumnCount(); j++) {
        Object o = dtm.getValueAt(i, j);
        System.out.println("object from table is  : " + o);
        pst.setString(j + 1, (String) o);
    }
    pst.executeUpdate();
    pst.clearParameters();
}

edited Mar 29 '16 at 13:39

Nikolas Charalambidis

40,893
16
117
183

answered Mar 29 '16 at 11:36

Manigandan Raamanathan

1

Adding multiple strings into SQL database using for loop

1 Answers1