I'm using an "insert or update" query such as the one below:
String sql =
"INSERT INTO servlets (path, applicationId, startTime, numOfRequests, totalResponseTime, totalBytes)" +
"VALUES (?, ?, NOW(), 1, ?, ?)" +
"ON DUPLICATE KEY UPDATE numOfRequests = numOfRequests + 1, " +
"totalResponseTime = totalResponseTime + ?, totalBytes = totalBytes + ?";
I'm using prepared statements and fill it with the relevant arguments in the following manner:
statement = connection.prepareStatement(sql);
statement.setString(1, i_ServletModel.GetPath());
statement.setInt(2, i_ServletModel.GetApplicationId());
statement.setLong(3, i_RequestStats.GetResponseTime());
statement.setLong(4, i_RequestStats.GetBytes());
statement.setLong(5, i_RequestStats.GetResponseTime());
statement.setLong(6, i_RequestStats.GetBytes());
Notice that argument 3 is exactly the same as argument 5 and argument 4 is exactly the same as argument 6 since they require the same value in the query above.
Is there anything I can change, either in the query or in the arguments filling methods to avoid such an "ugly" syntax?
Using a local variable, you can make the code less ugly and error-prone. But the shortcoming of JDBC
that it does not support named parameters still holds. There will be again multiple lines for the same parameter.
statement = connection.prepareStatement(sql);
long time = i_RequestStats.GetResponseTime();
long bytes = i_RequestStats.GetBytes();
statement.setString(1, i_ServletModel.GetPath());
statement.setInt(2, i_ServletModel.GetApplicationId());
statement.setLong(3,time);
statement.setLong(4, bytes);
statement.setLong(5, time);
statement.setLong(6, bytes);
Can't you change the SQL syntax to refer back to the 'VALUES' you've already declared? Something like the following:
String sql =
"INSERT INTO servlets (path, applicationId, startTime, numOfRequests, totalResponseTime, totalBytes)" +
"VALUES (?, ?, NOW(), 1, ?, ?)" +
"ON DUPLICATE KEY UPDATE numOfRequests = numOfRequests + 1, " +
"totalResponseTime = totalResponseTime + VALUES(5), totalBytes = totalBytes + VALUES(6)";
...that way you need only add each parameter once.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With