Hibernate Criteria Query to get specific columns

Question

I am using Criteria Query in my code. It always fires select * from ...

Instead I want to neglect one column(field) from my query as that field have large number of data stored in bytes. And that causing performance issue.

Can any one give an idea for that?

---

Some Update

I added a projection in my query and it created a query like...

select
    this_.TEMPLATE_ID as y0_,
    this_.TEMPLATE_NAME as y1_,
    this_.CREATE_DATE as y2_,
    this_.UPDATE_DATE as y3_,
    this_.STATUS_CODE as y4_,
    this_.USER_ID as y5_,
    this_.UPDATED_BY as y6_,
    this_.CATEGORY_ID as y7_,
    this_.PRACTICE_ID as y8_ 
from
    templates this_ 
inner join
    user user1_ 
        on this_.USER_ID=user1_.USER_ID 
inner join
    template_categories category2_ 
        on this_.CATEGORY_ID=category2_.CATEGORY_ID 
where
    y4_=? 
    and y8_=? 
    and y5_ in (
        ?, ?
    ) 
order by
    y1_ asc limit ?

And now issue is like.. Unknown column 'y4_' in 'where clause' and same error for y8_ , y5_ means for all where close it gave an error.

I modified it to Query like ...

select
    this_.TEMPLATE_ID as y0_,
    this_.TEMPLATE_NAME as y1_,
    this_.CREATE_DATE as y2_,
    this_.UPDATE_DATE as y3_,
    this_.STATUS_CODE as y4_,
    this_.USER_ID as y5_,
    this_.UPDATED_BY as y6_,
    this_.CATEGORY_ID as y7_,
    this_.PRACTICE_ID as y8_ 
from
    templates this_ 
inner join
    user user1_ 
        on this_.USER_ID=user1_.USER_ID 
inner join
    template_categories category2_ 
        on this_.CATEGORY_ID=category2_.CATEGORY_ID 
where
    this_.STATUS_CODE=1
    and this_.PRACTICE_ID=1 
    and this_.USER_ID in (
        1, 2
    ) 
order by
    y1_ asc limit ?

and it worked. But I don't know how to modify it in HQL?

Answer 1

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();

Answer 2

You can use JPQL as well as JPA Criteria API for any kind of DTO projection(Mapping only selected columns to a DTO class) . Look at below code snippets showing how to selectively select various columns instead of selecting all columns . These example also show how to select various columns from joining multiple columns . I hope this helps .

JPQL code :

String dtoProjection = "new com.katariasoft.technologies.jpaHibernate.college.data.dto.InstructorDto"
                + "(i.id, i.name, i.fatherName, i.address, id.proofNo, "
                + " v.vehicleNumber, v.vechicleType, s.name, s.fatherName, "
                + " si.name, sv.vehicleNumber , svd.name) ";

        List<InstructorDto> instructors = queryExecutor.fetchListForJpqlQuery(
                "select " + dtoProjection + " from Instructor i " + " join i.idProof id " + " join i.vehicles v "
                        + " join i.students s " + " join s.instructors si " + " join s.vehicles sv "
                        + " join sv.documents svd " + " where i.id > :id and svd.name in (:names) "
                        + " order by i.id , id.proofNo , v.vehicleNumber , si.name , sv.vehicleNumber , svd.name ",
                CollectionUtils.mapOf("id", 2, "names", Arrays.asList("1", "2")), InstructorDto.class);

        if (Objects.nonNull(instructors))
            instructors.forEach(i -> i.setName("Latest Update"));

        DataPrinters.listDataPrinter.accept(instructors);

JPA Criteria API code :

@Test
    public void fetchFullDataWithCriteria() {
        CriteriaBuilder cb = criteriaUtils.criteriaBuilder();
        CriteriaQuery<InstructorDto> cq = cb.createQuery(InstructorDto.class);

        // prepare from expressions
        Root<Instructor> root = cq.from(Instructor.class);
        Join<Instructor, IdProof> insIdProofJoin = root.join(Instructor_.idProof);
        Join<Instructor, Vehicle> insVehicleJoin = root.join(Instructor_.vehicles);
        Join<Instructor, Student> insStudentJoin = root.join(Instructor_.students);
        Join<Student, Instructor> studentInsJoin = insStudentJoin.join(Student_.instructors);
        Join<Student, Vehicle> studentVehicleJoin = insStudentJoin.join(Student_.vehicles);
        Join<Vehicle, Document> vehicleDocumentJoin = studentVehicleJoin.join(Vehicle_.documents);

        // prepare select expressions.
        CompoundSelection<InstructorDto> selection = cb.construct(InstructorDto.class, root.get(Instructor_.id),
                root.get(Instructor_.name), root.get(Instructor_.fatherName), root.get(Instructor_.address),
                insIdProofJoin.get(IdProof_.proofNo), insVehicleJoin.get(Vehicle_.vehicleNumber),
                insVehicleJoin.get(Vehicle_.vechicleType), insStudentJoin.get(Student_.name),
                insStudentJoin.get(Student_.fatherName), studentInsJoin.get(Instructor_.name),
                studentVehicleJoin.get(Vehicle_.vehicleNumber), vehicleDocumentJoin.get(Document_.name));

        // prepare where expressions.
        Predicate instructorIdGreaterThan = cb.greaterThan(root.get(Instructor_.id), 2);
        Predicate documentNameIn = cb.in(vehicleDocumentJoin.get(Document_.name)).value("1").value("2");
        Predicate where = cb.and(instructorIdGreaterThan, documentNameIn);

        // prepare orderBy expressions.
        List<Order> orderBy = Arrays.asList(cb.asc(root.get(Instructor_.id)),
                cb.asc(insIdProofJoin.get(IdProof_.proofNo)), cb.asc(insVehicleJoin.get(Vehicle_.vehicleNumber)),
                cb.asc(studentInsJoin.get(Instructor_.name)), cb.asc(studentVehicleJoin.get(Vehicle_.vehicleNumber)),
                cb.asc(vehicleDocumentJoin.get(Document_.name)));

        // prepare query
        cq.select(selection).where(where).orderBy(orderBy);
        DataPrinters.listDataPrinter.accept(queryExecutor.fetchListForCriteriaQuery(cq));

    }

Answer 3

I like this approach because it is simple and clean:

    String getCompaniesIdAndName = " select "
            + " c.id as id, "
            + " c.name as name "
            + " from Company c ";

    @Query(value = getCompaniesWithoutAccount)
    Set<CompanyIdAndName> findAllIdAndName();

    public static interface CompanyIdAndName extends DTO {
        Integer getId();

        String getName();

    }