I have two entities Requirement and Product in a Spring Boot Application. Requirement has One to Many relationship with Product.
In the product repo interface I have a simple query method that fetches list of products with given requirement_id. Here's the code:
interface ProductRepo: JpaRepository<Product, Int>{
fun findProductsByRequirement_Id(id:Int) : List<Product>
}
However when this method is called from a Rest Controller I can see that along with each product, hibernate is returning the corresponding requirement object instead of just requirement_id. It doesn't stop there, it also returns every foreign objects (Deal & Sale) associated with the requirement object in the JSON result.
I have set this option "spring.jpa.properties.hibernate.show_sql=true" in the application.properties file and every time that query method is called I see in the console that Hibernate keeps calling lots of complex outer join queries even I haven't asked for it. In my opinion this is consuming unnecessary resource when I don't need for all these foreign objects.
Here is the sql queries that hibernate runs in the background:
Hibernate: select requiremen0_.id as id1_8_0_, requiremen0_.deal_id as deal_id4_8_0_, requiremen0_.requirement_total as requirem2_8_0_, requiremen0_.sale_id as sale_id5_8_0_, requiremen0_.title as title3_8_0_, deal1_.id as id1_4_1_, deal1_.account_id as account_7_4_1_, deal1_.crm_customer_id as crm_cust8_4_1_, deal1_.exp_closing_date as exp_clos2_4_1_, deal1_.requirement_title as requirem3_4_1_, deal1_.sales_value as sales_va4_4_1_, deal1_.stage as stage5_4_1_, deal1_.type as type6_4_1_, deal1_.user_id as user_id9_4_1_, account2_.id as id1_0_2_, account2_.address as address2_0_2_, account2_.crm_customer_id as crm_cust9_0_2_, account2_.email as email3_0_2_, account2_.key_person as key_pers4_0_2_, account2_.name as name5_0_2_, account2_.phone as phone6_0_2_, account2_.status as status7_0_2_, account2_.type as type8_0_2_, account2_.user_id as user_id10_0_2_, crmcustome3_.id as id1_2_3_, crmcustome3_.address as address2_2_3_, crmcustome3_.email as email3_2_3_, crmcustome3_.key_person as key_pers4_2_3_, crmcustome3_.name as name5_2_3_, crmcustome3_.number_of_users as number_o6_2_3_, crmcustome3_.phone as phone7_2_3_, crmcustome3_.subscription_type as subscrip8_2_3_, crmcustome3_.validity_exp_date as validity9_2_3_, user4_.id as id1_11_4_, user4_.crm_customer_id as crm_cust8_11_4_, user4_.designation as designat2_11_4_, user4_.hierarchy_level as hierarch3_11_4_, user4_.logged_in as logged_i4_11_4_, user4_.login_id as login_id5_11_4_, user4_.login_pw as login_pw6_11_4_, user4_.name as name7_11_4_, sale5_.id as id1_9_5_, sale5_.crm_customer_id as crm_cust4_9_5_, sale5_.deal_id as deal_id5_9_5_, sale5_.sales_date as sales_da2_9_5_, sale5_.sales_value as sales_va3_9_5_, sale5_.user_id as user_id6_9_5_, crmcustome6_.id as id1_2_6_, crmcustome6_.address as address2_2_6_, crmcustome6_.email as email3_2_6_, crmcustome6_.key_person as key_pers4_2_6_, crmcustome6_.name as name5_2_6_, crmcustome6_.number_of_users as number_o6_2_6_, crmcustome6_.phone as phone7_2_6_, crmcustome6_.subscription_type as subscrip8_2_6_, crmcustome6_.validity_exp_date as validity9_2_6_, deal7_.id as id1_4_7_, deal7_.account_id as account_7_4_7_, deal7_.crm_customer_id as crm_cust8_4_7_, deal7_.exp_closing_date as exp_clos2_4_7_, deal7_.requirement_title as requirem3_4_7_, deal7_.sales_value as sales_va4_4_7_, deal7_.stage as stage5_4_7_, deal7_.type as type6_4_7_, deal7_.user_id as user_id9_4_7_, user8_.id as id1_11_8_, user8_.crm_customer_id as crm_cust8_11_8_, user8_.designation as designat2_11_8_, user8_.hierarchy_level as hierarch3_11_8_, user8_.logged_in as logged_i4_11_8_, user8_.login_id as login_id5_11_8_, user8_.login_pw as login_pw6_11_8_, user8_.name as name7_11_8_ from requirements requiremen0_ left outer join deals deal1_ on requiremen0_.deal_id=deal1_.id left outer join accounts account2_ on deal1_.account_id=account2_.id left outer join crm_customers crmcustome3_ on deal1_.crm_customer_id=crmcustome3_.id left outer join users user4_ on deal1_.user_id=user4_.id left outer join sales sale5_ on requiremen0_.sale_id=sale5_.id left outer join crm_customers crmcustome6_ on sale5_.crm_customer_id=crmcustome6_.id left outer join deals deal7_ on sale5_.deal_id=deal7_.id left outer join users user8_ on sale5_.user_id=user8_.id where requiremen0_.id=?
What can I do to avoid this situation?
Here are the Entity classes for Product and Requirement:
package com.ksk.crmserver.Model
import com.fasterxml.jackson.annotation.JsonIgnore
import javax.persistence.*
@Entity
@Table(name = "requirements")
data class Requirement(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Int = 0,
@Column(name = "title")
val title: String = "",
@OneToMany(mappedBy = "requirement")
@JsonIgnore
val products: List<Product>? = null,
@Column(name = "requirement_total")
val requirementTotal: Int = 0,
@ManyToOne
var deal: Deal? = null,
@ManyToOne
val sale: Sale?=null
)
package com.ksk.crmserver.Model
import javax.persistence.*
@Entity
@Table(name = "products", indexes = [Index(name = "idx_product", columnList = "name")])
data class Product(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
val id: Int = 0,
val name: String = "",
val description: String? = "",
@Column(name = "unit_cost")
val unitCost: Double = 0.0,
@Column(name = "qty")
val qty: Double? = 0.0,
val unit: String? = "",
@Column(name = "total_Cost")
val totalCost: Double? = 0.0,
@Column(name = "profit_margin")
val profitMargin: Double? = 0.0,
val profit: Double? = 0.0,
@Column(name = "vat_percentage")
val vatPercentage: Double? = 0.0,
val vat: Double? = 0.0,
@Column(name = "total_price")
val totalPrice: Double? = 0.0,
@ManyToOne
@JoinColumn(name = "requirement_id")
val requirement: Requirement? = null
)
Edit: I have tried using EntityManager.getReference to get an instance of Requirement Object. But it is still running a long sql query to fetch the Requirement by id from DB. Here is my code from the RestController class for Product:
I'm afraid it's still running the sql join query. Here is my Rest Controller code:
@RestController
@CrossOrigin
class ProductRoute(val productRepo: ProductRepo, val requirementRepo: RequirementRepo) {
@Autowired
val entityManager: EntityManager?=null
@GetMapping("/get/productsByReqId")
fun getProductsByRequirementId(@RequestParam id: Int): List<Product> {
val requirement = this.entityManager?.getReference(Requirement::class.java, id) as Requirement
print("Done fetching the requirement")
return this.productRepo.findProductsByRequirement(requirement)
}
This is the sql Hibernate is running:
Hibernate: select requiremen0_.id as id1_8_0_, requiremen0_.deal_id as deal_id4_8_0_, requiremen0_.requirement_total as requirem2_8_0_, requiremen0_.sale_id as sale_id5_8_0_, requiremen0_.title as title3_8_0_, deal1_.id as id1_4_1_, deal1_.account_id as account_7_4_1_, deal1_.crm_customer_id as crm_cust8_4_1_, deal1_.exp_closing_date as exp_clos2_4_1_, deal1_.requirement_title as requirem3_4_1_, deal1_.sales_value as sales_va4_4_1_, deal1_.stage as stage5_4_1_, deal1_.type as type6_4_1_, deal1_.user_id as user_id9_4_1_, account2_.id as id1_0_2_, account2_.address as address2_0_2_, account2_.crm_customer_id as crm_cust9_0_2_, account2_.email as email3_0_2_, account2_.key_person as key_pers4_0_2_, account2_.name as name5_0_2_, account2_.phone as phone6_0_2_, account2_.status as status7_0_2_, account2_.type as type8_0_2_, account2_.user_id as user_id10_0_2_, crmcustome3_.id as id1_2_3_, crmcustome3_.address as address2_2_3_, crmcustome3_.email as email3_2_3_, crmcustome3_.key_person as key_pers4_2_3_, crmcustome3_.name as name5_2_3_, crmcustome3_.number_of_users as number_o6_2_3_, crmcustome3_.phone as phone7_2_3_, crmcustome3_.subscription_type as subscrip8_2_3_, crmcustome3_.validity_exp_date as validity9_2_3_, user4_.id as id1_11_4_, user4_.crm_customer_id as crm_cust8_11_4_, user4_.designation as designat2_11_4_, user4_.hierarchy_level as hierarch3_11_4_, user4_.logged_in as logged_i4_11_4_, user4_.login_id as login_id5_11_4_, user4_.login_pw as login_pw6_11_4_, user4_.name as name7_11_4_, products5_.requirement_id as require13_5_5_, products5_.id as id1_5_5_, products5_.id as id1_5_6_, products5_.description as descript2_5_6_, products5_.name as name3_5_6_, products5_.profit as profit4_5_6_, products5_.profit_margin as profit_m5_5_6_, products5_.qty as qty6_5_6_, products5_.requirement_id as require13_5_6_, products5_.total_cost as total_co7_5_6_, products5_.total_price as total_pr8_5_6_, products5_.unit as unit9_5_6_, products5_.unit_cost as unit_co10_5_6_, products5_.vat as vat11_5_6_, products5_.vat_percentage as vat_per12_5_6_, sale6_.id as id1_9_7_, sale6_.crm_customer_id as crm_cust4_9_7_, sale6_.deal_id as deal_id5_9_7_, sale6_.sales_date as sales_da2_9_7_, sale6_.sales_value as sales_va3_9_7_, sale6_.user_id as user_id6_9_7_, crmcustome7_.id as id1_2_8_, crmcustome7_.address as address2_2_8_, crmcustome7_.email as email3_2_8_, crmcustome7_.key_person as key_pers4_2_8_, crmcustome7_.name as name5_2_8_, crmcustome7_.number_of_users as number_o6_2_8_, crmcustome7_.phone as phone7_2_8_, crmcustome7_.subscription_type as subscrip8_2_8_, crmcustome7_.validity_exp_date as validity9_2_8_, deal8_.id as id1_4_9_, deal8_.account_id as account_7_4_9_, deal8_.crm_customer_id as crm_cust8_4_9_, deal8_.exp_closing_date as exp_clos2_4_9_, deal8_.requirement_title as requirem3_4_9_, deal8_.sales_value as sales_va4_4_9_, deal8_.stage as stage5_4_9_, deal8_.type as type6_4_9_, deal8_.user_id as user_id9_4_9_, user9_.id as id1_11_10_, user9_.crm_customer_id as crm_cust8_11_10_, user9_.designation as designat2_11_10_, user9_.hierarchy_level as hierarch3_11_10_, user9_.logged_in as logged_i4_11_10_, user9_.login_id as login_id5_11_10_, user9_.login_pw as login_pw6_11_10_, user9_.name as name7_11_10_ from requirements requiremen0_ left outer join deals deal1_ on requiremen0_.deal_id=deal1_.id left outer join accounts account2_ on deal1_.account_id=account2_.id left outer join crm_customers crmcustome3_ on deal1_.crm_customer_id=crmcustome3_.id left outer join users user4_ on deal1_.user_id=user4_.id left outer join products products5_ on requiremen0_.id=products5_.requirement_id left outer join sales sale6_ on requiremen0_.sale_id=sale6_.id left outer join crm_customers crmcustome7_ on sale6_.crm_customer_id=crmcustome7_.id left outer join deals deal8_ on sale6_.deal_id=deal8_.id left outer join users user9_ on sale6_.user_id=user9_.id where requiremen0_.id=?
Done fetching the requirement 2019-02-16 21:01:00.341 INFO 80558 --- [nio-8080-exec-1] o.h.h.i.QueryTranslatorFactoryInitiator : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select product0_.id as id1_5_, product0_.description as descript2_5_, product0_.name as name3_5_, product0_.profit as profit4_5_, product0_.profit_margin as profit_m5_5_, product0_.qty as qty6_5_, product0_.requirement_id as require13_5_, product0_.total_cost as total_co7_5_, product0_.total_price as total_pr8_5_, product0_.unit as unit9_5_, product0_.unit_cost as unit_co10_5_, product0_.vat as vat11_5_, product0_.vat_percentage as vat_per12_5_ from products product0_ where product0_.requirement_id=?
