Search code examples
javahibernatepostgresqlnativequery

Join external column to Hibernate entity using native SQL

I have a (simplified) table structure that looks something like that:

customer table:

id       name
-------------------
 1       customer1

alias table:

customer_id       alias
-------------------------------
 1                 customer one
 1                 customer uno

When I run the following query I easily get the list of aliases per customer:

select * from customer_alias where customer_id=1;

I would like to use this query in my hibernate to populate a list of type String. I tried using @Formula as follows:

@Entity
@Table(name = "customer")
public class Customer {
      @Id
      @Column(name = "id")
      @GeneratedValue(strategy= GenerationType.AUTO)
      private Long id;

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

      @Formula("(select alias from customer_alias where customer_id = id)")
      private List<String> aliases;

      // Getters, setters, etc...
}

It didn't work and I got this exception: 

 Could not determine type for: java.util.List, at table: customer, for columns: [org.hibernate.mapping.Formula( (select alias from customer_alias where customer_id = id) )]

Is there anyway to achieve this? Doesn't have to be with @Formula of course. Any reasonable way would be great.

Here is an SQLFiddle of my example

Solution

  • You could use @ElementCollection for having a List of related aliases without the need to map the whole entity:

    @ElementCollection
@CollectionTable(name = "customer_alias", joinColumns = @JoinColumn(name = "customer_id") )
@Column(name = "alias")
private List<String> aliases;

    See also: