Projection vers un DTO et requête complexe

Bonjour,
Nous migrons d'Hibernate Criteria vers JPA, non sans problèmes.
Voici quelques unes des classes que nous utilisons :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class FluxEntrant
{
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "flen_sequence")
    @SequenceGenerator(name = "flen_sequence", sequenceName = "FLEN_ID_SEQUENCE", allocationSize = 1000)
    @Column(name = "FLEN_ID", nullable = false)
    private Long id;
 
    /** version. */
    @Version
    @Column(name = "VERSION", nullable = false)
    private int version;
 
    @Column(name = "DATE", nullable = true)
    protected LocalDateTime date;
 
    @Column(name = "CODEIC", nullable = true, length = 4)
    protected String codeIc;
 
    @Column(name = "CODESTRUCT", nullable = true, length = 6)
    protected String codestruct;
 
    @Column(name = "CODECOMPLTSTRUCT", nullable = true, length = 1)
    protected String codeCompltStruct;
 
 
    @Column(name = "ETATOPE", nullable = false)
    @Enumerated(EnumType.STRING)
    protected EtatOpe etatOpe;
 
....
 
 
    @ManyToOne
    @JoinColumn(name = "JORE_ID_JOURNALRECEPTION", nullable = true, foreignKey = @ForeignKey(name = "FK_CORD_JORE_1"))
    protected JournalReception journalReception;
 
    /** Le montant. */
    @Column(name = "MONTANT", nullable = false)
    protected BigDecimal montant;
}
 
 
 
 
@Entity
public abstract class FluxEntrantPlat extends FluxEntrant
{
.....
 
    @ManyToOne
    @JoinColumn(name = "COBD_ID_CODEOPERATION", nullable = true)
    private CodeOperation codeOperation;
}
 
@Entity
public abstract class FluxEntrantPlatPrincipal extends FluxEntrantPlat
{
 
   ...
    @Column(name = "NNE", nullable = true)
    private String nne;
}
 
 
@Entity
public abstract class FeppPartenaire extends FluxEntrantPlatPrincipal
{
 
...
    @ManyToOne
    @JoinColumn(name = "APPL_ID_APPLICATION", nullable = true)
    private Application application;
}
 
 
@Entity
@Table(name = "FEPPLCR_FLCR")
public class FeppPartenaireLcr extends FeppPartenaire
{.....}
 
@Entity
@Table(name = "FEPPPIM_FPIM")
public class FeppPartenairePim extends FeppPartenaire
{....}
 
@MappedSuperclass
public abstract class FeppPartenaireTip extends FeppPartenaire
{....}
 
@Entity
@Table(name = "FEPPTIPVIR_FVIR")
public class FeppPartenaireVir extends FeppPartenaire
{....}
 
 
 
@Entity
public abstract class FeppRetour extends FluxEntrantPlatPrincipal
{
 
    @ManyToOne
    @JoinColumn(name = "APPL_ID_APPLICATION", nullable = true)
    private Application application;
.....}
 
@Entity
@Table(name = "FEPPCORR_FCOR")
public class FeppCorrection extends FeppRetour
{
....}
 
@Entity
@Table(name = "FEPPREJET_FREJ")
public class FeppRejet extends FeppRetour
{ ...}

