Good evening guys, I have a filter that I do manually with @query () that in it I do a total calculating join of a sale ie
SUM(valor * quantidade) as total
I'm not sure what to do. what happens is this, this total "temporary" field that refers to the total of the sale, when I create it in my entity the jpa wants to persist it in the bank, hence I use @transient but it is not visible in the view, I would have to have a setTotal () in the controller so it can have some value ...
I've tried it in several ways, see below ....
@NumberFormat(pattern = "#,##0.00")
transient BigDecimal total;
In this way, it is not visible in the view, it does not return the value of the bank, only if I give a setTotal ().
@Column(updatable = false, insertable = false) // até assim (não funciona)
@NumberFormat(pattern = "#,##0.00")
@Transient
private BigDecimal valortemporario;
As above, it is visible in the view, but tries to persist in the database, which gives error, since the field does not exist in the table.
@Column(updatable = false, insertable = false) // até assim (não funciona)
@NumberFormat(pattern = "#,##0.00")
private BigDecimal valortemporario;
That way, it also does not work, it tries to write to the tbm bank ..
I just left my Repository just to see the general notion of the thing:
public interface AtendimentosRepository extends JpaRepository<Atendimentos, Long> {
String sqlPrincipal = "SELECT id_heados, pecastotal.total as total, "
+" maoobra_heados,+"veiculo_km_heados,bsextras_heados,"
+" tipoatendimento_heados, "
+ " datasaida_heados FROM heados "
+ " LEFT JOIN ( (SELECT pedos.controle_pedos,adicional_pedos,"
+ " sum( ( COALESCE(valor_pedos,'0') * COALESCE(quantidade_pedos,'0') ) - ( "
+ " ( COALESCE(valor_pedos,'0') * COALESCE(quantidade_pedos,'0') ) * COALESCE(desconto_pedos,'0') /100"
+ " ) ) as total FROM pedos as pedos WHERE adicional_pedos = 'N'"
+ " group by pedos.controle_pedos) ) pecastotal ON controle_pedos = id_heados"
+ " WHERE id_heados = :controle ";
@Query(value = sqlPrincipal, nativeQuery = true)
public Atendimentos atendimentoByControle(@Param("controle") Long controle);
}
I tried to find a way to remove from my Entity class the field to the total, but I did not find any function for that.
Has anyone gone through this ?? have another solution ??
Thank you !!