Spring Data. Save BigDecimal attribute with native insert query

Question

I have an entity:

@Data
public class MoneyEntity {

   @Id
   @GeneratedValue
   @Column(name = "id", updatable = false, nullable = false)
   private UUID id;

   @Column(name = "money")
   private BigDecimal money;

   @Column(name = "date")
   private Instant date;
}

The goal is to save it to PostgreSQL database with native query:

@Modifying
@Query(value = "INSERT INTO performance_info (id, money, date) VALUES "
        + "(:#{#entity.id}, "
        + ":#{#entity.nav}, "
        + ":#{#entity.date} )"
        + " ON CONFLICT (date) DO NOTHING", nativeQuery = true)
void saveWithout(MoneyEntity entity);

The issue is that BigDecimal values convert like bytea instead of numeric. Here is the error message:

Caused by: org.postgresql.util.PSQLException: ERROR: column "money" is of type numeric but expression is of type bytea Hint: You will need to rewrite or cast the expression.

I have already tried to add annotation with key and class @Type(type = "org.hibernate.type.BigDecimalType"). There is no effect.

Tried to implement custom converter:

@Converter
public class DecimalConverter implements AttributeConverter<BigDecimal, Double> {
    @Override
    public Double convertToDatabaseColumn(final BigDecimal attribute) {
        return attribute.doubleValue();
    }

    @Override
    public BigDecimal convertToEntityAttribute(final Double dbData) {
        return BigDecimal.valueOf(dbData);
    }
}

The issue was that only BigDecimal can be converted to Numeric Postgres type.

Is there any way to save BigDecimal attributes with native query using Spring Data?

Your error seems to be caused by the `date` column, not the `money` column — timsmelik, Jan 26 '21 at 20:19
@timsmelik sorry, I attached the wrong error message, the question is updated — Uladzislau Kaminski, Jan 27 '21 at 09:11
Take a look at this answer [here](https://stackoverflow.com/questions/4643592/which-postgresql-column-type-should-be-used-to-store-a-java-bigdecimal). — dextertron_, Jan 28 '21 at 01:18
@dextertron_ thanks for suggestion, I still don't see the the solution — Uladzislau Kaminski, Jan 28 '21 at 20:18

score 0 · Answer 1 · answered Jan 28 '21 at 20:29

0

You can use numeric or decimal and specify needed precision. Take a look at Postgresql documentation here.

For example:

t.decimal "money", precision: 12, scale: 4, default: "0.0"

answered Jan 28 '21 at 20:29

dextertron_

921
1
10
19

is the example is sample of request? – Uladzislau Kaminski Jan 29 '21 at 09:48

Spring Data. Save BigDecimal attribute with native insert query

1 Answers1