Ci-dessous, une méthode Java permettant de réaliser une projection vers un DTO :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
public List<BeanProjectionFeppPourStats> makeListeBeanProjectionFeppPourStats(
        JournalReception unJournalReception, int pPremiereLigneALire, int pMaxLignesAlire)
 
    {
 
        DetachedCriteria criteria = DetachedCriteria.forClass(FluxEntrantPlatPrincipal.class);
        criteria.createAlias("codeOperation", "codeOperation");
        criteria.createAlias("application", "application", JoinType.LEFT_OUTER_JOIN);
        criteria.createAlias("journalReception", "journalReception");
        Emetteur[] emetteursRecherches = {Emetteur.RADIO, Emetteur.RSS};
        criteria.add(Restrictions.in("journalReception.emetteur", emetteursRecherches));
        criteria.add(Restrictions.eq("etatOpe", EtatOpe.CORRECT));
 
        final ProjectionList uneProjectionSpe = Projections.projectionList();
 
        uneProjectionSpe.add(Projections.groupProperty("codeIc"), "codeIc")
            .add(Projections.groupProperty("codestruct"), "codestruct")
            .add(Projections.groupProperty("codeCompltStruct"), "codeCompltStruct")
            .add(Projections.groupProperty("date"), "dateRecherchee")
            .add(Projections.groupProperty("nne"), "nne")
            .add(Projections.groupProperty("application"), "application")
            .add(Projections.groupProperty("codeOperation"), "codeOperation")
            .add(Projections.groupProperty("journalReception.emetteur"), "emetteur")
            .add(Projections.rowCount(), "nbOperations")
            .add(Projections.sum("montant"), "montant");
 
        criteria.setProjection(uneProjectionSpe).setResultTransformer(
            new AliasToBeanResultTransformer(BeanProjectionFeppPourStats.class)).add(
                Restrictions.eq("journalReception", unJournalReception));
 
        criteria.addOrder(Order.asc("codeIc")).addOrder(Order.asc("codestruct")).addOrder(
            Order.asc("codeCompltStruct")).addOrder(Order.asc("dateRecherchee")).addOrder(Order.asc("nne")).addOrder(
                Order.asc("application"))
            .addOrder(Order.asc("codeOperation")).addOrder(Order.asc("emetteur"));
 
        return (List<BeanProjectionFeppPourStats>) getHibernateTemplate().findByCriteria(criteria, pPremiereLigneALire,
            pMaxLignesAlire);
    }

Cette méthode génère le SQL suivant, qui fonctionne parfaitement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
   select
        this_.codeIc as y0_,
        this_.codestruct as y1_,
        this_.codeCompltStruct as y2_,
        this_.DATE as y3_,
        this_.nne as y4_,
        this_.APPL_ID_APPLICATION as y5_,
        this_.COBD_ID_CODEOPERATION as y6_,
        journalrec3_.emetteur as y7_,
        count(*) as y8_,
        sum(this_.montant) as y9_ 
    from
        ( select
            OPER_ID,
            DATE,
            codeIc,
            codestruct,
            codeCompltStruct,
            libStruc,
            etatoperation,
            montant,
			JORE_ID_JOURNALRECEPTION,
            COBD_ID_CODEOPERATION,
            nne,
            null as APPL_ID_APPLICATION,
            9 as clazz_ 
        from
            FEPPLCR_FLCR
        union
        all select
            OPER_ID,
            DATE,
            codeIc,
            codestruct,
            codeCompltStruct,
            libStruc,
            etatoperation,
            montant,
            JORE_ID_JOURNALRECEPTION,
            COBD_ID_CODEOPERATION,
            nne,
            null as APPL_ID_APPLICATION,
            4 as clazz_ 
        from
            FEPPPIM_FPIM 
        union
        all select
            OPER_ID,
            DATE,
            codeIc,
            codestruct,
            codeCompltStruct,
            libStruc,
            etatoperation,
            montant,
            JORE_ID_JOURNALRECEPTION,
            COBD_ID_CODEOPERATION,
            nne,
            null as APPL_ID_APPLICATION,
            5 as clazz_ 
        from
            FEPPTIPVIR_FVIR 
        union
        all select
            OPER_ID,
            DATE,
            codeIc,
            codestruct,
            codeCompltStruct,
            libStruc,
            etatoperation,
            montant,
            JORE_ID_JOURNALRECEPTION,
            COBD_ID_CODEOPERATION,
            nne,
            APPL_ID_APPLICATION,
            12 as clazz_ 
        from
            FEPPCORR_FCOR 
        union
        all select
            OPER_ID,
            DATE,
            codeIc,
            codestruct,
            codeCompltStruct,
            libStruc,
            etatoperation,
            montant,
            JORE_ID_JOURNALRECEPTION,
            COBD_ID_CODEOPERATION,
            nne,
            APPL_ID_APPLICATION,
            17 as clazz_ 
        from
            FEPPREJET_FREJ 
    ) this_ 
