L'une des tâches les plus fastidieuses pour les développeurs Java réside souvent dans l'intégration de requêtes SQL directement au sein du code applicatif. Cette difficulté est exacerbée lorsque ces requêtes nécessitent une construction dynamique, un scénario où les fichiers externes ou les procédures stockées ne suffisent pas toujours. Bien que MyBatis offre des capacités robustes de génération SQL dynamique via ses mappings XML, il existe des situations où la construction de requêtes directement en Java est inévitable. Dans ces cas, l'API SQL de MyBatis se révèle être un outil précieux, simplifiant la gestion des concaténations, des guillemets, des retours à la ligne, du formatage et surtout, de la logique complexe des clauses conditionnelles (WHERE, AND, OR).

La génération manuelle de SQL dynamique en Java peut rapidement devenir un cauchemar de maintenance, comme l'illustre l'exemple suivant :

String rawSql = "SELECT U.USER_ID, U.LOGIN_NAME, U.EMAIL, U.FIRST_NAME, " +
               "U.LAST_NAME, U.CREATED_ON, U.LAST_MODIFIED " +
               "FROM APP_USER U, USER_ROLE UR " +
               "INNER JOIN DEPARTMENT D ON D.DEPARTMENT_ID = U.DEPARTMENT_ID " +
               "INNER JOIN COMPANY C ON D.COMPANY_ID = C.COMPANY_ID " +
               "WHERE (U.USER_ID = UR.USER_ID AND U.FIRST_NAME LIKE ?) " +
               "OR (U.LAST_NAME LIKE ?) " +
               "GROUP BY U.USER_ID " +
               "HAVING (U.LAST_NAME LIKE ?) " +
               "OR (U.FIRST_NAME LIKE ?) " +
               "ORDER BY U.USER_ID, U.FIRST_NAME";

Simplification avec la Classe SQL de MyBatis

MyBatis 3 introduit la classe utilitaire org.apache.ibatis.jdbc.SQL pour relever ce défi. Grâce à cette classe, la création de requêtes SQL dynamiques est grandement simplifiée. Il suffit d'instancier la classe et d'appeler ses méthodes pour construire la requête étape par étape. Reprenons l'exemple précédent avec l'API SQL :

private String buildUserDetailedQuery() {
   return new SQL() {{
       SELECT("U.USER_ID, U.LOGIN_NAME, U.EMAIL, U.FIRST_NAME");
       SELECT("U.LAST_NAME, U.CREATED_ON, U.LAST_MODIFIED");
       FROM("APP_USER U");
       FROM("USER_ROLE UR");
       INNER_JOIN("DEPARTMENT D ON D.DEPARTMENT_ID = U.DEPARTMENT_ID");
       INNER_JOIN("COMPANY C ON D.COMPANY_ID = C.COMPANY_ID");
       WHERE("U.USER_ID = UR.USER_ID");
       WHERE("U.FIRST_NAME LIKE ?");
       OR();
       WHERE("U.LAST_NAME LIKE ?");
       GROUP_BY("U.USER_ID");
       HAVING("U.LAST_NAME LIKE ?");
       OR();
       HAVING("U.FIRST_NAME LIKE ?");
       ORDER_BY("U.USER_ID");
       ORDER_BY("U.FIRST_NAME");
   }}.toString();
}

L'avantage principal de cette approche est l'élimination des soucis liés aux opérateurs logiques tels que AND ou OR dans les clauses WHERE et HAVING. La classe SQL gère automatiquement l'insertion appropriée de WHERE initial, puis des AND successifs, et les OR lorsque spécifiés, tout en prenant en charge les concaténations de chaînes.

Exemples d'utilisation de la classe SQL

Voici d'autres cas d'utilisation pour diverses opérations SQL :

// Style classe anonyme avec bloc d'initialisation
public String removeUserRecord() {
 return new SQL() {{
   DELETE_FROM("APP_USER");
   WHERE("USER_ID = #{id}");
 }}.toString();
}

// Style constructeur fluent
public String registerNewUser() {
 String sql = new SQL()
   .INSERT_INTO("APP_USER")
   .VALUES("USER_ID, FIRST_NAME", "#{userId}, #{givenName}")
   .VALUES("LAST_NAME", "#{familyName}")
   .toString();
 return sql;
}

// Conditions dynamiques (les paramètres doivent être `final` pour être accessibles dans la classe anonyme)
public String searchUsersByCriteria(final String identifier, final String firstNamePattern, final String lastNamePattern) {
 return new SQL() {{
   SELECT("U.USER_ID, U.LOGIN_NAME, U.FIRST_NAME, U.LAST_NAME");
   FROM("APP_USER U");
   if (identifier != null) {
     WHERE("U.USER_ID LIKE #{identifier}");
   }
   if (firstNamePattern != null) {
     WHERE("U.FIRST_NAME LIKE #{firstNamePattern}");
   }
   if (lastNamePattern != null) {
     WHERE("U.LAST_NAME LIKE #{lastNamePattern}");
   }
   ORDER_BY("U.LAST_NAME");
 }}.toString();
}

public String modifyUserFirstName() {
 return new SQL() {{
   UPDATE("APP_USER");
   SET("FIRST_NAME = #{givenName}");
   WHERE("USER_ID = #{userId}");
 }}.toString();
}

Méthodes de la Classe SQL

Le tableau suivant répertorie les principales méthodes disponibles dans la classe SQL :

Il est important de noter que la classe SQL insère les clauses LIMIT, OFFSET, OFFSET n ROWS et FETCH FIRST n ROWS ONLY telles quelles. Aucune transformation n'est effectuée pour les bases de données qui ne supportent pas ces syntaxes. L'utilisateur doit donc s'assurer de la compatibilité avec la base de données cible pour éviter les erreurs d'exécution.

