NativeQuery Spring Data return object

Question

I need to implement a query in Spring Data like this :-

Select User.name, sum(Activity.minutes) 
From User, Activity, ActivityStatus
Where User.id = ActivityStatus.userId
And Activity.id = ActivityStatus.activityId
AND ActivityStatus = "COMPLETED"
GROUP BY user.name;

So i need to join 3 tables, therefore I have to use @Query with nativeQuery = true ( correct me if I'm wrong here )

And so my Repository method looks like this :-

@Query(value = "Select User.name, sum(Activity.minutes) as total_minutes
    From User, Activity, ActivityStatus
    Where User.id = ActivityStatus.userId
    And Activity.id = ActivityStatus.activityId
    AND ActivityStatus = "COMPLETED"
    AND User.Type = ?1
    GROUP BY user.name;",
    nativeQuery = true
    )
List<MyObj> getTotalActivityMinutesByUserType(String userType);

MyObj class looks like this :-

public class MyObj {
    String name;
    long total_minutes;

// getter and setter methods

    public MyObj(String name, long total_minutes) {
        this.name = name;
        this.total_minutes = total_minutes;
    }
}

My Test Method :-

@Test
public void TotalActivityTest() throws Exception {
    List<MyObj> objA = myRepository.getTotalActivityMinutesByUser("TEST");

}

and i get the following exception :-

org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.Object[]] to type [com.mycomp.MyObj] for value '{TEST, 5.0}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.lang.String] to type [com.mycomp.dto.MyObj]

I need a way to return the result as MyObj. ( Or at least a way to cast it to MyObj) Is this possible?

EDIT:

Building from @Cepr0's answer My Entity class looks like this :-

@Entity
public class ActivityStatus extends Base {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH, CascadeType.DETACH})
    private Activity activity;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH, CascadeType.DETACH})
    private User user;

}

I am not sure how the JOIN query in JPQL should look like...

have you executed this query in db ui like sqldeveloper because where are you using joins over here to establish the relation ship? — Pradeep
– Pradeep, Commented Jun 8, 2017 at 13:15
Yes, there's no issues with the query. From the exception you can observe that the result is {TEST,5.0} which is exactly the result I get from running the query on the workbench. The problem is in mapping the result set to MyObj. — Chirrut Imwe
– Chirrut Imwe, Commented Jun 8, 2017 at 13:19
The query is returning object array which you are storing it in in an object.Iterate through object array and set it to object — Pradeep
– Pradeep, Commented Jun 8, 2017 at 13:32

Cepr0 · Accepted Answer · 2017-06-08 14:14:58Z

13

Just use Projection and JPQL query:

public interface NameAndDuration {
    String getName();
    Long getDuaration();
}

@Query("select u.name as name, sum(a.minutes) as duration from User u join u.activityStatus st join st.activity a where st.status = "COMPLETED" and u.type = ?1 group by u.name")
List<NameAndDuration> getNameAndDurationByUserType(String userType);

List<NameAndDuration> list = getNameAndDurationByUserType("TEST");
String userName = list.get(0).getName();

This query is probably not exact what you need because I don't know a structure of your entity classes. But if you show them I will correct the query...

edited Jun 8, 2017 at 14:14

answered Jun 8, 2017 at 14:00

Cepr0

31.2k9 gold badges82 silver badges105 bronze badges

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

4 Comments

Chirrut Imwe Over a year ago

Thanks for the help, not quite working yet. Here's how my Entity class look like :-

@Entity public class ActivityStatus extends Base {     @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private Long id;      @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH, CascadeType.DETACH})     private Activity activity;      @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH, CascadeType.DETACH})     private User user; }

In this case, how should i write the JOIN query in JPQL?

Chirrut Imwe Over a year ago

Thanks! I'm sorry for the messy comments, but i managed to build the correct JPQL query and it worked! will try it out more tomorrow. Your answer definitely looks cleaner. so I'm marking this as the answer.

Mark.ewd Over a year ago

As I found the key to success is using an interface (rather than a class) and naming the result set names to match the interface. Thanks.

Manan Shah Over a year ago

Worked for me! The only thing to keep in mind that order of the column (in native-query) must be same for the interface. :)

Pradeep · Accepted Answer · 2017-06-08 13:37:34Z

0

The query is returning object array which you are storing in an normal object.Iterate through object array and set it to object like below

List<MyObj > test= new ArrayList<>();
List<Object[]> rows= query.list();
for (Object[] row : rows) {
MyObj temp=new MyObj (arg1,arg2);
temp.set((Dataype) row[0])//You need to create getters and setters for your pojo
..
test.add(temp);
}

answered Jun 8, 2017 at 13:37

Pradeep

1,9753 gold badges25 silver badges47 bronze badges

2 Comments

Chirrut Imwe Over a year ago

Thanks for the help Pradeep, I'm pretty sure your answer works as well, but I guess Cepr0's answer is cleaner. Appreciate your help though.

Pradeep Over a year ago

Glad that you are able to fix the issue.You r Welcome.

Patrick Dezenzio · Accepted Answer · 2018-10-16 19:52:36Z

0

I actually had a similar issue with this exception. I have 3 tables: Project, Asset, and ProjectAsset. ProjectAsset is the reference table where one project can have many assets. So I created 3 repositories, one for each entity. The problem is I placed my @Query in the ProjectAssetRepository and that didn't work due to the repository extending CrudRepository. ProjectAssetId is an embedded id made up of projectid and assetid. I can't just return Asset objects in this repository so I moved the method to AssetRepository and everything worked. If you are using cross-reference tables, make sure you pull the correct object or else you will run into this exception.

answered Oct 16, 2018 at 19:52

Patrick Dezenzio

277 bronze badges

Collectives™ on Stack Overflow

NativeQuery Spring Data return object

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related