[JAVA] Comment construire un peu délicat avec la génération de requêtes SQL dynamiques

Contexte

J'étais en train de réviser le code d'un nouveau venu (historique PG environ 3 semaines?), Et je générais une requête SQL légèrement intéressante, alors partagez l'histoire. Je parle d'utiliser une sorte de mappeur OR ou Doma, mais laissez cela de côté.

supposition

Ce que je veux faire, c'est qu'il y ait plusieurs zones de saisie sur l'écran de recherche, et les éléments saisis par l'utilisateur sont ajoutés aux conditions de recherche SQL.

À moins que vous n'utilisiez un mappeur OR de type Active Record, vous n'utilisez pas une bibliothèque SQL bidirectionnelle comme Doma.

Si vous en avez envie, vous pouvez découper le SQL dans un fichier externe, mais je pense que l'idée habituelle pour les débutants est de générer dynamiquement des chaînes et de construire du SQL.

Par conséquent, vous générez normalement des requêtes SQL avec des jointures de chaîne dans votre code.

** Cette fois, schéma: ** http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05780-02/scripts.htm#Cihgfecd

Exemple: Une requête lorsque vous souhaitez rechercher "Les ventes sont incluses dans la catégorie d'emploi" et "Le salaire est de 6 millions de yens ou plus" dans la table des employés. Permettez-moi d'omettre le paramètre d'échappement dans la clause Like.

SQL que vous souhaitez générer

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 '%' || ? || '%';     --Je veux l'ajouter à la condition si spécifié par l'utilisateur
AND jb.SALARY >= ?                         --Je veux l'ajouter à la condition si spécifié par l'utilisateur
;

Habituellement, la présence ou l'absence de combinaison de chaînes de caractères est souvent dérivée de la présence ou de l'absence de la valeur. Mis à part l'utilisation de StringBuilder, je ne sais pas.

python

String sql = ~ ; //réduction. Hypothèse de SQL jusqu'à la première ligne de la clause WHERE.

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

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

Cependant, un nouveau venu faisait cela.

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 '%' || ? || '%'"  //point
+ minSalaryComment + "AND jb.SALARY >= ?"                     //point
;

Je ne pouvais pas comprendre l'intention quand j'ai regardé Pat, et elle est restée bloquée pendant quelques secondes, mais quand j'ai regardé de près, je n'ai pas pu la comprendre. J'essaie de changer d'utiliser ou non les conditions de recherche SQL avec "-" indiquant un commentaire.

Par exemple, un exemple de génération SQL après exécution lorsqu'aucune des conditions n'est définie est le suivant.

Lorsque la condition de recherche n'est pas définie

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 '%' || ? || '%'  /*★ Les deux seront commentés*/
-- AND jb.SALARY >= ?　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　/*★ Les deux seront commentés*/
;

Puisque la condition de recherche est vide, la condition de raffinement de la clause Where est commentée et invalidée.

Voici un exemple de génération lorsque seul le nom du travail est défini.

Lorsque seul le nom du travail est défini

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 '%' || 'Ventes' || '%'  /*C'est valable*/
-- AND jb.SALARY >= '??'                      /*Cela reste commenté*/
;

Si la condition de recherche existe, la condition de raffinement correspondante dans la clause Where est valide. Je pensais que c'était étonnamment facile à voir.

Résumé

• Il peut être intéressant d'avoir une sortie SQL bidirectionnelle comme celle-ci.
• Mais vous avez l'impression de pouvoir utiliser Doma.