Get result from native query using Java Interface

Question

I have these Postgres tables:

create table deals_new
(
    id                  bigserial primary key,
    slip_id             text,
    deal_type           integer,
    timestamp           timestamp,
    employee_id         bigint
        constraint employee_id_fk
            references common.employees
);

create table twap
(
    id                 bigserial     primary key,
    deal_id            varchar          not null,
    employee_id        bigint
        constraint fk_twap__employee_id
            references common.employees,
    status             integer
);

create table common.employees
(
    id              bigint primary key,
    first_name      varchar(150),
    last_name       varchar(150)
);

I created this JPA repository:

public interface DealsRepository extends JpaRepository<Employee, Long> {

    @Query (value =
            "SELECT e.first_name, e.last_name " +
                    "FROM common.deals_new d " +
                    "JOIN common.employees e ON e.id = d.employee_id " +
                    "LEFT OUTER JOIN common.twap t on " +
                    "        t.deal_id = d.slip_id AND " +
                    "        d.timestamp between '11-11-2010' AND '11-11-2011' AND " +
                    "        d.deal_type in (1, 2) " +
                    "OFFSET :offset " +
                    "LIMIT :limit ",
            nativeQuery = true)
    List<IdsOnly> getHistoryAllPairsSearchParam(@Param("offset") int offset,
                                                      @Param("limit") int limit);
}

As you can see I get the result using this Interface:

public interface IdsOnly {
    String getFirstName();
    String getLastName();
}

private DealsList getResponseParams(List<IdsOnly> deals) {
        return new DealsList( // get here the values from the interface and convert them);
    }

It's not clear to me how I get the values from the interface and do some processing. Can you give me some advice how I can for example print them?

I don't see idsOnlyObject into the above code? Where did you get it? — user1285928
– user1285928, Commented Jan 11, 2022 at 15:15
It is called a projection, Spring JPA creates a proxy that implements the IdsOnly interface and calling upon getFirstName() will return the value found in database. Read more about it here baeldung.com/spring-data-jpa-projections — asgarov1
– asgarov1, Commented Jan 11, 2022 at 15:32
what's your class object for common.employees called? Change the name of the interface "IdsOnly" to "<ObjectName>View" and see if that works. — Ganesh Budhathoki
– Ganesh Budhathoki, Commented Jan 11, 2022 at 20:36

v.ladynev · Accepted Answer · 2022-01-13 12:12:47Z

5

You have to use aliases in your select clause

SELECT e.first_name as firstName, e.last_name as lastName

They correspond getFirstName() and getLastName() methods names in the IdsOnly interface

public interface IdsOnly {
    String getFirstName();
    String getLastName();
}

answered Jan 13, 2022 at 12:12

v.ladynev

20.1k9 gold badges50 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anurag Bhalekar Over a year ago

note the camel case naming.

Collectives™ on Stack Overflow

Get result from native query using Java Interface

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related