Hibernate Query HQL - Alternative à MEMBER ?

Bonjour,

Je travail sur le debogage d'une application Java servant à extraire des données d'une BDD PostgreSQL.J'ai réussi à cerner le problème et je vais essayer de vous l'exposer facilement. La base de données étant lourde je ne vous présente que les 3 tables importantes.

Table reponsecomplexe

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE TABLE reponsecomplexe
(
  id bigint NOT NULL,
  codereponse character varying(255),
  valeur character varying(255),
  alias_id character varying(255),
  enquete_code character varying(255),
  numordre integer NOT NULL,
  CONSTRAINT reponsecomplexe_pkey PRIMARY KEY (id ),
  CONSTRAINT fka476da6e93992e18aa842243 FOREIGN KEY (enquete_code)
      REFERENCES enquete (code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Table ocr

Code:

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE ocr
(
  code character varying(255) NOT NULL,
  nom character varying(255),
  CONSTRAINT ocr_pkey PRIMARY KEY (code )
)
WITH (
  OIDS=FALSE
);

Table reponsecomplexe_ocr

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE TABLE reponsecomplexe_ocr
(
  reponsecomplexe_id bigint NOT NULL,
  ocr_code character varying(255) NOT NULL,
  CONSTRAINT reponsecomplexe_ocr_pkey PRIMARY KEY (reponsecomplexe_id , ocr_code ),
  CONSTRAINT fkab7fa902e11f1938 FOREIGN KEY (ocr_code)
      REFERENCES ocr (code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkab7fa902ef289ca6 FOREIGN KEY (reponsecomplexe_id)
      REFERENCES reponsecomplexe (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

Ci-dessous, le mappage des objets dans le code Java :

ReponseComplexe

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
 
package inra.fr.enquete.domain;
 
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Entity;
import javax.persistence.ManyToMany;
 
/**
 * classe de modÃ©lisation d'une rÃ©ponse complexe
 * qui est une rÃ©ponse ordonnÃ©e 
 * et qui fait rÃ©fÃ©rence une variable prÃ©cise
 * @author H.BOUSSARD
 */
@Entity
public class ReponseComplexe extends Reponse {
 
	/**
         * NumÃ©ro d'ordre, les rÃ©ponses pouvant Ãªtre multiples.
         */
	private int numOrdre; 
 
	/**
         * Liste d'OCR associÃ©es Ã* la rÃ©ponse.
         */
	@ManyToMany
	private Set<OCR> OCR;
 
	/**
         * Constructeur.
         */
	protected ReponseComplexe(){}
 
	/**
         * Constructeur.
         * @param enquete son enquÃªte. 
         * @param alias son alias.
         * @param codeReponse son code rÃ©ponse.
         * @param numOrdre son numÃ©ro d'odre.
         */
	public ReponseComplexe(Enquete enquete, Alias alias, String codeReponse, int numOrdre){
		super(enquete,alias,codeReponse);
		this.numOrdre = numOrdre;
		this.OCR = new HashSet<OCR>();
	}
 
	/**
         * Getter du numÃ©ro d'odre.
         * @return le numÃ©ro d'odre.
         */
	public int getNumOrdre(){
		return numOrdre;
	}
 
	/**
         * Setter d'un OCR.
         * @param oCR l'OCR.
         */
	public void addOCR(OCR oCR){
		this.OCR.add(oCR);
	}
 
	/**
         * Getter des OCR.
         * @return les OCR.
         */
	public Set<OCR> getOCR(){
		return OCR;
	}
 
 
}

OCR

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
 
package inra.fr.enquete.domain;
 
import java.io.Serializable;
 
import javax.persistence.Entity;
import javax.persistence.Id;
 
/**
 * Classe modelisant tous les OCR, occurence culturales rÃ©pÃ©tÃ©es.
 * @author jamanley
 *
 */
@Entity
public class OCR implements Serializable  {
 
	/**
         * 
         */
	private static final long serialVersionUID = 8305794855031527687L;
 
	/**
         * identifiant.
         */
	@Id
	private String code;
 
	/**
         * Son nom.
         */
	private String nom;
 
	/**
         * constructeur.
         */
	public OCR(){}
 
	/**
         * Constructeur.
         * @param code son identifiant.
         * @param nom son nom.
         */
	public OCR(String code, String nom){
		this.code = code;
		this.nom = nom;
	}
 
	/**
         * Retourne une chaÃ®ne de caractÃ¨res.
         * @return  une chaÃ®ne de caractÃ¨res.
         */
	public String toString(){
		return code+" : "+nom;
	}
 
	/**
         * getter de son identifiant.
         * @return son identifiant.
         */
	public String getCode() {
		return code;
	}
 
	/**
         * Getter de son nom.
         * @return son nom.
         */
	public String getNom() {
		return nom;
	}
 
}

Une fois cette requête terminé je ne veux garder que les lignes faisant partie d'un OCR particulier choisis au préalable.
Pour ce faire, la personne m'ayant précédé à fait cela de cette manière :

Requête HQL hibernate :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
from ReponseComplexe r 
where 
/** Toute cette partie ne nous interesse pas, elle fonctionne comme il faut.
r.alias.variable.type = 'multiple' 
and ( r.alias.variable.code = :variable0 
or r.alias.variable.code = :variable2 
or r.alias.variable.code = :variable4 
or r.alias.variable.code = :variable6 ) 
and ( r.enquete.departement.code = :departement0 
or r.enquete.departement.code = :departement1 ) 
and ( r.enquete.departement.region.code = :region0) 
and ( r.enquete.culture.code = :culture0) 
and ( r.enquete.culture.groupe.code = :groupe0) 
and ( r.enquete.campagne.annee = :campagne0) 
**/
and (  :ocr0 MEMBER r.OCR  ) // C'est cette ligne qui m'interesse
order by r.numOrdre, r.enquete

Traduction pour le SGBD PostgreSQL :

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
 
select reponsecom0_.id as id13_, reponsecom0_.alias_id as alias5_13_, reponsecom0_.codeReponse as codeRepo2_13_, reponsecom0_.enquete_code as enquete4_13_, reponsecom0_.valeur as valeur13_, reponsecom0_.numOrdre as numOrdre14_ 
from ReponseComplexe reponsecom0_, 
	( select id, null::varchar as liste_code, alias, variable_code, campagne_annee, 0 as clazz_ from Alias union all select id, liste_code, alias, variable_code, campagne_annee, 1 as clazz_ from AliasAPropositions union all select id, null::varchar as liste_code, alias, variable_code, campagne_annee, 2 as clazz_ from AliasCulture ) alias1_, 
	Variable variable2_, 
	Enquete enquete22_, 
	Departement departemen27_, 
	Culture culture30_ 
where reponsecom0_.alias_id=alias1_.id 
and alias1_.variable_code=variable2_.code 
and reponsecom0_.enquete_code=enquete22_.code 
and enquete22_.departement_code=departemen27_.code 
and enquete22_.culture_code=culture30_.code 
and variable2_.type='multiple' 
and (alias1_.variable_code='REG' 
	or alias1_.variable_code='DEP' 
	or alias1_.variable_code='CCOMM' 
	or alias1_.variable_code='COMEXP') 
and (enquete22_.departement_code='54' 
	or enquete22_.departement_code='55') 
and departemen27_.region_code='41' 
and enquete22_.culture_code='1' 
and culture30_.groupe_code='1' 
and (enquete22_.campagne_annee='2001' 
	or enquete22_.campagne_annee='1994' 
	or enquete22_.campagne_annee='2006')
//La traduction de :ocr# MEMBER r.OCR
and ('HERBICIDE' in (select ocr35_.code 
		from ReponseComplexe_OCR ocr34_, OCR ocr35_ 
		where reponsecom0_.id=ocr34_.ReponseComplexe_id 
		and ocr34_.OCR_code=ocr35_.code)) 
order by reponsecom0_.numOrdre, reponsecom0_.enquete_code

Pour résumer, la requête retourne une table de requetecomplexe d'après quelques critère. Jusque la cela fonctionne bien. Une fois arriver au MEMBER (ou IN dans la requete SQL) le SGBD recherche le ou les OCR de chaque requete complexe à l'aide de la table requetecomplexe_ocr et ne concerve que les lignes qui pointent sur un OCR correspondant à l'OCR choisis (ici HERBICIDE).

Le problème est que, certaine requête ne dure que quelques seconde et d'autre plus d'une heure ce qui n'est absolument pas envisageable.

Une alternative qui marche très bien en SQL et d'ajouter dans le from la table requetecomplexe_ocr, d'ajouter les conditions requetecomplexe_ocr.code = 'HERBICIDE' et requetecomplexe.id = requetecomplexe_ocr.requetecomplexe_id

Exemple :

Traduction pour le SGBD PostgreSQL :

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
 
select reponsecom0_.id as id13_, reponsecom0_.alias_id as alias5_13_, reponsecom0_.codeReponse as codeRepo2_13_, reponsecom0_.enquete_code as enquete4_13_, reponsecom0_.valeur as valeur13_, reponsecom0_.numOrdre as numOrdre14_ 
from ReponseComplexe reponsecom0_, 
	( select id, null::varchar as liste_code, alias, variable_code, campagne_annee, 0 as clazz_ from Alias union all select id, liste_code, alias, variable_code, campagne_annee, 1 as clazz_ from AliasAPropositions union all select id, null::varchar as liste_code, alias, variable_code, campagne_annee, 2 as clazz_ from AliasCulture ) alias1_, 
	Variable variable2_, 
	Enquete enquete22_, 
	Departement departemen27_, 
	Culture culture30_, 
        //On ajoute la table reponsecomplexe_ocr aux sources
        ReponseComplexe_OCR ocr31_
where reponsecom0_.alias_id=alias1_.id 
and alias1_.variable_code=variable2_.code 
and reponsecom0_.enquete_code=enquete22_.code 
and enquete22_.departement_code=departemen27_.code 
and enquete22_.culture_code=culture30_.code 
and variable2_.type='multiple' 
and (alias1_.variable_code='REG' 
	or alias1_.variable_code='DEP' 
	or alias1_.variable_code='CCOMM' 
	or alias1_.variable_code='COMEXP') 
and (enquete22_.departement_code='54' 
	or enquete22_.departement_code='55') 
and departemen27_.region_code='41' 
and enquete22_.culture_code='1' 
and culture30_.groupe_code='1' 
and (enquete22_.campagne_annee='2001' 
	or enquete22_.campagne_annee='1994' 
	or enquete22_.campagne_annee='2006')
//La modification :
and ocr31_.code = 'HERBICIDE'
and reponsecom0_.id = ocr31_.reponsecomplexe_id
order by reponsecom0_.numOrdre, reponsecom0_.enquete_code

Le modèle objet du code java ne possède pas d'objet pour la table reponsecomplexe_ocr, la table ne servant qu'au mapping entre, les tables et objets, reponsecomplexe et ocr.

Je cherche un moyen d'effectuer ma requête simplement sans revoir la conception du modèle coté java (J'ai à peine une semaine d'expérience dans tout ces framework qui sont tout nouveau pour moi).

Ce que j'aimerais au final, c'est modifier cette partie de la création de la requête HQL en touchant le moins possible au mapping :

Code:

1
2
3
4
5
6
7
8
if(ocr.size() > 0){
			b.append(" and ( ");
			b.append(" :ocr0 MEMBER r.OCR ");
			for(int i=1; i<ocr.size(); i++){
				b.append(" or :ocr"+i+" MEMBER r.OCR");
			}
			b.append(" ) ");
		}

Si vous avez des informations sur le pourquoi le "IN" peut être si gourmand en temps (certaines requète durait plus d'une heure alors que la table de départ avant le IN est vide la ou le WHERE ne dure que quelques seconde.), je suis preneur aussi.

Merci de m'avoir lu jusqu'ici. ;)