Join external column to Hibernate entity using native SQL

Question

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

Why not use `oneToMany` annotation with `JoinColumn`? I think you must have both the tables as entities in your app. — Madhusudana Reddy Sunnapu, Apr 08 '16 at 10:08
@MadhusudanaReddySunnapu - because the second table is just a list of strings I tried to find something more elegant that would not require me to create another entity. If I won't find such solution then what you suggested seems like the logical solution. — Avi, Apr 08 '16 at 10:10
Can you try `@Formula("(select alias from customer_alias where customer_id = id)")` — Madhusudana Reddy Sunnapu, Apr 08 '16 at 10:14
@MadhusudanaReddySunnapu - Sorry! That's what I tried of course. I accidentally put `*` - changing the question. — Avi, Apr 08 '16 at 10:19

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

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:

Difference between @OneToMany and @ElementCollection?

edited May 23 '17 at 11:45

Community

1
1

answered Apr 08 '16 at 10:34

Aritz

30,971
16
136
217

Madhusudana Reddy Sunnapu · Answer 2 · 2016-04-08T10:44:31.487

I think you don't want to use OneToMany annotation as the second table is just a list of strings you want to find something more elegant that would not require me to create another entity.

You can use @ElementCollection as below:

@Entity
@Table(name="college")
class College implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Column(name="college_id")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
private int collegeId;  

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

@ElementCollection
@CollectionTable(name="student", joinColumns=@JoinColumn(name="college_id"))
@Column(name="student_name")
private Set<String> students;


public College() {
}

public Set<String> getStudents() {
    return students;
}

public void setStudents(Set<String> students) {
    this.students = students;
}

public int getCollegeId() {
    return collegeId;
}

public void setCollegeId(int collegeId) {
    this.collegeId = collegeId;
}

public String getCollegeName() {
    return collegeName;
}

public void setCollegeName(String collegeName) {
    this.collegeName = collegeName;
}

@Override
public String toString() {
    return "College [collegeId=" + collegeId + ", collegeName=" + collegeName + ", students=" + students + "]";
}

}

I don't think @Formula annotation supports collection it can only be applied for single-valued properties. Can't say if if there exists any tweak.

Looking for a collection here too. – mjs Sep 29 '20 at 17:15 — mjs, Sep 29 '20 at 17:15

Join external column to Hibernate entity using native SQL

2 Answers2