Good day, community!
Problem:
I am trying to simply SELECT orders from DB using SpringData and CrudRepository<Order, Long>, but receiving next errors:
2021-11-22 12:34:27.787 WARN 3119 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 42601
2021-11-22 12:34:27.788 ERROR 3119 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: syntax error at or near "order"
Position: 1136
Another entities like User, Contract or Producer are fetching normally. Also I tried fetch Order without OrderProduct but error was the same.
Entities:
OrderEntity
@Entity
@Table(name = "order")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
public Long getId() { return id; }
@ManyToOne
@JoinColumn(name = "creator_id")
private User creator;
public User getCreator() { return creator; }
public void setCreator(final User creator) { this.creator = creator; }
@ManyToOne
@JoinColumn(name = "contract_id")
private Contract contract;
public Contract getContract() { return contract; }
public void setContract(final Contract contract) { this.contract = contract; }
@OneToMany(cascade = CascadeType.ALL, mappedBy = "order")
private List<OrderProduct> orderProducts;
public void setOrderProducts(final List<OrderProduct> orderProducts) { this.orderProducts = orderProducts; }
public List<OrderProduct> getOrderProducts() { return orderProducts; }
private Date arrivingDate;
public void setArrivingDate(final Date arrivingDate) { this.arrivingDate = arrivingDate; }
public Date getArrivingDate() { return arrivingDate; }
@Enumerated(EnumType.STRING)
private Status status;
public void setStatus(final Status status) { this.status = status; }
public Status getStatus() { return status; }
private Date creationDate;
public void setCreationDate(final Date creationDate) { this.creationDate = creationDate; }
public Date getCreationDate() { return creationDate; }
private String document;
public void setDocument(final String document) { this.document = document; }
public String getDocument() { return document; }
protected Order() {}
public Order(User creator, Contract contract, List<OrderProduct> orderProducts, Date arrivingDate, String document, Status status, Date creationDate) {
this.creator = creator;
this.contract = contract;
this.arrivingDate = arrivingDate;
this.status = status;
this.creationDate = creationDate;
this.document = document;
this.orderProducts = orderProducts;
}
}
OrderProductEntity
@Entity
@Table(name = "order_product")
public class OrderProduct {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
public Long getId() { return id; }
@ManyToOne
@JoinColumn(name = "product_id")
private Product product;
public Product getProduct() { return product; }
public void setProduct(final Product product) { this.product = product; }
@ManyToOne
@JoinColumn(name = "order_id")
private Order order;
public Order getOrder() { return order; }
public void setOrder(final Order order) { this.order = order; }
private Integer quantity;
public void setQuantity(final Integer quantity) { this.quantity = quantity; }
public Integer getQuantity() { return quantity; }
protected OrderProduct() {}
public OrderProduct(final Product product, final Order order, final Integer quantity) {
this.product = product;
this.order = order;
this.quantity = quantity;
}
}
Fetching code
@Override
public Order findById(final Long id) throws Exception {
return orderRepository.findById(id)
.orElseThrow(() -> new Exception("There is no order by id:" + id));
}
Hibernate PSQL debug:
select
order0_.id as id1_2_0_,
order0_.arriving_date as arriving2_2_0_,
order0_.contract_id as contract6_2_0_,
order0_.creation_date as creation3_2_0_,
order0_.creator_id as creator_7_2_0_,
order0_.document as document4_2_0_,
order0_.status as status5_2_0_,
contract1_.id as id1_1_1_,
contract1_.creator_id as creator_4_1_1_,
contract1_.end_date as end_date2_1_1_,
contract1_.producer_id as producer5_1_1_,
contract1_.start_date as start_da3_1_1_,
user2_.id as id1_0_2_,
user2_.email as email2_0_2_,
user2_.name as name3_0_2_,
user2_.password as password4_0_2_,
user2_.phone as phone5_0_2_,
user2_.position as position6_0_2_,
user2_.surname as surname7_0_2_,
producer3_.id as id1_4_3_,
producer3_.city as city2_4_3_,
producer3_.contact_email as contact_3_4_3_,
producer3_.contact_name as contact_4_4_3_,
producer3_.contact_phone as contact_5_4_3_,
producer3_.country as country6_4_3_,
producer3_.street as street7_4_3_,
user4_.id as id1_0_4_,
user4_.email as email2_0_4_,
user4_.name as name3_0_4_,
user4_.password as password4_0_4_,
user4_.phone as phone5_0_4_,
user4_.position as position6_0_4_,
user4_.surname as surname7_0_4_
from
order order0_ left outer join
contract contract1_
on order0_.contract_id=contract1_.id
left outer join
client user2_
on contract1_.creator_id=user2_.id
left outer join
producer producer3_
on contract1_.producer_id=producer3_.id
left outer join
client user4_
on order0_.creator_id=user4_.id
where
order0_.id=?
DB diagram:
Thankful for your time and attention!
