recherche multicritere java-> sql

Version imprimable

salut,

j'ai un petit prob pour effectuer une recherche multi critere

petit topo:

je recois comme arg ds une classe dbaccess : String n,String m,String t,int constr,int cli

et j'aimerais bien avec cela faire une instruction en fonction de l'etat du string ou de l'int
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 String instruction=("Select * from Vehicule where "); if (!numPlaque.equals("")) { instruction+="NUMEROPLAQUE_ID='"+numPlaque+"' and "; } if (!modele.equals("")) { instruction+="MODÈLE='"+modele+"' and "; } if (!typeCarburant.equals("")) { //instruction+="TYPECARBURANT='"+typeCarburant+"' and "; } if (Constructeur_id!=0) instruction+="CONSTRUCTEUR_ID="+Constructeur_id+" and "; if (Client_id!=0) instruction+="CLIENT_ID="+Client_id+";";
ca devient vite lourd, et ca plante a cause des "and"

y a t'il un moyen facile pour gerer une instruction? ou faut t'il faire tous les cas envisageable? :s
cad ici alors, plein de if en cascade pour traire les 5* cas??

merci ;)

Je crois que tu es obligé de passer par des if. Je crois que ce sujet à déjà été abordé mais je ne le trouve plus.

Sinon, si t'as base est légère tu peux faire une clause toujours vraie dans le WHERE (mais bon à voir pour les perfs).

Il faut utiliser un stringBuilder et un PreparedStatement.
Code:

1 2 3 4 5 6 7 8 9 StringBuilder instruction = new StringBuilder ("Select * from Vehicule where 1=1 "); if (! "".equals(numPlaque)) { // il faut faire dans ce sens les equals instruction.append(" AND NUMEROPLAQUE_ID= ? "); } // ... // tu crées ensuite ton PreparedStatement ...
Bon après c'est peut être à améliorer mais ça devrait déjà être un peu mieux ...

Salut,

Citation:

Envoyé par in

Il faut utiliser un stringBuilder et un PreparedStatement.

+1

Le StringBuilder doit être privilégié à la contaténation de String, et le PreparedStatement simplifie grandement l'utilisation de plusieurs types différents, et en particulier des String (gestion des caractères protégés, etc.)

Citation:

Envoyé par in

Je crois que tu es obligé de passer par des if.

Là par contre je ne suis pas trop d'accord... il est assez facile même sans utiliser aucune librairie tierces ni framework de généré dynamiquement une requête SQL...

Par exemple avec cette classe :

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
public class SQLBuilder {
 
	/** Le buffer qui contiendra la requête SQL */
	private final StringBuilder buffer;
	/** La liste des valeurs passé en paramètre au PreparedStatement */
	private final List<Object> args;
	/** Indicateur de présence du where dans la requête */
	private boolean useAnd;
 
	public SQLBuilder(String baseSql) {
		// On initialise le buffer avec notre requete SQL de base : 
		this.buffer = new StringBuilder(baseSql);
		// On isntancie la liste des paramètres de la requête :
		this.args = new ArrayList<Object>();
		// On détecte la présence de la clause where dans le requete de base :
		this.useAnd = baseSql.matches("(?i)^.*?\\swhere\\s.*?$");
	}
 
	/**
         * Cette méthode retourne " where " ou " and " selon l'état de la requête :
         */
	private String whereOrAdd() {
		String str = this.useAnd ? " and " : " where ";
		this.useAnd = true;
		return str;
	}
 
	/**
         * Ajout d'une contrainte AND si la condition est respécté
         * @param condition La condition a vérifier avant de faire l'ajout.
         * @param sql Le code SQL à ajouter à la requete
         * @param value La valeur de l'objet utilisé dans la condition.
         */
	public void and(boolean condition, String sql, Object value) {
		// On n'ajoute la contrainte que si la condition est vérifié :
		if (condition) {
			// On ajoute where ou and selon le cas :
			this.buffer.append(whereOrAdd());
			// Puis on ajoute le morceau de requête SQL
			this.buffer.append(sql);
			// Et on ajoute la valeur associé dans la liste des arguments
			this.args.add(value);
		}
	}
 
	/**
         * Création du PreparedStatement correctement initialisé
         * (avec ses paramètres).
         */
	public PreparedStatement createStatement(Connection con) throws SQLException {
		// Création du PreparedStatement initialisé avec la requête :
		PreparedStatement stmt = con.prepareStatement(this.buffer.toString());
 
		// On affecte chaque paramètre au PreparedStatement :
		int i = 1; // les index commencent à 1
		for (Object arg : this.args) {
			stmt.setObject(i, arg);
			i++;
		}
		return stmt;
	}
}

Qui pourrait être utilisé de la façon suivante dans ce cas :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
	// Création de la requête SQL et de ses contraintes optionnels :
	SQLBuilder gen = new SQLBuilder("Select * from Vehicule");
	gen.and(!numPlaque.equals(""),	"NUMEROPLAQUE_ID = ?",	numPlaque);
	gen.and(!modele.equals(""),	"MODÈLE = ?",		modele);
	gen.and(!typeCarburant.equals(""),"TYPECARBURANT = ?",	typeCarburant);
	gen.and(Constructeur_id!=0,	"CONSTRUCTEUR_ID = ?",	Constructeur_id);
	gen.and(Client_id!=0,		"CLIENT_ID = ?",	Client_id);
 
	// Création du Statement et exécution de la requête :
	PreparedStatement stmt = gen.createStatement(con);
	try {
		ResultSet rs = stmt.executeQuery();
		try {
			while (rs.next()) {
				// Traitement sur le ResultSet
				// ...
			}
		} finally {
			rs.close();
		}
	} finally {
		stmt.close();
	}

C'est du code Java 5.0 mais cela peut facilement être adapté pour du 1.4, en utilisant une StringBuffer à la place de la StringBuilder et en virant les quelques lignes de Generics. Par contre il faudra alors surcharger la méthode add() pour gérer les tous types primitifs (car avec Java 5.0 on bénéficie de l'autoboxing). Par exemple avec les int :

