java – Hibernate: problem with JOIN FETCH – entity with two relationships as embeededID


I’m developing a project using hibernate and i have small problem with fetching all related objects in one select query.
Data model is OrderDetails that has inside Product, Order as a Composite Id(Embeeded object)
below implementation of that entites

Orders:

@Entity
public class Orders {

@Id
@Column(name = "order_id")
@SequenceGenerator(name = "orderSEQ", sequenceName = "order_id_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "orderSEQ")
private short orderId;
@Column(name = "order_date")
private LocalDate orderDate;
@Column(name = "required_date")
private LocalDate requiredDate;
@Column(name = "shipped_date")
private LocalDate shippedDate;
@Column(name = "freight")
private Float freight;
@Column(name = "ship_name")
private String shipName;
@Column(name = "ship_address")
private String shipAddress;
@Column(name = "ship_city")
private String shipCity;
@Column(name = "ship_region")
private String shipRegion;
@Column(name = "ship_postal_code")
private String shipPostalCode;
@Column(name = "ship_country")
private String shipCountry;

@ManyToOne(targetEntity = Shippers.class)
@JoinColumn(name = "ship_via", referencedColumnName = "shipper_id")
private Shippers shippers;

@ManyToOne
@JoinColumn(name = "employee_id", referencedColumnName = "employee_id")
private Employees employees;

@ManyToOne
@JoinColumn(name = "customer_id", referencedColumnName = "customer_id")
private Customers customers;

@OneToMany(targetEntity = OrderDetails.class, fetch = FetchType.LAZY)
@JsonIgnore
private Set<OrderDetails> orderDetails;

Products:

@Entity
@Table(name = "products")
public class Products {

@Id
@Column(name = "product_id")
@SequenceGenerator(name = "productSEQ", sequenceName = "product_id_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "productSEQ")
private short productId;
@Column(name = "product_name")
private String productName;
@Column(name = "quantity_per_unit")
private String quantityPerUnit;
@Column(name = "unit_price")
private BigDecimal unitPrice;
@Column(name = "units_in_stock")
private Short unitsInStock;
@Column(name = "units_on_order")
private Short unitsOnOrder;
@Column(name = "reorder_level")
private Short reorderLevel;
@Column(name = "discontinued")
private int discontinued;

@ManyToOne
@JoinColumn(name = "supplier_id", referencedColumnName = "supplier_id")
private Suppliers suppliers;

@ManyToOne
@JoinColumn(name = "category_id", referencedColumnName = "category_id")
private Categories categories;

OrderDetails:

@Entity
@Table(name = "order_details")
public class OrderDetails {

@EmbeddedId
private OrderDetailsId orderDetailsId;
@Column(name = "unit_price")
private float unitPrice;
@Column(name = "quantity")
private short quantity;
@Column(name = "discount")
private float discount;

OrderDetailsId:

@Embeddable
public class OrderDetailsId implements Serializable {

@ManyToOne
@JoinColumn(name = "order_id", referencedColumnName = "order_id")
private Orders orders;

@ManyToOne
@JoinColumn(name = "product_id", referencedColumnName = "product_id")
private Products products;

And example query:

@Repository
public class OrderDetailsRepository extends AbstractRepository {

public List<OrderDetails> getAll() {
    Session session = getOpenSession();
    Transaction transaction = session.beginTransaction();
    List<OrderDetails> orderDetails = session
            .createQuery("SELECT o FROM OrderDetails o" +
                    " LEFT JOIN FETCH o.orderDetailsId.orders ord" +
                    " LEFT JOIN FETCH ord.customers c" +
                    " LEFT JOIN FETCH ord.employees e" +
                    " LEFT JOIN FETCH ord.shippers sh " +
                    " LEFT JOIN FETCH o.orderDetailsId.products p" +
                    " LEFT JOIN FETCH p.categories cat" +
                    " LEFT JOIN FETCH p.suppliers s", OrderDetails.class)
            .list();
    transaction.commit();
    session.close();
    return orderDetails;
}

This implemenatation ends up with plenty of select calls to the database.
Could someone please point out what is wrong with this query and how to get rid of the problem?
I just want to grab the OrderDetails with all its relationships in one query (changes on database structure is not allowed)

Thank you in advance



Source link

xandoblogs

An open minded personality.. fun to be with, because of my positive vibes. God fearing, for without God I am nothing.. Moved with compassion when dealing with you, not selfish or self-centered...

Leave a Reply

Your email address will not be published. Required fields are marked *

error

Enjoy this blog? Please spread the word :)