MyBatis – SQLs Dinámicas

5
12052

MyBatis – SQLs Dinámicas

0. Índice de contenidos.

1. Introducción

En muchas ocasiones nos encontramos con la necesidad de generar una query dinámica para cubrir los requisitos del desarrollo, ya sea porque las condiciones de la consulta dependen del estado de alguna variable o para evitar la generación de multitud de consultas estáticas que cubran todas las posibilidades.

Cuando la capa de datos está implementada directamente con JDBC, la solución pasaría por generar dinámicamente la query que le pasamos al PreparedStatement, estableciendo las condiciones y los bucles necesarios para generarla. Al final, tenemos consultas embebidas en el código, entre multitud de condiciones y bucles, dificultando su legibilidad y mantenimiento.

En este tutorial me gustaría repasar la solución propuesta por MyBatis para la generación de estas queries dinámicas y comprobar la mejora que supone tanto en legibilidad como en mantenimiento respecto a la generación de queries dinámicas generadas por código, manualmente, tal como se hacía en JDBC.

2. Entorno

Para realizar este tutorial se ha empleado el siguiente entorno de desarrollo:

  • Hardware: Mac Book Pro 15″ Intel Core i7 2,8 GHz, 16 GB RAM
  • Sistema Operativo: Mac OS X Yosemite
  • MySQL Community Server 5.6.22

3. Preparación de entorno.

Para realizar este tutorial, vamos a partir del siguiente modelo de datos:

4. Limitaciones de las consultas estáticas

Como comentaba, en multitud de casos las queries estáticas no son suficientes para resolver las necesidades que se nos presentan. Es en estos casos donde necesitamos montar queries dinámicas que se formen de acuerdo a los parámetros recibidos en los distintos flujos de la aplicación.

Un ejemplo que se repite constantemente es la aplicación de filtros en informes web.

Veamos un ejemplo práctico. Imaginemos que, basándonos en el modelo de datos anterior, tenemos un informe en el que queremos mostrar para cada curso los alumnos del mismo.

Este informe lo resolveríamos con una query del siguiente estilo:

Con la query anterior recuperaríamos toda la información, sobre la que podremos aplicar los filtros necesarios.

Ahora definamos un filtro opcional por curso, en el que podamos seleccionar o bien todos los cursos (sin indicar ninguno) o bien el id de un curso concreto.Este caso podría solucionarse facilmente basándonos en la misma query estática:

Pero supongamos que queremos incluir también un filtro por nombre o parte del nombre del curso. Este caso también lo podríamos solucionar con una query estática, pero se va complicando más el tema:

Y podríamos seguir incluyendo filtros por cada uno de los campos del informe.

Los problemas más claros de este planteamientoson las siguientes:

  • Desde el punto de vista de la lógica funcional, se está depositando en la base de datos la responsabilidad de aplicar o no cada uno de los filtros, cuando debería recaer en la capa de acceso a datos.
  • Desde el punto de vista del mantenimiento, la complejidad de la query generada aumenta con cada uno de los posibles filtros aplicables, lo que aumenta las posibilidades de error tanto durante el desarrollo como dura el mantenimiento.
  • Desde el punto de vista del rendimiento, se incrementan los filtros aplicados a la query incluso en los casos en que no se quiera aplicar ningún filtro, cuando esto debería ser controlado por la capa de acceso a datos de la aplicación.

Veamos cómo MyBatis plantea la resolución de estos problemas.

5. Consultas dinámicas en MyBatis.

