Java JPA 查询创建顺序

JPA query creation order by

提问by Kuurde

I'm trying to learn Spring on my own, and I'm planning to do that by creating a blog web app. I already have the basic blog functionality working, which is a page to display blog posts, and a page with a form to submit one. The page to display the blog posts shows the latest blog post, and a list of the titles of all the blog posts in the db.

我正在尝试自己学习 Spring，我计划通过创建一个博客 Web 应用程序来实现这一点。我已经有了基本的博客功能，这是一个显示博客文章的页面，以及一个带有提交表单的页面。显示博客文章的页面显示最新的博客文章，以及数据库中所有博客文章的标题列表。

To get an ordered list of blog posts out of the database I first created an sql query inside my Repository interface. This works, but now I want to use the functionality where I can just type the method name in the interface, instead of hardcoded sql. I found the supported keywords here: http://docs.spring.io/spring-data/jpa/docs/1.4.2.RELEASE/reference/html/jpa.repositories.html#jpa.query-methods.query-creation, and tried to implement it.

为了从数据库中获取博客文章的有序列表，我首先在我的 Repository 界面中创建了一个 sql 查询。这有效，但现在我想使用我可以在界面中输入方法名称的功能，而不是硬编码的 sql。我在这里找到了支持的关键字：http: //docs.spring.io/spring-data/jpa/docs/1.4.2.RELEASE/reference/html/jpa.repositories.html#jpa.query-methods.query-creation，并试图实现它。

So with my method findAllOrderByIdDesc()I'm trying to achieve the same thing as with my sql query. I'm not sure why it doesn't work, I think I used the keywords correctly?

所以用我的方法findAllOrderByIdDesc()我试图实现与我的 sql 查询相同的事情。我不确定为什么它不起作用，我想我正确使用了关键字？

The stackstrace (which I don't fully understand):
Caused by: org.springframework.data.mapping.PropertyReferenceException: No property desc found for type int at org.springframework.data.mapping.PropertyPath.(PropertyPath.java:75) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:327) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:307) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:330) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:353) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:307) at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:271) at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:245) at org.springframework.data.repository.query.parser.Part.(Part.java:72) at org.springframework.data.repository.query.parser.PartTree$OrPart.(PartTree.java:188) at org.springframework.data.repository.query.parser.PartTree$Predicate.buildTree(PartTree.java:277) at org.springframework.data.repository.query.parser.PartTree$Predicate.(PartTree.java:257) at org.springframework.data.repository.query.parser.PartTree.(PartTree.java:71) at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.(PartTreeJpaQuery.java:57) at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:90) at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:162) at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44) at org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 more

堆栈跟踪（我不完全理解）：
引起：org.springframework.data.mapping.PropertyReferenceException：在 org.springframework.data.mapping.PropertyPath.(PropertyPath.java:75) at org.springframework.data.mapping.PropertyPath.create 处找不到类型 int 的属性 desc (PropertyPath.java:327) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:307) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:330) at org.springframework。 data.mapping.PropertyPath.create(PropertyPath.java:353) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:307) at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java: 271) 在 org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:245) 在 org.springframework.data.repository.query.parser。Part.(Part.java:72) at org.springframework.data.repository.query.parser.PartTree$OrPart.(PartTree.java:188) at org.springframework.data.repository.query.parser.PartTree$Predicate。 buildTree(PartTree.java:277) 在 org.springframework.data.repository.query.parser.PartTree$Predicate.(PartTree.java:257) 在 org.springframework.data.repository.query.parser.PartTree.(PartTree. java:71) 在 org.springframework.data.jpa.repository.query.PartTreeJpaQuery.(PartTreeJpaQuery.java:57) 在 org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:90) ) 在 org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:162) 在 org.springframework。data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework .repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core .support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68) 在 org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) 在 org.springframework.coredata.repository .support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport .getObject(RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68) 在 org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) 在 org.springframework.coredata.repository .support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport .getObject(RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.core.support.repositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) .getRepository(RepositoryFactorySupport.java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport) .java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:68) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.core.support.repositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) .getRepository(RepositoryFactorySupport.java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport) .java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多resolveQuery(JpaQueryLookupStrategy.java:68) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport) .java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44) ) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多resolveQuery(JpaQueryLookupStrategy.java:68) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport) .java:158) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44) ) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) at org.springframework.data.repository.core.support .RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromSupportBean(FactoryBeanRegistry) .java:144) ... 32 更多core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.(RepositoryFactorySupport.java:290) at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) at org.springframework.data.repository.core.support .RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) 在 org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromSupportBean(FactoryBeanRegistry) .java:144) ... 32RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject( RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:158) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject(RepositoryFactoryBeanSupport.java:162) at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.getObject( RepositoryFactoryBeanSupport.java:44) 在 org.springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多springframework.beans.factory.support.FactoryBeanRegistrySupport.doGetObjectFromFactoryBean(FactoryBeanRegistrySupport.java:144) ... 32 更多

My repository interface:

我的存储库界面：

public interface PostRepository extends CrudRepository<Post, Integer> {

@Query("select p from Post p order by p.id desc")
Iterable<Post> findLastFirst();

Iterable<Post> findAllOrderByIdDesc();

}

My Post entity:

我的帖子实体：

@Entity
public class Post {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;
private Date date;
private String title;
@Lob
private String body;

protected Post() {
    date = new Date();
}

public Post(String title, String body) {
    this.date = new Date();
    this.title = title;
    this.body = body;
}

public int getId() {
    return id;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public Date getDate() {
    return date;
}

public String getBody() {
    return body;
}

public void setBody(String body) {
    this.body = body;
}

采纳答案by Oskar

There is an extension to CrudRepository called PagingAndSortingRepository

CrudRepository 有一个扩展名为PagingAndSortingRepository

 public interface PostRepository extends PagingAndSortingRepository<Post, Integer> {}

Then just call .findAll(new Sort(Sort.Direction.DESC, "id")); instead of findAllOrderByIdDesc();

然后只需调用 .findAll(new Sort(Sort.Direction.DESC, "id")); 而不是 findAllOrderByIdDesc();

回答by Michel Z

May be my answer is stupid but did you try just "from Post order by id desc"instead of "select p from Post p order by p.id desc"

可能是我的回答很愚蠢，但你有没有尝试"from Post order by id desc"过"select p from Post p order by p.id desc"

回答by Paulo Pedroso

I know it's a bit late but this may help others.

我知道这有点晚了，但这可能对其他人有所帮助。

Just put BYbefore ORDER, like this: findAllByOrderByIdDesc

放在BY之前ORDER，像这样：findAllByOrderByIdDesc

It should work.

它应该工作。

回答by adenix

I know I'm a little late to the game but..

我知道我玩游戏有点晚了但是..

Another fix would be to correct the syntax on your named query in your repository.

另一个修复方法是更正存储库中命名查询的语法。

You have: Iterable<Post> findAllOrderByIdDesc();

你有： Iterable<Post> findAllOrderByIdDesc();

When it should be: Page<Post> findAllByOrderByIdDesc();

什么时候应该： Page<Post> findAllByOrderByIdDesc();

Note the addition of the Bykeyword after findAll. This marks the beginning of your filtering statements.

请注意在 之后添加了By关键字findAll。这标志着过滤语句的开始。