How does one create a custom query in jparepositor

Question

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

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

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.

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?

Answer 1

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

Along with this, a standard PStringRepository is needed:

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

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

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.

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.