What kind of treatment can be performed in this case?

Question

At the moment I want to learn more about exception treatment, a topic that is very well commented by @Maniero. I read several of his answers about exceptions, but I still had a question.

The method prepareStatement() as well as the methods setString(), setInt(), etc, require the treatment of a SQLException.

After reading several articles I understood that manipulating an exception and treating it are your different things.

In my view, the code below is only manipulating the exception but does not treat 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 an appropriate treatment for this exception in this case.
2. By manipulating this checked Exception, storing the StrackTrace in a log and showing a warning to the user that something could not be done update of the data, it would be considered that the exception was treated?
3. And if the above topic were added to clear the fields for filling in personal data and return to the initial data change screen for the user to try editing again, it would be considered that the exception was treated?

Answer 1

1. I would like to see an example of an appropriate treatment for this exception in this case.

It depends a lot on the type of system.

If it is desktop, you can show a dialog box with the error message. If it is web, you can return the page to the user with the error message in some part of HTML.

The important thing when dealing with an exception is that the behavior of the system is consistent, that it does not fall into a worse error yet and does not hide the user’s mistakes.

How to display error messages should not be the responsibility of database access classes (Data Access Objects, for example), so one way to perform a proper treatment is to encapsulate the database exceptions in a generic database exception and treat 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 in a log using its own API, as "printing" errors for the output is not a good practice.

In addition, it encapsulates any exception in a custom exception DataBaseException. This exception would be used in all database access routines.

Another detail is that a block is required finally to close the connection, otherwise an error would result in leakage of connections, i.e., multiple open and unopened connections. That’s a big headache in poorly planned systems. The server starts crashing after some time of use and it will take someone to figure out the problem.

The second catch does not need treatment beyond the log because it just tries to close the connection. If this fails the problem is lower and there is not much to do.

In general, it is "ugly" to capture 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. And there’s a rule of the business world: does not show technical error to the user.

Miss a mistake like NullPointerException or StringIndexOutOfBroundsException to the screen is worse than showing a generic error. The message "unexpected error occurred, contact support" is sometimes the most useful thing for the user who knows nothing about technology.

Error cases where 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 display 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 putting catch with Exception, but I will leave it at that for example. This mechanism could consist of a global treatment routine that showed the user different messages for each type of exception.

Let’s imagine now a method that treats the user action to update the data from Pessoa.

If it were 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 were 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 above method receives a request POST to update the person. If successful the user is directed to the search screen with the success message. If failure occurs, the user is directed to the edit screen showing the person and also the error message.

Another approach would be to treat separately SQLException of another kind of Exception, but it must be examined whether in practice it will make any difference, because replicating the error treatment to do the same thing would simply not make sense.

2. By manipulating this checked Exception, storing Stracktrace in a Log and showing a warning to the user that something that could not be done to update the data, it would be considered that the exception has been handled?

Yes, since it ensures that the system will not be in an inconsistent state.

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

For cases, for example, there are changed several tables, it is important to place the changes in a transaction and do the rollback in case of failure.

Take 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();
    }
}

3. And if the above topic were added to clear the fields for filling in personal data and return to the initial data change screen for the user to try editing again, it would be considered that the exception was treated?

Wiping the data is usually not a good idea, after all it is too much work and headache for the user.

Showing the fields with problem is a better task if it is the case. For this it is important to validate the fields before to try to execute the query in the database.

In general, the constraints of the database as Unic or not null should be used to ensure the integrity of the bank, but for user input validation they should be the last resource.

And if the SQL error, as an incorrect syntax, will not help the user to try again, it is probably a system bug.

Considerations

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

It is best to code "securely" by validating all entries, creating unit tests to validate your queries and thus minimize any possibility of an SQL error.

Answer 2

1. Actually the way to treat depends on the need of the application. The requirements of the application will determine what you should do. The question you should ask is what should happen to the application if you have a particular problem.

   An important observation is that you are only capturing SQL exception. No problem in this, it may be the right one to do. But have you asked yourself if there are other exceptions that should be dealt with there as well? I’m just saying this for reflection, I’m not saying it’s wrong. Only you know what the app needs to do.

2. Yes, the exception was dealt with there. It did not solve the problem but was dealt with. If you do something useful, do something that is provided for in the application, there is a treatment. Log in and warn the user is usually one of the most common treatments, especially in SQL errors.

   In the presented code you are not doing either of the two things clearly, but I believe it is only because it is an example. Even so it is not presenting an error for the user, only it is not in a beautiful way. I don’t know if the application can continue running in reliable state after this. In the current form the method update has no mechanism to indicate that there has been an SQL error. Nor did it allow the exception to overflow to another method to treat or inform in any way that the error occurred. Without seeing the rest of the code something tells me that something wrong will happen as a result.

