CODEWITHSUNDEEP
javapostgresqlhibernatespring-data-jpajpql
user
user

Reputation: 4830

Hibernate query to Sql query

My entity of product looks like below:

@Entity
@Table(name = "order")

public class OrderEntity {

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

   @ManyToMany(cascade = CascadeType.ALL)
   @JoinTable(
        name = "order_products",
        joinColumns = @JoinColumn(name = "order_id", referencedColumnName = "order_id"),
        inverseJoinColumns = @JoinColumn(name = "product_id", referencedColumnName = "id")
   )
   private Set<ProductEntity> products = new HashSet<>();
}

ProductEntity:

@Entity
@Table(name = "product")
public class ProductEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(unique = true)
    private String name;

    @ManyToMany(mappedBy = "products")
    private Set<OrderEntity> orders = new HashSet<>();
}

I want to get all orders where product name is equal to wanted value. And I write sql query to get result from database, but I cannot write hibernate query for Spring Data JPA.

My query for postgreSQL looks like this:

SELECT o.order_id, op.product_id, p.name
  FROM public.order o
  INNER JOIN public.order_products op
    ON p.order_id = op.product_id
  INNER JOIN public.product p
    ON op.product_id = p.id
  WHERE p.name = 'Foo';

And this query return me an id of order, product_id and name of product. And this works. But I didn't know how to write this question as spring query using @Query.

I need a metohod in my repository:

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, Long> {
    @Query("") <- place for my query in Hibernate sql
    List<OrderEntity> findAllByProductName(@Param("name") String name);
}

Upvotes: 2

Views: 2928

Answers (2)

xyz
xyz

Reputation: 5407

try this: (it returns full OrderEntity objects )

@Query("select  o from OrderEntity o join o.products prod where prod.name = :name")
List<OrderEntity> findAllByProductName(@Param("name") String name);

if you need fetch eager all data for products use : ....OrderEntity o join o.products... in query instead of OrderEntity o join o.products

Upvotes: 1

Maciej Kowalski
Maciej Kowalski

Reputation: 26492

This is a projection consisting of columns from many entties, so you would have to go for the Result Class strategy.

Basically, you create a POJO class with expected result fields an an equivalent constructor:

public class ResultClass{

    private Integer orderId;
    private Integer productId;
    private String name;

    public ResultClass(Integer orderId, Integer productId, String name){
        // set the fields
    }
}

Then you alter the query a bit:

SELECT new com.mypkg.ResultClass(o.order_id, op.product_id, p.name)
FROM public.order o
INNER JOIN public.order_products op
  ON p.order_id = op.product_id
INNER JOIN public.product p
  ON op.product_id = p.id
WHERE p.name = 'Foo';

And change the return type on the interface method:

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, Long> {
    @Query("...") 
    List<ResultClass> findAllByProductName(@Param("name") String name);
}

Upvotes: 1

Related Questions