MySQL Suchfunktion

[mister-man]

Hallo,

für unsere Raumverwaltungssoftware sind wir derzeit Dabei eine Suchfunktion zu implementieren, dazu nutzen wir "Mysql + Like".

Beispiel:

 // Formatiere den Suchbegriff für Mysql
        if(Sdebnr.length() != 0)
        {
            Qdebnr = Sdebnr;
        }
        else
        {
            Qdebnr = "%";
        }
        
        if(Snname.length() != 0)
        {
            Qnname = Snname;
        }
        else
        {
            Qnname = "%";
        }
        
        if(Svname.length() != 0)
        {
            Qvname = Svname;
        }
        else
        {
            Qvname = "%";
        }
        
        if(Sstrasse.length() != 0)
        {
            Qstrasse = Sstrasse;
        }
        else
        {
            Qstrasse = "%";
        }
        
        if(Sweb.length() != 0)
        {
            Qweb = Sweb;
        }
        else
        {
            Qweb = "%";
        }
        
        if(Stel.length() != 0)
        {
            Qtel = Stel;
        }
        else
        {
            Qtel = "%";
        }
        
        if(Sfax.length() != 0)
        {
            Qfax = Sfax;
        }
        else
        {
            Qfax = "%";
        }
        
        if(Semail.length() != 0)
        {
            Qemail = Semail;
        }
        else
        {
            Qemail = "%";
        }
        
        if(Sortnr.length() != 0)
        {
            Qortnr = Sortnr;
        }
        else
        {
            Qortnr = "%";
        }
        
        if(Stnr.length() != 0)
        {
            Qtnr = Stnr;
        }
        else
        {
            Qtnr = "%";
        }
        
        if(Sanredenr.length() != 0)
        {
            Qanredenr = Sanredenr;
        }
        else
        {
            Qanredenr = "%";
        }
        
        if(Sinaktiv.length() != 0)
        {
            Qinaktiv = Sinaktiv;
        }
        else
        {
            Qinaktiv = "%";
        }
        
        if(Sbemerkung.length() != 0)
        {
            Qbemerkung = Sbemerkung;
        }
        else
        {
            Qbemerkung = "%";
        }

 query = String.format("SELECT kdnr FROM kunde WHERE debnr LIKE '%s' "
                + "AND nname LIKE '%s' AND vname LIKE '%s' AND strasse LIKE '%s' AND web LIKE '%s' AND tel LIKE '%s' AND email LIKE '%s' AND fk_ortnr LIKE '%s' AND fk_tnr LIKE '%s' AND fk_anredenr LIKE '%s' AND inaktiv LIKE '%s' AND bemerkung LIKE '%s'", Qdebnr, Qnname, Qvname, Qstrasse, Qweb, Qtel, Qfax, Qemail, Qortnr, Qtnr, Qanredenr, Qinaktiv, Qbemerkung);
        result = mysql.returnQuery(query);

Bisher funktionierte die suche immer einwandfrei, außer wenn ein Feld in der Datenbank NULL ist. Anschzeinend werden NULL felder von % nicht mitgesucht.

Gibt es eine bessere möglichkeit oder eine Möglichkeit das Problem zu lösen?


Danke !

 

[SlaterB]

was bringt einen dazu, bei einer einzelnen Problembetrachtung gleich 120 Code-Zeilen mit 13x denselben Code zu posten?

na immerhin sieht man ein großes Schlamassel, welches umgehend zu beheben ist,
eine solche Codewiederholung ist mit das schlimmste was man produzieren kann

auf die Frage bezogen brauchst du für jedes Feld entweder eine zusammengesetzte Abfrage, mit OR auch auf null prüfen
bzw. noch viel besser gleich überhaupt nicht einschränken

baue eine Methode, die das Feld und den Wert übergeben bekommt entweder einen Leerstring oder das Like für dieses Feld zurückgibt,
sammle diese in einer Liste

