JPA Query selecting only specific columns without using Criteria Query

Question

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

User · Answer

You can use something like this   List lt Object   gt  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

User · Answer

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

User · Answer

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 lt CustomObject gt  typedQuery   em createQuery query   CustomObject class   List lt CustomObject gt  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

User · Answer

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 lt YOUR ENTITY  Long gt        CustomObject findByObjectName String name

User · Answer

Excellent answer  I do have a small addition  Regarding this solution   TypedQuery lt CustomObject gt  typedQuery   em createQuery query   String query    SELECT NEW CustomObject i firstProperty  i secondProperty  FROM ObjectName i WHERE i id 100   TypedQuery lt CustomObject gt  typedQuery   em createQuery query   CustomObject class   List lt CustomObject gt  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 lt CustomObject gt  typedQuery   em createQuery query   CustomObject class   List lt CustomObject gt  results   typedQuery getResultList

User · Answer

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

[java] JPA Query selecting only specific columns without using Criteria Query?

Examples related to java

Examples related to jpa

Examples related to jpa-2.0

Examples related to jpa-2.1

Examples related to criteriaquery