NativeQuery Spring数据返回对象

Question

我需要在Spring数据中实现一个查询，如下所示：-

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;

@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);

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;
    }
}

我的测试方法：-

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

}

我得到以下异常：-

org.springframework.core.convert.conversionFailedException：未能将值“{TEST,5.0}”的类型[java.lang.object[]]转换为类型[com.mycomp.myobj]；嵌套异常是org.springframework.core.convert.converterNotFoundException：找不到能够从[java.lang.string]类型转换为[com.mycomp.dto.myobj]类型的转换器

@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;

}

我不确定JPQL中的联接查询应该是什么样子...

Answer 1

只需使用投影和JPQL查询：

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();

这个查询可能不是您所需要的，因为我不知道实体类的结构。但如果你给他们看我会纠正这个问题...