Map new column from Spring Native query to entity

Question

I have a case statement in my Native query where I am attempting to override a field in my entity.

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

I am using this with JpaRepository as a native query, with pagination.

When I run the native query against my db directly, the result set looks as though I expect.

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

When I run the native query from my JpaRepository, everything works there, except word is always null. I cant' seem to figure out how to map the additional String word result to a field in my Entity.

Is there a way to get this to map? Or will I have to create an entire @SqlResultSetMapping() for all of my fields coupled with a native query? (hoping not)

UPDATE: 1

I was generalizing above. Here is my Query.

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

}

Obviously a bit more complex.

I can get it to map to the entity field with using a named query, but then I loose all the default mappings:

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

}

I guess, I am hoping there is a way to provide a single mapping on-top of the default mapping that is done by ORM. The above code results in only a pageable of Content PERMISSION, rather than the whole entity + permission.

UPDATE: 2

Ok, so I am getting closer... Seems by removing the @ColumnResult I do get the default mapping, plus the PERMISSION field mapped over! Looks like this:

@SqlResultSetMapping(
        name="listPagedMapping"
)

The last issue is it does not accept my CountQuery, and causes my tests to fail whenever a Pagination Query results with multiple pages. Looks like Spring try's to come up with its own CountQuery, which is not correct.

UPDATE: 3

To finish this off, looks like I can provide the Count Query as described here: Spring Data - Why it's not possible to have paging with native query

I will give this a go and update back.

please show us the query method and the class used as a return type — Jens Schauder, Mar 28 '18 at 04:50

score 0 · Answer 1 · answered Mar 29 '18 at 13:07

I never got this to work quite how I wanted. I am sure I could by mapping my entire entity, but, that would have been painstaking. I ended up solving this by using NamedNativeQueries, with mapping for the additional Column as a result of my Case statement. My entity class is now annotated like:

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

We also dont need the permission field in this entity anymore. I removed that.

Then in my Repository:

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

Thats it! Now the result of my case statement is returned with each entity.

Object[0] = original, default mapped entity. Object[1] = permission

Map new column from Spring Native query to entity

1 Answers1