IBatis (MyBatis): Trabalhando com Queries Dinâmicas (SQL)

22 Mar 2011

6 mins read

Este tutorial tem como objetivo fazer o setup de uma aplicação Java usando iBatis (MyBatis) demonstrando o uso de queries (sql) dinâmicas.

Pré-Requisitos

Para este tutorial usei:

IDE: Eclipse (você pode usar a sua IDE favorita)
DataBase: MySQL
Libs/jars: Mybatis, MySQL conector e JUnit (para testes)

No Eclipse, a estrutura do seu projeto vai ficar assim:

Dados de Exemplo

Execute o script que está dentro da pasta sql antes de começar o projeto. Este arquivo contém os dados de exemplo usados neste tutorial.

1 - Article POJO

Representei o POJO que vamos usar neste tutorial com um diagrama UML, mas você pode fazer o download do código fonte completo no final deste tutorial.

O objetivo desse tutorial é demonstrar como obter os dados de um Artigo (Article) do banco de dados usando SQL dinâmico para filtrar os dados.

2 - Article Mapper - XML

Umas das features mais poderosas do Mybatis é a capacidade de lidar com SQL dinâmico. Se você tem alguma experiência com JDBC ou qualquer outro framework similar, você sabe como é doloroso ficar concatenando strings de SQL, ficar atento para não esquecer nenhum espaço ou vírgula. SQL dinâmicos podem ser bem dolorosos.

Trabalhar com SQL dinâmicos nunca será uma festa, mas o MyBatis certamente ajuda nessa situação com uma linguagem dinâmica bem poderosa.

Os elementos de SQL Dinâmico devem ser familiares para quem já usou JSTL ou qualquer XML baseado em processador de texto. Nas versões anteriores do MyBatis, existiam vários elementos que precisavam ser compreendidos. O Mybatis 3 melhorou muito nesse quesito, e agora existem apenas metade daqueles elementos, que fazem tudo o que os outros elementos faziam. o MyBatis utiliza expressões baseadas em OGNL para eliminar os outros elementos que não são mais usados.

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

Vamos explicar cada um desses elementos com exemplos.

1 - Primeiro cenário: queremos buscar todos os artigos que um filtro opcional: title (título), ou seja, se o usuário quiser filtrar por título, ele/ela pode faz isso. Iremos filtrar os artigos que fazem o match com o título especificado, caso contrário iremos buscar todas as informações. Vamos então usar uma condição (if):

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByTitle" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
WHERE id_status = 1
<if test="title != null">
AND title LIKE #{title}
</if>
</select>
[/code]

2 - Segundo cenário: agora temos dois filtros opcionais: title (título) e author (autor). O usuário pode especificar ambos, nenhum ou apenas um. Então vamos implementar duas condições:

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByTitleAndAuthor" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
WHERE id_status = 1
<if test="title != null">
AND title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</select>
[/code]

3 - Terceiro cenário: agora queremos dar ao usuário apenas uma opção: o usuário deverá especificar apenas um dos filtros: title ou author, caso contrário iremos buscar todos os artigos da categoria (category) iBatis. Então vamos usar um elemento Choose:

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByTitleOrAuthorOrCategory"
parameterType="com.loiane.model.Article" resultType="Article">
SELECT id, title, author
FROM article
WHERE id_status = 1
<choose>
<when test="title != null">
AND title LIKE #{title}
</when>
<when test="author != null">
AND author LIKE #{author}
</when>
<otherwise>
AND id_category = 3
</otherwise>
</choose>
</select>
[/code]

4 - Quarto cenário: dê uma olhada nos três exemplos anteriores. Todos eles têm uma condição em comum: WHERE id_status = 1. Ou seja, estamos filtrando também os artigos que estão ativos automaticamente. Vamos remover essa condição "automática" para deixar o cenário um pouco mais interessante.

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByTitleAndAuthor" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
WHERE
<if test="title != null">
title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</select>
[/code]

