如何在 jparepository 中创建自定义查询但返回实体以外的对象? [英] How does one create a custom query in jparepository but return an object other than the entity?

问题描述

我们有一个名为Location的类的项目，并且已经成功实现了jparepositories，定义了几个搜索方法.我们想要一个额外的搜索方法来返回一组街道名称(从位置表中提取)而不是一组位置 - 这样我们可以在用户输入街道时实现自动完成.首先我们尝试了@Query注解:

We have a project with a class called Location, and have successfully implemented jparepositories, defining several search methods. We would like to have an additional search method that would return a set of streetnames (extracted from the Location table) rather than a set of Locations - so we could implement auto-complete when the User types in streets. First we tried the @Query annotation:

 @RepositoryRestResource(collectionResourceRel = "locations", path = "locations")
 public interface LocationRepository extends JpaRepository<Location, Integer>, LocationRepositoryCustom {
    List<Location> findByStreetNameStartingWithIgnoreCase(@Param("street") String streetName);

    @Modifying
    @Query("select x.streetName from Location x where x.streetName like :street%")
    List<String> findStreetNameStartingWith(@Param("street") String streetName);
 }

如果我在程序中查询一条不存在的街道(例如，没有以 X 开头的街道)，我会得到一个空集 {}.如果我查询确实存在的街道(街道 = Br，因为百老汇存在于数据库中)，我得到

If I queried the program for a street that did not exist (no streets starting with X, for instance), I got an empty set {} returned. If I queried for a street that did exist (street=Br, since Broadway exists in the database), I get

 {"cause":null,"message":"PersistentEntity must not be null!"}

然后我们尝试将其实现为自定义查询，使用:

We then tried to implement it as a custom query, using:

 public interface LocationRepositoryCustom {
    @Query("select x.streetName from Location x where x.streetName like :streetName")
    public List<String> collectStreetNames(@Param("streetName") String streetName);
 }

 class LocationRepositoryImpl implements LocationRepositoryCustom {

   @PersistenceContext
   private EntityManager em;

   @Override
   public List<String> collectStreetNames(String streetName) {
    List<String> retList = new ArrayList<String>();
    retList.add("start");
    retList.add("end");
    return retList;
   }
 }

这也给了我们PersistentEntity must not be null"错误.实现中的代码用于返回硬编码结果，因此我们不会试图弄清楚我们的 SQL 是否错误或我们的架构是否错误.我们在调试下运行它并验证确实返回了两个项目的列表.

That also gives us the "PersistentEntity must not be null" error. The code inside the implementation was used to return a hard-code result, so we weren't trying to figure out if our SQL was wrong or our architecture. We ran it under debug and verified that, indeed, the list of two items was returned.

问题似乎是从存储库返回 List 以外的任何内容.这是对这种架构的限制吗?还是我们做错了什么，如果我们学会了秘密握手，一切都会变得笨拙?

The problem appears to be returning anything other than List from the repository. Is that a restriction on this architecture? or is there something we are doing wrong, and that if we learn the secret handshake everything will be hunky-dory?

推荐答案

我错过的线索是PersistentEntity must not be null".存储库框架想要返回已注册实体的列表 - 而不是任何旧的 POJO 或原语.解决方案是定义一个查询可以返回的实体:

The clue I had missed was 'PersistentEntity must not be null'. The repository framework wants to return a List of registered entities - not any old POJO or primitive. The solution was to define an entity that the query could return:

@Entity
@Table(name="PString")
public class PString {

  @Column(name="Name", length=40)   
  @Id   
  private String value;

  public PString() { }
  public PString(String name) {
    this.value = name;
  }

  public String getValue() {
    return value;
  }
  public void setValue(String value) {
    this.value = value;
  }
}

除此之外，还需要一个标准的 PStringRepository:

Along with this, a standard PStringRepository is needed:

@RepositoryRestResource(collectionResourceRel = "strings", path = "strings")
public interface PStringRepository extends JpaRepository<PString, String> {
}

然后，我在 LocationRepositoryCustom 中的自定义函数变为:

Then, my custom function in LocationRepositoryCustom becomes:

@Override 
public List<PString> collectStreetNames(String streetName) 
{ 
    Query query = em.createNativeQuery("select distinct streetName from Location where streetName like ?"); 
    query.setParameter(1, streetName + "%"); 
    List<PString> returned = new ArrayList<PString>();

    @SuppressWarnings("unchecked")
    List<String> list = query.getResultList(); 

    for (String string : list) 
    {
        returned.add(new PString(string));
    }   

    return returned; 
}

现在返回一个 StreetName 列表.(它们被格式化为字符串项的 hrefs，因此所有空格都替换为 %20，但我可以处理.)有趣的是，PString 表不需要存在于数据库模式中 - 返回的 hrefs 实际上并不引用到数据库中的实际数据项.

This now returns a list of StreetNames. (They are formatted as hrefs for string items, thus all spaces are replaced with %20, but I can handle that.) Interesting to note that the PString table does not need to exist in the database schema - the returned hrefs do not actually refer to actual data items in the database.

请注意，这并没有回答如何使用@Query 注释进行操作的问题.我通过返回 List 再次尝试，但仍然出现相同的错误.

Note that this does not answer the question of how to do it with the @Query annotation. I tried that again by returning List, but still got the same error.

这篇关于如何在 jparepository 中创建自定义查询但返回实体以外的对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！