1

I have a lookup table with the schema

enter image description here

In my controller I call this table based on "lookup_type"

    FormProdIndex f = new FormProdIndex();
    model.addAttribute(AuthenticationBase.MODEL_FORMPRODINDEX, f);

    // Lookup Models
    System.out.println("TEST 1");
    model.addAttribute(AuthenticationBase.LOOKUP_FACTORY, this.appLookupDAO.getFactoryList());

This lookup model is associated with my main model "FormProdIndex"

@Entity
@Table(name = "Form_Prod_Index")
public class FormProdIndex {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "index_id", nullable = false)
    private Long indexId;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "factory_Id", referencedColumnName = "lookup_Id")
    private AppLookup lookupFactory;

    // getter and setters
}

and my lookup model

@Entity
@Table(name = "App_Lookup")
public class AppLookup {
 
    @Id
    @GeneratedValue
    @Column(name = "Lookup_Id", nullable = false)
    private Long lookupId;
 
    @Column(name = "Lookup_type", length = 30, nullable = false)
    private String lookupType;
 
    @Column(name = "Lookup_Name", length = 30, nullable = false)
    private String lookupName;

    @OneToOne(mappedBy = "lookupFactory")
    @Fetch(FetchMode.JOIN)
    private FormProdIndex FormProdIndexFactory;

    // getter and setter
}

As you can see in the java log below the SELECT query for my main model "FormProdIndex" is being called multiple times.

TEST 1
Hibernate: select applookup0_.lookup_id as lookup_i1_0_, applookup0_.lookup_desc as lookup_d2_0_, applookup0_.lookup_name as lookup_n3_0_, applookup0_.lookup_type as lookup_t4_0_ from app_lookup applookup0_ where applookup0_.lookup_type='factory' order by applookup0_.lookup_id
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?
Hibernate: select formprodin0_.index_id as index_id1_3_1_, formprodin0_.approved_by as approved2_3_1_, formprodin0_.approved_date as approved3_3_1_, formprodin0_.break_time_mins as break_ti4_3_1_, formprodin0_.created_by as created_5_3_1_, formprodin0_.created_date as created_6_3_1_, formprodin0_.end_date as end_date7_3_1_, formprodin0_.end_time as end_time8_3_1_, formprodin0_.interval_mins as interval9_3_1_, formprodin0_.is_deleted as is_dele10_3_1_, formprodin0_.factory_id as factory21_3_1_, formprodin0_.pack_size as pack_si11_3_1_, formprodin0_.pack_supplier as pack_su12_3_1_, formprodin0_.reject_id as reject_13_3_1_, formprodin0_.rejected_by as rejecte14_3_1_, formprodin0_.rejected_date as rejecte15_3_1_, formprodin0_.remarks as remarks16_3_1_, formprodin0_.session_id as session17_3_1_, formprodin0_.start_date as start_d18_3_1_, formprodin0_.start_time as start_t19_3_1_, formprodin0_.status as status20_3_1_, applookup1_.lookup_id as lookup_i1_0_0_, applookup1_.lookup_desc as lookup_d2_0_0_, applookup1_.lookup_name as lookup_n3_0_0_, applookup1_.lookup_type as lookup_t4_0_0_ from form_prod_index formprodin0_ left outer join app_lookup applookup1_ on formprodin0_.factory_id=applookup1_.lookup_id where formprodin0_.factory_id=?

From my understanding, the SELECT query for my main model should not run as this is a new object. This is just one lookup type, if I add the "line" and "pack_mat" the query becomes longer and page loads longer also. Any idea how to fix this?

Arnold Cristobal
  • 843
  • 2
  • 16
  • 36

1 Answers1

0

You need to use EntityGraph to reduce the number of queries.

@Entity
@Table(name = "App_Lookup")
@NamedEntityGraph(name = "graph.AppLookup.FormProdIndex", attributeNodes = { @NamedAttributeNode(value = "FormProdIndexFactory") })
public class AppLookup {
 
    @Id
    @GeneratedValue
    @Column(name = "Lookup_Id", nullable = false)
    private Long lookupId;
 
    @Column(name = "Lookup_type", length = 30, nullable = false)
    private String lookupType;
 
    @Column(name = "Lookup_Name", length = 30, nullable = false)
    private String lookupName;

    @OneToOne(mappedBy = "lookupFactory")
    @Fetch(FetchMode.JOIN)
    private FormProdIndex FormProdIndexFactory;

    // getter and setter
}

Used this graph in your repository

@Repository
public interface AppLookupRepository extends CrudRepository<AppLookup, Long> {

    @EntityGraph(value = "graph.AppLookup.FormProdIndex", type = EntityGraphType.FETCH)
    public Optional<AppLookup> findByLookupTypeIsAndLookupIdIs(String lookupType, Long lookupId);

}

You can fin more here
https://www.baeldung.com/spring-data-jpa-named-entity-graphs
Why we need to use entity graph?

SSK
  • 3,444
  • 6
  • 32
  • 59