Query multi tables' columns with join in spring data jpa

Question

Description:

I have two tables, Shop_Employee and Shop_Employee_Type. I wanna display typeName directly when show the employee detail information, but I don't want to config the relationship(OneToMany or ManyToOne) between this two entity.
Because this will load all Shop_Employee_Type column's value, but these values are useless for me, I just need typeName of Shop_Employee_Type. Below is my code, but it doesn't work.

ShopEmployeeType:

@Entity
@Data
//@DynamicUpdate
public class ShopEmployeeType {
    @Id
    private String typeId;
    private String shopId;
    private String typeName;
    private Integer typeStatus;
    private String typeDescription;
}

Shop_Employee:

@Entity
@Data
public class ShopEmployee {

    @Id
    private  String employeeId;
    private String shopId;
    private String typeId;
    private String name;
    private String code;
    private String phone;
    private Integer status;
    private String idcardNumber;
    private String image;
    //@Transient
    private String typeName;

    public ShopEmployee() {
    }
}

Repository:

@Query(value = "select u.*,t.type_name from shop_employee u inner join shop_employee_type t on u.type_id=t.type_id", nativeQuery = true)
List<ShopEmployee> findAllData();

This could show typeName as I wished, but there is an error appears when I save a new entity Shop_Employee; If I add a @Transient for 'typeName', It could save successfully, but the value of 'typeName' is null when I query entity Shop_Employee.

Youcef LAIDANI · Accepted Answer · 2017-11-15 07:10:05Z

2

Your query should return two Objects Shop_Employee and a String, so the return results should not be List<ShopEmployee> it should be :

@Query(value = "select u.*, t.type_name from shop_employee ...", nativeQuery = true)
List<Object[]> findAllData();

Then you can get the ShopEmployee using :

List<Object[]> list = findAllData();
for(Object[] obj : list){
   ShopEmployee shopEmployee = (ShopEmployee) obj[0];
   String type_name = (String) obj[1];
}

So, in ShopEmployee entity you don't need to use :

//@Transient
//private String typeName;

answered Nov 15, 2017 at 7:10

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query multi tables' columns with join in spring data jpa

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related