CODEWITHSUNDEEP
javajpajpa-2.0jpa-2.1criteriaquery
Edy Bourne
Edy Bourne

Reputation: 6187

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?

Upvotes: 64

Views: 185950

Answers (7)

Wais Kamal
Wais Kamal

Reputation: 6180

I was facing the same problem. I had a users table with an id column (the primary key) and a name column. I wanted to get the name field of all records, so I tried both findName and findNames but both did not work.

My solution was to create an interface with only a getName() method in the entity class:

@Entity
@Getter
@Setter
@NoArgsConstructor
@Accessors(chain = true)
@Table(name = "users")
public class User {
    @Id
    private String id;
    private String name;
    ...

    public interface UserName {
        public String getName();
    }
}

Then use "id not null" as the filter in my JPA repository:

public interface UserRepository extends JpaRepository<User, Integer> {
    ...
    User.UserName findNameByIdNotNull();
}

Upvotes: 0

Patrick Leitermann
Patrick Leitermann

Reputation: 2164

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.

Upvotes: 106

pL4Gu33
pL4Gu33

Reputation: 2085

You can use something like this:

List<Object[]> list = em.createQuery("SELECT p.field1, p.field2 FROM Entity p").getResultList();

then you can iterate over it:

for (Object[] obj : list){
    System.out.println(obj[0]);
    System.out.println(obj[1]);
}

BUT if you have only one field in query, you get a list of the type not from Object[]

Upvotes: 26

Abdullah Mohammad Motiullah
Abdullah Mohammad Motiullah

Reputation: 12542

Projections can be used to select only specific properties(columns) of an entity object.

From the docs

Spring Data Repositories usually return the domain model when using query methods. However, sometimes, you may need to alter the view of that model for various reasons. In this section, you will learn how to define projections to serve up simplified and reduced views of resources.

Define an interface with only the getters you want.

interface CustomObject {  
    String getA(); // Actual property name is A
    String getB(); // Actual property name is B 
}

Now return CustomObject from your repository like so :

public interface YOU_REPOSITORY_NAME extends JpaRepository<YOUR_ENTITY, Long> {
    CustomObject findByObjectName(String name);
}

Upvotes: 15

sagneta
sagneta

Reputation: 1691

Excellent answer! I do have a small addition. Regarding this solution:

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

To prevent a class not found error simply insert the full package name. Assuming org.company.directory is the package name of CustomObject:

String query = "SELECT NEW org.company.directory.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();

Upvotes: 8

ez121sl
ez121sl

Reputation: 2429

Yes, it is possible. All you have to do is change your query to something like SELECT i.foo, i.bar FROM ObjectName i WHERE i.id = 10. The result of the query will be a List of array of Object. The first element in each array is the value of i.foo and the second element is the value i.bar. See the relevant section of JPQL reference.

Upvotes: 0

SerhatCan
SerhatCan

Reputation: 590

I suppose you could look at this link if I understood your question correctly http://www.javacodegeeks.com/2012/07/ultimate-jpa-queries-and-tips-list-part_09.html

For example they created a query like:

 select id, name, age, a.id as ADDRESS_ID, houseNumber, streetName ' +
 20' from person p join address a on a.id = p.address_id where p.id = 1'

Upvotes: 0

Related Questions