blocks|key|1893985|text|您可以在Repository类的@Query注释中设置nativeQuery+=+true，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1893986|public+static+final+String+FIND_PROJECTS+=+"SELECT+projectId,+projectName+FROM+projects";

@Query(value+=+FIND_PROJECTS,+nativeQuery+=+true)
public+List<Object[]>+findProjects();|code-block|syntax|javascript|1893987|请注意，您必须自己进行映射。只使用像这样的常规映射查找可能更简单，除非您真的只需要这两个值：|1893988|public+List<Project>+findAll()|1893989|同样值得一看的是Spring+data+docs。|1893990|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query^0|4|A|G|6|R|I|0|0|0|0|K|4|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|15|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|Q|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]]]

You can set <code>nativeQuery = true</code> in the <code>@Query</code> annotation from a <code>Repository</code> class like this:

<pre><code>public static final String FIND_PROJECTS = "SELECT projectId, projectName FROM projects";

@Query(value = FIND_PROJECTS, nativeQuery = true)
public List&lt;Object[]&gt; findProjects();
</code></pre>

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:

<pre><code>public List&lt;Project&gt; findAll()
</code></pre>

It's probably worth looking at the Spring data <a href="http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query" rel="noreferrer">docs</a> as well.

blocks|key|1894056|text|您可以使用来自Spring+Data+JPA+(doc)的投影。在您的示例中，创建接口：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1894057|interface+ProjectIdAndName{
++++String+getId();
++++String+getName();
}|code-block|syntax|javascript|1894058|并将以下方法添加到您的存储库|1894059|List<ProjectIdAndName>+findAll();|1894060|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections^0|N|5|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

You can use projections from Spring Data JPA <a href="http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections" rel="noreferrer">(doc)</a>. In your case, create interface:

<pre><code>interface ProjectIdAndName{
 String getId();
 String getName();
}
</code></pre>

and add following method to your repository

<pre><code>List&lt;ProjectIdAndName&gt; findAll();
</code></pre>

blocks|key|1680188|text|我不太喜欢它的语法(它看起来有点老套……)但这是我所能找到的最优雅的解决方案(它在JPA仓库类中使用了自定义的JPQL查询)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1680189|@Query("select+new+com.foo.bar.entity.Document(d.docId,+d.filename)+from+Document+d+where+d.filterCol+=+?1")
List<Document>+findDocumentsForListing(String+filterValue);|code-block|syntax|javascript|1680190|当然，您只需为Document提供一个构造函数，该构造函数接受docId+&+filename作为构造函数参数。|offset|length|style|CODE|1680191|entityMap^0|0|0|7|8|V|5|13|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>@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);
</code></pre>

Then of course, you just have to provide a constructor for <code>Document</code> that accepts <code>docId</code> &amp; <code>filename</code> as constructor args.

blocks|key|1680198|text|在我的情况下，我只需要json结果，这对我有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1680199|public+interface+SchoolRepository+extends+JpaRepository<School,Integer>+{
++++@Query("select+s.id,+s.name+from+School+s")
++++List<Object>+getSchoolIdAndName();
}|code-block|syntax|javascript|1680200|在控制器中：|1680201|@Autowired
private+SchoolRepository+schoolRepository;

@ResponseBody
@RequestMapping("getschoolidandname.do")
public+List<Object>+getSchool()+{
++++List<Object>+schools+=+schoolRepository.getSchoolIdAndName();
++++return+schools;
}|1680202|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

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

in Controller:

<pre><code>@Autowired
private SchoolRepository schoolRepository;

@ResponseBody
@RequestMapping("getschoolidandname.do")
public List&lt;Object&gt; getSchool() {
 List&lt;Object&gt; schools = schoolRepository.getSchoolIdAndName();
 return schools;
}
</code></pre>

blocks|key|1894071|text|在我的例子中，我创建了一个单独的实体类，不包含不需要的字段(只包含需要的字段)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1894072|将实体映射到同一个表。现在，当需要所有列时，我使用旧的实体，当只需要一些列时，我使用lite实体。|1894073|例如：|1894074|@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;
}|code-block|syntax|javascript|1894075|您可以创建类似如下的内容：|1894076|@Entity
@Table(name+=+"user")
Class+UserLite{
+++++++++@Column(name+=+"id",+unique=true,+nullable=false)
+++++++++int+id;
+++++++++@Column(name+=+"name",+nullable=false)
+++++++++String+name;
}|1894077|当您知道要获取的列时，这是有效的(并且这不会改变)。|1894078|如果您需要动态决定列，则不起作用。|1894079|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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. 

<pre><code>@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;
}
</code></pre>

You can create something like :

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

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.

blocks|key|1894181|text|在我看来，这是一个很好的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1894182|interface+PersonRepository+extends+Repository<Person,+UUID>+{

++++<T>+Collection<T>+findByLastname(String+lastname,+Class<T>+type);
}|code-block|syntax|javascript|1894183|并像这样使用它|1894184|void+someMethod(PersonRepository+people)+{

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

++Collection<NamesOnly>+aggregates+=
++++people.findByLastname("Matthews",+NamesOnly.class);
}|1894185|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In my opinion this is great solution:

<pre><code>interface PersonRepository extends Repository&lt;Person, UUID&gt; {

 &lt;T&gt; Collection&lt;T&gt; findByLastname(String lastname, Class&lt;T&gt; type);
}
</code></pre>

