[FIXED] Spring Data JPA editing @query value on condition

Issue

Good morning,

I have the following query:

@Modifying(clearAutomatically = true)
@Transactional
@Query(nativeQuery = true, value = "UPDATE tb_pessoal_funcionarios SET salario = salario+(salario * :percentualAumento)/100 WHERE id IN (:funcionarioId) AND condicao <> 'Inativo' ")
void AlterarSalarioPorPercentual(Double percentualAumento, Long funcionarioId);

but if the user checks a checkbox on the screen, the query would have to be:

@Modifying(clearAutomatically = true)
@Transactional
@Query(nativeQuery = true, value = "UPDATE tb_pessoal_funcionarios SET salario = salario+(salario * :percentualAumento)/100 WHERE id IN (:funcionarioId)")
void AlterarSalarioDemitidosPorPercentual(Double percentualAumento, Long funcionarioId);

is there any way I can do this without having to do Two queries?

Solution

One possible way to do this is to pass a boolean flag from your controller, let's say filterByCondicao, and then have a clause like:

AND (:filterByCondicao is false or condicao <> 'Inativo')

When the checkbox is set, you should pass false, otherwise true when it's not set. This way when it's false the first part of the clause will evaluate to true and your row will be fetched, otherwise, when it's true it will filter by condicao column.

I don't have an example as is but you have to do the following:

1. Send a request parameter from your frontend like <your_url>?filterByCondicao=false when the checkbox is checked and true otherwise. I can't help you here because I work only with the backend and don't really have any front-end knowledge.
2. In your rest controller add a new parameter @RequestParam(required = true) Boolean filterByCondicao
3. Pass that parameter to your service and then to your repository, the repository query has to be updated as stated above.

Answered By - Vitaly Chura
Answer Checked By - Gilberto Lyons (JavaFixing Admin)