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"
}
CodePudding user response:
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.
CodePudding user response:
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 paremeters
@Query(value = "select * from `ORDER` o where o.basket_id= :basketId", nativeQuery = true)
Optional<Order> getOrderByBasketId(@Param("basketId") Long basketId);