iBATIS - SQL dynamique

Dynamic SQL est une fonctionnalité très puissante d'iBATIS. Parfois, vous devez modifier le critère de la clause WHERE en fonction de l'état de votre objet paramètre. Dans de telles situations, iBATIS fournit un ensemble de balises SQL dynamiques qui peuvent être utilisées dans des instructions mappées pour améliorer la réutilisabilité et la flexibilité du SQL.

Toute la logique est placée dans un fichier .XML en utilisant des balises supplémentaires. Voici un exemple où l'instruction SELECT fonctionnerait de deux manières -

  • Si vous transmettez un identifiant, il renverra tous les enregistrements correspondant à cet identifiant.
  • Sinon, il renverrait tous les enregistrements où l'ID d'employé est défini sur NULL.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Employee">

   <select id="findByID" resultClass="Employee">
      SELECT * FROM EMPLOYEE
		
      <dynamic prepend="WHERE ">
         <isNull property="id">
            id IS NULL
         </isNull>
			
         <isNotNull property="id">
            id = #id#
         </isNotNull>
      </dynamic>
		
   </select>
</sqlMap>

Vous pouvez vérifier une condition à l'aide de la balise <isNotEmpty> comme suit. Ici, une condition ne serait ajoutée que lorsqu'une propriété passée n'est pas vide.

..................
<select id="findByID" resultClass="Employee">
   SELECT * FROM EMPLOYEE
	
   <dynamic prepend="WHERE ">
      <isNotEmpty property="id">
         id = #id#
      </isNotEmpty>
   </dynamic>
	
</select>
..................

Si vous voulez une requête où nous pouvons sélectionner un identifiant et / ou le prénom d'un employé, votre instruction SELECT serait la suivante -

..................
<select id="findByID" resultClass="Employee">
   SELECT * FROM EMPLOYEE
	
   <dynamic prepend="WHERE ">
      <isNotEmpty prepend="AND" property="id">
         id = #id#
      </isNotEmpty>
		
      <isNotEmpty prepend="OR" property="first_name">
         first_name = #first_name#
      </isNotEmpty>
   </dynamic>
</select>
..................

Exemple SQL dynamique

L'exemple suivant montre comment vous pouvez écrire une instruction SELECT avec SQL dynamique. Considérez, nous avons la table EMPLOYEE suivante dans MySQL -

CREATE TABLE EMPLOYEE (
   id INT NOT NULL auto_increment,
   first_name VARCHAR(20) default NULL,
   last_name  VARCHAR(20) default NULL,
   salary     INT  default NULL,
   PRIMARY KEY (id)
);

Supposons que cette table ne comporte qu'un seul enregistrement comme suit -

mysql> select * from EMPLOYEE;
+----+------------+-----------+--------+
| id | first_name | last_name | salary |
+----+------------+-----------+--------+
|  1 | Zara       | Ali       |   5000 |
+----+------------+-----------+--------+
1 row in set (0.00 sec)

Classe POJO des employés

Pour effectuer l'opération de lecture, ayons une classe Employee dans Employee.java comme suit -

public class Employee {
   private int id;
   private String first_name; 
   private String last_name;   
   private int salary;  

   /* Define constructors for the Employee class. */
   public Employee() {}
  
   public Employee(String fname, String lname, int salary) {
      this.first_name = fname;
      this.last_name = lname;
      this.salary = salary;
   }

   /* Here are the method definitions */
   public int getId() {
      return id;
   }
	
   public String getFirstName() {
      return first_name;
   }
	
   public String getLastName() {
      return last_name;
   }
	
   public int getSalary() {
      return salary;
   }
	
} /* End of Employee */

Fichier Employee.xml

Pour définir l'instruction de mappage SQL à l'aide d'iBATIS, nous ajouterions la balise <select> modifiée suivante dans Employee.xml et à l'intérieur de cette définition de balise, nous définirions un "id" qui sera utilisé dans IbatisReadDy.java pour exécuter la requête Dynamic SQL SELECT sur base de données.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Employee">
   <select id="findByID" resultClass="Employee">
      SELECT * FROM EMPLOYEE
	
      <dynamic prepend="WHERE ">
         <isNotNull property="id">
            id = #id#
         </isNotNull>
      </dynamic>
		
   </select>
</sqlMap>

L'instruction SELECT ci-dessus fonctionnerait de deux manières -

  • Si vous transmettez un ID, il renvoie les enregistrements correspondant à cet ID. Sinon, il renvoie tous les enregistrements.

Fichier IbatisReadDy.java

Ce fichier a une logique au niveau de l'application pour lire les enregistrements conditionnels de la table Employee -

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.*;
import java.sql.SQLException;
import java.util.*;

