I use only Spring Boot default configuration and Spring Data JDBC. I have 2 Entity. Order and DetailOrder
@Table(name = "orders")
public class Order {
@Id
@Column("id")
private Long id;
@Column(value = "order_number")
private String orderNumber;
@Column(value = "total_amount")
private Long totalAmount;
@Column(value = "order_date")
private Date orderDate;
@Column(value = "customer_name")
private String customerName;
@Column(value = "address")
private String deliveryAddress;
@Column(value = "payment_type")
private String paymentType;
@Column(value = "delivery_type")
private String deliveryType;
@MappedCollection(idColumn = "order_id", keyColumn = "id")
private List<DetailOrder> detailOrders;
public Order(Long id, String orderNumber, Long totalAmount, Date orderDate, String customerName, String deliveryAddress, String paymentType, String deliveryType, List<DetailOrder> detailOrders) {
this.id = id;
this.orderNumber = orderNumber;
this.totalAmount = totalAmount;
this.orderDate = orderDate;
this.customerName = customerName;
this.deliveryAddress = deliveryAddress;
this.paymentType = paymentType;
this.deliveryType = deliveryType;
this.detailOrders = detailOrders;
}
and all getters and setters
@Table(name = "products")
public class DetailOrder {
@Id
private Long id;
@Column(value = "product_article")
private Long productArticle;
@Column(value = "product_name")
private String productName;
@Column(value = "product_amount")
private int productAmount;
@Column(value = "product_price")
private int productPrice;
@Column(value = "order_id")
private Long orderId;
public DetailOrder(Long id, Long productArticle, String productName, int productAmount, int productPrice, Long orderId) {
this.id = id;
this.productArticle = productArticle;
this.productName = productName;
this.productAmount = productAmount;
this.productPrice = productPrice;
this.orderId = orderId;
}
OrderRepository
public interface OrderRepository extends ListCrudRepository<Order, Long> {
List<Order> findByOrderDateAndTotalAmountGreaterThan(Date date, long amount);
}
OrderService
public interface OrderService {
Order createOrder(Order order);
Optional<Order> getOrder(long id);
List<Order> findAll();
List<Order> findByOrderDateAndTotalAmountGreaterThan(Date date, long amount);
}
OrderServiceImpl
@Service
public class OrderServiceImpl implements OrderService {
private final OrderRepository orderRepository;
@Autowired
public OrderServiceImpl(OrderRepository orderRepository) {
this.orderRepository = orderRepository;
}
@Override
public Order createOrder(Order order) {
try {
order.setOrderNumber("generated");
order.setOrderDate(Date.valueOf(LocalDate.now()));
order.setTotalAmount(order.getDetailOrders().stream().mapToLong(detail -> detail.getProductPrice() * detail.getProductAmount()).sum());
return orderRepository.save(order);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
OrderController
@PostMapping("/orders")
public ResponseEntity<Order> createOrder(@RequestBody Order order) {
try {
Order response = orderService.createOrder(order);
System.out.println(response);
return new ResponseEntity<>(response, HttpStatus.CREATED);
} catch (Exception e) {
return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
}
}
Table Created like this
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY ,
product_article BIGINT NOT NULL,
product_name VARCHAR(255) NOT NULL,
product_amount INT NOT NULL,
product_price INT NOT NULL,
order_id BIGINT,
CONSTRAINT fk_order
FOREIGN KEY (order_id)
REFERENCES orders (id)
ON DELETE SET NULL
);
CREATE TABLE orders(
id BIGSERIAL PRIMARY KEY,
order_number varchar(255) not null,
total_amount bigint not null,
customer_name varchar(255) not null,
address varchar(255) not null,
delivery_type varchar(255),
payment_type varchar(255),
order_date date
)
When i try POST request
{
"customerName": "Andrew Kramer",
"deliveryAddress": "London",
"paymentType": "Wired",
"deliveryType": "TEST",
"detailOrders": [
{
"productArticle": 52341252,
"productName": "test8",
"productAmount": 4,
"productPrice": 2000
},
{
"productArticle": 52341252,
"productName": "test9",
"productAmount": 2,
"productPrice": 2000
}
]
}
Failed to execute BatchWithValue{actions=[Insert{entity=DetailOrder{id=null, productArticle=12151,
and this TRACE
Executing prepared SQL statement [INSERT INTO "products" ("id", "order_id", "product_amount", "product_article", "product_name", "product_price") VALUES (?, ?, ?, ?, ?, ?)]
2024-12-02T21:44:36.809+03:00 TRACE 16736 --- [orders_service] [ main] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 1, parameter value [0], value class [java.lang.Integer], SQL type 4
2024-12-02T21:44:36.809+03:00 TRACE 16736 --- [orders_service] [ main] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 2, parameter value [25], value class [java.lang.Long], SQL type -5
2024-12-02T21:44:36.809+03:00 TRACE 16736 --- [orders_service] [ main] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 3, parameter value [4], value class [java.lang.Integer], SQL type 4
2024-12-02T21:44:36.809+03:00 TRACE 16736 --- [orders_service] [ main] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 4, parameter value [12151], value class [java.lang.Long], SQL type -5
2024-12-02T21:44:36.809+03:00 TRACE 16736 --- [orders_service] [ main] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 5, parameter value [Morojja], value class [java.lang.String], SQL type 12
2024-12-02T21:44:36.809+03:00 TRACE 16736 --- [orders_service] [ main] o.s.jdbc.core.StatementCreatorUtils : Setting SQL statement parameter value: column index 6, parameter value [1200], value class [java.lang.Integer],
What am i doing wrong?????
I am trying all things. Change constructors. Use @PersistenceCreator. Try delete keyColumn = "id". But if delete i have new error about orders_key Executing prepared SQL statement [INSERT INTO "products" ("order_id", "orders_key", "product_amount", "product_article", "product_name", "product_price") VALUES (?, ?, ?, ?, ?, ?)]. I tried change List to Set and have same error
You are explicitly trying to manage foreign key in DetailOrder class , which is causing the issues. let spring data jpa take care of it automatically as you are using @MappedCollection in your Order class. So you need to remove below code from your DetailOrder class.
@Column(value = "order_id") private Long orderId;
And also remove it from your constructor as well , remove this line this.orderId = orderId;
Also, i see that there is no need for having keyColumn = "id" in your @MappedCollection in your Order class , so remove it . so it should look something like this
@MappedCollection(idColumn = "order_id") private List detailOrders;
Your updated code should be like this for DetailOrder class and Order class :
@Table(name = "products") public class DetailOrder {
@Id
private Long id;
@Column(value = "product_article")
private Long productArticle;
@Column(value = "product_name")
private String productName;
@Column(value = "product_amount")
private int productAmount;
@Column(value = "product_price")
private int productPrice;
public DetailOrder(Long id, Long productArticle, String productName, int productAmount, int productPrice) {
this.id = id;
this.productArticle = productArticle;
this.productName = productName;
this.productAmount = productAmount;
this.productPrice = productPrice;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getProductArticle() {
return productArticle;
}
public void setProductArticle(Long productArticle) {
this.productArticle = productArticle;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public int getProductAmount() {
return productAmount;
}
public void setProductAmount(int productAmount) {
this.productAmount = productAmount;
}
public int getProductPrice() {
return productPrice;
}
public void setProductPrice(int productPrice) {
this.productPrice = productPrice;
}
}
@Table(name = "orders") public class Order {
@Id
@Column("id")
private Long id;
@Column(value = "order_number")
private String orderNumber;
@Column(value = "total_amount")
private Long totalAmount;
@Column(value = "order_date")
private Date orderDate;
@Column(value = "customer_name")
private String customerName;
@Column(value = "address")
private String deliveryAddress;
@Column(value = "payment_type")
private String paymentType;
@Column(value = "delivery_type")
private String deliveryType;
@MappedCollection(idColumn = "order_id")
private List<DetailOrder> detailOrders;
public Order(Long id, String orderNumber, Long totalAmount, Date orderDate, String customerName, String deliveryAddress, String paymentType, String deliveryType, List<DetailOrder> detailOrders) {
this.id = id;
this.orderNumber = orderNumber;
this.totalAmount = totalAmount;
this.orderDate = orderDate;
this.customerName = customerName;
this.deliveryAddress = deliveryAddress;
this.paymentType = paymentType;
this.deliveryType = deliveryType;
this.detailOrders = detailOrders;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public Long getTotalAmount() {
return totalAmount;
}
public void setTotalAmount(Long totalAmount) {
this.totalAmount = totalAmount;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getDeliveryAddress() {
return deliveryAddress;
}
public void setDeliveryAddress(String deliveryAddress) {
this.deliveryAddress = deliveryAddress;
}
public String getPaymentType() {
return paymentType;
}
public void setPaymentType(String paymentType) {
this.paymentType = paymentType;
}
public String getDeliveryType() {
return deliveryType;
}
public void setDeliveryType(String deliveryType) {
this.deliveryType = deliveryType;
}
public List<DetailOrder> getDetailOrders() {
return detailOrders;
}
public void setDetailOrders(List<DetailOrder> detailOrders) {
this.detailOrders = detailOrders;
}
}