inner join
    JOURNALRECEPTIO_JORE journalrec3_ 
        on this_.JORE_ID_JOURNALRECEPTION=journalrec3_.JORE_ID 
inner join
    CODEOPERATION_COBD codeoperat1_ 
        on this_.COBD_ID_CODEOPERATION=codeoperat1_.COBD_ID 
left outer join
    APPLICATION_APPL applicatio2_ 
        on this_.APPL_ID_APPLICATION=applicatio2_.APPL_ID 
where
    journalrec3_.emetteur in (
        ?, ?
    ) 
    and this_.etatoperation=? 
    and this_.JORE_ID_JOURNALRECEPTION=? 
group by
    this_.codeIc,
    this_.codestruct,
    this_.codeCompltStruct,
    this_.DATE,
    this_.nne,
    this_.APPL_ID_APPLICATION,
    this_.COBD_ID_CODEOPERATION,
    journalrec3_.emetteur 
order by
    y0_ asc,
    y1_ asc,
    y2_ asc,
    y3_ asc,
    y4_ asc,
    y5_ asc,
    y6_ asc,
    y7_ asc fetch first ? rows only;

J'essaie donc de passer en JPA Criteria :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
public List<BeanProjectionFeppPourStats> makeListeBeanProjectionFeppPourStats(
        JournalReception unJournalReception, int pPremiereLigneALire, int pMaxLignesAlire)
 
    {
 
        CriteriaBuilder requeteBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<BeanProjectionFeppPourStats> maRequeteProjection =
            requeteBuilder.createQuery(BeanProjectionFeppPourStats.class);
        Root<FluxEntrantPlatPrincipal> baseDuFrom =
            maRequeteProjection.from(FluxEntrantPlatPrincipal.class);
 
        Join<FluxEntrantPlatPrincipal, JournalReception> joinFluxEntrantPlatPrincipalJournalReception =
            baseDuFrom.join(FluxEntrantPlatPrincipal_.journalReception, JoinType.INNER);
 
        Root<FeppPartenaire> baseFeppPartenaire = maRequeteProjection.from(FeppPartenaire.class);
        Join<FeppPartenaire, Application> joinApplicationFeppPartenaire =
            (Join<FeppPartenaire, Application>) baseFeppPartenaire.join(FeppPartenaire_.application, JoinType.LEFT);
        //requeteBuilder.treat(baseDuFrom, FeppPartenaire.class).get(FeppPartenaire_.application).alias("application");
 
        Root<FeppRetour> baseFeppRetour = maRequeteProjection.from(FeppRetour.class);
        Join<FeppRetour, Application> joinApplicationFeppRetour =
            (Join<FeppRetour, Application>) baseFeppRetour.join(FeppRetour_.application, JoinType.LEFT);
        //requeteBuilder.treat(baseDuFrom, FeppRetour.class).get(FeppRetour_.application).alias("application");
 
        List<Predicate> listePredicate = new ArrayList<>();
 
        Predicate eqJournalReception = requeteBuilder.equal(baseDuFrom.get(FluxEntrantPlatPrincipal_.journalReception), unJournalReception);
        listePredicate.add(eqJournalReception);
        Predicate eqEtatCorrect = requeteBuilder.equal(baseDuFrom.get(FluxEntrant_.etatOpe), EtatOpe.CORRECT);
        listePredicate.add(eqEtatCorrect);
 
        List<Emetteur> laListeEmetteur = new LinkedList<>();
        laListeEmetteur.add(Emetteur.RADIO);
        laListeEmetteur.add(Emetteur.RSS);
 
        Predicate inEmetteurs = joinFluxEntrantPlatPrincipalJournalReception.get(JournalReception_.emetteur).in(laListeEmetteur);
        listePredicate.add(inEmetteurs);
 
 
        Selection<BeanProjectionFeppPourStats> selection =
            requeteBuilder.construct(BeanProjectionFeppPourStats.class,
                baseDuFrom.get(FluxEntrantPlatPrincipal_.codeIc),
                baseDuFrom.get(FluxEntrantPlatPrincipal_.codestruct),
                baseDuFrom.get(FluxEntrantPlatPrincipal_.codeCompltStruct),
                baseDuFrom.get(FluxEntrantPlatPrincipal_.date),
                baseDuFrom.get(FluxEntrantPlatPrincipal_.nne),
 
                requeteBuilder.selectCase()
                    .when(requeteBuilder.equal(baseDuFrom.type(), FeppPartenaire.class),
                        requeteBuilder.treat(baseFeppPartenaire, FeppPartenaire.class).get(FeppPartenaire_.application))
                    .otherwise(requeteBuilder.treat(baseFeppRetour, FeppRetour.class).get(FeppRetour_.application)),
                baseDuFrom.get(FluxEntrantPlatPrincipal_.codeOperation),
                joinFluxEntrantPlatPrincipalJournalReception.get(JournalReception_.emetteur),
                requeteBuilder.countDistinct(baseDuFrom.get(FluxEntrantPlatPrincipal_.id)),
                requeteBuilder.sum(baseDuFrom.get(FluxEntrantPlatPrincipal_.montant)));
 
        maRequeteProjection.groupBy(
            baseDuFrom.get(FluxEntrantPlatPrincipal_.codeIc),
            baseDuFrom.get(FluxEntrantPlatPrincipal_.codestruct),
            baseDuFrom.get(FluxEntrantPlatPrincipal_.codeCompltStruct),
            baseDuFrom.get(FluxEntrantPlatPrincipal_.date),
            baseDuFrom.get(FluxEntrantPlatPrincipal_.nne),
            requeteBuilder.selectCase()
            .when(requeteBuilder.equal(baseDuFrom.type(), FeppPartenaire.class),
                requeteBuilder.treat(baseFeppPartenaire, FeppPartenaire.class).get(FeppPartenaire_.application))
            .otherwise(requeteBuilder.treat(baseFeppRetour, FeppRetour.class).get(FeppRetour_.application)),
            baseDuFrom.get(FluxEntrantPlatPrincipal_.codeOperation),
            joinFluxEntrantPlatPrincipalJournalReception.get(JournalReception_.emetteur));
 
        maRequeteProjection.select(selection).where(requeteBuilder.and(listePredicate.toArray(new Predicate[listePredicate.size()])));
 
        List<Order> lesOrdreDeTri = new ArrayList<>();
        lesOrdreDeTri.add(requeteBuilder.asc(baseDuFrom.get(FluxEntrantPlatPrincipal_.codeIc)));
        lesOrdreDeTri.add(requeteBuilder.asc(baseDuFrom.get(FluxEntrantPlatPrincipal_.codestruct)));
        lesOrdreDeTri.add(requeteBuilder.asc(baseDuFrom.get(FluxEntrantPlatPrincipal_.codeCompltStruct)));
        lesOrdreDeTri.add(requeteBuilder.asc(baseDuFrom.get(FluxEntrantPlatPrincipal_.date)));
        lesOrdreDeTri.add(requeteBuilder.asc(baseDuFrom.get(FluxEntrantPlatPrincipal_.nne)));
        lesOrdreDeTri.add(requeteBuilder.asc(requeteBuilder.selectCase()
            .when(requeteBuilder.equal(baseDuFrom.type(), FeppPartenaire.class),
                requeteBuilder.treat(baseFeppPartenaire, FeppPartenaire.class).get(FeppPartenaire_.application))
            .otherwise(requeteBuilder.treat(baseFeppRetour, FeppRetour.class).get(FeppRetour_.application))));
        lesOrdreDeTri.add(requeteBuilder.asc(joinFluxEntrantPlatPrincipalJournalReception.get(JournalReception_.emetteur)));
 
        TypedQuery<BeanProjectionFeppPourStats> query = entityManager.createQuery(maRequeteProjection.orderBy(lesOrdreDeTri))
            .setFirstResult(pPremiereLigneALire).setMaxResults(pMaxLignesAlire);
 
        return query.getResultList();
 
    }