list.add(buildLike("debnr",Sdebnr);
list.add(buildLike("nname",Snname);

spart schon einen Haufen Variablen a la Qdebnr, ewig Zeilen, das list.add() kann vielleicht auch in die Methode,
je nachdem wie groß man alles aufbaut,

in der Liste hat man dann vielleicht "", "", "vname LIKE 'R%diger'", "", ""

diese Liste in einer Schleife durchgehen und daraus ein WHERE zusammenbasteln,
wenn hier anscheinend nichts anderes reinkommt dann darauf achten dass vor der ersten Teilbedingung != "" kein AND kommt,
falls Liste überhaupt nix gutes enthält auf das ganze WHERE verzichten usw.,
möglicherweise immer Klammern verwenden wenn die Bedingungen zusammengesetzt sein können,
viele kleine Dinge zu beachten,
evtl. tricksen und mit "WHERE 1 = 1 " beginnen, dann kann in jedem Fall mit AND weiter gemacht werden

kurz: Query strukturiert aufbauen

 

[mister-man]

Hay, ich hab da mal ein bisschen rumgebastelt, wie ist das jetzt?

public static List kontigente(String name, String menge, String preis, String inaktiv, String bemerkung, String einkauf)
   {
       
       Map<String,String> suche = new HashMap<>();
       String query;
       ResultSet result;
       List id = new ArrayList();
       
       suche.put("name", name);
       suche.put("menge", menge);
       suche.put("preis", preis);
       suche.put("inaktiv", inaktiv);
       suche.put("bemerkung", bemerkung);
       suche.put("einkauf", einkauf);
       
       query = "SELECT knr FROM kontingente";
       query = format_search(query,suche);
       
       
       // Sichere Suchergebniss vor dem schließen der Mysql Verbindung
       try
       {
            result = mysql.returnQuery(query);
            while (result.next()) 
            {
                id.add(result.getInt(1));
            }
       }
       catch (SQLException e)
       {
           System.out.println("load(): Transaktionsfehler");
           System.out.println(e);
       }
       
        return id;
   }
   
   private static String format_search(String query , Map <String,String> suche)
   {
       boolean first = false;
       
       for (String key : suche.keySet()) 
       {
            if(suche.get(key).toString().length() != 0)
            {
                if(first == false)
                {
                    query += " WHERE ";
                    query += key + " LIKE " + "'%" + suche.get(key) + "%'";
                    first = true;
                }
                else
                {
                    query += " AND ";
                    query += key + " LIKE " + "'%" + suche.get(key) + "%'";
                }
            }
       }
       return query;
   }

Es geht alles, aber wie ist der code zu bewerten......

 

[SlaterB]

ich findes es logischerweise gut, vielleicht kein objektiver Bewerter


in meinem Tunnelblick auf Wiederholung und sonstige einfache Fehler noch kleine Anmerkungen:

> suche.get(key)
taucht 3x in der Schleife an, lege eine Variable dafür an,

das ist übrigens ein String, da brauchst du nicht toString() aufrufen

Zeile 49 und 55 sind gleich, die könnten nur einmal hinter der if-else-Verzweigung stehen, kommt immer dran

'first' sollte logisch korrekt eher anfangs auf true stehen, dann auf false setzen

 

[crashfinger]

Hmm, man kann Zahlen-Werte nicht mit LIKE Abfragen - Menge und Preis sind doch keine Strings in der DB oder?

Ich bastel mal was. Kleinen Moment ...

 

[crashfinger]

So, das ist dabei herausgekommen:

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Vector;

public class GenerateWhereConditions {

	private Map<String, Vector<String>> columnConditionsMap = new HashMap<>();
	
	public GenerateWhereConditions() {}
	
	public void appendCondition(String columnName, String value) throws Exception{
		if(columnName == null)	 {throw new NullPointerException("Kein Spaltenname angegeben");}
		if(columnName.equals("")){throw new Exception("Kein Spaltenname angegeben");}
		if(value == null)		 {throw new NullPointerException("Kein Suchwert angegeben");}
		if(!columnConditionsMap.containsKey(columnName)){columnConditionsMap.put(columnName, new Vector<String>());}
		columnConditionsMap.get(columnName).add("("+ columnName +" LIKE '%"+ value +"%')");
	}
	public void appendCondition(String columnName, int value) throws Exception{
		if(columnName == null)	 {throw new NullPointerException("Kein Spaltenname angegeben");}
		if(columnName.equals("")){throw new Exception("Kein Spaltenname angegeben");}
		if(!columnConditionsMap.containsKey(columnName)){columnConditionsMap.put(columnName, new Vector<String>());}
		columnConditionsMap.get(columnName).add("("+ columnName +" = "+ value +")");
	}
	
	public String getWhereConditions(){
		String result;
		StringBuilder andSb = new StringBuilder();
		StringBuilder orSb  = new StringBuilder();
		andSb.append("(1=1)");
		
		Vector<String> conditionsVec;
		for(Iterator<Vector<String>> it = columnConditionsMap.values().iterator(); it.hasNext();){
			conditionsVec = it.next();
			if(conditionsVec.size()==1){
				andSb.append(" AND "+ conditionsVec.get(0));
			}else{
				for(Iterator<String> itVec = conditionsVec.iterator(); itVec.hasNext();){
					orSb.append(" OR " + itVec.next());
				}
			}
		}
		result = " WHERE " + andSb.toString();
		if(orSb.length() != 0){result += " AND ((1=2) " +orSb.toString() +")";}
		return result;
	}
	
	@Override
	public String toString() {return this.getWhereConditions();}

	public static void main(String[] args) {
		GenerateWhereConditions gsqs = new GenerateWhereConditions();
		try{//gsqs.appendCondition(null, 3);
			gsqs.appendCondition("column1", 3);
			gsqs.appendCondition("column1", 5);
			gsqs.appendCondition("column2", "test");
		}catch(Exception e){e.printStackTrace();}
		System.out.println(gsqs);
	}

}

Mit dieser Klasse können pro Tabellen-Spalte auch mehrere Werte gesucht werden.
Wird für eine Spalte nur ein Wert gesucht wird die WHERE-Bedingung einfach mit AND erweitert. Werden für eine Spalte mehrere Werte gesucht wird die WHERE-Bedingung über geschachtelte OR erweitert.
Die Methoden zum hinzufügen von Bedingungen(appendCondition) decken im Moment nur String- und int-Suchwerte ab, können aber leicht um weitere Methoden erweitert werden die andere Datentypen unterstützen (z.B. Datumswerte, NULL-Einträge usw.).
Dadurch dass man für jeden Datentyp eine eigene Methode erstellen kann, bringt den Vorteil mit sich, dass man die jeweilige WHERE-Bedingung genau auf den Datentyp der Sql-Tabellenspalte anpassen kann.

 

[crashfinger]

Da war noch ein kleiner Fehler drin, hier nun die korrekte Version:

import java.util.Map;
import java.util.HashMap;
import java.util.Vector;
import java.util.Iterator;

public class GenerateWhereConditions {

	private Map<String, Vector<String>> columnConditionsMap = new HashMap<>();
	
	public GenerateWhereConditions() {}
	
	public void appendCondition(String columnName, String searchValue) throws Exception{
		if(columnName == null)	 {throw new NullPointerException("Kein Spaltenname angegeben");}
		if(columnName.equals("")){throw new Exception("Kein Spaltenname angegeben");}
		if(searchValue == null)	 {throw new NullPointerException("Kein Suchwert angegeben");}
		if(!columnConditionsMap.containsKey(columnName)){columnConditionsMap.put(columnName, new Vector<String>());}
		columnConditionsMap.get(columnName).add("("+ columnName +" LIKE '%"+ searchValue +"%')");
	}
	public void appendCondition(String columnName, int searchValue) throws Exception{
		if(columnName == null)	 {throw new NullPointerException("Kein Spaltenname angegeben");}
		if(columnName.equals("")){throw new Exception("Kein Spaltenname angegeben");}
		if(!columnConditionsMap.containsKey(columnName)){columnConditionsMap.put(columnName, new Vector<String>());}
		columnConditionsMap.get(columnName).add("("+ columnName +" = "+ searchValue +")");
	}
	
	public String getWhereConditions(){
		boolean	isFirstAndCondition = true;
		boolean	isFirstOrCondition  = true;
		StringBuilder sb = new StringBuilder();
		
		Vector<String> conditionsVec;
		for(Iterator<Vector<String>> it = columnConditionsMap.values().iterator(); it.hasNext();){
			conditionsVec = it.next();
			if(conditionsVec.size()==1){
				sb.append(isFirstAndCondition ? "":" AND ");
				sb.append(conditionsVec.get(0));
				isFirstAndCondition = false;
			}else{
				sb.append(isFirstAndCondition ? " (":" AND (");
				isFirstOrCondition = true;
				for(Iterator<String> itVec = conditionsVec.iterator(); itVec.hasNext();){
					sb.append(isFirstOrCondition ? "":" OR ");
					sb.append(itVec.next());
					isFirstOrCondition = false;
				}
				sb.append(")");
				isFirstAndCondition = false;
			}
		}
		return " WHERE " + sb.toString();
	}
	
	@Override
	public String toString() {return this.getWhereConditions().replaceAll("AND ", "AND\n\t\t");} //replace ist nur zum aufhübschen der Konsolenausgabe

	public static void main(String[] args) { // nur zum testen
		GenerateWhereConditions gwc = new GenerateWhereConditions();
		try{//gwc.appendCondition(null, "exceptionTest");
			gwc.appendCondition("column1", 11);
			gwc.appendCondition("column1", 12);
			gwc.appendCondition("column2", "test2");
			gwc.appendCondition("column3", "test31");
			gwc.appendCondition("column3", "test32");
			gwc.appendCondition("column4", "test4");
			gwc.appendCondition("column5", 51);
			gwc.appendCondition("column5", 52);
		}catch(Exception e){e.printStackTrace();}
		System.out.println(gwc);
	}
}

 

[mister-man]

Hay, kann ich leider nicht ganz bestätigen.
Bei mir klappt die Suche nach preisen (Float) sowie personen (INT) einwandfrei. Weiterhin können alle Boolwerte und Strings gesucht werden. Weitere Werte haben wir nicht verbaut !

 

[crashfinger]

Hast recht, hab's gerade nachgelesen. Ich dachte MySql hält sich an den Standard ^^

 

[mister-man]

Nur mal so aus interesse, warum wäre es gegen den Standart mit Like eine zahl zu suchen?!

Aber schonmal danke, das du dir die Mühe mit der Suchfunktion gemacht hast.....

 

[SlaterB]

eine genaue Definition zu finden ist gar nicht so leicht, aber vielleicht sagt es schon etwas aus,
dass du wohl kaum im Internet ein LIKE im Einsatz bei Zahlen finden kannst, oder doch?

jede beliebige SQL-Seite a la
SQL: LIKE Condition
redet gar nicht erst über den Einsatz zu Zahlen, kümmert sich unausgesprochen nur um Text

------

wobei, weiter unten steht auch
> WHERE account_number like '12317_';

mit 'sql like number' kommt man natürlich besser zu passenden Ergebnissen a la
ms access - How to use LIKE condition in SQL with numeric field? - Stack Overflow

es ist für die DB natürlich relativ einfach, aus einer Zahl einen Text zu machen, der dann durchsucht werden kann,
soweit könnte ich mich auch damit abfinden

 

[mister-man]

Okay, wieder etwas gelernt. Ich sag mal danke an euch beiden !

 

[crashfinger]

Na da will ich noch schell meinen Senf dazugeben

Grundsätzlich spricht nichts dagegen Zahlen mit LIKE zu suchen (wenn die jeweilige DB die Möglichkeit dazu bietet), aber der Aufwand für die DB einen String zu vergleichen ist um ein vielfaches höher (abhängig vom String-Datentyp) als bei einem Vergleich von Zahlen.
Aus diesem Grund werden große Tabellen auch indiziert und mit Keys versehen.

 

[mister-man]

Na da will ich noch schell meinen Senf dazugeben

Grundsätzlich spricht nichts dagegen Zahlen mit LIKE zu suchen (wenn die jeweilige DB die Möglichkeit dazu bietet), aber der Aufwand für die DB einen String zu vergleichen ist um ein vielfaches höher (abhängig vom String-Datentyp) als bei einem Vergleich von Zahlen.
Aus diesem Grund werden große Tabellen auch indiziert und mit Keys versehen.

Gut, da wir aber in 99% der fällen nach texten suchen und zahlen kaum bis gar nicht gesucht werden, kann ich es wahrscheinlich so lassen.......

Danke !