0

I defined my model classes like below.

@Entity
@Table(name = "my_employee")
public class Employee {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    private String name;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinTable(name = "emp_address_mapping", joinColumns = @JoinColumn(name = "emp_id"), inverseJoinColumns = @JoinColumn(name = "address_id"))
    private List<Address> addresses = new ArrayList<Address>();

    .......
    .......
}

@Entity
@Table(name = "my_address")
public class Address {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;
    private String country;
    .....
    .....
}

public class EmployeeDetails {
    private int empId;
    private String name;
    private String country;
    ......
    ......
}

How can I write a query using @Query annotation to populate all the EmployeeDetails.

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {

    @Query("SELECT new com.sample.app.model.EmployeeDetails......")
    List<EmployeeDetails> getEmployeeDetails();
}
Hari Krishna
  • 3,658
  • 1
  • 36
  • 57
  • What exactly is the problem? The start of the query you wrote looks good to me. And there are a gazillion ways to flatten a 1:M relationship. – Jens Schauder Jul 10 '19 at 06:16
  • Can you provide me any links in flattening 1:M, Many-to-many relations to a new model via @Query annotation. – Hari Krishna Jul 10 '19 at 06:23
  • https://www.logicbig.com/tutorials/java-ee-tutorial/jpa/jpql-aggregate-functions.html https://stackoverflow.com/questions/3730625/jpa-join-in-jpql – Jens Schauder Jul 10 '19 at 06:36

1 Answers1

1

Create the constructor in EmployeeDetails

public EmployeeDetails(int id,String name,String country){
  this.id=id;
  this.name=name;
  this.country=country;
}

Try this query

To get all employee details:

SELECT new com.sample.app.model.EmployeeDetails(e.id,e.name,a.country) from Employee e,Address a
Sana
  • 360
  • 3
  • 13