JPA @Query issues with the returned object

Question

I have added this in my DAO:

@Query("SELECT distinct LBL.cId, LBL from FooBar LBL ORDER BY LBL.collectedAt DESC")
    List<FooBar> findDistinctcIdOrderByCollectedAtDesc();

And then this is in my service

List<FooBar> fooBars = this.liveBusLocationDao.findDistinctcIdOrderByCollectedAtDesc();

And this is what I got when I debug contents of fooBars

fooBars = {ArrayList@11035}  size = 1
 0 = {Object[2]@11093} 
   0 = "string"
   1 = {FooBar@11095} "FooBar(cId=string, internalcId=123456789012, createdAt=2011-11-02 02:59:12.208, collectedAt=2017-10-06 14:26:26.544)"

How can I traverse this result?

this is Spring Data JPA not the JPA API. i.e there is no such JPA @Query. perhaps tag your question correctly — user8558216
– user8558216, Commented Oct 6, 2017 at 12:43

Maciej Kowalski · Accepted Answer · 2017-10-06 12:41:53Z

0

You would need to create a result class and use special syntax in the jpa query if you want to use projection without getting the Object[] result.

package com.example

class ResultClass{

   int fieldOne;
   String fieldTwo;

  public (int fieldOne, intfieldTwo){
     // .. set fields
  }

}

and query:

@Query("SELECT distinct new com.example.ResultClass(LBL.cId, LBL.strProperty) from FooBar LBL ORDER BY LBL.collectedAt DESC")
    List<ResultClass> findDistinctcIdOrderByCollectedAtDesc();

answered Oct 6, 2017 at 12:41

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

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

Comments

DamienB · Accepted Answer · 2017-10-06 12:44:33Z

0

Your Query is requesting on 2 attributes. you can make a Custom class that as the id and the object or you could just remove LBL.cId from SELECT

Your request should be :

@Query("SELECT distinct LBL from FooBar LBL ORDER BY LBL.collectedAt DESC")
List<FooBar> findDistinctcIdOrderByCollectedAtDesc();

The distinct is working on the whole Row. take a look to it, if you are not shure about it, you can use group by LBL

https://stackoverflow.com/a/10066187/3543153

edited Oct 6, 2017 at 12:44

answered Oct 6, 2017 at 12:39

DamienB

4671 gold badge6 silver badges20 bronze badges

2 Comments

nirvair Over a year ago

But what result would this query give me? On which column is distinct condition applied?

DamienB Over a year ago

i updated my answer. Distinct work on the whole row. You acn also replace distinct with a group by

Collectives™ on Stack Overflow

JPA @Query issues with the returned object

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related