Issue
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
Solution
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.
Answered By - Alex Chereshnev
Answer Checked By - Clifford M. (JavaFixing Volunteer)
