I am using Hibernate in my Java based REST API. I am using HQL mainly because it is easy. Also for this project, I am loading data from the main table, its associated tables, their associated tables and so on. The reason is then in the app, I have all the data I need in a one JSON string instead of running multiple methods.
Now, take a look at the following database structure.
Let’s take a look at the code. I am going to run getAll method on Stock. It will return me all the stock data + its product data + product’s vehicle_model data+ product’s spare_part data
import beans.Stock;
import beans.TempStock;
import dao.SessionFactoryBuilder;
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
public class StockDAOImpl implements StockDAOInterface {
private final SessionFactoryBuilder sessionFactoryBuilder = SessionFactoryBuilder.getInstance();
@Override
public List<Stock> getAllStock(Session session) {
Query query = session.createQuery("FROM Stock s");
List<Stock> list = (List<Stock>) query.list();
return list;
}
}
I am just posting the data grab code.
Hibernate took 40+ seconds to return me the data! I also noticed the more data I have the more time it will take.
Below are my entities
Stock
public class Stock implements java.io.Serializable {
private Integer idstock;
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
private Product product;
private int quantity;
private Date dateCreated;
private Date lastUpdated;
public Stock() {
}
public Stock(Product product, int quantity) {
this.product = product;
this.quantity = quantity;
}
public Stock(Product product, int quantity, Date dateCreated, Date lastUpdated) {
this.product = product;
this.quantity = quantity;
this.dateCreated = dateCreated;
this.lastUpdated = lastUpdated;
}
public Integer getIdstock() {
return this.idstock;
}
public void setIdstock(Integer idstock) {
this.idstock = idstock;
}
public Product getProduct() {
return this.product;
}
public void setProduct(Product product) {
this.product = product;
}
public int getQuantity() {
return this.quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
public Date getDateCreated() {
return this.dateCreated;
}
public void setDateCreated(Date dateCreated) {
this.dateCreated = dateCreated;
}
public Date getLastUpdated() {
return this.lastUpdated;
}
public void setLastUpdated(Date lastUpdated) {
this.lastUpdated = lastUpdated;
}
}
VehicleModel
public class VehicleModel implements java.io.Serializable {
private Integer idvehicleModel;
private String modelName;
private String code;
private boolean enable;
public VehicleModel() {
}
public VehicleModel(String modelName, boolean enable) {
this.modelName = modelName;
this.enable = enable;
}
public Integer getIdvehicleModel() {
return this.idvehicleModel;
}
public void setIdvehicleModel(Integer idvehicleModel) {
this.idvehicleModel = idvehicleModel;
}
public String getModelName() {
return this.modelName;
}
public void setModelName(String modelName) {
this.modelName = modelName;
}
public boolean getEnable() {
return this.enable;
}
public void setEnable(boolean enable) {
this.enable = enable;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}
SparePart
public class SparePart implements java.io.Serializable {
private Integer idsparePart;
private String sparePartName;
private String code;
private boolean enable;
public SparePart() {
}
public SparePart(String sparePartName, boolean enable) {
this.sparePartName = sparePartName;
this.enable = enable;
}
public Integer getIdsparePart() {
return this.idsparePart;
}
public void setIdsparePart(Integer idsparePart) {
this.idsparePart = idsparePart;
}
public String getSparePartName() {
return this.sparePartName;
}
public void setSparePartName(String sparePartName) {
this.sparePartName = sparePartName;
}
public boolean getEnable() {
return this.enable;
}
public void setEnable(boolean enable) {
this.enable = enable;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}
This is just a part of the SQL output made by the Hibernate, you can see it tried to take data from each table by trying to execute SQL Queries individually.
Hibernate: select stock0_.idstock as idstock1_5_, stock0_.idproduct as idproduc2_5_, stock0_.quantity as quantity3_5_, stock0_.date_created as date_cre4_5_, stock0_.last_updated as last_upd5_5_ from aaa.stock stock0_ Hibernate: select product0_.idproduct as idproduc1_0_0_, product0_.idspare_part as idspare_2_0_0_, product0_.idvehicle_model as idvehicl3_0_0_, product0_.unit_price as unit_pri4_0_0_, product0_.qrcode as qrcode5_0_0_, product0_.enable as enable6_0_0_, product0_.minimum_stock_level as minimum_7_0_0_, product0_.stock_reorder_level as stock_re8_0_0_ from aaa.product product0_ where product0_.idproduct=? Hibernate: select sparepart0_.idspare_part as idspare_1_4_0_, sparepart0_.spare_part_name as spare_pa2_4_0_, sparepart0_.code as code3_4_0_, sparepart0_.enable as enable4_4_0_ from aaa.spare_part sparepart0_ where sparepart0_.idspare_part=? Hibernate: select vehiclemod0_.idvehicle_model as idvehicl1_10_0_, vehiclemod0_.model_name as model_na2_10_0_, vehiclemod0_.code as code3_10_0_, vehiclemod0_.enable as enable4_10_0_ from aaa.vehicle_model vehiclemod0_ where vehiclemod0_.idvehicle_model=? Hibernate: select product0_.idproduct as idproduc1_0_0_, product0_.idspare_part as idspare_2_0_0_, product0_.idvehicle_model as idvehicl3_0_0_, product0_.unit_price as unit_pri4_0_0_, product0_.qrcode as qrcode5_0_0_, product0_.enable as enable6_0_0_, product0_.minimum_stock_level as minimum_7_0_0_, product0_.stock_reorder_level as stock_re8_0_0_ from aaa.product product0_ where product0_.idproduct=? Hibernate: select sparepart0_.idspare_part as idspare_1_4_0_, sparepart0_.spare_part_name as spare_pa2_4_0_, sparepart0_.code as code3_4_0_, sparepart0_.enable as enable4_4_0_ from aaa.spare_part sparepart0_ where sparepart0_.idspare_part=? Hibernate: select vehiclemod0_.idvehicle_model as idvehicl1_10_0_, vehiclemod0_.model_name as model_na2_10_0_, vehiclemod0_.code as code3_10_0_, vehiclemod0_.enable as enable4_10_0_ from aaa.vehicle_model vehiclemod0_ where vehiclemod0_.idvehicle_model=?
If this is normal SQL this will use Joins. How can I fix this issue and get the data as fast as possible? More importantly any way to tell Hibernate to run the query as a single unit, and use joins or something?
Advertisement
Answer
Answer is to use joins.
select s from Stock s join fetch s.product p join fetch p.sparePart sp join fetch p.vehicleModel v