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

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

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 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 lt InstructorDto gt  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  gt   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 - gt  i setName  Latest Update              DataPrinters listDataPrinter accept instructors     JPA Criteria API code      Test     public void fetchFullDataWithCriteria             CriteriaBuilder cb   criteriaUtils criteriaBuilder            CriteriaQuery lt InstructorDto gt  cq   cb createQuery InstructorDto class               prepare from expressions         Root lt Instructor gt  root   cq from Instructor class           Join lt Instructor  IdProof gt  insIdProofJoin   root join Instructor  idProof           Join lt Instructor  Vehicle gt  insVehicleJoin   root join Instructor  vehicles           Join lt Instructor  Student gt  insStudentJoin   root join Instructor  students           Join lt Student  Instructor gt  studentInsJoin   insStudentJoin join Student  instructors           Join lt Student  Vehicle gt  studentVehicleJoin   insStudentJoin join Student  vehicles           Join lt Vehicle  Document gt  vehicleDocumentJoin   studentVehicleJoin join Vehicle  documents               prepare select expressions          CompoundSelection lt InstructorDto gt  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 lt Order gt  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

[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