Is it good practice to put all Java JDBC Select statements in a try-catch block ? Currently I write most of my code without it. However, I do try-catch for insert/update/delete.

String sqlQuery = "Select productId, productName, productStartDate from dbo.product where productId = 5"

public getProductData() {
    ....
    List<Product> productList =  namedJdbcTemplate.query(sqlQuery, new ProductMapper());

CodePudding user response：

Try-catch for DB Layer code is important if you're querying with JDBC.

Think about, what if the connection broke? Or what if Database crashed ? Or some other unfortunate scenario comes up.

For these things, I will recommend you to always keep the DB layer code within try-catch.

It's also recommended for you to have some fallback mechanism in-case of the above events.

CodePudding user response：

First of all, if you're working with raw JDBC API, you should always use PreparedStatement.

Yes, you'll just have to wrap the code with try-catch block at some point, though it's a good practice to catch exceptions just right away or at the point where it's logically suits. In case of SQL queries, you actually should wrap all of them into some Service class that will give you an access to modify your database objects without running through JDBC API every time. For example:

public class UserService {
    private static final String CREATE_USER_SQL = "...";
    private final Connection jdbcConnection;

    public @Nullable User createUser(final String name) {
        try (final PreparedStatement stmt = jdbcConnection.prepareStatement(CREATE_USER_SQL)) {
            jdbcConnection.setAutoCommit(false);
            stmt.setString(1, name);
            stmt.executeQuery();
            jdbcConnection.commit();
            return new User(name);
        } catch (final SQLException createException) {
            System.out.printf("User CREATE failed: %s\n", createException.getMessage());
            try {
                jdbcConnection.rollback();
            } catch (final SQLException rollbackException) {
                System.out.printf("Rollback failed: %s\n", rollbackException.getMessage());
            }
            return null;
        }
    }
}

This solves two problems right away:

1. You won't need to put boilerplate JDBC code everywhere;

2. It will log any JDBC errors right away, so you won't need to go through a complex debugging process.