liamw9 liamw9 - 1 year ago 54
SQL Question

Is it bad practice to create multiple PreparedStatements inside a single try-catch block?`

I'm using JDBC to execute SQL queries. If I have multiple SQL queries inside a method that need executing, is it bad practice to create several PreparedStatements inside the main try-catch block?

private Result method(long id) {
final String STMT1 = "SELECT ..."
final String STMT2 = "SELECT ..."
final String STMT3 = "SELECT ..."

List<Things> thingsList;

try(PreparedStatement p1 = c.prepareStatement(STMT1)) {
PreparedStatement p2 = c.prepareStatement(STMT2);
PreparedStatement p3 = c.prepareStatement(STMT3);

p1.setLong(1, id);
p2.setLong(2, id);
p3.setLong(3, id);

ResultSet r1 = p1.executeQuery();
ResultSet r2 = p2.executeQuery();
ResultSet r3 = p3.executeQuery();

while( && && {

} catch(){...}

If it is bad practice, how would I go about doing it in a more conventional way?

Answer Source

Although your question borders on asking for opinions, I want to focus on the things to think about for addressing what is a real issue. Here are three key considerations for thinking about this. There is a balance among:

  • Identifying the point-of-failure
  • Error handling
  • Coding simplicity and maintenance

Your example is not actually particularly interesting, because SELECT does not modify the database. So, there really is no error handling (in the sense of rolling back transactions, for instance). The main consideration for SELECTs is identifying the point of failure. That is a project-requirements issue.

When the steps actually modify the database, then they are often wrapped in a transaction that needs to be rolled back. Once again, whether you roll back in three separate catch blocks or in one is a balance among the above considerations. My personal preference is to find a way to do the rolling back only once for failures within a given transaction, although that may not always be possible.