Mais là, je rencontre plusieurs difficultés :
- d'abord, je ne suis pas certain du bon fonctionnement du selectCase()...when()...otherwise() ici. Le but est de préciser à JPA qu'il y a bien deux attributs "application" différents, donc deux manières différentes d'y accéder. Auparavant, Hibernate semblait bien plus permissif/tolérant.
- avant d'en arriver à ce code, j'ai dû modifier quelques annotations sur des classes, de @MappedSuperClass à @Entity, puisqu'une mapped superclass, contrairement à une entity, n'est pas requêtable. ("Mapped super class is not queryable and must not be passed as an argument to EntityManager or Query operations. Consider using an abstract class instead, mapped as an Entity."
A votre avis, le mapping indiqué est-il le bon?
- lors de l'exécution de la requête, j'observe une erreur et des phénomènes troublants :
- l'erreur : Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: n'est pas une expression GROUP BY
- apparitions de cross join que je ne comprends pas
- le sql généré :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
select fepp0_.CODEIC as col_0_0_, fepp0_.CODESTRUCT as col_1_0_, fepp0_.CODECOMPLTSTRUCT as col_2_0_, fepp0_.DATE as col_3_0_, fepp0_.NNE as col_4_0_, 
	 case when fepp0_.clazz_=:1  then fepppartenaire1_.APPL_ID_APPLICATION 
	 else feppeetour2_.APPL_ID_APPLICATION end as col_5_0_, fepp0_.COBD_ID_CODEOPERATION as col_6_0_, journalrec3_.EMETTEUR as col_7_0_, count(distinct fepp0_.FEPP_ID) as col_8_0_, 
	 sum(fepp0_.MONTANT) as col_9_0_ 
	 from (
	 select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, 
	 CODELIAMLISSEMENTDESTINATAIRE, CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, 
	 NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, VARIABLESELONAPPLI, APPL_ID_APPLICATION, MORE_ID_MOTIFRETOUR, CODELIAMCORRIGE, CODEMELINACORRIGE, DATEREGLTOPINITIALE, NUMBRUTCORRIGE, DATEECHEANCELCR, 
	 LIBELLEABREVDOMICILIATION, REFDESTINATAIRE, REFFINANCIALHEA, REFEMETTEURCORRECTION, REFOPINITIALE, NERISOFINANCIALHEA, null as GINGEREMETTEUR, null as REFPRESENTATEUR, null as CODEACCEPTATION, null as CODEENTREEPORTEFEUILLE, null as DATECREATION, 
	 null as DATEECHEANCE, null as DATEENTREEPORTEFEUILLE, null as REFTIRE, null as REFTIREUR, null as NERISOTIREUR, null as ACCEPTATION, null as MONTANTORIGINE, null as REFD6OPINITIALE, null as REFEMETTEURPAPAYE, null as NERISODESTINATAIRE, null as NERISOEMETTEUR, 
	 null as DATEREGLTIMAGEQUECHUAORIGINE, null as NOMDUTIREUR, null as NUMEROQUECHUA, null as PRENOMDUTIREUR, null as HUADUCOMPTABLE, null as LIBELLE1, null as LIBELLE2, null as TYPEPILOTISVUGM, 9 as clazz_ from FEPPCORR_FCOR 
	 union all 
	 select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, 
	 CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, 
	 VARIABLESELONAPPLI, APPL_ID_APPLICATION, MORE_ID_MOTIFRETOUR, null as CODELIAMCORRIGE, null as CODEMELINACORRIGE, DATEREGLTOPINITIALE, null as NUMBRUTCORRIGE, DATEECHEANCELCR, null as LIBELLEABREVDOMICILIATION, REFDESTINATAIRE, REFFINANCIALHEA, null as REFEMETTEURCORRECTION, 
	 null as REFOPINITIALE, null as NERISOFINANCIALHEA, null as GINGEREMETTEUR, null as REFPRESENTATEUR, null as CODEACCEPTATION, CODEENTREEPORTEFEUILLE, DATECREATION, null as DATEECHEANCE, DATEENTREEPORTEFEUILLE, null as REFTIRE, null as REFTIREUR, null as NERISOTIREUR, 
	 ACCEPTATION, MONTANTORIGINE, REFD6OPINITIALE, REFEMETTEURPAPAYE, NERISODESTINATAIRE, NERISOEMETTEUR, null as DATEREGLTIMAGEQUECHUAORIGINE, null as NOMDUTIREUR, null as NUMEROQUECHUA, null as PRENOMDUTIREUR, null as HUADUCOMPTABLE, null as LIBELLE1, null as LIBELLE2, 
	 null as TYPEPILOTISVUGM, 12 as clazz_ from FEPPREJET_FREJ 
	 union all select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, 
	 SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, 
	 NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, null as VARIABLESELONAPPLI, APPL_ID_APPLICATION, null as MORE_ID_MOTIFRETOUR, null as CODELIAMCORRIGE, null as CODEMELINACORRIGE, null as DATEREGLTOPINITIALE, null as NUMBRUTCORRIGE, 
	 null as DATEECHEANCELCR, LIBELLEABREVDOMICILIATION, null as REFDESTINATAIRE, null as REFFINANCIALHEA, null as REFEMETTEURCORRECTION, null as REFOPINITIALE, null as NERISOFINANCIALHEA, GINGEREMETTEUR, REFPRESENTATEUR, CODEACCEPTATION, CODEENTREEPORTEFEUILLE, 
	 DATECREATION, DATEECHEANCE, DATEENTREEPORTEFEUILLE, REFTIRE, REFTIREUR, NERISOTIREUR, null as ACCEPTATION, null as MONTANTORIGINE, null as REFD6OPINITIALE, null as REFEMETTEURPAPAYE, null as NERISODESTINATAIRE, null as NERISOEMETTEUR, null as DATEREGLTIMAGEQUECHUAORIGINE, 
	 null as NOMDUTIREUR, null as NUMEROQUECHUA, null as PRENOMDUTIREUR, null as HUADUCOMPTABLE, null as LIBELLE1, null as LIBELLE2, null as TYPEPILOTISVUGM, 11 as clazz_ from FEPPLCR_FLCR 
	 union all select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, 
	 ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, 
	 NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, null as VARIABLESELONAPPLI, APPL_ID_APPLICATION, null as MORE_ID_MOTIFRETOUR, null as 
	 CODELIAMCORRIGE, null as CODEMELINACORRIGE, null as DATEREGLTOPINITIALE, null as NUMBRUTCORRIGE, null as DATEECHEANCELCR, LIBELLEABREVDOMICILIATION, null as REFDESTINATAIRE, null as REFFINANCIALHEA, null as REFEMETTEURCORRECTION, null as REFOPINITIALE, null as NERISOFINANCIALHEA, 
	 GINGEREMETTEUR, REFPRESENTATEUR, null as CODEACCEPTATION, null as CODEENTREEPORTEFEUILLE, null as DATECREATION, null as DATEECHEANCE, null as DATEENTREEPORTEFEUILLE, null as REFTIRE, null as REFTIREUR, null as NERISOTIREUR, null as ACCEPTATION, null as MONTANTORIGINE, 
	 null as REFD6OPINITIALE, null as REFEMETTEURPAPAYE, null as NERISODESTINATAIRE, null as NERISOEMETTEUR, DATEREGLTIMAGEQUECHUAORIGINE, NOMDUTIREUR, NUMEROQUECHUA, PRENOMDUTIREUR, HUADUCOMPTABLE, null as LIBELLE1, null as LIBELLE2, null as TYPEPILOTISVUGM, 13 
	 as clazz_ from FEPPPIM_FPIM 
	 union all
	 select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, 
	 CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, 
	 null as VARIABLESELONAPPLI, APPL_ID_APPLICATION, null as MORE_ID_MOTIFRETOUR, null as CODELIAMCORRIGE, null as CODEMELINACORRIGE, null as DATEREGLTOPINITIALE, null as NUMBRUTCORRIGE, null as DATEECHEANCELCR, LIBELLEABREVDOMICILIATION, null as REFDESTINATAIRE, 
	 null as REFFINANCIALHEA, null as REFEMETTEURCORRECTION, null as REFOPINITIALE, null as NERISOFINANCIALHEA, GINGEREMETTEUR, REFPRESENTATEUR, null as CODEACCEPTATION, null as CODEENTREEPORTEFEUILLE, null as DATECREATION, null as DATEECHEANCE, null as DATEENTREEPORTEFEUILLE, 
	 null as REFTIRE, null as REFTIREUR, null as NERISOTIREUR, null as ACCEPTATION, null as MONTANTORIGINE, null as REFD6OPINITIALE, null as REFEMETTEURPAPAYE, null as NERISODESTINATAIRE, null as NERISOEMETTEUR, null as DATEREGLTIMAGEQUECHUAORIGINE, null as NOMDUTIREUR, null as NUMEROQUECHUA, 
	 null as PRENOMDUTIREUR, null as HUADUCOMPTABLE, LIBELLE1, LIBELLE2, TYPEPILOTISVUGM, 14 as clazz_ from FEPPTIPVIR_FVIR ) fepp0_ inner join JOURNALRECEPTIO_JORE journalrec3_ on fepp0_.JORE_ID_JOURNALRECEPTION=journalrec3_.JORE_ID inner join CODEOPERATION_COBD codeoperat8_ 
	 on fepp0_.COBD_ID_CODEOPERATION=codeoperat8_.COBD_ID cross join ( select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, 
	 ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, 
	 COBD_ID_CODEOPERATION, LIBELLEABREVDOMICILIATION, GINGEREMETTEUR, REFPRESENTATEUR, APPL_ID_APPLICATION, CODEACCEPTATION, CODEENTREEPORTEFEUILLE, DATECREATION, DATEECHEANCE, DATEENTREEPORTEFEUILLE, REFTIRE, REFTIREUR, NERISOTIREUR, null as DATEREGLTIMAGEQUECHUAORIGINE, 
	 null as NOMDUTIREUR, null as NUMEROQUECHUA, null as PRENOMDUTIREUR, null as HUADUCOMPTABLE, null as LIBELLE1, null as LIBELLE2, null as TYPEPILOTISVUGM, 11 as clazz_ from FEPPLCR_FLCR 
	 union all select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, 
	 CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, 
	 LIBELLEABREVDOMICILIATION, GINGEREMETTEUR, REFPRESENTATEUR, APPL_ID_APPLICATION, null as CODEACCEPTATION, null as CODEENTREEPORTEFEUILLE, null as DATECREATION, null as DATEECHEANCE, null as DATEENTREEPORTEFEUILLE, null as REFTIRE, null as REFTIREUR, null as NERISOTIREUR, 
	 DATEREGLTIMAGEQUECHUAORIGINE, NOMDUTIREUR, NUMEROQUECHUA, PRENOMDUTIREUR, HUADUCOMPTABLE, null as LIBELLE1, null as LIBELLE2, null as TYPEPILOTISVUGM, 13 as clazz_ from FEPPPIM_FPIM 
	 union all select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, 
	 CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, 
	 LIBELLEABREVDOMICILIATION, GINGEREMETTEUR, REFPRESENTATEUR, APPL_ID_APPLICATION, null as CODEACCEPTATION, null as CODEENTREEPORTEFEUILLE, null as DATECREATION, null as DATEECHEANCE, null as DATEENTREEPORTEFEUILLE, null as REFTIRE, null as REFTIREUR, null as 
	 NERISOTIREUR, null as DATEREGLTIMAGEQUECHUAORIGINE, null as NOMDUTIREUR, null as NUMEROQUECHUA, null as PRENOMDUTIREUR, null as HUADUCOMPTABLE, LIBELLE1, LIBELLE2, TYPEPILOTISVUGM, 14 as clazz_ from FEPPTIPVIR_FVIR ) fepppartenaire1_ left outer join APPLICATION_APPL applicatio4_ 
	 on fepppartenaire1_.APPL_ID_APPLICATION=applicatio4_.APPL_ID cross join ( select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, 
	 SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, 
	 NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, VARIABLESELONAPPLI, APPL_ID_APPLICATION, MORE_ID_MOTIFRETOUR, CODELIAMCORRIGE, CODEMELINACORRIGE, DATEREGLTOPINITIALE, NUMBRUTCORRIGE, DATEECHEANCELCR, LIBELLEABREVDOMICILIATION, REFDESTINATAIRE, 
	 REFFINANCIALHEA, REFEMETTEURCORRECTION, REFOPINITIALE, NERISOFINANCIALHEA, null as ACCEPTATION, null as CODEENTREEPORTEFEUILLE, null as DATECREATION, null as DATEENTREEPORTEFEUILLE, null as MONTANTORIGINE, null as REFD6OPINITIALE, null as REFEMETTEURPAPAYE, 
	 null as NERISODESTINATAIRE, null as NERISOEMETTEUR, 9 as clazz_ from FEPPCORR_FCOR 
	 union all select FEPP_ID, CODECOMPLTSTRUCT, CODEIC, CODESTRUCT, DATE, ETATOPE, LIBELLEPOSTE, MONTANT, VERSION, ANOP_ID_ANOOPERATION, COFI_ID_PATRONYFICTIF, JORE_ID_JOURNALRECEPTION, SEME_ID_SELECTIONMELIF, ARTICLEORIGINE, CODELIAMLISSEMENTDESTINATAIRE, 
	 CODEMELINADESTINATAIRE, CODESCR, DATEPRESENTATION, CATEGORIEFLUXFINANCIER, NATUREDETAILLEE, NOMDESTINATAIRE, GINGERDESTINATAIRE, CODELIAMLISSEMENTEMETTEUR, CODEMELINAEMETTEUR, NNE, NOMEMETTEURDONNEURDORDRE, NUMEROENREGISTREMENT, COBD_ID_CODEOPERATION, 
	 VARIABLESELONAPPLI, APPL_ID_APPLICATION, MORE_ID_MOTIFRETOUR, null as CODELIAMCORRIGE, null as CODEMELINACORRIGE, DATEREGLTOPINITIALE, null as NUMBRUTCORRIGE, DATEECHEANCELCR, null as LIBELLEABREVDOMICILIATION, REFDESTINATAIRE, REFFINANCIALHEA, null as REFEMETTEURCORRECTION, 
	 null as REFOPINITIALE, null as NERISOFINANCIALHEA, ACCEPTATION, CODEENTREEPORTEFEUILLE, DATECREATION, DATEENTREEPORTEFEUILLE, MONTANTORIGINE, REFD6OPINITIALE, REFEMETTEURPAPAYE, NERISODESTINATAIRE, NERISOEMETTEUR, 12 as clazz_ from FEPPREJET_FREJ ) 
	 feppeetour2_ left outer join APPLICATION_APPL applicatio5_ on feppeetour2_.APPL_ID_APPLICATION=applicatio5_.APPL_ID where fepp0_.JORE_ID_JOURNALRECEPTION=:2  and fepp0_.ETATOPE=:3  and (journalrec3_.EMETTEUR in (:4  , :5 )) 
	 group by fepp0_.CODEIC , fepp0_.CODESTRUCT , fepp0_.CODECOMPLTSTRUCT , fepp0_.DATE , fepp0_.NNE , 
	 case when fepp0_.clazz_=:6  
	 then fepppartenaire1_.APPL_ID_APPLICATION 
	 else feppeetour2_.APPL_ID_APPLICATION end , fepp0_.COBD_ID_CODEOPERATION , journalrec3_.EMETTEUR order by fepp0_.CODEIC asc, fepp0_.CODESTRUCT asc, fepp0_.CODECOMPLTSTRUCT asc, fepp0_.DATE asc, fepp0_.NNE asc, 
	 case when fepp0_.clazz_=:7  then fepppartenaire1_.APPL_ID_APPLICATION 
	 else feppeetour2_.APPL_ID_APPLICATION end asc, journalrec3_.EMETTEUR asc fetch first :8  rows only;
	 Error Msg = ORA-00979: n'est pas une expression GROUP BY

Au final, je ne vois plus du tout comment, dans le cas présent, réécrire la méthode en utilisant JPA.

Merci pour votre aide.
Cordialement,
Thomas