Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA Query selecting only specific columns without using Criteria Query?

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?

like image 591
Edy Bourne Avatar asked Jul 12 '14 07:07

Edy Bourne


People also ask

How do you select specific columns in criteria query?

One of the JPA ways for getting only particular columns is to ask for a Tuple object. then you can use T directly in your CriteriaQuery constructor: CriteriaQuery<T> cq = builder. createQuery(T.

What are three methods to execute queries in JPA?

There are three basic types of JPA Queries:Query, written in Java Persistence Query Language (JPQL) syntax. NativeQuery, written in plain SQL syntax. Criteria API Query, constructed programmatically via different methods.

What is criteria query multiselect?

CriteriaQuery. multiselect. This method takes one or more Selection items as parameters. The parameters specify the result returned by the Criteria Query. The multiselect method can be used to select a single entity, single or multiple values of the same entity or of different entities.

What is Criteria query in JPA?

The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax.


1 Answers

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.

like image 63
Patrick Leitermann Avatar answered Sep 21 '22 16:09

Patrick Leitermann