E se title e author forem nulls? O MyBatis irá gerar o seguinte sql:

[code lang="sql" firstline="1" toolbar="true" collapse="false" wraplines="false"]
SELECT id, title, author
FROM article
WHERE
[/code]

E se apenas author não for null? O MyBatis irá gerar o seguinte sql:

[code lang="sql" firstline="1" toolbar="true" collapse="false" wraplines="false"]
SELECT id, title, author
FROM article
WHERE
AND author LIKE #{author}
[/code]

E temos um FAIL!

Como consertar isso?

5 - Quinto cenário: queremos obter as informações dos artigos com dois filtros opcionais: title e author. Para evitar o que aconteceu no quarto cenário, vamos usar o elemento Where:

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByTitleAndAuthorDynamic" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
<where>
<if test="title != null">
title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</where>
</select>
[/code]

O MyBatis tem uma resposta bem simple que irá funcionar em 90% dos casos. E nos casos em que não funciona, você pode customizar para funcionar.

E elemento where sabe inserir o "WHERE" apenas se retornar algum conteúdo das tags. Se o conteúdo começar com "AND" ou "OR", o MyBatis sabe que precisa tirá-los.

Se o elemento where não funcionar como você deseja, você pode customizar definindo o seu próprio elemento trim. Por exemplo, o elemento trim equivalente ao elemento where é:

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByTitleAndAuthorDynamic2" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="title != null">
title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</trim>
</select>
[/code]

Você também pode usar o elemento trim com um SET (update ..... set).

6 - Sexto cenário: o usuário vai escolher quais categorias um artigo pode pertencer para fazer o filtro. Então nesse caso, teremos uma lista (collection), e vamos iterar uma coleção e para isso vamos usar o elemento foreach:

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<select id="selectArticleByListCategories" resultType="Article">
SELECT id, title, author
FROM article
WHERE id_category IN
<foreach item="category" index="index" collection="list" open="("
separator="," close=")">
#{category}
</foreach>
</select>
[/code]

O elemento foreach é muito poderoso, e permite que você especifique uma collection, declare um item e variáveis de index que podem ser usadas dentro do corpo do elemento. Também lhe permite especificar as strings de abertura e fechamento, e adicionar um separator para ser colocado entrar as iterações. O elemento é bem espero e não irá colocar separators extras por acidente.

Nota: você pode pode passar uma instância de uma List ou Array para o MyBatis como parâmetro. Quando fizer isso, o MyBatis automaticamente irá transformar num Map, e a chave será o nome. Instâncias de List terão como key "list" e instâncias de Array terão como key "array".

O arquivo Article.xml completo fica assim:

[code lang="xml" firstline="1" toolbar="true" collapse="false" wraplines="false"]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="Article">

<select id="selectArticleByTitle" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
WHERE id_status = 1
<if test="title != null">
AND title LIKE #{title}
</if>
</select>

<select id="selectArticleByTitleAndAuthor" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
WHERE id_status = 1
<if test="title != null">
AND title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</select>

<select id="selectArticleByTitleOrAuthorOrCategory" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
WHERE id_status = 1
<choose>
<when test="title != null">
AND title LIKE #{title}
</when>
<when test="author != null">
AND author LIKE #{author}
</when>
<otherwise>
AND id_category = 3
</otherwise>
</choose>
</select>

<select id="selectArticleByTitleAndAuthorDynamic" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
<where>
<if test="title != null">
title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</where>
</select>

<select id="selectArticleByTitleAndAuthorDynamic2" parameterType="com.loiane.model.Article"
resultType="Article">
SELECT id, title, author
FROM article
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="title != null">
title LIKE #{title}
</if>
<if test="author != null">
AND author LIKE #{author}
</if>
</trim>
</select>

<select id="selectArticleByListCategories" resultType="Article">
SELECT id, title, author
FROM article
WHERE id_category IN
<foreach item="category" index="index" collection="list"
open="(" separator="," close=")">
#{category}
</foreach>
</select>
</mapper>
[/code]