java - Spring + Hibernate: How do I efficiently chain two link tables and include resulting data in single entity? (user-role-right) -

short version

i have basic setup user table linked role table , role table linked right. these both many-to-many relations. roles dynamic entity , not of interest application (only visual aspects). when fetch user want return data in user table including list of names of rights.

to clarify, want solution do:

i managed rights in user object , return them, it's inefficient due query calls hibernate makes after original query called.

detailed version

let me first give information on how entities linked , code looks like. (simplified) database table structure looks this:

user.java

@entity @table(name = "user") public class user {    @id   @column(name = "user_id", columndefinition = "user_id")   private long userid;    @transient   private list<string> rights    @manytomany   @jointable(     name = "user_role",     joincolumns = @joincolumn(name = "user_id", referencedcolumnname = "user_id"),     inversejoincolumns = @joincolumn(name = "role_id", referencedcolumnname = "role_id"))   @jsonignore   private list<role> roles;    //getters , setters } 

role.java

@entity @table(name = "role") public class role {    @id   @column(name = "role_id", columndefinition = "role_id")   private long roleid;    @manytomany   @jointable(     name = "user_role",     joincolumns = @joincolumn(name = "role_id", referencedcolumnname = "role_id"),     inversejoincolumns = @joincolumn(name = "user_id", referencedcolumnname = "user_id"))   @jsonignore   private list<employee> employees;    @manytomany   @jointable(     name = "role_right",     joincolumns = @joincolumn(name = "role_id", referencedcolumnname = "role_id"),     inversejoincolumns = @joincolumn(name = "right_id", referencedcolumnname = "right_id"))   @jsonignore   private list<right> rights;     //getters , setters } 

right.java

@entity @table(name = "right") public class right {    @id   @column(name = "right_id", columndefinition = "right_id")   private long rightid;    @column(name = "name", columndefinition = "name")   private string name;    @manytomany   @jointable(     name = "role_right",     joincolumns = @joincolumn(name = "right_id", referencedcolumnname = "right_id"),     inversejoincolumns = @joincolumn(name = "role_id", referencedcolumnname = "role_id"))   @jsonignore   private list<role> roles;    //getters , setters } 

it's important know use java specifications api join tables:

return (root, query, cb) -> {   query.distinct(true);   join rolesjoin = root.join("roles", jointype.left);   join rightsjoin = rolesjoin.join("rights", jointype.left);   return cb.conjunction(); }; 

this creates correct query:

select <columns go here>  employee user0_ left outer join user_role roles1_ on user0_.user_id=roles1_.user_id left outer join role role2_ on roles1_.role_id=role2_.role_id left outer join role_right rights3_ on role2_.role_id=rights3_.role_id left outer join right right4_ on rights3_.right_id=right4_.right_id 

everything looked me till now. when tried fetch names of roles, there more 2 queries (count page , original one) being executed

//the original code uses lambda for(role role : user.getroles()){   for(right right: role.getrights()){     user.addright(right.getname());   } } 

the query looks like:

select <column stuff>  role_right rights0_ inner join right right1_ on rights0_.right_id=right1_.right_id rights0_.role_id=? 

this makes call inefficient me. in case it's single user, multiple users adds up.

is there way have single query put names of rights in user entity, without adding query executions?

things tried far:

• using @secondarytable directly define column right table in user entity. not first link role user , use fields role table link right table. in end have @secondarytable annotation on top of user object , define columns of right object below.

• using @formula in user entity insert native call query. did not work annotation did not understand how map list of rights.

there might other options here, or did horribly wrong implementing ones above. don't way go in finding solution problem. if tell me, great.

thanks in advance,
robin

you using root.join joining of tables purposes of query; lazy associations in loaded entities still not initialized.

as see, intention initialize lazy collections well. have use root.fetch (defined in interface method inherited fetchparent):

create fetch join specified collection-valued attribute using given join type.

however, intention not practice; not join multiple collections in 1 query, otherwise query result set explode full cartesian product between joined collections. result set contains <num of users> * <num of roles per user> * <num of rights per role> rows. so, each user data repeated <num of roles per user> * <num of rights per role> times in generated result set.

the approach find best , straightforward specify batch size on lazy associations.