and using it like so

<pre><code>void someMethod(PersonRepository people) {

 Collection&lt;Person&gt; aggregates =
 people.findByLastname("Matthews", Person.class);

 Collection&lt;NamesOnly&gt; aggregates =
 people.findByLastname("Matthews", NamesOnly.class);
}
</code></pre>

blocks|key|1680170|text|我想最简单的方法可能是使用QueryDSL，它随Spring-Data一起提供。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1680171|对于你的问题，答案可以是|1680172|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+"+%2B+row.get(project.projectId));
+System.out.println("project+Name+"+%2B+row.get(project.projectName));+
}}|code-block|syntax|javascript|1680173|实体管理器可以是自动连接的，您将始终使用对象和类，而无需使用*QL语言。|1680174|正如你在链接中看到的，对我来说，最后一个选择似乎更优雅，也就是使用DTO存储结果。应用于您的示例将是：|1680175|JPAQuery+query+=+new+JPAQuery(entityManager);
QProject+project+=+QProject.project;
List<ProjectDTO>+dtos+=+query.from(project).list(new+QProjectDTO(project.projectId,+project.projectName));|1680176|将ProjectDTO定义为：|1680177|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(){....}
}|1680178|entityMap|0|LINK|mutability|MUTABLE|url|http://www.querydsl.com/static/querydsl/3.4.2/reference/html/ch03s02.html^0|D|8|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|17|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|18|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|C|$]]|$1|O|3|P|5|H|7|1A|8|@]|9|@]|C|$I|J]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@]|C|$]]|$1|S|3|T|5|H|7|1C|8|@]|9|@]|C|$I|J]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]]]

I guess the easy way may be is using <a href="http://www.querydsl.com/static/querydsl/3.4.2/reference/html/ch03s02.html" rel="noreferrer">QueryDSL</a>, that comes with the Spring-Data.

Using to your question the answer can be

<pre><code>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)); 
}}
</code></pre>

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:

<pre><code>JPAQuery query = new JPAQuery(entityManager);
QProject project = QProject.project;
List&lt;ProjectDTO&gt; dtos = query.from(project).list(new QProjectDTO(project.projectId, project.projectName));
</code></pre>

Defining ProjectDTO as:

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

blocks|key|1894091|text|使用Spring+Data+JPA可以从数据库中选择特定的列|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1894092|-在DAOImpl中|1894093|@Override
++++@Transactional
++++public+List<Employee>+getAllEmployee()+throws+Exception+{
++++LOGGER.info("Inside+getAllEmployee");
++++List<Employee>+empList+=+empRepo.getNameAndCityOnly();
++++return+empList;
++++}|code-block|syntax|javascript|1894094|-在回购中|1894095|public+interface+EmployeeRepository+extends+CrudRepository<Employee,Integer>+{
++++@Query("select+e.name,+e.city+from+Employee+e"+)
++++List<Employee>+getNameAndCityOnly();
}|1894096|它在我的案例中100%25起作用。谢谢。|1894097|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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

---- In DAOImpl ----

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

---- In Repo ----

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

It worked 100% in my case.
Thanks.

blocks|key|1893944|text|您可以使用JPQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1893945|TypedQuery+<Object[]>+query+=+em.createQuery(
++"SELECT+p.projectId,+p.projectName+FROM+projects+AS+p",+Object[].class);

List<Object[]>+results+=+query.getResultList();|code-block|syntax|javascript|1893946|或者，您可以使用原生sql查询。|1893947|Query+query+=+em.createNativeQuery("sql+statement");
List<Object[]>+results+=+query.getResultList();|1893948|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can use JPQL: 

<pre><code>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();
</code></pre>

or you can use native sql query.

<pre><code>Query query = em.createNativeQuery("sql statement");
List&lt;Object[]&gt; results = query.getResultList();
</code></pre>

blocks|key|1680280|text|可以在原生sql中将null指定为字段值。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1680281|@Query(value+=+"select+p.id,+p.uid,+p.title,+null+as+documentation,+p.ptype+"+%2B
++++++++++++"+from+projects+p+"+%2B
++++++++++++"where+p.uid+=+(:uid)"+%2B
++++++++++++"++and+p.ptype+=+'P'",+nativeQuery+=+true)
Project+findInfoByUid(@Param("uid")+String+uid);|code-block|syntax|javascript|1680282|entityMap^0|A|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

It is possible to specify <code>null</code> as field value in native sql.

<pre><code>@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);
</code></pre>

blocks|key|1680403|text|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);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1680404|这对我很管用|unstyled|1680405|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>public static final String FIND_PROJECTS = &quot;select ac_year_id,ac_year from tbl_au_academic_year where ac_year_id=?1&quot;;

 @Query(value = FIND_PROJECTS, nativeQuery = true)
 public List&lt;Object[]&gt; findByAcYearId(Integer ac_year_id);
</code></pre>
this works for me

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: 
<code>SELECT projectId, projectName FROM projects</code>

Spring JPA selecting specific columns

数据库

我使用Spring JPA来执行所有的数据库操作。但是，我不知道如何在Spring JPA中从表中选择特定的列？例如：SELECT projectId, projectName FROM projects

问Spring JPA选择特定列
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Spring JPA选择特定列EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Spring JPA选择特定列
EN