将新列从Spring本机查询映射到实体

Question

SELECT i.id, i.ONE_TO_ONE_ID, i.ANOTHER, CASE(WHEN condition THEN 'YES' WHEN another_condition THEN 'NO' ELSE 'MAYBE' END) as word ....

当我直接对我的db运行本机查询时，结果集看起来就像我所期望的那样。

| id_value | MAPPED_ENTITY_ID_value | another value | word_value (YES) |

当我从我的JpaRepository运行本机查询时，除了word始终为null之外，所有的东西都在那里工作。我似乎想不出如何将额外的字符串单词结果映射到实体中的一个字段。

有没有办法把这个弄到地图上？还是必须为所有字段创建一个完整的@SQLResultSetMapping()，并与本机查询耦合？（希望不会）

我刚才是在概括。以下是我的疑问。

@Query(
            name = "listPagedMapping",
            value = "SELECT DISTINCT i.ID, i.INSTANCE_ID, i.REGION, i.CNAME_STACK_ID, i.INSTANCE_STATE, i.IP_ADDRESS, i.EC2_ROLE_NAME, i.INSTANCE_OWNER, i.IS_MASTER, i.EC2_MASTER_ID, i.CNAME, i.EC2_START_TIMESTAMP, i.PRIVATE_DNS, i.INSTANCE_NAME, i.AUTO_TERMINATE, i.AUTO_TERMINATE_DATE, i.TERMINATION_ZONE, i.ADMIN_GROUP_AD_LDAP_ID, i.USER_GROUP_AD_LDAP_ID,  (CASE WHEN i.INSTANCE_OWNER=:username THEN 'OWNER' WHEN i.ADMIN_GROUP_AD_LDAP_ID IN (g.AD_LDAP_ID) THEN 'ADMIN' WHEN  i.USER_GROUP_AD_LDAP_ID IN (g.AD_LDAP_ID) THEN 'USER' END) as PERMISSION FROM USER u, USER_ACCESS_GROUPS g, EC2_PROVISIONING i WHERE i.INSTANCE_OWNER=:username and i.INSTANCE_STATE in (:instanceStates) or u.username=:username and i.INSTANCE_STATE in (:instanceStates) and g.USER_ID=u.USER_ID and (i.ADMIN_GROUP_AD_LDAP_ID IN (g.AD_LDAP_ID) or i.USER_GROUP_AD_LDAP_ID IN (g.AD_LDAP_ID))",
            countQuery = "SELECT count(*) FROM (SELECT DISTINCT i.* FROM USER u, USER_ACCESS_GROUPS g, EC2_PROVISIONING i WHERE i.INSTANCE_OWNER=:username and i.INSTANCE_STATE in (:instanceStates) or u.username=:username and i.INSTANCE_STATE in (:instanceStates) and g.USER_ID=u.USER_ID and (i.ADMIN_GROUP_AD_LDAP_ID IN (g.AD_LDAP_ID) or i.USER_GROUP_AD_LDAP_ID IN (g.AD_LDAP_ID))) as ug",
            nativeQuery = true)
    Page<Ec2Instance> findAllByPermissionUserAdminOrOwnerAndInstanceStateIn(
            @Param("username")final String username,
            @Param("instanceStates") final Set<String> instanceStates,
            final Pageable pageable);

}

显然要复杂一些。

我可以通过使用命名查询将其映射到entity字段，但随后我松开了所有默认映射：

@JsonInclude(JsonInclude.Include.NON_NULL)
@SuppressWarnings("unused")
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(exclude={"masterNode", "workers", "associatedBuckets"})
@Entity
@Table(name = "EC2_PROVISIONING")
@SqlResultSetMapping(
        name="listPagedMapping",
        columns = {
                @ColumnResult(name = "permission", type = String.class)
        }
)
@NamedNativeQuery(
        name = "listAccessibleInstances",
        query = ACCESSIBLE_QUERY,
        resultSetMapping = "listPagedMapping"

)
public class Ec2Instance {

    ....

    private String permission;

    @column(name = "INSTANCE_ID")
    private String instanceId;

    @ManyToOne
    @JoinColumn(name = "EC2_MASTER_ID")
    private Ec2Instance masterNode;

        @Setter(AccessLevel.NONE)
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "WORKER_EC2_NODES", joinColumns = { @JoinColumn(name = "EC2_MASTER_ID") }, inverseJoinColumns = {
            @JoinColumn(name = "ID") })
    private Set<Ec2Instance> workers = new HashSet<>();

    ... More fields ..

}

好吧，所以我越来越近了...似乎通过删除@columnresult我确实得到了默认映射，以及映射到上面的权限字段！看起来是这样的：

@SqlResultSetMapping(
        name="listPagedMapping"
)

最后一个问题是它不接受CountQuery，并且导致每当分页查询结果为多个页面时我的测试失败。看起来Spring Try想出自己的CountQuery，这是不正确的。

更新：3

Answer 1

我一直没能按我想的那样工作。我确信我可以通过映射我的整个实体，但是，这将是一个艰苦的。最后，我通过使用NamedNativeQueries解决了这个问题，并通过Case语句映射了附加列。我的实体类现在注释如下：

@JsonInclude(JsonInclude.Include.NON_NULL)
@SuppressWarnings("unused")
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
@Entity
@Table(name = "EC2_PROVISIONING")
@SqlResultSetMappings({
        @SqlResultSetMapping(
                name = "listPagedMapping",
                entities = {
                        @EntityResult(
                                entityClass = Ec2Instance.class
                        )
                },
                columns = {@ColumnResult(name = "permission", type = String.class)}
        ),
        @SqlResultSetMapping(name = "listPagedMapping.count", columns = @ColumnResult(name = "cnt"))
})
@NamedNativeQueries({
    @NamedNativeQuery(
            name = "Ec2Instance.listAccessibleInstances",
            query = ACCESSIBLE_QUERY,
            resultClass = Ec2Instance.class,
            resultSetMapping = "listPagedMapping"
    ),
    @NamedNativeQuery(
            name = "Ec2Instance.listAccessibleInstances.count",
            resultSetMapping = "listPagedMapping.count",
            query = ACCESSIBLE_QUERY_COUNT
    )
})

我们也不再需要这个实体中的权限字段。我把它去掉了。

然后在我的存储库中：

Page<Object[]> listAccessibleInstances(
        @Param("username")final String username,
        @Param("instanceStates") final Set<String> instanceStates,
        final Pageable pageable);

就是这样！现在我的case语句的结果与每个实体一起返回。