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

User · Answer

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

    }

User · Answer

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 lt CompanyIdAndName gt  findAllIdAndName         public static interface CompanyIdAndName extends DTO           Integer getId             String getName

User · Answer

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 lt User gt  list   cr list

User · Answer

You can use multiselect function for this     CriteriaBuilder cb session getCriteriaBuilder                CriteriaQuery lt Object   gt  cquery cb createQuery Object   class               Root lt Car gt  root cquery from User class               cquery multiselect root get  quot id quot   root get  quot Name quot                 Query lt Object   gt  q session createQuery cquery               List lt Object   gt  list q getResultList                System out println  quot id        Name quot                for  Object   objects   list                    System out println objects 0   quot          quot  objects 1                                 This is supported by hibernate 5  createCriteria is deprecated in further version of hibernate  So you can use criteria builder instead

User · Answer

You can map another entity based on this class  you should use entity-name in order to distinct the two  and the second one will be kind of dto  dont forget that dto has design issues     you should define the second one as readonly and give it a good name in order to be clear that this is not a regular entity   by the way select only few columns is called projection   so google with it will be easier   alternative - you can create named query with the list of fields that you need  you put them in the select   or use criteria with projection

[java] Hibernate Criteria Query to get specific columns

Examples related to java

Examples related to mysql

Examples related to hibernate

Examples related to hibernate-criteria