What is wrong with my native query in jpa?

Question

I'm sending a very simple query to the database, but I'm getting an error. It feels like I'm missing something very simple. I guess it wouldn't allow me to create it because the word order is a keyword on the h2 db, so I put it in quotation marks within the table annotation.

   @Query(value = "select * from `ORDER` o where o.basket_id= :basketId ", nativeQuery = true)
    Optional<Order> getOrderByBasketId(Long basketId);

@Entity
@Getter
@Setter
@Table(name = "`ORDER`")
public class Order extends BaseExtendedModel{
            private BigDecimal price;
            @Enumerated(EnumType.STRING)
            private OrderStatus orderStatus;
            @OneToOne
            private Customer customer;
            @OneToOne(cascade = CascadeType.MERGE)
            private Basket basket;
            @OneToOne(cascade = CascadeType.ALL, mappedBy = "order")
            private OrderAddress orderAddress;
}

{
    "errorMessage": "could not prepare statement; SQL [select * from `ORDER` o where o.basket_id= ? ]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement"
}

Answer 1

The problem is easier to identidy when you have a look at the logs. You'll see an entry like this:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "ORDER" not found; SQL statement:

So let's see what SQL statements are executed. So we add the following to application.properties

spring.jpa.show-sql=true

Assuming you let spring boot create your tables, you will see the following:

Hibernate: drop table if exists "order" CASCADE Hibernate: create table "order" ...

And when we hit the repository method we see

select * from `ORDER` o where o.name= ? [42102-200]

So why did it create the table in lowercase, even though we specified @Table(name = "`ORDER`")?

The default for spring.jpa.hibernate.naming.physical-strategy is org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy, which

• replaces dots with underscores
• changes CamelCase to snake_case
• lower-cases table names.

But we want it to take the names we use in @Table. That works when setting the property to spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl.

Yor native query will need the matching casing though.

Answer 2

You need to use index parameters

@Query(value = "select * from `ORDER` o where o.basket_id= ?1", nativeQuery = true)
Optional<Order> getOrderByBasketId(Long basketId);

or named parameters

@Query(value = "select * from `ORDER` o where o.basket_id= :basketId", nativeQuery = true)
Optional<Order> getOrderByBasketId(@Param("basketId") Long basketId);