CODEWITHSUNDEEP
javamysqlsqlconnection
Paul
Paul

Reputation: 169

statement.executeBatch() vs statement.updateQuery() when there is only one query to be executed

OK, I know that Batch Processing allows to group related SQL statements into a batch and submit them with one call to the database. When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance. In this particular situation (see code below) I don't think batching does it's sole purpose. Cause stmt.executeBatch() is called straight away after adding a batch(?) Wouldn't stmt.executeUpdate() do the same thing?

public void tableChanged(TableModelEvent e) {
    int row = e.getFirstRow();
    int col = e.getColumn();
    model = (MyTableModel) e.getSource();
    String stulpPav = model.getColumnName(col);
    Object data = model.getValueAt(row, col);
    Object studId = model.getValueAt(row, 0);
    System.out.println("tableChanded works");
    try {
        new ImportData(stulpPav, data, studId);
        bottomLabel.setText(textForLabel());
    } catch (ClassNotFoundException e1) {
        e1.printStackTrace();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}
public class ImportData {    

    public ImportData(String a, Object b, Object c)
            throws ClassNotFoundException, SQLException {
        Statement stmt = null;
        try {
            connection = TableWithBottomLine.getConnection();
            String stulpPav = a;
            String duom = b.toString();
            String studId = c.toString();
            System.out.println(duom);
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
            stmt.addBatch("update finance.fin set " + stulpPav + " = " + duom
                    + " where ID = " + studId + ";");
            stmt.executeBatch();
            connection.commit();
        } catch (BatchUpdateException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (stmt != null)
                stmt.close();
            connection.setAutoCommit(true);
            System.out.println("Data was imported to database");
        }
    }   
}

Upvotes: 2

Views: 1839

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109014

In this case using batch has no advantage at all. It might even introduce additional overhead over a direct executeUpdate (but that is driver and database dependent).

However, don't assume that batching has advantages with all JDBC drivers. I haven't looked at the specifics of MySQL, but I know there are JDBC drivers where batching internally is a normal execute for each statement in the batch.

The code in your question however has a bigger problem: it is vulnerable to SQL injection.

Upvotes: 5

Related Questions