Spring Boot Embeddable Composite Key with H2 returns JdbcSQLSyntaxErrorException - Field not found even though the field exists

Question

I am building a SpringBoot API and have entity CarEntity with a composite key

@Entity
@Table(name = "CAR")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ToString
public class CarEntity implements Serializable {

    private static final long serialVersionUID = ...;

    @EmbeddedId
    private CarEntityId id; // COMPOSITE PK

    @Column(name = "SELL_ID")
    private String sellId;

    @Formula("amount / 100")
    @Column(name = "AMOUNT")
    private Double amount;

    @Column(name = "DESCRIPTION")
    private String description;

    ...
}

And here is the composite key embeddable is CarEntityId defined as:

@Embeddable
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ToString
public class CarEntityId implements Serializable {

    private static final long serialVersionUID = -...

    @Column(name = "PRODUCTION_DATE", length = 8, nullable = false)
    @Convert(converter = CarProductionDateConverter.class)
    private LocalDate productionDate;

    @Column(name = "TYPE_CODE", length = 1, nullable = false)
    private String typeCode;

    @Column(name = "SEQ_NUM", length = 4, nullable = false)
    private int seqNum;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "ASSEMBLY_ID", nullable = false)
    private AssemblyEntity assemblyEntity;

    @Override
    public boolean equals(Object o) {
        ...
    }

    @Override
    public int hashCode() {
        return ...
    }
}

When I issue GET request to get some data, I get InvalidDataAccessResourceUsageException:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement

its cause field is a JdbcSQLSyntaxErrorException:

rg.h2.jdbc.JdbcSQLSyntaxErrorException: Column "CARENTITY0_.AMOUNT" not found

WHAT I FOUND SO FAR:

I looked in H2 console CAR table and I found that indeed, the AMOUNT field is missing. The only difference for AMOUNT field definition in the CarEntity (it is nullable) is that it is annotated with @Format annotation.

I am trying to understand why I am getting this error but I am not going anywhere with it. Any help would be greatly appreciated.

Answer 1

In the @Formula annotation you should use raw SQL. In your case:

Formula("AMOUNT / 100")

instead of

Formula("amount / 100")

And remove the @Column annotation. The field should be calculated and not mapped from the database column.

If you want to have a column created by JPA then you should separate both fields. Like amount and amountInCents.

Answer 2

Based on the information provided, the issue seems to be related to the missing "AMOUNT" column in the CAR table of the H2 database.

This issue can be caused by using the @Formula annotation on the amount field of the CarEntity class. The @Formula annotation is typically used to define a SQL formula that calculates derived values ​​based on other columns in the table. In your case, it looks like you are trying to calculate the value "AMOUNT" based on the "Amount" column divided by 100.

However, the @Formula annotation does not automatically create corresponding columns in the database table. Values ​​are only calculated when the entity is retrieved from the database. Therefore, the column 'AMOUNT' is missing in the database table and causes a JdbcSQLSyntaxErrorException.

There are several options to resolve this issue.

If you want to store the calculated "AMOUNT" value in the database, you should remove the @Formula annotation from the amount field and create a separate column for "AMOUNT" in the CAR table.

If you don't need to store the calculated 'AMOUNT' value in the database, you can remove the @Formula annotation and change the field's type 'Amount' to 'int' or 'long' instead of 'Double'. You can then use the code to calculate the amount on the fly by dividing the amount value by 100. If you want to keep the @Formula annotation and don't want the AMOUNT values ​​saved to the database, you can modify the query or condition to exclude the AMOUNT column from the SELECT clause.

Always update the database schema after changing entity classes.