JPA & Criteria API - Select Only Specific Columns

JPA & Criteria API - Select only specific columns

One of the JPA ways for getting only particular columns is to ask for a Tuple object.

In your case you would need to write something like this:

CriteriaQuery<Tuple> cq = builder.createTupleQuery();
// write the Root, Path elements as usual
Root<EntityClazz> root = cq.from(EntityClazz.class);
cq.multiselect(root.get(EntityClazz_.ID), root.get(EntityClazz_.VERSION));  //using metamodel
List<Tuple> tupleResult = em.createQuery(cq).getResultList();
for (Tuple t : tupleResult) {
    Long id = (Long) t.get(0);
    Long version = (Long) t.get(1);
}

Another approach is possible if you have a class representing the result, like T in your case. T doesn't need to be an Entity class. If T has a constructor like:

public T(Long id, Long version)

then you can use T directly in your CriteriaQuery constructor:

CriteriaQuery<T> cq = builder.createQuery(T.class);
// write the Root, Path elements as usual
Root<EntityClazz> root = cq.from(EntityClazz.class);
cq.multiselect(root.get(EntityClazz_.ID), root.get(EntityClazz_.VERSION));  //using metamodel
List<T> result = em.createQuery(cq).getResultList();

See this link for further reference.

Spring boot JPA & Criteria API - Select single column

in CustomRepositoryMethod replace first
line CriteriaQuery<Comment> cq = cb.createQuery(Comment.class); to CriteriaQuery<Note> cq = cb.createQuery(Note.class)

cb.createQuery parameter accept result Class in docs you can see.

update

// assuming query like
// select oComment from comment inner join Note on comment.noteuuid=Note.noteuuid where Note.noteUuid = 1 and version > 0;

CriteriaBuilder cb = em.getCriteriaBuilder();
// data type of oComment
CriteriaQuery<Note> query = cb.createQuery(Note.class);
// from comment
Root<Comment> comment = query.from(Comment.class);

//join
Join<Comment, Note> note = comment.join(comment.get("oNote"));

//version Condition
Predicate version=cb.greaterThan(comment.get("version"),0 );

//Note condition
predicate note=cb.equal(note.get("noteuuid"),note.getNoteUuid());

// get oComment and where condtion 
query.select(comment.get("oComment")).where(cb.and(version,note));

    return  em.createQuery(query).setFirstResult(iOffset).setMaxResults(iResultSize).getResultList();

Selecting specific columns in jpa 2 Criteria API?

Yes, it does. The select() method is what you need to use. From the openJPA manual:

The select() method defines the result of the query. If left unspecified, the select projection is assumed to be the root domain object. However, you can specify the selected projections explicitly as a list: qdef.select(customer.get(Customer_.name), order.get(Order_.status));

JPA Query selecting only specific columns without using Criteria Query?

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.

Hibernate Criteria Query to get specific columns

Use Projections to specify which columns you would like to return.

Example

SQL Query

SELECT user.id, user.name FROM user;

Hibernate Alternative

Criteria cr = session.createCriteria(User.class)
    .setProjection(Projections.projectionList()
      .add(Projections.property("id"), "id")
      .add(Projections.property("Name"), "Name"))
    .setResultTransformer(Transformers.aliasToBean(User.class));

  List<User> list = cr.list();

Spring JPA selecting specific columns

You can set nativeQuery = true in the @Query annotation from a Repository class like this:

public static final String FIND_PROJECTS = "SELECT projectId, projectName FROM projects";

@Query(value = FIND_PROJECTS, nativeQuery = true)
public List<Object[]> findProjects();

Note that you will have to do the mapping yourself though. It's probably easier to just use the regular mapped lookup like this unless you really only need those two values:

public List<Project> findAll()

It's probably worth looking at the Spring data docs as well.

Java Criteria API: Select single column from one-to-many relationship table

I found the problem:

filters.add(builder.equal(root.join("StockItem").get("id"), vendorNumber));

I was joining based on the StockItem id and not the StockItem.itemNumber

I used two queries to solve the issue of joining the Itemvalues map (it was returning 32,000+ results)

Hibernate Criteria Query to get only specific columns

I have problem because when I setResultTransformer it returns objects
with null variables. Example. getId() is null (I added
projectionList.add(Projections.property("id"));)

You need to specify an alias of the projection

projectionList.add(Projections.property("id").as("id"));

Yes, it worked. Thanks. I have one more question. If I want return
also in this query entity Report and columns value0, value1 what I
have to do?

You can get a part of Report by specifying projections for columns of it. You need to get ReportRows as root and properties of Report and DataRow using nested projections.

For nested projections you can use a custom transformer

How to transform a flat result set using Hibernate

---

Related Topics