[JAVA] Wie man mit der Generierung dynamischer SQL-Abfragen ein wenig knifflig baut

Hintergrund

Ich habe den Code eines Neulings überprüft (PG-Verlauf ca. 3 Wochen?) Und eine leicht interessante SQL-Abfrage generiert. Teilen Sie also die Geschichte mit. Ich spreche über die Verwendung einer Art OP-Mapper oder Doma, aber lassen Sie das weg.

Annahme

Ich möchte, dass auf dem Suchbildschirm mehrere Eingabefelder vorhanden sind und die vom Benutzer eingegebenen Elemente zu den SQL-Suchbedingungen hinzugefügt werden.

Sofern Sie keinen Active Record-ähnlichen OR-Mapper verwenden, verwenden Sie keine 2-Wege-SQL-Bibliothek wie Doma.

Wenn Sie Lust dazu haben, können Sie SQL in eine externe Datei ausschneiden, aber ich denke, dass die übliche Idee für Anfänger darin besteht, Strings dynamisch zu generieren und SQL zu erstellen.

Daher würden Sie normalerweise SQL-Abfragen mit Zeichenfolgenverknüpfungen in Ihrem Code generieren.

** Dieses Mal Schema: ** http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05780-02/scripts.htm#Cihgfecd

Beispiel: Eine Abfrage, wenn Sie in der Mitarbeitertabelle nach "Verkäufe sind in der Jobkategorie enthalten" und "Gehalt beträgt 6 Millionen Yen oder mehr" suchen möchten. Lassen Sie mich die Escape-Einstellung in der Like-Klausel weglassen.

SQL, das Sie generieren möchten

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || ? || '%';     --Ich möchte es der Bedingung hinzufügen, wenn es vom Benutzer angegeben wird
AND jb.SALARY >= ?                         --Ich möchte es der Bedingung hinzufügen, wenn es vom Benutzer angegeben wird
;

Normalerweise wird das Vorhandensein oder Fehlen einer Zeichenkettenkombination häufig vom Vorhandensein oder Fehlen des Werts verzweigt. Abgesehen von der Verwendung von StringBuilder weiß ich es nicht.

python

String sql = ~ ; //Kürzung. Annahme von SQL bis zur ersten Zeile der WHERE-Klausel.

if (jobTitle != null && jobTitle.length >= 0) {
  sql += "AND jb.JOB_TITLE LIKE '%' || ? || '%'"
}

if (minSalary != null && minSalaly.length >= 0) {
  sql += "AND jb.SALARY >= ?"
}

Ein Neuling tat dies jedoch.

jobTitleComment = "";
minSalaryComment = "";
if (jobTitle != null && jobTitle.length >= 0) {
 jobTitleComment = "--";
}

if (minSalary != null && minSalaly.length >= 0) {
 minSalaryComment = "--";
}

String sql = "SELECT"
+ " em.EMPLOYEE_ID"
+ ", em.FIRST_NAME"
+ ", em.LAST_NAME"
+ "FROM"
+ "  EMPLOYEES em"
+ ", INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)"
+ ", INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)"
+ "WHERE 1 = 1"
+ "AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE"
+ jobTitleComment + "AND jb.JOB_TITLE LIKE '%' || ? || '%'"  //Punkt
+ minSalaryComment + "AND jb.SALARY >= ?"                     //Punkt
;

Ich konnte die Absicht nicht verstehen, als ich Pat ansah, und sie blieb einige Sekunden lang hängen, aber als ich genau hinschaute, konnte ich sie nicht verstehen. Ich versuche zu wechseln, ob SQL-Suchbedingungen verwendet werden sollen, wobei "-" einen Kommentar angibt.

Ein Beispiel für die SQL-Generierung nach der Ausführung, wenn keine der beiden Bedingungen festgelegt ist, lautet beispielsweise wie folgt.

Wenn die Suchbedingung nicht festgelegt ist

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
-- AND jb.JOB_TITLE LIKE '%' || ? || '%'  /*★ Beide werden auskommentiert*/
-- AND jb.SALARY >= ?　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　/*★ Beide werden auskommentiert*/
;

Da die Suchbedingung leer ist, wird die Verfeinerungsbedingung der Where-Klausel auskommentiert und ungültig gemacht.

Weiter ist ein Beispiel für die Generierung, wenn nur der Jobname festgelegt ist.

Wenn nur der Jobname festgelegt ist

SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || 'Der Umsatz' || '%'  /*Dies ist gültig*/
-- AND jb.SALARY >= '??'                      /*Dies bleibt auskommentiert*/
;

Wenn die Suchbedingung vorhanden ist, ist die entsprechende Verfeinerungsbedingung in der Where-Klausel gültig. Ich fand es überraschend leicht zu sehen.

Zusammenfassung

• Es könnte interessant sein, eine solche 2-Wege-SQL-Ausgabe zu haben.
• Aber es fühlt sich an, als könnten Sie Doma verwenden.