dimanche 13 septembre 2015

SQL statement within while loop, is there a way to increase performance?

I'm very new to SQL and I implemented a SQLite database (no DMS) in one of my applications. Everything works like a charm but I have one problem in a method as stated below. This method should insert the same data into the database several times:

try (PreparedStatement statement = connection.prepareStatement(insertNewRequest)) {

            statement.setString(1, request.getDate().toString());
            statement.setString(2, request.getTime().toString());
            statement.setString(3, request.getRequestType().toString());
            statement.setString(4, request.getOperatingSystem().toString());
            statement.setString(5, request.getComment().toString());
            statement.setString(6, request.getLocation().toString());

            while (quantity > 0) {
                statement.executeUpdate();
                quantity--;
            }

}

Generally speaking this works but I experience a huge performance loss. As far as I know this could be because the database driver is locking and unlocking the database everytime?

So my question is: How would an more experienced Java/Sql developer implement this method? I could build the statement at runtime in the while loop but there I see the problem that I'm not able to prevent SQLInjection with the setString() method.

Thank you in advance!

Aucun commentaire:

Enregistrer un commentaire