Multiple insert in a loop in jdbc

Question

while (tokens.hasMoreTokens()) 
{
    keyword = tokens.nextToken();
    System.out.println("File= "+fileid+" Keyword=" + keyword);
    stmt.executeUpdate(
        "INSERT into TEXTVALUEINVERTEDINDEX " + "(FILEID, KEYWORD) values ('"
        + fileid + "', '" + keyword + "')"
    );      
}

This is the loop in which I'm updating the rows. The problem I'm facing is that when i run this only 1 value gets updated and when I comment the stmt.executeUpdate() line it displays all the possible entries in the database.

Answer 1

Your code should work. Make sure the sentence is not throwing any Exceptions when running by surrounding it with a try/catch block:

try {
    stmt.executeUpdate("INSERT into TEXTVALUEINVERTEDINDEX " + 
         "(FILEID, KEYWORD) "+"values ('"+fileid+"', '"+keyword+"')"); 
} catch (SQLException e) {
    e.printStackTrace();
}

You should also consider using a PreparedStament instead since its use is very appropriate for your described scenario:

Something like this:

String sql = "insert into textvalueinvertedindex (fileid, keyword) values (?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
while (tokes.hasMoreTokens()) {
    keywords = tokens.nextToken();
    pstmt.setString(1, fileid);
    pstmt.setString(2, keyword);
    pstmt.executeUpdate();
}
pstmt.close();

Answer 2

If you want all updates to be applied at once you can use batch execution, here is an example

Answer 3

You need to use preparedStatements...

PreparedStatement pStmt = connection.prepareStatement("INSERT into TEXTVALUEINVERTEDINDEX (FILEID, KEYWORD) values(?,?)");
while (tokens.hasMoreTokens()) 
    {
        keyword = tokens.nextToken();
        System.out.println("File= "+fileid+"    Keyword="+keyword);

        pStmt.setString(1, fileid); //This might be pStmt.SetInt(0, fileid) depending on teh type of fileid)
        pStmt.setString(2, keyword);

        pStmt.executeUpdate();
    }

then using this you can extend to us batch update...

PreparedStatement pStmt = connection.prepareStatement("INSERT into TEXTVALUEINVERTEDINDEX (FILEID, KEYWORD) values(?,?)");
    while (tokens.hasMoreTokens()) 
        {
            keyword = tokens.nextToken();
            System.out.println("File= "+fileid+"    Keyword="+keyword);

            pStmt.setString(1, fileid); //This might be pStmt.SetInt(0, fileid) depending on teh type of fileid)
            pStmt.setString(2, keyword);

            pStmt.addBatch();
        }
pStmt.executeBatch();

Not sure why your code isn't working though - but this will probably help in the long run...

Answer 4

Your date range and filter selection contained no results.