java – What kind of treatment can be carried out in this case?

Question:

Right now I want to learn more about exception handling, a topic that is very well commented on by @Maniero. I read several of his responses about exceptions, but I still had a question.

The prepareStatement() method as well as the setString() , setInt() , etc. methods require handling an SQLException .

After reading several articles I understood that handling an exception and handling it are different things.

As I see it, the code below is just handling the exception but not handling it.

public void update(Pessoa pessoa) {

    try {
        PreparedStatement preparedStatement = conexao.prepareStatement(sqlUpdate);
        preparedStatement.setString(1, pessoa.getNome());
        preparedStatement.setString(2, pessoa.getCpf());
        preparedStatement.setInt(3, pessoa.getIdPessoa());
        preparedStatement.execute();
        preparedStatement.close();
        conexao.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
  1. I would like to see an example of a proper handling of this exception in this case.
  2. When handling this checked exception, saving the StrackTrace in a log and showing a warning to the user that something that was not possible to update the data, would it be considered that the exception was handled?
  3. And if the above topic was added to clear the fields for filling in personal data and return to the initial data change screen for the user to try to edit again, would it be considered that the exception was handled?

Answer:

  1. I would like to see an example of a proper handling of this exception in this case.

It depends a lot on the type of system.

If it's desktop, you might show a dialog box with the error message. If it's web, you can bounce the page back to the user with the error message somewhere in the HTML.

The important thing when handling an exception is that the system's behavior is consistent, that it doesn't fall into a worse error and doesn't hide the user's errors.

How to display error messages should not be the responsibility of the database access classes ( Data Access Objects , for example), so one way to perform proper handling is to encapsulate the database exceptions in a generic database exception and handle this other exception in control classes ( Controllers ).

For example:

public void update(Pessoa pessoa) throws DataBaseException {
    Connection conexao = getConexao();
    try {
        PreparedStatement preparedStatement = conexao.prepareStatement(sqlUpdate);
        preparedStatement.setString(1, pessoa.getNome());
        preparedStatement.setString(2, pessoa.getCpf());
        preparedStatement.setInt(3, pessoa.getIdPessoa());
        preparedStatement.execute();
        preparedStatement.close();
        conexao.close();
    } catch (Exception e) {
        logger.error(e); 
        throw new DataBaseException("Erro ao atualizar a pessoa!", e);
    } finally {
        if (conexao != null) {
            try {
               conexao.close();
            } catch (SQLException e) {
                logger.error(e); 
            }
        }     
    }
}

The above method saves the original error to a log file using its own API, as "printing" errors to the output is not a good practice.

Furthermore, it wraps any exception in a custom DataBaseException exception. This exception would be used in all database access routines.

Another detail is that a finally block is needed to close the connection, otherwise an error would result in connections leaking, that is, several open and unclosed connections. That's a big headache in poorly designed systems. The server starts to hang after some time of use and it will take time for someone to discover the problem.

The second catch doesn't need processing beyond the log as it only tries to close the connection. If that fails the problem is more down and there's not much you can do.

In general, it's "ugly" to catch a generic exception like Exception . But there is a reason for this. There are cases, such as the developer putting an extra parameter in the PreparedStatement , where an error other than SQLException would occur. And there is a rule of thumb in the business world: don't show the technical error to the user .

StringIndexOutOfBroundsException an error like NullPointerException or StringIndexOutOfBroundsException to the screen is worse than showing a generic error. The message "an unexpected error has occurred, please contact support" is sometimes the most helpful for the user who is not tech-savvy.

Error cases in which the user can actually act can be dealt with specifically. For example, if the user tries to delete a record that is a foreign key from another table, then it is possible to show a specific message that it is necessary to delete the data from the other table before deleting the current record.

You can make a more sophisticated mechanism than just catch with Exception , but I'll leave it that way in this answer for the purpose of example. This mechanism could consist of a global handler that shows the user different messages for each type of exception.

Now let's imagine a method that handles the user action to update Pessoa data.

If it was a desktop system:

public void doCliqueBotarAtualizarAction() {
    Pessoa pessoa = recuperarPessoaDosCamposDaTela();
    try {
        pessoaDAO.update(pessoa);
        mostrarDialogoSucesso("Dados alterados com sucesso!");
    } catch (DataBaseException e) {
        mostrarDialogoErro(e.getMessage());
    }
}

The above method simply shows a success or failure box depending on the case.

If it was a web system with Spring MVC, for example:

@RequestMapping(value="/atualizaPessoa", method=RequestMethod.POST)
public ModelAndView atualizarPessoa(Pessoa pessoa) {
    try {
        pessoaDAO.update(pessoa);
        return new ModelAndView("tela-pesquisa")
            .addObject("mensagem", "Dados alterados com sucesso!");
    } catch (DataBaseException e) {
        return new ModelAndView("tela-edicao")
            .addObject("pessoa", pessoa)
            .addObject("mensagemErro", e.getMessage());
    }
}

The method above receives a POST request to update the person. If successful, the user is directed to the search screen with the success message. If it fails, the user is taken to the editing screen showing the person and also the error message.

Another approach would be to handle SQLException separately from another type of Exception , but you should analyze whether in practice it will make any difference, as replicating the error handling to do the same thing would simply not make sense.

  1. When handling this checked exception, saving the StrackTrace in a Log and showing a warning to the user that something that it was not possible to update the data, would it be considered that the exception was handled?

Yes, as long as it guarantees that the system will not be left in an inconsistent state.

Showing the message is enough if there are no unwanted side effects.

For cases, for example, when several tables are changed, it is important to place the changes in a transaction and roll back them in case of failure.

See an example:

public void transferencia(Conta fonte, Conta destino, BigDecimal valor) throws BusinessException {
    boolean sucesso = false;
    iniciarTransacao();
    try {
        contaDao.debitar(fonte, valor);
        contaDao.creditar(destino, valor);
        sucesso = true;
    } catch (Exception e) {
        logger.error(e);
        throw new BusinessException("Não foi possível efetuar a transferência!", e);
    } finally {
        if (sucesso) efetuarTransacao();
        else desfazerTransacao();
    }
}
  1. And if the above topic was added to clear the fields for filling in personal data and return to the initial data change screen for the user to try to edit again, would it be considered that the exception was handled?

Cleaning the data is generally not a good idea, after all it is a lot of work and a headache for the user.

Showing the problem fields is a better task if at all. For this it is important to validate the fields before trying to execute the query in the database.

In general, database constraints such as unique or not null should be used to ensure database integrity, but for user input validation they should be a last resort.

And if the error is SQL, such as incorrect syntax, it won't do the user to try again, it's probably a system bug.

Considerations

The level of detail in error handling varies greatly from case to case.

It is best to code in a "safe" way validating all input well, creating unit tests to validate your queries and thus minimizing any possibility of an error in SQL.

Scroll to Top