Lazy Fetch Of Element Collection

Hibernate ORM
1

Good morning.

I am having an issue using the usual ORM (not reactive). I have defined an @ElementCollection as
LazyFetched but it always is fetched via a left join when looking at the generated SQL.

I did find that there seems to have been some form of fix in the Reactive ORM:
Git Issue LazyFetch Element Collection Reactive ORM

I may just be doing something daft of course.

(Changed the names of some columns)


    //User.class Entity

    @ElementCollection(fetch = FetchType.LAZY)
    @CollectionTable(name = "user_favourite_nums", joinColumns = @JoinColumn(name = "user_id"))
    @Column(name = "number")
    private List<Integer> usersFavouriteNums;

Schema:

CREATE TABLE user_favourite_nums(
`number` int NOT NULL,
`user_id` bigint NOT NULL,
CONSTRAINT `PK_UserFavNums` PRIMARY KEY (`number`,`user_id`),
CONSTRAINT `UK_UserFaveNums` UNIQUE (number,user_id),
CONSTRAINT `FK_UserFaveNum_User` FOREIGN KEY (user_id) REFERENCES `users`(user_id) ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The Test:

    @Test
    @DisplayName("Can persist a user with favourite numbers")
    public void persistUserWithFavouriteNumbers() {
        Role role = roleRepo.findOneByRoleName("USER").get();
        User user = new User();
        user.setEmail("user@test.com");
        //Some other Settings...
        user.setUsersFavouriteNumbers(Stream.of(1,2,3,4).collect(Collectors.toList()));
        userRepo.save(user);


       //Does an eager LEFT JOIN FETCH of the users fave nums...
        mUser = userRepo.findOneByEmail("user@test.com").get();
       
       //Assertions below...(None here yet! so there is no .get() to cause the fetch)

    }

The generated SQL (removed some of the SELECT bits for clarity…)

Hibernate: 
    select
        user0_.user_id as user_id1_9_,
        user0_.email as email3_9_
    from
        users user0_ 
    left outer join
        user_favourite_nums user0_1_ 
            on user0_.user_id=user0_1_.user_id 
    where
        user0_.email=?

Thank you for any help. Driving me a bit crazy and I don’t really want to make an entity of this ha.

2

OK I am sorry, I have just noticed I had left this on the class.

@SecondaryTable(name = "user_favourite_numbers", pkJoinColumns = @PrimaryKeyJoinColumn(name = "user_id"))

Does this mean that it was using the class level annotation for secondary table OVER the ElementCollection?

I have of course removed the SecondaryTable it but it didn’t throw any errors.

3

Are you sure that the left join was created for the collection fetch? It seems not as no columns from that table are selected. So the join you saw was due to your @SecondaryTable usage.