Hibernate - Create alias projection for INNER JOIN query

Question

(This is a simplified version of my domain)

I have an entity Product which has a 1-to-N relationship to Connection. The mapping works correctly so that when I need to retrieve connections for a product I just do product.getConnections();.

I need to integrate with a legacy system's GUI which requires that I produce a list on this format:

|PRODUCT_ID|NAME|CONNECTION_ID|NAME|
|1|Product A|10|Connection A|
|1|Product A|11|Connection B|
|1|Product A|12|Connection C|

which is basically a result from this query:

SELECT *
FROM Product P
INNER JOIN Connection C ON C.PRODUCT_ID = P.PRODUCT_ID

I would like to create a property on the Product entity that could be populated with the connection's name so that I can reuse existing logic on my service. I tried to use @Formula but that does not work. It seems like I can achieve this by creating a Projection alias, but I was not successful at that.

We use Hibernate's Criteria and Projection API to perform the query.

Criteria productCriteria = session.createCriteria(Product.class);
ProjectionList columns = Projections.projectionList();

columns.add(Projections.property("productId"));
columns.add(Projections.property("name"));
columns.add(Projections.property("connectionName")); // How to make this work?
productCriteria.setProjection(columns);

List<Product> prodList = productCriteria.list();

These are my entities:

class Product {
    private Long productId;
    private String name;

    @OneToMany(mappedBy = "product")
    private List<Connection> connections;

    // How to get this populated?
    @Transient 
    private String connectionName;


}

class Connection {
    private Long connectionId;
    @Column(name = "PRODUCT_ID")
    private Long productId;
    private String name;

    @ManyToOne
    @JoinColumn(name = "PRODUCT_ID", insertable = false, updatable = false)
    private Product product;

}

Answer 1

The idead is to create criteria to join table; try something like :

Criteria productCriteria = session.createCriteria(Product.class);
Criteria connectionCriteria  = criteria.createCriteria("connections", "c", CriteriaSpecification.INNER_JOIN);

ProjectionList columns = Projections.projectionList();

columns.add(Projections.property("productId"));
columns.add(Projections.property("name"));
columns.add(Projections.property(connectionCriteria.getAlias() + ".connectionId"));
columns.add(Projections.property(connectionCriteria.getAlias() + ".name"));

productCriteria.setProjection(columns);

List<Product> prodList = productCriteria.list();