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 ?
