iBATIS - SQL dinámico

El SQL dinámico es una característica muy poderosa de iBATIS. A veces tienes que cambiar el criterio de la cláusula WHERE según el estado de tu objeto de parámetro. En tales situaciones, iBATIS proporciona un conjunto de etiquetas SQL dinámicas que se pueden usar dentro de declaraciones mapeadas para mejorar la reutilización y la flexibilidad de SQL.

Toda la lógica se coloca en un archivo .XML usando algunas etiquetas adicionales. A continuación se muestra un ejemplo en el que la instrucción SELECT funcionaría de dos maneras:

  • Si pasa una identificación, entonces devolverá todos los registros correspondientes a esa identificación.
  • De lo contrario, devolvería todos los registros donde el ID de empleado se establece en 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>

Puede verificar una condición usando la etiqueta <isNotEmpty> de la siguiente manera. Aquí se agregaría una condición solo cuando una propiedad pasada no esté vacía.

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

Si desea una consulta en la que podamos seleccionar una identificación y / o el nombre de un empleado, su declaración SELECT sería la siguiente:

..................
<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>
..................

Ejemplo de SQL dinámico

El siguiente ejemplo muestra cómo puede escribir una instrucción SELECT con SQL dinámico. Considere, tenemos la siguiente tabla EMPLOYEE en 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)
);

Supongamos que esta tabla tiene solo un registro de la siguiente manera:

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

Clase POJO para empleados

Para realizar la operación de lectura, tengamos una clase de empleado en Employee.java de la siguiente manera:

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 */

Archivo Employee.xml

Para definir la declaración de mapeo SQL usando iBATIS, agregaríamos la siguiente etiqueta <select> modificada en Employee.xml y dentro de esta definición de etiqueta, definiríamos un "id" que se usará en IbatisReadDy.java para ejecutar la consulta Dynamic SQL SELECT en base de datos.

<?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>

La declaración SELECT anterior funcionaría de dos maneras:

  • Si pasa una ID, devuelve los registros correspondientes a esa ID. De lo contrario, devuelve todos los registros.

Archivo IbatisReadDy.java

Este archivo tiene lógica de nivel de aplicación para leer registros condicionales de la tabla de empleados -

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 ");
   }
}

Compilación y ejecución

Estos son los pasos para compilar y ejecutar el software mencionado anteriormente. Asegúrese de haber configurado PATH y CLASSPATH correctamente antes de proceder con la compilación y ejecución.

  • Cree Employee.xml como se muestra arriba.
  • Cree Employee.java como se muestra arriba y compílelo.
  • Cree IbatisReadDy.java como se muestra arriba y compílelo.
  • Ejecute IbatisReadDy binary para ejecutar el programa.

Obtendría el siguiente resultado y se leería un registro de la tabla EMPLOYEE.

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

Pruebe el ejemplo anterior pasando nullcomo smc.queryForList ("Employee.findByID", nulo) .

Expresiones iBATIS OGNL

iBATIS proporciona poderosas expresiones basadas en OGNL para eliminar la mayoría de los otros elementos.

  • si declaración
  • elegir, cuando, de lo contrario Declaración
  • donde Declaración
  • declaración foreach

La declaración if

Lo más común que se puede hacer en SQL dinámico es incluir condicionalmente una parte de una cláusula where. Por ejemplo

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

Esta declaración proporciona un tipo de funcionalidad de búsqueda de texto opcional. Si no pasa ningún título, se devuelven todos los blogs activos. Pero si pasa un título, buscará un título con ellike condición.

Puede incluir varios if condiciones de la siguiente manera:

<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>

Las declaraciones elegir, cuándo y de otro modo

iBATIS ofrece una chooseelemento que es similar a la declaración de cambio de Java. Ayuda a elegir solo un caso entre muchas opciones.

El siguiente ejemplo buscaría solo por título si se proporciona uno, luego solo por autor si se proporciona uno. Si no se proporciona ninguno, solo devuelve blogs destacados:

<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 declaración where

Eche un vistazo a nuestros ejemplos anteriores para ver qué sucede si no se cumple ninguna de las condiciones. Terminaría con un SQL que se ve así:

SELECT * FROM BLOG
WHERE

Esto fallaría, pero iBATIS tiene una solución simple con un simple cambio, todo funciona 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>

los whereEl elemento inserta un DONDE solo cuando las etiquetas contenedoras devuelven algún contenido. Además, si ese contenido comienza con Y u O, sabe quitarlo.

La declaración de foreach

El elemento foreach le permite especificar una colección y declarar el elemento y las variables de índice que se pueden usar dentro del cuerpo del elemento.

También le permite especificar cadenas de apertura y cierre, y agregar un separador para colocar entre iteraciones. Puedes construir unIN condición de la siguiente manera -

<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>