Spring JPA selecting specific columns

Question

I am using Spring JPA to perform all database operations  However I don t know how to select specific columns from a table in Spring JPA   For example  SELECT projectId  projectName FROM projects

User · Answer

You can use projections from Spring Data JPA (doc). In your case, create interface:

interface ProjectIdAndName{
    String getId();
    String getName();
}

and add following method to your repository

List<ProjectIdAndName> findAll();

User · Answer

With the newer Spring versions One can do as follows   If not using native query this can done as below   public interface ProjectMini       String getProjectId        String getProjectName       public interface ProjectRepository extends JpaRepository lt Project  String gt          Query  SELECT p FROM Project p       List lt ProjectMini gt  findAllProjectsMini        Using native query the same can be done as below   public interface ProjectRepository extends JpaRepository lt Project  String gt          Query value    SELECT projectId  projectName FROM project   nativeQuery   true      List lt ProjectMini gt  findAllProjectsMini        For detail check the docs

User · Answer

I guess the easy way may be is using QueryDSL  that comes with the Spring-Data   Using to your question the answer can be  JPAQuery query   new JPAQuery entityManager   List lt Tuple gt  result   query from projects  list project projectId  project projectName   for  Tuple row   result     System out println  project ID     row get project projectId     System out println  project Name     row get project projectName          The entity manager can be Autowired and you always will work with object and clases without use  QL language   As you can see in the link the last choice seems  almost for me  more elegant  that is  using DTO for store the result  Apply to your example that will be   JPAQuery query   new JPAQuery entityManager   QProject project   QProject project  List lt ProjectDTO gt  dtos   query from project  list new QProjectDTO project projectId  project projectName      Defining ProjectDTO as   class ProjectDTO     private long id   private String name    QueryProjection  public ProjectDTO long projectId  String projectName      this id   projectId     this name   projectName      public String getProjectId           public String getProjectName

User · Answer

It is possible to specify null as field value in native sql    Query value    select p id  p uid  p title  null as documentation  p ptype                   from projects p                  where p uid     uid                    and p ptype    P    nativeQuery   true  Project findInfoByUid  Param  uid   String uid

User · Answer

I don t like the syntax particularly  it looks a little bit hacky     but this is the most elegant solution I was able to find  it uses a custom JPQL query in the JPA repository class     Query  select new com foo bar entity Document d docId  d filename  from Document d where d filterCol    1   List lt Document gt  findDocumentsForListing String filterValue     Then of course  you just have to provide a constructor for Document that accepts docId  amp  filename as constructor args

User · Answer

Using Native Query   Query query   entityManager createNativeQuery  SELECT projectId  projectName FROM projects    List result   query getResultList

User · Answer

You can apply the below code in your repository interface class      entityname means your database table name like projects    And List means Project is Entity class in your Projects     Query value  select p from    entityName  p where p id  projectId and p projectName  projectName    List lt Project gt  findAll  Param  projectId   int projectId   Param  projectName   String projectName

User · Answer

In my situation  I only need the json result  and this works for me   public interface SchoolRepository extends JpaRepository lt School Integer gt         Query  select s id  s name from School s       List lt Object gt  getSchoolIdAndName        in Controller    Autowired private SchoolRepository schoolRepository    ResponseBody  RequestMapping  getschoolidandname do   public List lt Object gt  getSchool         List lt Object gt  schools   schoolRepository getSchoolIdAndName        return schools

User · Answer

Using Spring Data JPA there is a provision to select specific columns from database  ---- In DAOImpl ----   Override      Transactional     public List lt Employee gt  getAllEmployee   throws Exception       LOGGER info  Inside getAllEmployee        List lt Employee gt  empList   empRepo getNameAndCityOnly        return empList          ---- In Repo ----  public interface EmployeeRepository extends CrudRepository lt Employee Integer gt         Query  select e name  e city from Employee e        List lt Employee gt  getNameAndCityOnly        It worked 100  in my case  Thanks

User · Answer

You can use the answer suggested by  jombie  and    place the interface in a separate file  outside the entity class  use native query or not  the choice depended on your needs   don t override findAll   method for this purpose but use name of your choice  remember to return a List parametrized with your new interface  e g  List lt SmallProject gt

User · Answer

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 lt Object   gt  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 lt Project gt  findAll     It s probably worth looking at the Spring data docs as well

User · Answer

In my case i created a separate entity class without the fields that are not required  only with the fields that are required    Map the entity to the same table  Now when all the columns are required i use the old entity  when only some columns are required  i use the lite entity   e g     Entity  Table name    user   Class User            Column name    id   unique true  nullable false           int id            Column name    name   nullable false           String name            Column name    address   nullable false           Address address      You can create something like     Entity  Table name    user   Class UserLite            Column name    id   unique true  nullable false           int id            Column name    name   nullable false           String name      This works when you know the columns to fetch  and this is not going to change    won t work if you need to dynamically decide the columns

User · Answer

In my opinion this is great solution   interface PersonRepository extends Repository lt Person  UUID gt          lt T gt  Collection lt T gt  findByLastname String lastname  Class lt T gt  type       and using it like so  void someMethod PersonRepository people       Collection lt Person gt  aggregates       people findByLastname  Matthews   Person class      Collection lt NamesOnly gt  aggregates       people findByLastname  Matthews   NamesOnly class

User · Answer

You can use JPQL    TypedQuery  lt Object   gt  query   em createQuery     SELECT p projectId  p projectName FROM projects AS p   Object   class    List lt Object   gt  results   query getResultList      or you can use native sql query   Query query   em createNativeQuery  sql statement    List lt Object   gt  results   query getResultList

[java] Spring JPA selecting specific columns

Examples related to java

Examples related to jpa

Examples related to spring-data-jpa