Para resolver este y otros casos, MyBatis proporciona una serie de tags que se pueden emplear para definir consultas dinámicas en sus mappers:

  • El tag <if>:

    Mediante este tag podremos definir segmentos de consulta como opcionales, incluyéndose únicamente cuando se cumpla una determinada condición.
    Su especificación es:

    Así podríamos codificar la query anterior empleando el tag <if> de la siguiente manera:

  • El tag <choose>:

    De igual forma, mediante este tag podremos establecer distintos segmentos de query dependiendo del valor de una variable. Su especificación es:

    Si por ejemplo añadieramos un selector «searchBy» para indicar por qué campo queremos filtrar, la query quedaría de la siguiente manera:

  • El tag <where>:

    Este tag se emplea cuando todas las condiciones de la query son opcionales. Este tag lo que hace es añadir la palabra reservada WHERE únicamente cuando sea necesario, cuando alguna de las condiciones opcionales es añadida. De igual forma, eliminará cualquier OR o AND que le preceda. Su especificación es:

    Por ejemplo, si quisieramos generar una query que nos filtrara el listado de cursos únicamente si el usuario ha seleccionado un filtro, podríamos hacerlo de la siguiente manera:

    De esta forma, si el usuario ha seleccionado un filtro por id de curso se incluirá la claúsula WHERE y la condición id_curso = #{idCurso} y si por el contrario seleccionó la busqueda por nombre de curso, incluirá la cláusula WHERE, eliminará la condición AND que le precede e incluirá la condición nombre_curso like '%#{curso}%'

  • El tag <trim>:

    Es bastante similar al tag <WHERE> pero ofrece más opciones. Veamos su especificación:

    Donde prefix y sufix se emplean para añadir una cadena antes y después del segmento de sql incluido. Ambos son opcionales y no son excluyentes y se incluirán únicamente si existe un segmento de sql, al igual que el tag <where>. De igual forma, mediante los parámetros prefixOverrides y sufixOverrides se puede eliminar una o varias cadenas del inicio o del fin del segmento de SQL. Normalmente se emplean para eliminar los AND y OR que sobran, ya sea al inicio o al fin del segmento. Para indicar que elimine los AND y OR sobrantes, se establecería así: prefixOverrides=»AND | OR»

    .

    La siguiente configuración de TRIM sería equivalente al tag <where>:

  • El tag <foreach>:

    Con este tag podremos iterar sobre una colección, generando un segmento de SQL para cada uno de los elementos. Si por ejemplo quisieramos buscar los cursos comunes a dos alumnos, podríamos incluir un campo List al filtro e indicar en ese campo los ids de los alumnos que buscamos. Incluiremos un <trim> para formar el conjunto de condiciones.La query quedaría así:

  • El tag <set>:

    Por último, este tag es muy similar al tag pero para las sentencias UPDATE. En este caso, este tag incluirá la palabra reservada SET si cualquiera de los <if> internos se cumple y eliminará las comas sobrantes. Veamos como ejemplo la sentencia de actualización de los cursos:

    La misma sentencia generada con un tag <trim> sería así:

6. Conclusiones.

Como habéis podido ver, mediante los tags de MyBatis conseguimos definir SQLs dinámicas de una forma más estructurada evitando tener que incluir condiciones para ver si tenemos que incluir o no un WHERE, un AND o un OR en la query. Como ejemplo, a continuación podemos ver la query que hemos empleado en los ejemplos generada con MyBatis y el código equivalente si lo generáramos con JDBC:

Versión JDBC:

Versión MyBatis:

Como conclusión, comparando ambas versiones, se puede ver claramente que con el empleo de las queries dinámicas de MyBatis no solo reducimos el código, sino que además lo hacemos más legible y estructurado, y por tanto, más mantenible.

5 Comentarios

  1. Este tutorial me ha sido de bastante utilidad, muchas gracias por compartirlo!

    Una consulta, cuando quiero aplicar el ejemplo con el comodin % para usar LIKE. El Sql me retorna ORA-00911: invalid character. Se debe de realizar alguna configuracion especial para el uso de estos?

  2. Muy buen tutorial, de bastante ayuda. Queria preguntar si sabe como declarar una constante, me explico, tengo la siguiente sentencia

    select ORDER_ID, DATEORDERED, ORDER_STATUS
    where ORDER_ID= #{orderid}
    and C_ORDERLINE.C_ORDER_ID=C_ORDER.C_ORDER_ID order by C_ORDER.C_ORDER_ID
    and ORDER_STATUS = ‘PEN’

    Pero sale el siguiente error:
    ### Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

    Y es por la siguiente linea and ORDER_STATUS = ‘PEN’

Dejar respuesta

Please enter your comment!
Please enter your name here