6

Is there a way for a CrudRepository interface to sort a table with multiple rows and simply return the first row, e.g. sort by a timestamp to return only the latest row?

public interface ImportReceiptRepository extends CrudRepository<ImportReceipt, Long>
{
    ImportReceipt getOneByImportTypeOrderByTimestampDesc(String importType);
    ImportReceipt findOneByImportTypeOrderByTimestampDesc(String importType);
}

Both findOneBy... and getOneBy... throw:

org.springframework.dao.IncorrectResultSizeDataAccessException: result returns more than one elements; nested exception is javax.persistence.NonUniqueResultException: result returns more than one elements
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:395)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:216)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:105)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy109.findOneByImportTypeOrderByTimestampDesc(Unknown Source)
at edu.ucdavis.dss.dw.services.DefaultImportReceiptService.getLatestOneByImportType(DefaultImportReceiptService.java:26)
...

Or, putting it another way, what's the CrudRepository equivalent of:

SELECT * FROM ImportReceipts ORDER BY timestamp DESC LIMIT 0,1;
Christopher
  • 1,635
  • 5
  • 19
  • 30
  • Not sure it's a duplicate: that answer and the one it is in turn a duplicate of refer to having JPA Query objects which is not the case with CrudRepository. – Christopher Feb 23 '15 at 18:28
  • Looks like Bludream's answer from http://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation is what I'm looking for. The syntax is "findFirst" or "findTop10" though it still returns a list. Thanks for the help holmis83, your link set me on the right path. – Christopher Feb 23 '15 at 18:33
  • Ah, I didn't know it was so different from "standard" JPA. Well, I learned something new. – holmis83 Feb 23 '15 at 18:50

1 Answers1

12

Bludream's answer from setMaxResults for Spring-Data-JPA annotation?… has the answer.

The syntax is "findFirst" or "findTop10" though it still returns a list.

List<ImportReceipt> findFirstByImportTypeOrderByTimestampDesc(String importType);

I suppose List will always be of size() 0 or 1.

Radu Ionescu
  • 3,462
  • 5
  • 24
  • 43
Christopher
  • 1,635
  • 5
  • 19
  • 30