Java Spring JPA 选择特定列

Spring JPA selecting specific columns

提问by user1817436

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?

我正在使用 Spring JPA 来执行所有数据库操作。但是我不知道如何从 Spring JPA 的表中选择特定列？

For example:
SELECT projectId, projectName FROM projects

例如：
SELECT projectId, projectName FROM projects

采纳答案by Durandal

You can set nativeQuery = truein the @Queryannotation from a Repositoryclass like this:

您可以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();

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 docsas well.

也可能值得查看 Spring 数据文档。

回答by Henrik

You can use JPQL:

您可以使用 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.

或者您可以使用本机 sql 查询。

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

回答by kszosze

I guess the easy way may be is using QueryDSL, that comes with the Spring-Data.

我想最简单的方法可能是使用Spring-Data 附带的QueryDSL。

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.

实体管理器可以自动装配，您将始终使用对象和类而不使用 *QL 语言。

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:

正如您在链接中看到的那样，最后一个选择似乎对我来说更优雅，即使用 DTO 存储结果。适用于您的示例：

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:

将 ProjectDTO 定义为：

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

回答by jm0

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

我特别不喜欢语法（它看起来有点老套......）但这是我能找到的最优雅的解决方案（它在 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);

Then of course, you just have to provide a constructor for Documentthat accepts docId& filenameas constructor args.

然后，当然，您只需要提供一个构造函数来Document接受docId&filename作为构造函数参数。

回答by Atal

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

在我的情况下，我只需要 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;
}

回答by mpr

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

您可以使用 Spring Data JPA (doc) 中的投影。在您的情况下，创建接口：

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

and add following method to your repository

并将以下方法添加到您的存储库

List<ProjectIdAndName> findAll();

回答by Sachin Sharma

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.

将实体映射到同一个表。现在，当需要所有列时，我使用旧实体，当只需要某些列时，我使用 lite 实体。

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.

如果您需要动态决定列，则不起作用。

回答by SR Ranjan

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

使用 Spring Data JPA 可以从数据库中选择特定的列

---- In DAOImpl ----

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

就我而言，它 100% 有效。谢谢。

回答by hahn

It is possible to specify nullas field value in native sql.

可以null在本机 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);

回答by ajaz

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.

entityname 表示您的数据库表名称，如项目。List 表示 Project 是项目中的实体类。

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