Hibernate @Formula which return collection

Question

I'm using a legacy database. In my example, we retrieve a product which have some characteristics. In the db, we can find a product table, a characteristic table and a jointable for the manyToMany association.

The only field i need is the label of the characteristics. So, my Product entity will contains a list of characteristics as String. I would like to not create to many entities in order to not overload my sourcecode. Let's see the example :

@Entity
@Table(name = "product")
public class Product implements Serializable {

    @Id
    @Column(name = "id")
    private Long id;

    // all field of Product entity

    @ElementCollection(targetClass = String.class)
    @Formula(value = "(SELECT characteristic.label FROM a jointable JOIN b characteristic ON jointable.characteristic_id = characteristic.id WHERE jointable.product_id = id)")
    private Set<String> characteristics = new HashSet<>();

    // Getter / setter
}

To represent my characteristics, i tried to use the association of @Formula and @ElementCollection. As you can see, the names of tables (a and b in the query) does not match with my representation of these datas.

But, when I try to load a product, I get an error like "PRODUCT_CHARACTERISTICS table not found".

Here the generated SQL query executed by hibernate :

SELECT product0_.id AS id1_14_0_,
       -- Other fields
       characteri10_.product_id AS product_1_15_1__,
       (SELECT characteristic.label 
          FROM a jointable JOIN b characteristic ON jointable.characteristic_id = characteristic.id 
          WHERE jointable.product_id = id) AS formula6_1__,
FROM product product0_
  -- Other Joins
  LEFT OUTER JOIN product_characteristics characteri10_ ON product0_.cdprd = characteri10_.product_cdprd
WHERE product0_.id = ?;

In the FROM part, we can refind the call of product_characteristics table (which not exist in the database).

So, my main question is the following : How can I get the list of characterics as entity attribute ? Can I reach this result with @Formula ?

Edit

In other words, i would like to load only one attribute from Many to Many mapping. I found an example here but it works only with the id (which can find in the jointable)

I just find some posts which talk about that. One with [no solution](https://stackoverflow.com/questions/61337372/jpa-many-to-many-as-elementcollection) which says that the creation of entity B (in my example) is needed. [Another post](https://stackoverflow.com/questions/49384488/using-hibernate-formula-to-fetch-a-collection) with validated answer but I failed to get the right result. — Julien, Oct 05 '21 at 12:23

score 0 · Answer 1 · answered Oct 18 '21 at 15:28

I assume that what you want to achieve here is reducing the amount of data that is fetched for a use case. You can leave your many-to-many mapping as it is, since you will need DTOs for this and I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Product.class)
public interface ProductDto {
    @IdMapping
    Long getId();
    String getName();
    @Mapping("characteristics.label")
    Set<String> getCharacteristicLabels();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

ProductDto a = entityViewManager.find(entityManager, ProductDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<ProductDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

Hibernate @Formula which return collection

Edit

1 Answers1