I want to convert sql query into Spring Data JPA based query

Question

Given my SQL query below:

SELECT
  maker_code
FROM
  maker
WHERE
  part_no = p_part_no
AND effect_date_in <= TODAY
AND effect_date_out > TODAY 
AND effect_date_in = (SELECT MAX(effect_date_in) FROM maker WHERE part_no =p_part_no)

I want to use Spring Data JPA with inbuilt methods like findBy---() which will return the same result as of the above sql query

I tried JPQL Query:

 @Query(value = "
    SELECT p FROM Pg6p0012_01PurpartQueryModel p where p.partNo = :p_part_no AND 
    p.effectDateIn <= TODAY AND p.effectDateOut > TODAY AND p.effectDateIn = 
    (
    select max(p.effectDateIn)from Pg6p0012_01PurpartQueryModel where partNo =:p_part_no)"
    )

public List<Pg6p0012_01PurpartQueryModel>findByPartNo(@Param("p_part_no") String p_part_no);

But I am getting an error: Bad use of aggregate in this context.

And where is the question? Which part of the JPQL are you having a problem with? What have you tried? where is your error? — Neil Stockton
– Neil Stockton, Commented Dec 31, 2016 at 10:42
JPA API has no such "inbuilt functions" like "findBy...". Please read the JPA spec if you don't realise this. You are presumably referring to SPRING DATA JPA ... which is NOT the JPA API. Understand what technology you use first — Neil Stockton
– Neil Stockton, Commented Dec 31, 2016 at 10:55
Thats correct I am using SPRING DATA JPA. Is there any way to achieve the above result using SPRING DATA JPA inbuilt functions. — SOP
– SOP, Commented Dec 31, 2016 at 10:57
Even if there was a way, you would end up with a method name that would be 250-characters long, and you really don't want that. Automatic query generation from method names is fine for simple queries like findByName(). They're not fine, and not intended to be used for complex queries. Use any method name, and annotate it with @Query, where you would define a standard JPQL query. — JB Nizet
– JB Nizet, Commented Dec 31, 2016 at 10:59

Ken Bekov · Accepted Answer · 2017-01-01 09:15:16Z

1

You can't access alias p from nested query for aggregation function like MAX. Nested query has to have its own alias for table. Something like:

 @Query(value = "
    SELECT p FROM Pg6p0012_01PurpartQueryModel p where p.partNo = :p_part_no AND 
    p.effectDateIn <= TODAY AND p.effectDateOut > TODAY AND p.effectDateIn = 
    (SELECT MAX(p2.effectDateIn) FROM Pg6p0012_01PurpartQueryModel p2 WHERE partNo =:p_part_no)"
 )

answered Jan 1, 2017 at 9:15

Ken Bekov

14.1k3 gold badges40 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

I want to convert sql query into Spring Data JPA based query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related