英文:
Spring Data JPA fetches all columns when mapped model is included
问题
Interface based projection - I want to select only few columns from Product and ProductImages DB tables.
Issue: If I mention mapped model in interface
public Set<ProductImagesDBResult> getProductImages();
it fetches all columns.
from both Product and ProductImages tables.Note: If I comment this out, it fetches only selected fields.
Entity models Product.java
@Entity @Table(name = "products") @EntityListeners(AuditingEntityListener.class) public class Product { // ... (other fields) @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY) @JoinColumn(name = "product_id") private Set<ProductImage> productImages; // ... (other fields) }
Entity model ProductImage.java
@Entity @Table(name = "product_images") public class ProductImage { // ... (fields) }
ProductDetailsDBResult.java (Projection interface)
public interface ProductDetailsDBResult { // ... (other methods) // public Set<ProductImagesDBResult> getProductImages(); interface ProductImagesDBResult { // ... (methods) } }
ProductRepository.java
public interface ProductRepository extends JpaRepository<Product, Long> { Optional<ProductDetailsDBResult> findBySlug(String slug); }
ProductService.java
ProductDetailsDBResult p = productRepository.findBySlug(slug) .orElseThrow(() -> new RecordNotFoundException("Invalid product slug " + slug));
The issue is:
When this line is commented out:
// public Set<ProductImagesDBResult> getProductImages();
Logs show (correct SQL as only mentioned columns are fetched):
Hibernate: select product0_.id as col_0_0_, product0_.slug as col_1_0_, product0_.title as col_2_0_ from products product0_ where product0_.slug=?
But when this line is uncommented:
public Set<ProductImagesDBResult> getProductImages();
SQL fetches all fields from both Product, Category and ProductImages tables: (Product is mapped with Category and ProductImages models)
Hibernate: select product0_.id as id1_5_, product0_.category_id as categor11_5_, ... from products product0_ where product0_.slug=? Hibernate: select category0_.id as id1_0_0_, ... from categories category0_ where category0_.id=? Hibernate: select productima0_.product_id as product_5_4_0_, ... from product_images productima0_ where productima0_.product_id=?
How can I fetch only the mentioned columns from both tables?
Any help would be appreciated. Thanks in advance!
英文:
> Interface based projection - I want to select only few columns from Product and ProductImages DB tables.
>
> Issue: If I mention mapped model in interface public Set<ProductImagesDBResult> getProductImages();
it fetches all columns.
> from both Product and ProductImages tables.
> Note: If I comment this out, it fetches only selected fields.
Entity models Product.java
@Entity
@Table(name = "products")
@EntityListeners(AuditingEntityListener.class)
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@OneToMany(cascade = CascadeType.ALL, fetch=FetchType.LAZY)
@JoinColumn(name = "product_id")
private Set<ProductImage> productImages;
@ManyToOne
@JoinColumn(name = "category_id")
private Category category;
private String sku;
private String slug;
private String title;
private String description;
private BigDecimal oldPrice;
private BigDecimal newPrice;
private int status;
/* getters and setters */
}
Entity model ProductImage.java
@Entity
@Table(name = "product_images")
public class ProductImage {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@JoinColumn(name = "product_id")
@ManyToOne
@JsonIgnore
private Product product;
private String image;
private int isDefault;
private int imgOrder;
}
ProductDetailsDBResult.java (Projection interface)
public interface ProductDetailsDBResult {
public long getId();
public String getSlug();
public String getTitle();
public Set<ProductImagesDBResult> getProductImages();
interface ProductImagesDBResult {
public long getId();
public String getImage();
}
/* getters and setters */
}
ProductRepository.java
public interface ProductRepository extends JpaRepository<Product, Long> {
Optional<ProductDetailsDBResult> findBySlug(String slug);
}
ProductService.java
ProductDetailsDBResult p = productRepository.findBySlug(slug)
.orElseThrow(() -> new RecordNotFoundException("Invalid product slug " + slug));
The issue is:
When this line is commented out:
public interface ProductDetailsDBResult {
/* other get methods ... */
// public Set<ProductImagesDBResult> getProductImages();
}
Logs show (correct SQL as only mentioned columns are fetched):
Hibernate: select product0_.id as col_0_0_, product0_.slug as col_1_0_, product0_.title as col_2_0_ from products product0_ where product0_.slug=?
But when this line is uncommented: public Set<ProductImagesDBResult> getProductImages();
SQL fetches all fields from both Product, Category and ProductImages tables: (Product is mapped with Category and ProductImages models)
Hibernate: select product0_.id as id1_5_, product0_.category_id as categor11_5_, product0_.created_at as created_2_5_, product0_.description as descript3_5_, product0_.new_price as new_pric4_5_, product0_.old_price as old_pric5_5_, product0_.sku as sku6_5_, product0_.slug as slug7_5_, product0_.status as status8_5_, product0_.title as title9_5_, product0_.updated_at as updated10_5_ from products product0_ where product0_.slug=?
Hibernate: select category0_.id as id1_0_0_, category0_.description as descript2_0_0_, category0_.is_featured as is_featu3_0_0_, category0_.preview_image as preview_4_0_0_, category0_.slug as slug5_0_0_, category0_.title as title6_0_0_ from categories category0_ where category0_.id=?
Hibernate: select productima0_.product_id as product_5_4_0_, productima0_.id as id1_4_0_, productima0_.id as id1_4_1_, productima0_.image as image2_4_1_, productima0_.img_order as img_orde3_4_1_, productima0_.is_default as is_defau4_4_1_, productima0_.product_id as product_5_4_1_ from product_images productima0_ where productima0_.product_id=?
How can I fetch only the mentioned columns from both tables?
Any help would be appreciated. Thanks in advance!
专注分享java语言的经验与见解,让所有开发者获益!
评论