Spring Data/JPS (N+1) SELECTs with OneToOne associations

Question

In my Spring Data application I ran into (N+1) selects issue.

I have a following Spring Data entities:

@Entity
@Table(name = "card_categories")
public class CardCategory extends BaseEntity implements Serializable {

    @Id
    @SequenceGenerator(name = "card_categories_id_seq", sequenceName = "card_categories_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "card_categories_id_seq")
    private Long id;

    private String name;
...

}

@Entity
@Table(name = "levels")
public class Level extends BaseEntity implements Serializable {

    @Id
    @SequenceGenerator(name = "levels_id_seq", sequenceName = "levels_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "levels_id_seq")
    private Long id;

    private String name;
...
}

@Entity
@Table(name = "card_categories_levels")
public class CardCategoryLevel extends BaseEntity implements Serializable {

    @Id
    @SequenceGenerator(name = "card_categories_levels_id_seq", sequenceName = "card_categories_levels_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "card_categories_levels_id_seq")
    private Long id;

    @OneToOne
    @JoinColumn(name = "card_category_id")
    private CardCategory cardCategory;

    @OneToOne
    @JoinColumn(name = "level_id")
    private Level level;
...

}

and empty Spring Data repository:

@Repository
public interface CardCategoryLevelRepository extends JpaRepository<CardCategoryLevel, Long> {
}

When I try to fetch all CardCategoryLevel entities by cardCategoryLevelRepository.findAll() method it produces 3 SELECTs for an each row in my card_categories_levels table.

In order to use one single JOIN instead of N+1 SELECTs I have reimplemented my CardCategoryLevelRepository:

@Repository
public interface CardCategoryLevelRepository extends JpaRepository<CardCategoryLevel, Long> {

    @Query(value = "SELECT ccl FROM CardCategoryLevel ccl LEFT JOIN FETCH ccl.cardCategory cc LEFT JOIN FETCH ccl.level l where cc = :cardCategory and l = :level")
    CardCategoryLevel findByCardCategoryAndLevel(@Param("cardCategory") CardCategory cardCategory, @Param("level") Level level);

    @Override
    @Query(value = "SELECT ccl FROM CardCategoryLevel ccl LEFT JOIN FETCH ccl.cardCategory LEFT JOIN FETCH ccl.level")
    List<CardCategoryLevel> findAll();

}

but I'm not sure I did it in a right optimal way.

Please validate my approach and tell it is optimal workaround for (N+1) SELECTs issue with OneToOne associations in Spring Data or no and what is the best way to solve it.

Should I leave it as is or may be move to some other abstraction.. for example like QueryDSL or something like that ?

Answer 1

Thanks to Xtreme Biker I have re-implemented my solution with entity graphs and QueryDSL