public class IbatisReadDy{
   public static void main(String[] args) throws IOException,SQLException{
   
      Reader rd=Resources.getResourceAsReader("SqlMapConfig.xml");
      SqlMapClient smc=SqlMapClientBuilder.buildSqlMapClient(rd);

      /* This would read all records from the Employee table.*/
      System.out.println("Going to read records.....");
      Employee rec = new Employee();
      rec.setId(1);

      List <Employee> ems = (List<Employee>)  
         smc.queryForList("Employee.findByID", rec);
      Employee em = null;
		
      for (Employee e : ems) {
         System.out.print("  " + e.getId());
         System.out.print("  " + e.getFirstName());
         System.out.print("  " + e.getLastName());
         System.out.print("  " + e.getSalary());
         em = e; 
         System.out.println("");
      }    
      System.out.println("Records Read Successfully ");
   }
}

Compilation et exécution

Voici les étapes pour compiler et exécuter le logiciel mentionné ci-dessus. Assurez-vous d'avoir correctement défini PATH et CLASSPATH avant de procéder à la compilation et à l'exécution.

  • Créez Employee.xml comme indiqué ci-dessus.
  • Créez Employee.java comme indiqué ci-dessus et compilez-le.
  • Créez IbatisReadDy.java comme indiqué ci-dessus et compilez-le.
  • Exécutez le binaire IbatisReadDy pour exécuter le programme.

Vous obtiendrez le résultat suivant et un enregistrement serait lu à partir de la table EMPLOYEE.

Going to read records.....
   1  Zara  Ali  5000
Record Reads Successfully

Essayez l'exemple ci-dessus en passant nullcomme smc.queryForList ("Employee.findByID", null) .

Expressions iBATIS OGNL

iBATIS fournit de puissantes expressions basées sur OGNL pour éliminer la plupart des autres éléments.

  • if Déclaration
  • choisir, quand, sinon Déclaration
  • où Déclaration
  • Déclaration foreach

La déclaration if

La chose la plus courante à faire en SQL dynamique est d'inclure conditionnellement une partie d'une clause where. Par exemple -

<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
   WHERE state = 'ACTIVE.
	
   <if test="title != null">
      AND title like #{title}
   </if>
	
</select>

Cette instruction fournit un type de fonctionnalité de recherche de texte facultatif. Si vous ne transmettez aucun titre, tous les blogs actifs sont renvoyés. Mais si vous passez un titre, il cherchera un titre avec lelike état.

Vous pouvez inclure plusieurs if conditions comme suit -

<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
   WHERE state = 'ACTIVE.
	
   <if test="title != null">
      AND title like #{title}
   </if>
	
   <if test="author != null">
      AND author like #{author}
   </if>
	
</select>

Les déclarations de choix, de quand et autrement

iBATIS propose un chooseélément similaire à l'instruction switch de Java. Cela permet de ne choisir qu'un seul cas parmi de nombreuses options.

L'exemple suivant rechercherait uniquement par titre s'il en est fourni un, puis uniquement par auteur s'il en est fourni un. Si aucun n'est fourni, il ne renvoie que les blogs en vedette -

<select id="findActiveBlogWithTitleLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
   WHERE state = 'ACTIVE.
	
   <choose>
      <when test="title != null">
         AND title like #{title}
      </when>
		
      <when test="author != null and author.name != null">
         AND author like #{author}
      </when>
		
      <otherwise>
         AND featured = 1
      </otherwise>
   </choose>
	
</select>

La déclaration where

Jetez un œil à nos exemples précédents pour voir ce qui se passe si aucune des conditions n'est remplie. Vous vous retrouveriez avec un SQL qui ressemble à ceci -

SELECT * FROM BLOG
WHERE

Cela échouerait, mais iBATIS a une solution simple avec un simple changement, tout fonctionne bien -

<select id="findActiveBlogLike" parameterType="Blog" resultType="Blog">
   SELECT * FROM BLOG
	
   <where>
      <if test="state != null">
         state = #{state}
      </if>
		
      <if test="title != null">
         AND title like #{title}
      </if>
		
      <if test="author != null>
         AND author like #{author}
      </if>
   </where>
	
</select>

le whereelement insère un WHERE uniquement lorsque les balises contenant renvoient un contenu. De plus, si ce contenu commence par AND ou OR, il sait le supprimer.

La déclaration foreach

L'élément foreach vous permet de spécifier une collection et de déclarer des variables d'élément et d'index qui peuvent être utilisées dans le corps de l'élément.

Il vous permet également de spécifier des chaînes d'ouverture et de fermeture et d'ajouter un séparateur à placer entre les itérations. Vous pouvez construire unIN condition comme suit -

<select id="selectPostIn" resultType="domain.blog.Post">
   SELECT *
   FROM POST P
   WHERE ID in
	
   <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
      #{item}
   </foreach>
	
</select>