CODEWITHSUNDEEP
springhibernatedatatablespring-security
user3715580
user3715580

Reputation: 1

Too Many - Spring Security Hibernate: select role0_.user_id as user_id1_3_1_

Hibernate seems to be doing this after each select:

Hibernate: select role0_.user_id as user_id1_3_1_, role0_.user_role_id as user_rol2_12_1_,
role1_.user_role_id as user_rol1_11_0_, role1_.role as role2_11_0_ from user_roles role0_
inner join user_role role1_ on role0_.user_role_id=role1_.user_role_id where
role0_.user_id=?

Hibernate: select emailrecei0_.email_received_id as email_re1_0_5_, emailrecei0_.deleted_flag as deleted_2_0_5_, emailrecei0_.email_body as email_bo3_0_5_, emailrecei0_.email_subject as email_su4_0_5_, emailrecei0_.email_from_id as email_fr6_0_5_, emailrecei0_.received_date as received5_0_5_, emailrecei0_.email_to_id as email_to7_0_5_, m2duser1_.user_id as user_id1_3_0_, m2duser1_.account_locked as account_2_3_0_, m2duser1_.active_flag as active_f3_3_0_, m2duser1_.cell_number as cell_num4_3_0_, m2duser1_.email_address as email_ad5_3_0_, m2duser1_.first_name as first_na6_3_0_, m2duser1_.home_number as home_num7_3_0_, m2duser1_.join_date as join_dat8_3_0_, m2duser1_.last_login_date as last_log9_3_0_, m2duser1_.last_name as last_na10_3_0_, m2duser1_.login_ip as login_i11_3_0_, m2duser1_.password as passwor12_3_0_, m2duser1_.system_message as system_13_3_0_, m2duser1_.ref_table_user_type_id as ref_tab15_3_0_, m2duser1_.username as usernam14_3_0_, m2duser1_.zipcode_id as zipcode16_3_0_, role2_.user_id as user_id1_3_7_, role3_.user_role_id as user_rol2_12_7_, role3_.user_role_id as user_rol1_11_1_, role3_.role as role2_11_1_, reftable4_.ref_table_id as ref_tabl1_10_2_, reftable4_.ref_type_code as ref_type2_10_2_, reftable4_.description as descript3_10_2_, reftable4_.ref_type_group as ref_type4_10_2_, reftable4_.ui_sort_order as ui_sort_5_10_2_, zipcode5_.zipcode_id as zipcode_1_13_3_, zipcode5_.city as city2_13_3_, zipcode5_.city_long as city_lon3_13_3_, zipcode5_.latitude as latitude4_13_3_, zipcode5_.longitude as longitud5_13_3_, zipcode5_.state as state6_13_3_, zipcode5_.zipcode as zipcode7_13_3_, m2duser6_.user_id as user_id1_3_4_, m2duser6_.account_locked as account_2_3_4_, m2duser6_.active_flag as active_f3_3_4_, m2duser6_.cell_number as cell_num4_3_4_, m2duser6_.email_address as email_ad5_3_4_, m2duser6_.first_name as first_na6_3_4_, m2duser6_.home_number as home_num7_3_4_, m2duser6_.join_date as join_dat8_3_4_, m2duser6_.last_login_date as last_log9_3_4_, m2duser6_.last_name as last_na10_3_4_, m2duser6_.login_ip as login_i11_3_4_, m2duser6_.password as passwor12_3_4_, m2duser6_.system_message as system_13_3_4_, m2duser6_.ref_table_user_type_id as ref_tab15_3_4_, m2duser6_.username as usernam14_3_4_, m2duser6_.zipcode_id as zipcode16_3_4_ from email_received emailrecei0_ left outer join m2d_user m2duser1_ on emailrecei0_.email_from_id=m2duser1_.user_id left outer join user_roles role2_ on m2duser1_.user_id=role2_.user_id left outer join user_role role3_ on role2_.user_role_id=role3_.user_role_id left outer join ref_table reftable4_ on m2duser1_.ref_table_user_type_id=reftable4_.ref_table_id left outer join zipcode zipcode5_ on m2duser1_.zipcode_id=zipcode5_.zipcode_id left outer join m2d_user m2duser6_ on emailrecei0_.email_to_id=m2duser6_.user_id where emailrecei0_.email_received_id=? 2014-06-04 22:05:46,118 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader
| Result set row: 0 2014-06-04 22:05:46,120 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader | Result row: EntityKey[com.testApp.meals2day.model.M2DUser#2], EntityKey[com.testApp.meals2day.model.Role#1], EntityKey[com.testApp.meals2day.model.RefTable#1], EntityKey[com.testApp.meals2day.model.Zipcode#1], EntityKey[com.testApp.meals2day.model.M2DUser#1], EntityKey[com.testApp.meals2day.model.EmailReceived#14] 2014-06-04 22:05:46,121 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader
| Found row of collection: [com.testApp.meals2day.model.M2DUser.role#2] 2014-06-04 22:05:46,121 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad
| Resolving associations for [com.testApp.meals2day.model.M2DUser#2] 2014-06-04 22:05:46,122 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Done materializing entity [com.testApp.meals2day.model.M2DUser#2] 2014-06-04 22:05:46,122 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Resolving associations for [com.testApp.meals2day.model.Role#1] 2014-06-04 22:05:46,122 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Done materializing entity [com.testApp.meals2day.model.Role#1] 2014-06-04 22:05:46,122 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Resolving associations for [com.testApp.meals2day.model.RefTable#1] 2014-06-04 22:05:46,123 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Done materializing entity [com.testApp.meals2day.model.RefTable#1] 2014-06-04 22:05:46,123 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Resolving associations for [com.testApp.meals2day.model.Zipcode#1] 2014-06-04 22:05:46,123 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Done materializing entity [com.testApp.meals2day.model.Zipcode#1] 2014-06-04 22:05:46,123 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Resolving associations for [com.testApp.meals2day.model.M2DUser#1] 2014-06-04 22:05:46,123 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Done materializing entity [com.testApp.meals2day.model.M2DUser#1] 2014-06-04 22:05:46,124 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Resolving associations for [com.testApp.meals2day.model.EmailReceived#14] 2014-06-04 22:05:46,124 DEBUG | tomcat-http--16 | org.hibernate.engine.internal.TwoPhaseLoad | Done materializing entity [com.testApp.meals2day.model.EmailReceived#14] 2014-06-04 22:05:46,124 DEBUG | tomcat-http--16 | o.h.engine.loading.internal.CollectionLoadContext | 1 collections were found in result set for role: com.testApp.meals2day.model.M2DUser.role 2014-06-04 22:05:46,124 DEBUG | tomcat-http--16 | o.h.engine.loading.internal.CollectionLoadContext | Collection fully initialized: [com.testApp.meals2day.model.M2DUser.role#2] 2014-06-04 22:05:46,124 DEBUG | tomcat-http--16 | o.h.engine.loading.internal.CollectionLoadContext | 1 collections initialized for role: com.testApp.meals2day.model.M2DUser.role 2014-06-04 22:05:46,125 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader
| Loading collection: [com.testApp.meals2day.model.M2DUser.role#1] 2014-06-04 22:05:46,125 DEBUG | tomcat-http--16 | org.hibernate.SQL
| select role0_.user_id as user_id1_3_1_, role0_.user_role_id as user_rol2_12_1_, role1_.user_role_id as user_rol1_11_0_, role1_.role as role2_11_0_ from user_roles role0_ inner join user_role role1_ on role0_.user_role_id=role1_.user_role_id where role0_.user_id=?

Hibernate: select role0_.user_id as user_id1_3_1_, role0_.user_role_id as user_rol2_12_1_, role1_.user_role_id as user_rol1_11_0_, role1_.role as role2_11_0_ from user_roles role0_ inner join user_role role1_ on role0_.user_role_id=role1_.user_role_id where role0_.user_id=?

2014-06-04 22:05:46,131 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader | Result set contains (possibly empty) collection: [com.testApp.meals2day.model.M2DUser.role#1] 2014-06-04 22:05:46,131 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader
| Result set row: 0 2014-06-04 22:05:46,131 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader | Result row: EntityKey[com.testApp.meals2day.model.Role#1] 2014-06-04 22:05:46,131 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader
| Found row of collection: [com.testApp.meals2day.model.M2DUser.role#1] 2014-06-04 22:05:46,131 DEBUG | tomcat-http--16 | o.h.engine.loading.internal.CollectionLoadContext | 1 collections were found in result set for role: com.testApp.meals2day.model.M2DUser.role 2014-06-04 22:05:46,131 DEBUG | tomcat-http--16 | o.h.engine.loading.internal.CollectionLoadContext | Collection fully initialized: [com.testApp.meals2day.model.M2DUser.role#1] 2014-06-04 22:05:46,132 DEBUG | tomcat-http--16 | o.h.engine.loading.internal.CollectionLoadContext | 1 collections initialized for role: com.testApp.meals2day.model.M2DUser.role 2014-06-04 22:05:46,132 DEBUG | tomcat-http--16 | org.hibernate.loader.Loader
| Done loading collection

@OneToMany(fetch=FetchType.EAGER)
@JoinTable(name="user_roles",
    joinColumns = @JoinColumn( name="user_id"),
    inverseJoinColumns = @JoinColumn( name="user_role_id"))
private Set<Role> role;

public Collection<? extends GrantedAuthority> getAuthorities() {
    List<GrantedAuthority> authorities = new ArrayList<GrantedAuthority>();
    for (Role r : role) {
        authorities.add(new SimpleGrantedAuthority(r.getRole()));
    }
    return authorities;
}

delimiter $$

    CREATE TABLE `user_role` (
  `user_role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1$$


@Query(value="select er from EmailReceived er JOIN FETCH er.toUser tu JOIN FETCH er.fromUser fu where er.toUser.userId = ?1",
      countQuery="select count(er) from EmailReceived er where er.toUser.userId = ?1")
Page<EmailReceived> findEmailReceivedByUserId(Integer userId, Pageable pageable);

My question is why is it going to the db after the initial Page select? It returns the page object fine.. but goes to the db for each row in the result page.

I have just moved from a stored proc to Spring/JPA/Paging. I am seeing paging and correct results. It is really slow.

Hibernate is calling this select above with every row in the paged results. Any help is appreciated?

Upvotes: 0

Views: 493

Answers (1)

ndrone
ndrone

Reputation: 3582

The reason it's called for every row is your fetchType is set to EAGER. Which is telling Hibernate to fetch all data ahead of time even when your not using it. Changing your fetch type to FetchType.LAZY should get you what you need. Only going to the database when it needs more information from the joins.

Upvotes: 0

Related Questions