NativeQuery Spring Data返回对象 [英] NativeQuery Spring Data return object

问题描述

我需要在Spring Data中实现这样的查询:-

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;

所以我需要联接3个表，因此我必须将@Query与nativeQuery = true一起使用(如果我在这里错了，请纠正我)

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

所以我的Repository方法看起来像这样:-

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类看起来像这样:-

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:失败 从类型[java.lang.Object []]转换为类型 [com.mycomp.MyObj]的值为'{TEST，5.0}';嵌套的 例外是 org.springframework.core.convert.ConverterNotFoundException:否 发现可以将类型[java.lang.String]转换为 输入[com.mycomp.dto.MyObj]

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]

我需要一种将结果返回为MyObj的方法. (或者至少是将其投射到MyObj的方法)这可能吗?

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

通过@ Cepr0的答案构建My Entity类如下:-

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;

}

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

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

推荐答案

只需使用投影和 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();

此查询可能与您需要的查询不完全相同，因为我不知道您的实体类的结构.但是，如果您显示它们，我将更正查询...

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

这篇关于NativeQuery Spring Data返回对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！