Code:

1
2
3
	public void and(boolean condition, String sql, int value) {
		and(condition, sql, new Integer(value));
	}

Bien sûr cela peut encore être largement amélioration, en gérant les OR ou d'autre structure SQL, ou en permettant d'ajouter des morceau de SQL avec plusieurs argument ?...

a++

04/10/2007, 11h19
in

Oui c'est sûr c'est beaucoup mieux ! ... moi et mon poil dans la main, 8-)

[EDIT] juste quand même, je pense qu'il est préférable de faire

Code:

! "".equals(numPlaque)

... mais bon ce n'est qu'un point de vue
04/10/2007, 11h31
adiGuba

Citation:

Envoyé par in

[EDIT] juste quand même, je pense qu'il est préférable de faire

Code:

! "".equals(numPlaque)

... mais bon ce n'est qu'un point de vue

+1 Tout à fait d'accord !
J'ai juste fait un copier-coller rapide :aie:

a++

Hello,

J'ai justement rencontré le problème très récemment.

J'ai developpé une hiérachie d'objets permettant de s'abstraire de la contrainte des 'if'

Une classe abstraite représente le corps de la requete à proprement dit (la partie fixe).

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
 
public abstract class AbstractSQLSearchQuery {
 
	protected final List params = new ArrayList(0); 
 
 
	/*protected List getParams(){
		return params;
	}*/
 
	abstract protected String getSelectClause();
	abstract protected String getStaticFromClause();
	abstract protected String getStaticWhereClause();
 
	/**
         * Methode permettant d'ajouter des parametres à la requete
         * @param param - AbstractParamSearchQuery
         */
	public void addParams(AbstractSQLParamSearchQuery _p){
		if(_p==null){
			throw new NullPointerException("Parametre de requete null");
		}
 
		params.add(_p);
	}
 
	/**
         * Methode permettant d'ajouter une liste de  parametres à la requete
         * @param param - AbstractParamSearchQuery
         */
	public void addParams(List _p){
		if(_p == null){
			throw new NullPointerException("La liste de parametres de requete null");
		}
 
		params.addAll(_p);
 
	}
 
	/**
         * methode permettant de construire la requête finale.
         * @return
         */
	final public String buildQuery(){
 
 
		String finalQuery = getSelectClause() + " " + getStaticFromClause();
 
		//ajout des parametres dynamiques dans la clause from
		int size = params.size();
		for(int i = 0; i<size;i++){
			AbstractSQLParamSearchQuery p = (AbstractSQLParamSearchQuery)params.get(i);
			finalQuery += p.buildFromClause();
		}
 
		finalQuery += " " + getStaticWhereClause();
 
		//ajout des parametres dynamiques dans la clause Where
		for(int i = 0; i<size;i++){
			AbstractSQLParamSearchQuery p = (AbstractSQLParamSearchQuery)params.get(i);
			finalQuery +=  p.buildWhereClause();
 
		}
 
		return finalQuery;
	}
 
}

Pour chaque requête de recherche on implantera une sous-classe définissant les trois méthodes abstraites.

Une autre classe abstraite definie un paramètre de cette requête, selon les cas des sous-classes représenteront des cas spécifiques.

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
 
/**
 * @author fsznajderman
 *
 * representation abstraite d'un parametre du moteur SQL de recherche
 */
public abstract class AbstractSQLParamSearchQuery {
 
	abstract String buildWhereClause();
 
 
	final String buildFromClause() {
		String fromClause = "";
		//verification que le parametre a été bien renseigné.
		if(isExitParam()){
			//verification si la table existe déja dans la clause from
			if(!isExistAlready()){
				fromClause += "," + getParentTable() + " " + getAliasTable() ;
			}
		}
		return fromClause;
	}
 
	abstract protected String getParentTable();
	abstract protected String getAliasTable();
	abstract protected String getFieldName();
 
	abstract protected String getTableRef();
	/**
         * Methode de verification permettant de savoir si le parametre à été selectionné pour la requete
         */
	abstract protected boolean isExitParam();
 
	/**
         * Methode permettant de savoir si la table existe déja dans la requete statique
         * @return
         */
	abstract protected boolean isExistAlready();
 
 
 
}

