java - 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?

question from:https://stackoverflow.com/questions/65908594/spring-data-save-bigdecimal-attribute-with-native-insert-query

Answer 1

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"