Spring Data JPA @Query - select max

Question

I m trying to write a query using select max&where with @Query

The following won't work, how can I fix it?

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
interface IntermediateInvoiceRepository extends JpaRepository<Invoice, String> {

@Query("SELECT max(i.sequence) " +
        "FROM Invoice as i " +
        "WHERE i.fleetId = :fleetId" +
        "   AND i.sequence IS NOT NULL")
Long findMaxSequence(@Param("fleetId") String fleetId);

}

I've run into another answer but it is using the entity manager explicitly, os its not the same

How do I write a MAX query with a where clause in JPA 2.0?

the error is :

2018-09-14T09:27:57,180Z  [main] ERROR o.s.boot.SpringApplication     - Application startup failed
org.springframework.data.mapping.PropertyReferenceException: No property findMaxSequence found for type Invoice!

the invoice class (simplified for brevity):

@Entity
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Table(name = "invoices", indexes = {
        @Index(name = "IDX_FLEET", columnList = "fleetId", unique = false)
        ,
        @Index(name = "IDX_USERSSS", columnList = "userId", unique = false)
        ,
        @Index(name = "IDX_TIME", columnList = "invoiceDate", unique = false)
        ,
        @Index(name = "IDX_SEQUENCE", columnList = "sequence", unique = false)
})
@JsonIgnoreProperties(ignoreUnknown = true)
public class Invoice implements Serializable {

    private static final long serialVersionUID = 1L;

    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(columnDefinition = "CHAR(36)")
    @Id
    private String id;

    @Column
    private long sequence;

...

Update:

maybe a work-around with findOne with DESC sort on the sequence column?

@Query("SELECT i.sequence " + "FROM Invoice as i " + "WHERE i.fleetId = :fleetId " + "ORDER BY i.sequence DESC ") Long getMaxSequence(@Param("fleetId") String fleetId);

But i need to LIMIT the resultset to 1 somehow

Update 2:

fixed the import org.springframework.data.jpa.repository.Query; still in error

Spring Data is very conventional, the method name starting with find might be throwing it off. What happens when you rename the method to 'getMaxSequence' ? — Gimby, Sep 14 '18 at 09:54
the same: org.springframework.data.mapping.PropertyReferenceException: No property getMaxSequence found for type Invoice! — Orkun, Sep 14 '18 at 10:08
can you try using nativequery and check if that gives the same problem — Syed Anas, Sep 14 '18 at 10:47
Could you check if you're using _org.springframework.data.jpa.repository.Query_ annotations instead of any other? — eltabo, Sep 14 '18 at 11:33
i m using : import org.springframework.data.mongodb.repository.Query; — Orkun, Sep 14 '18 at 12:03
i ve changed the Query import to the jpa one. i get a java.lang.NullPointerException now . i ve added AND i.sequence IS NOT NULL in my query (pls see the update). still the same error. — Orkun, Sep 14 '18 at 12:51
`@Query("select max(i.sequence) ...")` is ok, maybe you get some other error but not about this. [This unit test code(repository.getMaxCode)](https://github.com/start-java/start-spring-data-jpa/blob/18.10.0/src/test/java/tech/simter/start/springdatajpa/repository/entity1/GetMaxCodeMethodTest.java#L57) can proved it. Beside that the annotation `@Repository` is not neccessary and should be removed. — RJ.Hwang, Oct 09 '18 at 15:46

eltabo · Accepted Answer · 2018-09-15T10:14:06.770

5

Since you're using JPA repositories, use:

org.springframework.data.jpa.repository.Query

annotation instead of

org.springframework.data.mongodb.repository.Query

You can create a query method, without using @Query annotation, like:

Invoice findFirstByFleetIdOrderBySequenceDesc(String fleetId);

that return the invoice that you need.

edited Sep 15 '18 at 10:14

answered Sep 14 '18 at 12:42

eltabo

3,749
1
21
33

score 0 · Answer 2 · answered Sep 14 '18 at 13:10

I ve found a workaround :

create a simple repository method returning Page and accepting a Pageable:

Page<Invoice> findByFleetId(String fleetId, Pageable pageable);

This way we can imitate an ORDER BY sequence LIMIT 1 via the following:

long maxNewSeq = 0;
PageRequest pageRequest = new PageRequest(0, 1, Sort.Direction.DESC, "sequence");
Page<Invoice> pageableInvoices = invoiceRepository.findByFleetId(invoice.getFleetId(), pageRequest);
if(pageableInvoices.getTotalElements() > 0){
    maxNewSeq = pageableInvoices.getContent().get(0).getSequence();
}

invoice.setSequence(Math.max(0, maxNewSeq) + 1);

seems to work like a charm.

Spring Data JPA @Query - select max

2 Answers2