Spring JPA Repository Sub-Query In Native Query

Question

I am trying to run a native query on a repository method so that it returns the results with some counts. It was too complicated to do with JPQL, so I opted for a native query instead.

Repository

@RepositoryRestResource(collectionResourceRel="projects", path="projects")
interface ProjectRepository extends BaseRepository<Project, Long>, ProjectRepositoryCustom {

    @Query(
        value="SELECT p.id, p.user_id, p.title, p.description, p.created_on, p.version,(SELECT COUNT(0) FROM projectparts WHERE project_id = p.id) AS parts,(SELECT COUNT(0) FROM requests WHERE project_id = p.id) AS requests FROM projects AS p ORDER BY ?#{#pageable}",
        countQuery="SELECT COUNT(0) FROM projects",
        nativeQuery=true
    )
    Page<Project> findAll(Pageable pageable)

}

The entity has 2 properties annotated with @Transient so that the info is not persisted to the database. All the data comes back fine except the 2 transient properties which return null for the values. When I copy the query from the console and paste it in MySQL Workbench, the results are as expected and I see the counts that I need. Anyhow, not sure if there is anything else that needs to be done in order to get this native query to work as an annotation. I hard coded a value in the sub-query SELECT 55 FROM... just to see if it was a problem with the count and it still returned as null. I ran the query in Workbench and it works fine.

I've tried changing the transient property type from Integer, Long, BigInteger, long, int... and none of that made a difference. Since I'm using Groovy, I also tried def to let Groovy infer the type and that didn't work either.

I also tried running the project from the terminal instead and it still didn't work. I've tried it on a Mac and Linux and had no luck with displaying the results of the counts.

Answer 1

This will not work. You could use an SQLConstructorExpression however the returned instances would be unmanaged which is a major drawback.

An better option is to create a simple DB view which holds the pieces of summary info for the Project. You can them map the Project entity to both it's table and the associated summary view using the @SecondaryTable functionality of JPA.

https://en.wikibooks.org/wiki/Java_Persistence/Tables#Example_mapping_annotations_for_an_entity_with_multiple_tables

An added benefit is that you can sort and query on the summary values as for any other property.

Updated mapping:

@Entity
@Table(name = "projects")
@SecondaryTable(name = "projects_summary_vw")
public class Project{

   //use Integer rather than int to avoid issue outlined here:
   //http://stackoverflow.com/a/37160701/1356423

    @Column(name  = "parts", table = "projects_summary_vw", 
          insertable="false", updateable="false")
    private Integer partsCount;

    @Column(name  = "requests", table = "requestsCount" 
          insertable="false", updateable="false")
    private Integer requestsCount;

    //other mappings as required
}

No Custom query required:

@RepositoryRestResource(collectionResourceRel="projects", 
        path="projects")
interface ProjectRepository extends BaseRepository<Project, Long>, 
        ProjectRepositoryCustom {

}

An alternative non-JPA compliant solution may be to use some vendor specific extension rather than a view. Hibernate for example has an @Formula annotation which could be used:

https://docs.jboss.org/hibernate/orm/5.1/javadocs/org/hibernate/annotations/Formula.html

@Entity
@Table(name = "projects")
public class Project{

    @Formula("my count query as native sql")
    private Integer partsCount;

    @Formula("my count query as native sql")
    private Integer requestsCount;

    //other mappings as required
}