hibernate - @ManyToMany Batch Select and Insert when doing insert operation

I have a User and Role class. The User can be in Many roles and a Role can be common for Many Users thus a @ManyToMany unidirectional association.

    @Entity
    @Table(name = "users")
    @Getter
    @Setter
    public class  User
    {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long id;
        private String firstName;
        private String lastName;
        private String email;
        private String password;
    
        @ManyToMany
        @JoinTable(name = "users_roles",
                   joinColumns = @JoinColumn(name = "user_id",referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "role_id",referencedColumnName = "id"))
        private Set<Role> roles=new HashSet<>();
 }

and Role Entity

@Entity
@Table(name = "roles")
@Getter
@Setter
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private String name;
}

Assuming that the following roles are present in Role Table

[{"id":1, "name":"Sales_Officer"},  {"id":2, "name":"Admin_Officer"},  {"id":5, "name":"Name3"},  {"id":6, "name":"Name4"},  {"id":7, "name":"Name5"},  {"id":8, "name":"Name6"},  {"id":9, "name":"Name7"}, {"id":10, "name":"Name8"},  {"id":11, "name":"Name9"},  {"id":12, "name":"Name10"},  {"id":13, "name":"Name11"},  {"id":14, "name":"Name12"},  {"id":15, "name":"Name13"}]

and User table

 [{"id":1, "email":"[email protected]", "first_name":"AMllard", "last_name":"Tujize", "password":"Axon Framework"}]

and UserRole Association Table 

[{"role_id":1, "user_id":1},  {"role_id":2, "user_id":1},  {"role_id":5, "user_id":1},  {"role_id":6, "user_id":1},  {"role_id":7, "user_id":1},  {"role_id":8, "user_id":1},  {"role_id":9, "user_id":1},  {"role_id":10, "user_id":1},  {"role_id":11, "user_id":1},  {"role_id":12, "user_id":1},  {"role_id":13, "user_id":1} ]

When I update the table it does select and Insert

 @Test
        @Transactional
        void updateUser(){
            User user=userRepository.findById(1l).get();
        
            Role role=new Role();
            role.setName("Database_Expert");
    
            List<Integer> roleIds=new LinkedList<>();
            roleIds.add(5);
            roleIds.add(6);
            roleIds.add(7);
            roleIds.add(8);
            roleIds.add(9);
            roleIds.add(10);
            roleIds.add(11);
            roleIds.add(12);
            roleIds.add(13);
            roleIds.add(14);
            roleIds.add(15);
            for(int roleId:roleIds){
                Role role1=new Role();
                role1.setId(roleId);
                user.getRoles().add(role1);
            }
            userRepository.saveAndFlush(user);
        }

post saveAndFlush(user) hibernate executes Select and Insert in seperate statements ,

Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)

I have tried to avoid select by overriding isNew but due to use cases variance I can't use isNew. Is there a way I can batch both Select and Insert statements in single batch ? like select in and Insert in using hibernate

I have a User and Role class. The User can be in Many roles and a Role can be common for Many Users thus a @ManyToMany unidirectional association.

    @Entity
    @Table(name = "users")
    @Getter
    @Setter
    public class  User
    {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private long id;
        private String firstName;
        private String lastName;
        private String email;
        private String password;
    
        @ManyToMany
        @JoinTable(name = "users_roles",
                   joinColumns = @JoinColumn(name = "user_id",referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "role_id",referencedColumnName = "id"))
        private Set<Role> roles=new HashSet<>();
 }

and Role Entity

@Entity
@Table(name = "roles")
@Getter
@Setter
public class Role {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private String name;
}

Assuming that the following roles are present in Role Table

[{"id":1, "name":"Sales_Officer"},  {"id":2, "name":"Admin_Officer"},  {"id":5, "name":"Name3"},  {"id":6, "name":"Name4"},  {"id":7, "name":"Name5"},  {"id":8, "name":"Name6"},  {"id":9, "name":"Name7"}, {"id":10, "name":"Name8"},  {"id":11, "name":"Name9"},  {"id":12, "name":"Name10"},  {"id":13, "name":"Name11"},  {"id":14, "name":"Name12"},  {"id":15, "name":"Name13"}]

and User table

 [{"id":1, "email":"[email protected]", "first_name":"AMllard", "last_name":"Tujize", "password":"Axon Framework"}]

and UserRole Association Table 

[{"role_id":1, "user_id":1},  {"role_id":2, "user_id":1},  {"role_id":5, "user_id":1},  {"role_id":6, "user_id":1},  {"role_id":7, "user_id":1},  {"role_id":8, "user_id":1},  {"role_id":9, "user_id":1},  {"role_id":10, "user_id":1},  {"role_id":11, "user_id":1},  {"role_id":12, "user_id":1},  {"role_id":13, "user_id":1} ]

When I update the table it does select and Insert

 @Test
        @Transactional
        void updateUser(){
            User user=userRepository.findById(1l).get();
        
            Role role=new Role();
            role.setName("Database_Expert");
    
            List<Integer> roleIds=new LinkedList<>();
            roleIds.add(5);
            roleIds.add(6);
            roleIds.add(7);
            roleIds.add(8);
            roleIds.add(9);
            roleIds.add(10);
            roleIds.add(11);
            roleIds.add(12);
            roleIds.add(13);
            roleIds.add(14);
            roleIds.add(15);
            for(int roleId:roleIds){
                Role role1=new Role();
                role1.setId(roleId);
                user.getRoles().add(role1);
            }
            userRepository.saveAndFlush(user);
        }

post saveAndFlush(user) hibernate executes Select and Insert in seperate statements ,

Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: select r1_0.id,r1_0.name from roles r1_0 where r1_0.id=?
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)
Hibernate: insert into users_roles (user_id,role_id) values (?,?)

Share Improve this question edited Mar 31 at 7:14 Gert Arnold 109k36 gold badges214 silver badges313 bronze badges asked Mar 31 at 5:03 Anand Kadhi 1,8885 gold badges29 silver badges42 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 0

Instead of using a for loop and creating new roles instances, maybe try a query to fetch roles 5-15 from the database in one go:

User user=userRepository.findById(1l).get();
List<Integer> roleIds = Arrays.asList(5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);
List<Role> roles = roleRepository.findByIds(roleIds)

user.getRoles().addAll(roles);
userRepository.saveAndFlush(user);

I don't know if you'll need the query annotation or not, but the findByIds method can just be a query of the form:

@Query("Select r from Roles r where r.id in :roleIds")
List<Role> findByIds(List<Integer> roleIds);

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

hibernate - @ManyToMany Batch Select and Insert when doing insert operation - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)