Utilisation des arguments de longueur variable (Varargs) - Depuis MyBatis 3.4.2

À partir de la version 3.4.2, il est possible d'utiliser des arguments de longueur varible pour simplifier l'écriture :

public String queryDetailedUserSql() {
 return new SQL()
   .SELECT("U.USER_ID", "UR.ROLE_NAME", "U.EMAIL", "U.FIRST_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME")
   .FROM("APP_USER U", "USER_ROLE UR")
   .INNER_JOIN("DEPARTMENT D ON D.DEPARTMENT_ID = U.DEPARTMENT_ID", "COMPANY C ON D.COMPANY_ID = C.COMPANY_ID")
   .WHERE("U.USER_ID = UR.USER_ID", "U.FIRST_NAME LIKE #{name}")
   .ORDER_BY("U.USER_ID", "U.FIRST_NAME")
   .toString();
}

public String insertUserMinimalSql() {
 return new SQL()
   .INSERT_INTO("APP_USER")
   .INTO_COLUMNS("USER_ID", "FIRST_NAME")
   .INTO_VALUES("#{id}", "#{givenName}")
   .toString();
}

public String updateUserContactSql() {
 return new SQL()
   .UPDATE("APP_USER")
   .SET("EMAIL = #{userEmail}", "PHONE_NUMBER = #{userPhone}")
   .WHERE("USER_ID = #{id}")
   .toString();
}

Construction d'instructions d'insertion en lot (Batch Inserts) - Depuis MyBatis 3.5.2

Depuis la version 3.5.2, vous pouvez construire des insertions en lot :

public String batchInsertUsersSql() {
 // INSERT INTO APP_USER (USER_ID, FIRST_NAME)
 //     VALUES (#{userA.id}, #{userA.givenName}) , (#{userB.id}, #{userB.givenName})
 return new SQL()
   .INSERT_INTO("APP_USER")
   .INTO_COLUMNS("USER_ID", "FIRST_NAME")
   .INTO_VALUES("#{userA.id}", "#{userA.givenName}")
   .ADD_ROW()
   .INTO_VALUES("#{userB.id}", "#{userB.givenName}")
   .toString();
}

Requêtes SELECT avec pagination (LIMIT/OFFSET) - Depuis MyBatis 3.5.2

Depuis la version 3.5.2, la construction de requêtes SELECT avec des clauses de pagination est prise en charge :

public String fetchUsersWithPagination() {
 // SELECT user_id, first_name FROM APP_USER
 //     LIMIT #{maxRows} OFFSET #{startIndex}
 return new SQL()
   .SELECT("user_id", "first_name")
   .FROM("APP_USER")
   .LIMIT("#{maxRows}")
   .OFFSET("#{startIndex}")
   .toString();
}

public String fetchUsersWithRowOffset() {
 // SELECT user_id, first_name FROM APP_USER
 //     OFFSET #{startIndex} ROWS FETCH FIRST #{maxRows} ROWS ONLY
 return new SQL()
   .SELECT("user_id", "first_name")
   .FROM("APP_USER")
   .OFFSET_ROWS("#{startIndex}")
   .FETCH_FIRST_ROWS_ONLY("#{maxRows}")
   .toString();
}

SqlBuilder et SelectBuilder (Obsolètes)

Avant la version 3.2, l'implémentation de la génération SQL dynamique était différente, utilisant des variables ThreadLocal pour masquer certaines contraintes du DSL Java. Avec l'évolution des frameworks de construction SQL et la familiarisation des développeurs avec le pattern "builder" et les classes anonymes, les classes SelectBuilder et SqlBuilder basées sur cette approche ont été dépréciées.

Les méthodes suivantes sont spécifiques aux classes obsolètes SqlBuilder et SelectBuilder :

SelectBuilder et SqlBuilder n'étaient pas magiques, mais leur fonctionnement reposait sur l'importation statique de leurs méthodes et l'utilisation de variables ThreadLocal pour offrir une syntaxe concise. Pour les utiliser, il fallait importer statiquement les méthodes de l'une des classes (mais pas les deux simultanément) :

import static org.apache.ibatis.jdbc.SelectBuilder.*;

import static org.apache.ibatis.jdbc.SqlBuilder.*;

Puis, les requêtes étaient construites comme suit :

/* Déprécié */
public String fetchProductNamesSql() {
 BEGIN(); // Réinitialise l'état ThreadLocal
 SELECT("PRODUCT_NAME");
 FROM("PRODUCTS");
 return SQL();
}

/* Déprécié */
private String fetchDetailedUserLegacySql() {
 BEGIN(); // Réinitialise l'état ThreadLocal
 SELECT("U.ID, U.LOGIN, U.EMAIL, U.NOM_COMPLET");
 SELECT("U.DATE_CREATION, U.DATE_MODIFICATION");
 FROM("UTILISATEUR U");
 FROM("COMPTE C");
 INNER_JOIN("SERVICE S ON S.ID = U.SERVICE_ID");
 INNER_JOIN("ENTREPRISE E ON S.ENTREPRISE_ID = E.ID");
 WHERE("U.ID = C.UTILISATEUR_ID");
 WHERE("U.NOM like ?");
 OR();
 WHERE("U.PRENOM like ?");
 GROUP_BY("U.ID");
 HAVING("U.PRENOM like ?");
 OR();
 HAVING("U.NOM like ?");
 ORDER_BY("U.ID");
 ORDER_BY("U.NOM_COMPLET");
 return SQL();
}