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

Answer 1

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

Answer 2

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<Document> findDocumentsForListing(String filterValue);

Then of course, you just have to provide a constructor for Document that accepts docId & filename as constructor args.

Answer 3

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<Object[]> 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<Project> findAll()

It's probably worth looking at the Spring data docs as well.

Answer 4

In my situation, I only need the json result, and this works for me:

public interface SchoolRepository extends JpaRepository<School,Integer> {
    @Query("select s.id, s.name from School s")
    List<Object> getSchoolIdAndName();
}

in Controller:

@Autowired
private SchoolRepository schoolRepository;

@ResponseBody
@RequestMapping("getschoolidandname.do")
public List<Object> getSchool() {
    List<Object> schools = schoolRepository.getSchoolIdAndName();
    return schools;
}

Answer 5

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<Project, String> { 
    @Query("SELECT p FROM Project p")
    List<ProjectMini> findAllProjectsMini();
}

Using native query the same can be done as below:

public interface ProjectRepository extends JpaRepository<Project, String> { 
    @Query(value = "SELECT projectId, projectName FROM project", nativeQuery = true)
    List<ProjectMini> findAllProjectsMini();
}

For detail check the docs

Answer 6

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.

Answer 7

In my opinion this is great solution:

interface PersonRepository extends Repository<Person, UUID> {

    <T> Collection<T> findByLastname(String lastname, Class<T> type);
}

and using it like so

void someMethod(PersonRepository people) {

  Collection<Person> aggregates =
    people.findByLastname("Matthews", Person.class);

  Collection<NamesOnly> aggregates =
    people.findByLastname("Matthews", NamesOnly.class);
}

Answer 8

Using Spring Data JPA there is a provision to select specific columns from database

---- In DAOImpl ----

@Override
    @Transactional
    public List<Employee> getAllEmployee() throws Exception {
    LOGGER.info("Inside getAllEmployee");
    List<Employee> empList = empRepo.getNameAndCityOnly();
    return empList;
    }

---- In Repo ----

public interface EmployeeRepository extends CrudRepository<Employee,Integer> {
    @Query("select e.name, e.city from Employee e" )
    List<Employee> getNameAndCityOnly();
}

It worked 100% in my case. Thanks.

Answer 9

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<Tuple> 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<ProjectDTO> 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(){....}
}

Answer 10

You can use JPQL:

TypedQuery <Object[]> query = em.createQuery(
  "SELECT p.projectId, p.projectName FROM projects AS p", Object[].class);

List<Object[]> results = query.getResultList();

or you can use native sql query.

Query query = em.createNativeQuery("sql statement");
List<Object[]> results = query.getResultList();

Answer 11

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

Answer 12

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<Project> findAll(@Param("projectId") int projectId, @Param("projectName") String projectName);

Answer 13

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<SmallProject>).

Answer 14

public static final String FIND_PROJECTS = "select ac_year_id,ac_year from tbl_au_academic_year where ac_year_id=?1";

    @Query(value = FIND_PROJECTS, nativeQuery = true)
    public  List<Object[]> findByAcYearId(Integer ac_year_id);

this works for me

Answer 15

You can update your JPARepository as below.

@Query("select u.status from UserLogin u where u.userId = ?1 or u.email = ?1 or u.mobile = ?1")
public UserStatus findByUserIdOrEmailOrMobile(String loginId);

Where UserStatus is a Enum

public enum UserStatus
{
    New,
    Active,
    Deactived,
    Suspended,
    Locked
}

Answer 16

You can use a DTO like that

 @Data
    public class UserDtoLight implements Serializable {
       private final Long id;
       private final String name;
    }

and in your repository

 List<UserDtoLight> findAll();

Answer 17

With current version I'm using (JDK 11, Spring Boot 3) It's very simple:

1. You need to add a constructor in your entity class for limited fields which you want to select:

    class Projects {
        private String projectId;
        private String projectName;
        // other fields
    
        // No argument / all arguments  constructor as needed
    
        public Projects(String projectId, String projectName) {
            this.projectId = projectId;
            this.projectName = projectName;
        }
    }

2. In your Repository, you can add Query & method as below (add WHERE condition as needed):

    @Query("SELECT new Projects (p.projectId, p.projectName) FROM Projects p")
    List<Projects > findAllProjects();

For me, this works & I get list of all objects with only 2 fields specified.

Answer 18

Using Native Query:

Query query = entityManager.createNativeQuery("SELECT projectId, projectName FROM projects");
List result = query.getResultList();

Answer 19

Use:

@Query("SELECT e FROM #{#entityName} e where e.userId=:uid")
List<ClienteEnderecoEntity> findInfoByUid(@Param("uid") UUID uid);

• hibernate - Native Query