title: Join queries with JPQL in Spring Data Jpa
body: <p>I created a <code>left join</code> query with <code>JPQL</code> in spring data jpa but failed in my unit test. There are two entities in the project.</p>
  
  <p><code>Product</code> entity:</p>
  
  <pre><code>@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
  }
  </code></pre>
  
  <p><code>Brand</code> entity:</p>
  
  <pre><code>@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&lt;Product&gt; products;
      // getter and setter
  }
  </code></pre>
  
  <p>And a third class <code>Prod</code> to map the query results to Object:</p>
  
  <pre><code>public class Prod implements Serializable {
      private Integer id;
      private String name;
      private double price;
      //private String brandName;
      // getter and setter
  }
  </code></pre>
  
  <p>It works fine with this query:</p>
  
  <pre><code>public interface ProductRepository extends JpaRepository&lt;Product, Integer&gt; {
      @Query(value = "select new com.pechen.domain.Prod(p.id, p.name, p.price) from Product p ")
      Page&lt;Prod&gt; pageForProd(Pageable pageRequest);
  }
  </code></pre>
  
  <p>But if I add new property <code>brandName</code> for <code>Prod</code> and refactor the query with <code>left join</code>, it test fails:</p>
  
  <pre><code>@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&lt;Prod&gt; pageForProd(Pageable pageRequest);
  </code></pre>
  
  <p>The problem seems to be here <code>on p.brand_id = b.id</code> because there is not a <code>brand_id</code> property in <code>Product</code>, it's just a column name. So how can I make this work?</p>
  
  <p><strong>Update:</strong></p>
  
  <p>There turned to be some sytax errors in the <code>JPQL</code> query, just fix it as the following:</p>
  
  <pre><code>@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&lt;Prod&gt; pageForProd(Pageable pageRequest);
  </code></pre>
  
  <p>Besides, it's very troublesome in this way to create another class everytime to map the query results into object(I mean the <code>Prod</code> class). So is there a good way to work with it? Any help would be appreciated.</p>