Hibernate query to Sql query

Question

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);
}

Have you read the documentation? The language is named HQL (or JPQL). Not Hibernate SQL. Read docs.jboss.org/hibernate/orm/current/userguide/html_single/… and try something. Your query is quite simple to write, if you take the time to read the documentation and examples, and try things. — JB Nizet
– JB Nizet, Commented Jul 3, 2017 at 16:58

xyz · Accepted Answer · 2017-07-03 17:26:18Z

1

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

edited Jul 3, 2017 at 17:26

answered Jul 3, 2017 at 17:18

xyz

5,4572 gold badges29 silver badges36 bronze badges

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

Comments

Maciej Kowalski · Accepted Answer · 2017-07-03 17:02:22Z

1

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);
}

answered Jul 3, 2017 at 17:02

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

3 Comments

user Over a year ago

I didn't understand. I ask for query for get all orders where product name is equal to wanted value.

Maciej Kowalski Over a year ago

Mmm... ok in that case that is too trivial of a answer anyway.. please read through the documentation as @JB Nizet pointed out. But in the end if you would want get columns from multiple tables as in your question.. then hopefully my post would come in handy

JB Nizet Over a year ago

This answer is wrong anyway. You're still using a SQL query, and not a JPQL one.

Collectives™ on Stack Overflow

Hibernate query to Sql query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related