Cette classe a deux classes filles représentant un paramètre avec plusieurs valeur (clause IN) ou valeur unique (champ1=Valeur).

Paramètre unique :

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
 
/**
 * @author fsznajderman
 *
 * TODO Pour changer le modèle de ce commentaire de type généré, allez à :
 * Fenêtre - Préférences - Java - Style de code - Modèles de code
 */
public abstract class AbstractSQLParamUniqueValue extends AbstractSQLParamSearchQuery {
 
 
 
	/* (non-Javadoc)
	 * @see com.devoteam.sql.AbstractParamSearchQuery#getWhereClause()
	 */
	protected String buildWhereClause() {
 
		String closeWhere = "";
		String paramValue = getValue();
 
		if (paramValue != null && paramValue.length() > 0) {
			closeWhere = " AND " + getTableRef() + "=" + getAliasTable() + "."
					+ getFieldName() + " AND " + getAliasTable() + "."
					+ getFieldName() + " = '" + paramValue + "'";
 
		}
 
		return closeWhere;
 
	}
 
	final protected boolean isExitParam() {
 
		return (getValue()!=null && getValue().length()!=0);
	}
 
	/**
         * Methode permettant de recupérer les parametres du critère
         * Les parametres doivent être de type String
         * @param values
         */
	abstract protected String getValue();
 
}

Paramètre multiple

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
 
/**
 * @author fsznajderman
 *
 * Classe representant une parametre contenant plusieurs valeurs
 */
public abstract class AbstractSQLParamMultiValues extends AbstractSQLParamSearchQuery {
 
 
	protected static final String start = "(";
	protected static final String stop = ")";
 
	/* (non-Javadoc)
	 * @see com.devoteam.sql.AbstractParamSearchQuery#getWhereClause()
	 */
	 String buildWhereClause() {
 
		List values = getValues();
		String closeWhere = "";
		if(values!=null && values.size() !=0){
			String params =  buildParams(values);
 
			closeWhere = (!isExistAlready()?" AND " + getTableRef()+ "=" + getAliasTable()+"." +getFieldName():"") +
			" AND " + getAliasTable()+"." +getFieldName() + " IN " +params ;
 
		}
		return closeWhere;
 
	}
 
	final protected boolean isExitParam() {
 
		return (getValues()!=null && getValues().size()!=0);
	}
 
 
	/**
         * Methode permettant de recupérer les parametres du critère
         * Les parametres doivent être de type String
         * @param values
         */
	abstract protected List getValues();
 
	/**
         * Construction du chaine de parametre de la forme : (a,b,c)
         * @param values
         * @return
         */
	private String buildParams(List values){
		String retValue = start;
 
		int size = values.size();
		for(int i= 0;i<size;i++){
			retValue += values.get(i);
			if(i<(size - 1)){
				retValue += ",";
			}
		}
 
		return retValue+stop;
	}
 
 
}

Voila une exemple de paramètre concret :

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
 
**
 * @author fsznajderman
 *
 * TODO Pour changer le modèle de ce commentaire de type généré, allez à :
 * Fenêtre - Préférences - Java - Style de code - Modèles de code
 */
public class BusinessUnitSQLParam extends AbstractSQLParamUniqueValue{
 
	private String param = null;
 
	public BusinessUnitSQLParam(String _param){
 
		param=_param;
	}
 
 
	protected String getValue() {
		return param;
	}
	protected String getAliasTable() {
		return "bu";
	}
	protected String getFieldName() {
		return "ID_BU";
	}
	protected String getParentTable() {
		return "BU";
	}
	protected String getTableRef() {
		return "off.ID_BU";
	}
	protected boolean isExistAlready() {
		return true;
	}
}

Comme le nombre de paramètre est fini (c'est à dire que l'on connait d'avance) on créé une classe par paramètre. C'est le coté un peu fastidieux, mais une fois les classes créées après il ne reste plus grand chose à faire :

:arrow: création d'un instance pour chaque paramètre recu
:arrow: ajout de cette instance dans une liste.
:arrow: une fois la liste "populée" il suffit de la passer à la classe représentant la requête et appeler la méthode buildQuery().

Dans la généralité celà fonctionne vraiment bien, l'avantage est que lorsque l'on ajoute un nouveau paramètre, il y a juste une classe à créer sans manipuler des structures conditionnelle (if).

Voila un exemple d'utilisation :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
//création artificiel de paramètre
		List listBu = new ArrayList();
		listBu.add("1");
		listBu.add("2");
		listBu.add("3");
		List listPosition = new ArrayList();
		listPosition.add("4");
		listPosition.add("5");
		listPosition.add("6");
 
 
		//création de l'instance representant la requete
		InternalSearchQuery isq = new InternalSearchQuery();
		//Ajout des paramètres
		isq.addParams(new BusinessRoleSQLParam(listBu));
		isq.addParams(new PositionSQLParam(listPosition));
		//génération de la requete
		System.out.println("query : " + isq.buildQuery());

J'espère que je n'ai pas été trop confu dans mes explications.. si il y a des questions je suis à votre disposition.