3. This procedure described is a way to treat the exception and solve the problem. I don’t know if I should clean the data (it was they who caused the problem and need to be typed again?).

   But there is a problem there. This method should just do an update. This method should not get involved with the user interface. The likely error there is that you are treating an exception where you should not. I say probable because I do not know the application as a whole.

   If the treatment is messing with the interface is likely what other method should treat this exception. In my answers I always say: don’t treat what you don’t know or can’t treat at that moment. In the update method you can not handle the interface (technically it is possible, but can disorganize the entire application, will hurt the principle of sole responsibility).

A possible solution

As the exception is checked the application is required to give some treatment, whatever it is, do not need to solve the problem. Want to know how to solve this in this method? Do not do anything in it, delegate to another method to solve the problem. Inform the compiler who calls the method update() should turn. If this method is part of the user interface, it will be the right place to handle the exception. If not, delegate again to another method.

And how to delegate to another method without hurting the exception checked? Simple, don’t capture it there and create a signature in the method requiring another method to handle the exception:

public void update(Pessoa pessoa) throws SQLException { //note o throws aqui
    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();
}

I put in the Github for future reference.

Note that this is a solution based on what you are saying in the question. It may be that the correct solution is to capture there within the same method. I just doubt it is. It’s not common to be.

Yeah, I know, I just said that that’s not the right place and I didn’t give the solution. I’m going to owe a more complete example because I don’t usually program in Java, especially with database and user interface.

If that hasn’t cleared your doubt, I hope you have another answer on the fly.

Answer 3

I will answer your questions by first making an analogy with real life, then commenting on the three points you raised:

Imagine that you are an employee of an industry and your direct boss asks you to go to a store and buy a certain product (say, the raw material). You take the car and arrive at this store at 12hrs and to your surprise the store is closed. At this time you notice an exception has occurred¹ since the store always opens from 8h to 18h (every day). But you will do everything to avoid major problems and then think: I’ll wait 30 minutes, sometimes the salesman went out for lunch and is already coming back². You go out for a walk and 30 minutes later you come back and you realize the store is still closed.³ - As you have no further indication that the store will reopen you realize that you no longer have any options since the only store you are allowed to buy is that.

You then go back to the industry and communicates your boss directly⁴. Your direct boss weirding out the situation notes that there was that problem for future audits⁵ and communicates the owner of the company⁶. The owner of the company immediately requests that your boss send you to a second shop⁷, you promptly fulfill the request and this time can successfully make the purchase.⁸

Moral of History

There isn’t always a mechanism to handle exceptions - for example, after trying a limited number of times you may conclude that your resources have run out to treat that exception and that’s why the right thing to do is to notify who asked you - pass on the exception - saying: I tried, but because of this ... I couldn’t.

When you make an exception you are basically "passing the problem on to your boss". Your boss may have a solution for each type of problem. In the analogy presented the boss spoke to the owner of the company because he also had no other way out for the LojaFechadaException. Maybe if the exception was PrecoAcimaDaMediaException your own boss could solve the problem without passing it to the owner of the company. That is why it is important to have specific exceptions and therefore it is bad to generalize them.

The fact is, if the exception came to the owner of the company and he didn’t even have a way out, a strategy of contouring, he couldn’t do anything but register what happened for future references: the famous sits and cries.

Explaining the Analogy (compare with the numbers in the text)

1- You notice that an exception has been made
2- You have a strategy to circumvent the exception
3- Your strategy did not work, you will cast the exception for someone treat
4- Whoever invoked his method is notified of the exception (the catch is invoked)
5- Also there is nothing to be done - it is only logged in the error log
6- You re-pass the exception to an even higher level
7- The last level has another mechanism to treat the exception that occurred there at the tip
8-The task is executed successfully and the user is happy

OBSERVING: If not even the last level knew how to treat the exception what usually happens is your program ends with non-zero code, or a failure.

Answering your questions

1- The treatment you gave may be the appropriate treatment. I don’t know the context of your program. If printing the log in the console is in fact your last instance then why would this be wrong? I just suggest you use a more interesting mechanism to log this log (outside the scope of the answer)

2- Yes. If this is the best you can do and you have no other options before displaying the error to the user, then this is an exception yes treatment - you even prevented your program from terminating.

3- You would only be giving work to your user. In fact this is more a matter of usability than exception handling.