@Entity
@Table(name = "product_table")
public class Product {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Product_id" , nullable = false, unique= true , length = 5)
    private int ProductId;
    
    @Column(name = "Product_Name" ,nullable = false , length = 50)
    private String ProductName;
    
    @Column(name = "Description" ,nullable = false , length = 200)
    private String Description;
    
    @Column(name ="Price" , nullable = false, unique= true , length = 5)
    private Double Price;
    
    @Column(name = "Discount" , nullable = false, unique= true , length = 5)
    private Double Discount;
    
    @Column(name ="Delivery_Charges" , nullable = false, unique= true , length = 5)
    private Double DeliveryCharges;
    
    @Column(name = "Avg_Rating",nullable = false, unique= true , length = 5)
    private int AvgRating;
    
    @Column(name = "Seller_Name",nullable = false, unique= true , length = 15)
    private String SellerName;
    
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "Product_id", insertable = false, updatable = false)
    @Fetch(FetchMode.JOIN)
    private Cart Cart;  
    
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ProductId", insertable = false, updatable = false)
    @Fetch(FetchMode.JOIN)
    private WishList WishList;  

    public String getSellerName() {
        return SellerName;
    }

    public void setSellerName(String sellerName) {
        SellerName = sellerName;
    }

    public int getProductId() {
        return ProductId;
    }

    public void setProductId(int productId) {
        ProductId = productId;
    }

    public String getProductName() {
        return ProductName;
    }

    public void setProductName(String productName) {
        ProductName = productName;
    }

    public String getDescription() {
        return Description;
    }

    public void setDescription(String description) {
        Description = description;
    }

    public Double getPrice() {
        return Price;
    }

    public void setPrice(Double price) {
        Price = price;
    }

    public Double getDiscount() {
        return Discount;
    }

    public void setDiscount(Double discount) {
        Discount = discount;
    }

    public Double getDeliveryCharges() {
        return DeliveryCharges;
    }

    public void setDeliveryCharges(Double deliveryCharges) {
        DeliveryCharges = deliveryCharges;
    }

    public int getAvgRating() {
        return AvgRating;
    }

    public void setAvgRating(int avgRating) {
        AvgRating = avgRating;
    }
}

@Entity
@Table(name = "Cart_table")
public class Cart {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Cart_Id" , nullable = false, unique = true, length = 5)
    private int CartId;
    
    @Column(name = "Product_Name" , nullable = false,  length = 50)
    private String ProductName;
    
    @Column(name = "Seller_Name" , nullable = false,length = 15)
    private String SellerName;
    
    @Column(name = "Quantity" , nullable = false)
    private int Quantity;
    
    @Column(name = "Cart_Offer_Price" , unique = true)
    private Double CartOfferPrice;
    
    @Column(name = "Product_id" , nullable = false, unique = true, length = 5)
    private int ProductId;

    
    public int getProductId() {
        return ProductId;
    }
    public void setProductId(int productId) {
        ProductId = productId;
    }
    
    @OneToOne(targetEntity = Product.class, mappedBy = "Cart", orphanRemoval = false, fetch = FetchType.LAZY)
    private Product product;
    

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "Cart_Id", insertable = false, updatable = false)
    @Fetch(FetchMode.JOIN)
    private OrdersCartMapping OrdersCartMapping;
        
    public int getCartId() {
        return CartId;
    }
    public void setCartId(int cartId) {
        CartId = cartId;
    }
    public String getProductName() {
        return ProductName;
    }
    public void setProductName(String productName) {
        ProductName = productName;
    }
    public String getSellerName() {
        return SellerName;
    }
    public void setSellerName(String sellerName) {
        SellerName = sellerName;
    }
    public int getQuantity() {
        return Quantity;
    }
    public void setQuantity(int quantity) {
        Quantity = quantity;
    }
    public Double getCartOfferPrice() {
        return CartOfferPrice;
    }
    public void setCartOfferPrice(Double cartOfferPrice) {
        CartOfferPrice = cartOfferPrice;
    }
}

@Repository       
public interface CartRepository extends JpaRepository<Cart , Integer> {
            
    @Query("SELECT new com.megamartonline.dto.CartProductDto(c.ProductName,c.CartOfferPrice,c.Quantity , p.Price ,p.Discount,p.DeliveryCharges,c.CartId ) "
              "FROM Cart c INNER JOIN c.product p")
    List<CartProductDto> fetchProductCartDataInnerJoin();
}

Here i m trying to join Product with Cart using Product_id column but when i test my repository method it is incorrectly joining as below

 select
        cart0_.product_name as col_0_0_,
        cart0_.cart_offer_price as col_1_0_,
        cart0_.quantity as col_2_0_,
        product1_.price as col_3_0_,
        product1_.discount as col_4_0_,
        product1_.delivery_charges as col_5_0_ 
    from
        cart_table cart0_ 
    inner join
        product_table product1_ 
            on cart0_.cart_id=product1_.product_id

please help , what i am doing wrong here. Here i m trying to join 2 Product with Cart using Product_id column but when i test my repository method it is incorrectly joining.

CodePudding user response：

In Product, instead of productId, you have to declare the join to JPA in the entity this way:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "Product_id")
private Product Product;

Hibernate now knows how to handle the join. You can't use both an ID column and a JoinColumn; it won't know which one to use, so you should remove productId.

I suggest a @ManyToOne because the @OneToOne join you are using seems strange for a cart. Usually a cart has multiple (many) products.

CodePudding user response：

You can easily change the fetch type from lazy to eager