JPA Query selecting only specific columns without using Criteria Query?

Edy Bourne picture Edy Bourne · Jul 12, 2014 · Viewed 123.6k times · Source

Is it possible to select, say, only properties A and B from an object using a JPA query without using criteria queries?

To select all properties I'd just do something like:

SELECT i FROM ObjectName i WHERE i.id = 10

But I have an object with many properties on a legacy system, and want to select just a few even though I'm aware selecting several properties is usually quick.

Is this possible without using criteria queries?

Thank you!

Answer

Patrick Leitermann picture Patrick Leitermann · Jul 12, 2014

Yes, like in plain sql you could specify what kind of properties you want to select:

SELECT i.firstProperty, i.secondProperty FROM ObjectName i WHERE i.id=10

Executing this query will return a list of Object[], where each array contains the selected properties of one object.

Another way is to wrap the selected properties in a custom object and execute it in a TypedQuery:

String query = "SELECT NEW CustomObject(i.firstProperty, i.secondProperty) FROM ObjectName i WHERE i.id=10";
TypedQuery<CustomObject> typedQuery = em.createQuery(query , CustomObject.class);
List<CustomObject> results = typedQuery.getResultList();

Examples can be found in this article.

UPDATE 29.03.2018:

@Krish:

@PatrickLeitermann for me its giving "Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate class ***" exception . how to solve this ?

I guess you’re using JPA in the context of a Spring application, don't you? Some other people had exactly the same problem and their solution was adding the fully qualified name (e. g. com.example.CustomObject) after the SELECT NEW keywords.

Maybe the internal implementation of the Spring data framework only recognizes classes annotated with @Entity or registered in a specific orm file by their simple name, which causes using this workaround.