Spring JPA Formula with multiple columns-CodePudding

I am trying to read data from another table based on the value of one of the fields in the current entity. But somehow I am facing an issue selecting multiple fields inside the formula.

@Entity
@Table(name = "contacts")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ContactInfo {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;

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

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

//Not working
@Formula("(select code,area from areas where area_id=id)")
private Map<String, String> vals;

//working
@Formula("(select code from areas where area_id=id)")
private String someVal;

}

is there any way that I can use the formula for retrieving multiple columns of data with multiple rows?

Thanks for your help.

CodePudding user response：

I'm not sure you can retrieve like Map<string, String>. Suppose you declare another field how you going to retrieve. you can receive as a List instead of Map<st..., str..>.

Instead of @Formulae, you can use @OneToMany & @ManyToOne. through a Bi-direction connection, you achieve your scenario.

CodePudding user response：

This looks more like it should be using JPA's ElementCollection mapping to the Area table, with a MapKeyColumn mapping:

public class ContactInfo {
  ..
  @ElementCollection
  @MapKeyColumn(name="code")//key
  @Column(name="area") //value
  @CollectionTable(name="areas",
        joinColumns=@JoinColumn(name="area_id"))//fk from Areas->contacts.id
  private Map<String, String> vals;
  ..

See this for some more information and examples.