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

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 2

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 3

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 4

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 5

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 6

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.