I want to extract entity, that contains only a specific part of data from DB. But Hibernate creates extra query, that fully fill my entity.

EXAMPLE

Pseudo Query: Select * From ShopUnitDB WHERE prices.date BETWEEN 2022-05-02 AND 2022-05-30;

Pseudo Expected: ShopUnit{1, Name, prices=[{2000, 2022-05-25}, {4000, 2022-05-29}]}

Result: All data from ShopUnitPrice table, related to ShopUnitDB.

Console log:

Hibernate: select shopunitdb0_.id as id1_0_, shopunitdb0_.name as name2_0_, shopunitdb0_.parent_id as parent_i3_0_, shopunitdb0_.type as type4_0_ from shop_unit shopunitdb0_ inner join shop_unit_price prices1_ on shopunitdb0_.id=prices1_.unit_id where shopunitdb0_.id=? and (prices1_.date between ? and ?)

Hibernate: select prices0_.unit_id as unit_id4_1_0_, prices0_.id as id1_1_0_, prices0_.id as id1_1_1_, prices0_.date as date2_1_1_, prices0_.price as price3_1_1_, prices0_.unit_id as unit_id4_1_1_ from shop_unit_price prices0_ where prices0_.unit_id=?

Look, first query extracts correct entity - that contains only a part based on WHERE ... BETWEEN statement. Next it creates new query to extract all data of sub entity of ShopUnitDB.

I use Spring Data JPA Specification. Also tryed to use @Query in JpaRepository. No luck.

Entity 1:

@Entity
@Table(name = "shop_unit")
public class ShopUnitDB {

    @Id
    private UUID id;

    @Column(name = "name")
    private String name;

    @Column(name = "parent_id")
    @Nullable
    private UUID parentId;

    @Enumerated(EnumType.STRING)
    private ShopUnitType type;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "unit_id", referencedColumnName = "id")
    private List<ShopUnitPrice> prices;

    @Transient
    private Set<ShopUnitDB> children;
    ...

    public List<ShopUnit> convertToShopUnitStatistic() {
        return prices.stream()
                .map(price -> new ShopUnit(id, name, price.getDate(), 
                                   parentId, type, price.getPrice()))
                .collect(Collectors.toList());
    }
...
}

Entity 2:

@Entity
@Table(name = "shop_unit_price")
public class ShopUnitPrice {

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

    @Column(name = "unit_id")
    private UUID unitId;

    @Column(name = "date")
    private LocalDateTime date;

    @Column(name = "price")
    private Integer price;
    ...
}

My Service, that call JpaRepository using Spring Data JPA Specification:

@Service
public class ShopUnitService {

    private ShopUnitRepository jpa;

    @Autowired
    public ShopUnitService(ShopUnitRepository jpa) {
        this.jpa = jpa;
    }

    public List<ShopUnit> getShopUnitStatistic(UUID uuid, LocalDateTime start, LocalDateTime end) 
    {
        unitDB = jpa.findAll(where(idLike(uuid).and(joinPrices(start, end)))).get(0);
        return unitDB.convertToShopUnitStatistic();
    }

    private Specification<ShopUnitDB> idLike(UUID id) {
        return (root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("id"), id);
    }

    private Specification<ShopUnitDB> joinPrices(LocalDateTime start, LocalDateTime end) {
        return (root, query, criteriaBuilder) -> {
            return criteriaBuilder.between(root.join("prices").get("date"), start, end);
        };
    }
}

Spring JpaRepository:

@Repository
public interface ShopUnitRepository extends JpaRepository<ShopUnitDB, UUID>, JpaSpecificationExecutor<ShopUnitDB> {}

UPDATE

spring-boot-starter-parent version: 2.6.8

application.properties

server.port=80

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/goods_warehouse
spring.datasource.username=username
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

spring.jpa.hibernate.ddl-auto=none
spring.jpa.open-in-view=false

spring.jpa.show-sql=true

CodePudding user response：

I see you are using fetch = FetchType.EAGER for OneToMany association (which is not recommended) in ShopUnitDB entity and criteria APIs to fetch the data. If you use JPQL/Criteria APIs to fetch data with fetchType=EAGER, it will fire 2 queries to retrieve data. Please refer this link for more details.

To fix your issue, I think you should either remove fetchType from OneToMany association in ShopUnitDB or make it fetchType = FetchType.LAZY

CodePudding user response：

According to @Daniel Wosch's comment, i understood the main problem. To solve it, i used to use JPARepository method this @Query like this:

@Repository
public interface ShopUnitRepository extends JpaRepository<ShopUnitDB, UUID>, JpaSpecificationExecutor<ShopUnitDB> {

    @Query("SELECT unit "  
            "FROM ShopUnitDB unit JOIN FETCH unit.prices price "  
            "WHERE unit.id = ?1 AND price.date BETWEEN ?2 AND ?3")
    ShopUnitDB findByIdAndDate(UUID uuid, LocalDateTime dateStart, LocalDateTime dateEnd);
}

Note that i used JOIN FETCH *ShopUnit collection field* and filtering that data set.