Join queries with JPQL in Spring Data Jpa

Question

I created a left join query with JPQL in spring data jpa but failed in my unit test. There are two entities in the project.

Product entity:

@Entity
@Table(name = "t_goods")
public class Product implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "id", length = 6, nullable = false)
    private Integer id;
    @Column(name = "name", length = 20, nullable = false)
    private String name;
    @Column(name = "description")
    private String desc;
    @Column(name = "category", length = 20, nullable = false)
    private String category;
    @Column(name = "price", nullable = false)
    private double price;
    @Column(name = "is_onSale", nullable = false)
    private Integer onSale;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "brand_id")
    private Brand brand;
    // getter and setter
}

Brand entity:

@Entity
@Table(name = "tdb_goods_brand")
public class Brand implements Serializable {

    @Id
    @GeneratedValue
    @Column(name = "id", length = 6, nullable = false)
    private  Integer id;
    @Column(name = "brand_name", unique = true, nullable = false)
    private String name;

    @OneToMany(mappedBy = "brand", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<Product> products;
    // getter and setter
}

And a third class Prod to map the query results to Object:

public class Prod implements Serializable {
    private Integer id;
    private String name;
    private double price;
    //private String brandName;
    // getter and setter
}

It works fine with this query:

public interface ProductRepository extends JpaRepository<Product, Integer> {
    @Query(value = "select new com.pechen.domain.Prod(p.id, p.name, p.price) from Product p ")
    Page<Prod> pageForProd(Pageable pageRequest);
}

But if I add new property brandName for Prod and refactor the query with left join, it test fails:

@Query(value = "select new com.pechen.domain.Prod(p.id, p.name, p.price, b.name) from Product p left join com.pechen.domain.Brand b on p.brand_id = b.id")
Page<Prod> pageForProd(Pageable pageRequest);

The problem seems to be here on p.brand_id = b.id because there is not a brand_id property in Product, it's just a column name. So how can I make this work?

Update:

There turned to be some sytax errors in the JPQL query, just fix it as the following:

@Query(value = "select new com.pechen.domain.Prod(p.id, p.name, p.price, b.name) from Product p left join p.brand b")
Page<Prod> pageForProd(Pageable pageRequest);

Besides, it's very troublesome in this way to create another class everytime to map the query results into object(I mean the Prod class). So is there a good way to work with it? Any help would be appreciated.

Answer 1

Instead of p.brand_id = b.id you should do p.brand.id = b.id