createNativeQuery to select specific Columns given in entity POJO

Question

I have a table called CUSTOMER which contains 100+ COLOUMNS. But I want to select only 6 columns which I specified in my POJO:

Entity POJO:

 @Entity @Data @Table(name = "CUSTOMER") public class CustomerEntity { @Id @Column(name = "C_ID") private String customerId; @Id @Column(name = "C_KEY") private String customerKey; @Column(name = "NAME") private String name; @Column(name = "FIRST_NAME") private String firstName; @Column(name = "LAST_NAME") private String lastName; @Column(name = "AGE") private String age; }

NativeQuery:

String query = "select * from CUSTOMER where (C_ID= '1' AND C_KEY= '12') OR (C_ID= '1' AND C_KEY= '13')) AND AGE>25"; Query q = e.createNativeQuery(query,CustomerEntity.class);

[Edit : For the reason to go with Native query]

Reason to choose Native Query :

I need to execute the below complex query in JPA.
In the complex query, I have subqueries, analytical method call. I assumed If I need to achieve this, then a native query will help.
The above native query I wrote is just to test the inner most query.

Complex Logic:

SELECT * FROM ( SELECT row_number() over(order by C_ID, C_KEY) RN, FEW-COLUMNS( SELECT * FROM BOOK WHERE (C_ID, C_KEY) IN (customerId1, customerKey1) (customerId2, customerKey2) (customerId3, customerKey3) ..... (customerIdn, customerKeyn) AND ROWNUM <= 340 )WHERE RN BETWEEN anyNumber and anyNumber )ORDER BY DESC RN;

Issues:

Since it is a namedQuery I am not able to pass the query as

String query = "select cu from CustomerEntity cu where ((cu.customerId = '1' AND cu.customerKey = '12') or (cu.customerId = '1' AND cu.customerKey = '13') AND cu.age > 25)";

If I use this query I am getting ORA -00947 Table or view doesn't exist Exception.

Is it possible to get only the specific columns?

The second query you wrote doesn't look ok. What is 'ct' in this query? what is 'cu' ? is that the exact query you use? — Emil Hotkowski
– Emil Hotkowski, Commented May 24, 2017 at 10:20
String query = "select ct from CustomerEntity ct where ((ct.customerId = '1' AND ct.customerKey = '12') or (ct.customerId = '1' AND ct.customerKey = '13') AND ct.age > 25)"; maybe like that — Emil Hotkowski
– Emil Hotkowski, Commented May 24, 2017 at 10:20
@Rjiuk, I meant to keep alias name(cu) for the Entity POJO. Now I edited the question. — Alagammal P
– Alagammal P, Commented May 24, 2017 at 10:25

Maciej Kowalski · Accepted Answer · 2017-05-24 10:24:45Z

5

You have to use aliases in order for this to work. Otherwise the persistence provider will not know how to perform the match:

select c_id as customerId, c_key as customerKey... from CUSTOMER where ...

Aliases should be exactly the same as corresponding field names of the entity class your mapping to.

answered May 24, 2017 at 10:24

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

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

8 Comments

Alagammal P Over a year ago

Do you mean I need to to use the select query with aliases for every column in the method createNativeQuery. (For the issue table or view does not exist)

Maciej Kowalski Over a year ago

yes, you plan to have only 6 columns in the select so its not much of hastle

Alagammal P Over a year ago

Ok. But in of my another entity I need to select more than 40 columns. In that case it will be a big query If I select each column name? Any alternative way to simplfy th query

Maciej Kowalski Over a year ago

how do expect to specify a subset of columns without doing it explicitly? Or maybe you want to select all the columns? Why use native query in that case then?

Maciej Kowalski Over a year ago

Native query will in general always be faster. You simply loose though all the orm benefits, including the database change rewrite. Your query though because of the complexity.. i would create a stored procedure out of it and use JPA API to call procedure. IF the db changes you wold only need to rewrtie the procedure without changing the java code / configuration.

|

Collectives™ on Stack Overflow

createNativeQuery to select specific Columns given in entity POJO

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related