특정 열을 선택하는 Spring JPA

특정 열을 선택하는 Spring JPA

---

Spring JPA를 사용하여 모든 데이터베이스 작업을 수행하고 있습니다. 그러나 Spring JPA의 테이블에서 특정 열을 선택하는 방법을 모르겠습니다.

예를 들면 다음과 같습니다.
SELECT projectId, projectName FROM projects

---

다음 과 같은 클래스 nativeQuery = true에서 @Query주석을 설정할 수 있습니다 Repository.

public static final String FIND_PROJECTS = "SELECT projectId, projectName FROM projects";

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

그래도 맵핑을 직접 수행해야합니다. 실제로 두 값만 필요하지 않으면 다음과 같이 정규 매핑 조회를 사용하는 것이 더 쉽습니다.

public List<Project> findAll()

Spring 데이터 문서 도 살펴볼 가치가 있습니다.

---

Spring Data JPA (doc)의 투영을 사용할 수 있습니다 . 귀하의 경우 인터페이스를 작성하십시오.

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

저장소에 다음 방법을 추가하십시오.

List<ProjectIdAndName> findAll();

---

나는 특히 문법이 마음에 들지 않습니다 (약간 해키처럼 보입니다 ...). 이것은 내가 찾은 가장 우아한 솔루션입니다 (JPA 저장소 클래스에서 사용자 정의 JPQL 쿼리를 사용합니다).

@Query("select new com.foo.bar.entity.Document(d.docId, d.filename) from Document d where d.filterCol = ?1")
List<Document> findDocumentsForListing(String filterValue);

그런 다음 물론 & 생성자 인수 Document를 허용 하는 생성자를 제공하면 됩니다.docIdfilename

---

필자의 경우 필자는 필요하지 않은 필드 (필수 필드 만)없이 별도의 엔티티 클래스를 만들었습니다.

엔티티를 동일한 테이블에 맵핑하십시오. 이제 모든 열이 필요할 때 이전 엔터티를 사용하고 일부 열만 필요한 경우 lite 엔터티를 사용합니다.

예 :

@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;
}

다음과 같은 것을 만들 수 있습니다.

@Entity
@Table(name = "user")
Class UserLite{
         @Column(name = "id", unique=true, nullable=false)
         int id;
         @Column(name = "name", nullable=false)
         String name;
}

가져올 열을 알고있을 때 작동합니다 (변경되지 않음).

열을 동적으로 결정 해야하는 경우 작동하지 않습니다.

---

내 상황에서는 json 결과 만 필요하며 이것은 나를 위해 작동합니다.

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

---

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

---

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

---

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

---

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.

---

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

---

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

---

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

---

Using Native Query:

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

---

Most of the answers here suggest using some variation of native SQL query. However, using inbuilt spring-data jpa also we can achieve it:

You just have to use the following method signature in Repository class.

ModelClass findBy$Column_1And$Column_2In(Object $col1Value, Object $col2Value );

Depending on the schema it could return a list or a single instance. This approach can be applied to single or multiple columns as shown above.

For your example it could be something like:

Project findByProjectIdAndProjectNameIn(long projectId, String projectName);

projectId, projectName

---

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

참고URL : https://stackoverflow.com/questions/22007341/spring-jpa-selecting-specific-columns