4

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...

Chirrut Imwe
  • 633
  • 10
  • 20
  • have you executed this query in db ui like sqldeveloper because where are you using joins over here to establish the relation ship? – Pradeep Jun 08 '17 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 Jun 08 '17 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 Jun 08 '17 at 13:32

3 Answers3

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...

Cepr0
  • 28,144
  • 8
  • 75
  • 101
  • 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 Jun 08 '17 at 15:52
  • 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. – Chirrut Imwe Jun 08 '17 at 16:12
  • 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. – Mark.ewd Mar 10 '18 at 15:20
  • Worked for me! The only thing to keep in mind that order of the column (in native-query) must be same for the interface. :) – Manan Shah Jun 21 '18 at 11:45
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);
}
Pradeep
  • 1,947
  • 3
  • 22
  • 45
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.

Patrick Dezenzio
  • 27
  • 7