Spring Data JPA在包括映射模型时抓取所有列。

huangapple 未分类评论41阅读模式
英文:

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! Spring Data JPA在包括映射模型时抓取所有列。

英文:

> 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&lt;ProductImagesDBResult&gt; 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 = &quot;products&quot;)
@EntityListeners(AuditingEntityListener.class)
public class Product {

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

   @OneToMany(cascade = CascadeType.ALL, fetch=FetchType.LAZY)
   @JoinColumn(name = &quot;product_id&quot;)
   private Set&lt;ProductImage&gt; productImages;

   @ManyToOne
   @JoinColumn(name = &quot;category_id&quot;)
   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 = &quot;product_images&quot;)
public class ProductImage {

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

    @JoinColumn(name = &quot;product_id&quot;)
    @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&lt;ProductImagesDBResult&gt; getProductImages();

   interface ProductImagesDBResult {
	   public long getId();
	   public String getImage();
   }

   /* getters and setters */
}

ProductRepository.java

public interface ProductRepository extends JpaRepository&lt;Product, Long&gt; {
    Optional&lt;ProductDetailsDBResult&gt; findBySlug(String slug);
}

ProductService.java

ProductDetailsDBResult p = productRepository.findBySlug(slug)
							.orElseThrow(() -&gt; new RecordNotFoundException(&quot;Invalid product slug &quot; + slug));

The issue is:

When this line is commented out:

public interface ProductDetailsDBResult {
    /* other get methods ... */
    // public Set&lt;ProductImagesDBResult&gt; 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&lt;ProductImagesDBResult&gt; 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! Spring Data JPA在包括映射模型时抓取所有列。

huangapple
  • 本文由 发表于 2020年4月10日 12:36:52
  • 转载请务必保留本文链接:https://java.coder-hub.com/61134120.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定