SQL查询太复杂，无法在JPA标准API中表达？

Question

SQL：

SELECT w.NAME   AS 'wave_name', 
   Count(*) AS 'num_lines', 
   Sum(qty_ordered) AS 'num_units', 
   Count(DISTINCT unit_of_work_id) AS 'num_units_of_work', 
   Sum(completed_units) AS 'completed_units', 
   ( Sum(completed_units) + Sum(qty_scratched) ) / Sum(qty_ordered) AS 'perc_completed_units' 
FROM   (SELECT t.id, 
           t.wave_id, 
           t.quantity_requested  AS 'qty_ordered', 
           t.quantity_scratched    AS 'qty_scratched', 
           t.unit_of_work_id     AS 'unit_of_work_id', 
           Ifnull(m.quantity, 0) AS 'qty_picked', 
           CASE 
             WHEN Ifnull(m.quantity, 0) > quantity_requested THEN 
             quantity_requested 
             ELSE Ifnull(m.quantity, 0) 
           END                   AS 'completed_units' 
    FROM   task t 
           LEFT OUTER JOIN (SELECT move.task_id, 
                                   Sum(quantity) AS 'quantity' 
                            FROM   move
                            GROUP  BY task_id) m 
                        ON m.task_id = t.id) s 
   JOIN wave w 
     ON w.id = s.wave_id 
GROUP  BY w.name;

各实体：

@Entity
@Table(name = "task")
public class Task {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @ManyToOne (cascade = CascadeType.ALL)
    @JoinColumn (name = "wave_id", nullable = false)
    private Wave wave;

    @ManyToOne (cascade = CascadeType.ALL)
    @JoinColumn (name = "unit_of_work_id", nullable = false)
    private UnitOfWork unitOfWork;

    @OneToMany (cascade = CascadeType.ALL, mappedBy = "task")
    private Set<Move> moves = new HashSet<Move>();

    @Column (name = "quantity_requested")
    private Long quantityRequested;

    @Column (name = "quantity_scratched")
    private Long quantityScratched;
}

@Entity
@Table(name = "wave")
public class Wave {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "wave", cascade = CascadeType.ALL)
    private Set<Task> tasks = new HashSet<Task>();
}

@Entity
@Table(name = "unit_of_work")
public class UnitOfWork {

    @Id
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    @Column (name = "id")
    private Long id;

    @OneToMany(mappedBy = "unitOfWork", cascade = CascadeType.ALL)
    private Set<Task> tasks = new HashSet<Task>();
}

@Entity
@Table(name = "move")
public class Move {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @ManyToOne (cascade = CascadeType.ALL)
    @JoinColumn (name = "task_id", nullable = false)
    private Task task;

    @Column (name = "quantity")
    private Long quantity;
}

Answer 1

我建议使用命名参数或本机查询方法。

例如：

命名参数：

 public interface Repo extends JpaRepository<AEntity, String> { 
    @Query("select a from AEntity a where a.BEntity.name = :name")

    public aMethod( @Param("name") String name)
    }

public interface Repo extends JpaRepository<AEntity, String> {    
    @Query(value = "select * from Tablename t where t.name = :name", nativeQuery=true)

    public aMethod(@Param("name") String name)
    }