The SQL Builder Class

El Problema

Una de las cosas más tediosas que un programador Java puede llegar a tener que hacer es incluir código SQL en código Java. Normalmente esto se hace cuando es necesario generar dinámicamente el SQL – de otra forma podrías externalizar el código en un fichero o un procedimiento almacenado. Como ya has visto, MyBatis tiene una respuesta potente a la generación dinámica de SQL mediante las capacidades del mapeo XML. Sin embargo, en ocasiones se hace necesario construir una sentencia SQL dentro del código Java. En este caso, MyBatis tiene una funcionalidad más para ayudarte en ello, antes de que comiences con el típico lío de signos de suma, comillas, líneas nuevas, problemas de formato y condicionales anidados para tratar con las comas extra y las conjunciones AND… Realmente, generar código dinámico en java, puede ser una verdadera pesadilla. Por ejemplo:

String sql = "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, "
"P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON " +
"FROM PERSON P, ACCOUNT A " +
"INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID " +
"INNER JOIN COMPANY C on D.COMPANY_ID = C.ID " +
"WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) " +
"OR (P.LAST_NAME like ?) " +
"GROUP BY P.ID " +
"HAVING (P.LAST_NAME like ?) " +
"OR (P.FIRST_NAME like ?) " +
"ORDER BY P.ID, P.FULL_NAME";

La Solución

MyBatis 3 introduce un concepto un tanto distinto para tratar con el problema. Con la clase SQL, puedes crear una sentencia SQL en un sólo paso invocando a sus métodos. El ejemplo anterior tendría este aspecto si se rescribe con la clase SQL:

private String selectPersonSql() {
  return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
    FROM("PERSON P");
    FROM("ACCOUNT A");
    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
    WHERE("P.ID = A.ID");
    WHERE("P.FIRST_NAME like ?");
    OR();
    WHERE("P.LAST_NAME like ?");
    GROUP_BY("P.ID");
    HAVING("P.LAST_NAME like ?");
    OR();
    HAVING("P.FIRST_NAME like ?");
    ORDER_BY("P.ID");
    ORDER_BY("P.FULL_NAME");
  }}.toString();
}

¿Qué hay de especial en este ejemplo? Bien, si lo miras detenidamente, verás que no hay que preocuparse de duplicar “AND”s, o elegir entre “WHERE” o “AND”, o ninguno de ambos! La clase SQL se ocupa de colocar el "WHERE" donde debe de ir, si debe usarse "AND" o no y de realizar todas las concatenaciones de Strings.

La clase SQL

Aqui van algunos ejemplos:

// Anonymous inner class
public String deletePersonSql() {
  return new SQL() {{
    DELETE_FROM("PERSON");
    WHERE("ID = #{id}");
  }}.toString();
}

// Builder / Fluent style
public String insertPersonSql() {
  String sql = new SQL()
    .INSERT_INTO("PERSON")
    .VALUES("ID, FIRST_NAME", "#{id}, #{firstName}")
    .VALUES("LAST_NAME", "#{lastName}")
    .toString();
  return sql;
}

// With conditionals (note the final parameters, required for the anonymous inner class to access them)
public String selectPersonLike(final String id, final String firstName, final String lastName) {
  return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FIRST_NAME, P.LAST_NAME");
    FROM("PERSON P");
    if (id != null) {
      WHERE("P.ID like #{id}");
    }
    if (firstName != null) {
      WHERE("P.FIRST_NAME like #{firstName}");
    }
    if (lastName != null) {
      WHERE("P.LAST_NAME like #{lastName}");
    }
    ORDER_BY("P.LAST_NAME");
  }}.toString();
}

public String deletePersonSql() {
  return new SQL() {{
    DELETE_FROM("PERSON");
    WHERE("ID = #{id}");
  }}.toString();
}

public String insertPersonSql() {
  return new SQL() {{
    INSERT_INTO("PERSON");
    VALUES("ID, FIRST_NAME", "#{id}, #{firstName}");
    VALUES("LAST_NAME", "#{lastName}");
  }}.toString();
}

public String updatePersonSql() {
  return new SQL() {{
    UPDATE("PERSON");
    SET("FIRST_NAME = #{firstName}");
    WHERE("ID = #{id}");
  }}.toString();
}

Metodo	Descripción
`SELECT(String)` `SELECT(String...)`	Comienza o añade a una sentencia SELECT. Se puede invocar más de una vez y los parámetros se irán añadiendo a la sentencia SELECT. Los parámetros son normalmente una lista de columnas o alias separados por comas, pero puede ser cualquier cosa que acepte el driver de base de datos.
`SELECT_DISTINCT(String)` `SELECT_DISTINCT(String...)`	Comienza o añade a una sentencia SELECT, también añade la palabra clave “DISTINCT” a la sentencia generada. Se puede invocar más de una vez y los parámetros se irán añadiendo a la sentencia SELECT. Los parámetros son normalmente una lista de columnas o alias separados por comas, pero puede ser cualquier cosa que acepte el driver de base de datos.
`FROM(String)` `FROM(String...)`	Comienza o añade a una cláusula FROM. Se puede invocar más de una vez y los parámetros se irán añadiendo a la clausula FROM. Los parámetros son normalmente un nombre de tabla o alias o cualquier cosa que acepte el driver de base de datos.
`JOIN(String)` `JOIN(String...)` `INNER_JOIN(String)` `INNER_JOIN(String...)` `LEFT_OUTER_JOIN(String)` `LEFT_OUTER_JOIN(String...)` `RIGHT_OUTER_JOIN(String)` `RIGHT_OUTER_JOIN(String...)`	Añade una nueva clausula JOIN del tipo apropiado, dependiendo al método que se haya llamado. El parámetro puede incluir un join estándar que consiste en las columnas y las condiciones sobre las que hacer la join.
`WHERE(String)` `WHERE(String...)`	Añade una nueva condición a la clausula WHERE concatenada con un AND. Puede llamarse más de una vez, lo cual hará que se añadan más condiciones todas ellas concatenadas con un AND. O usa OR() para partirlas con un OR().
`OR()`	Parte las condiciones actuales de la WHERE con un OR. Puede llamarse más de una vez, pero llamarlas más de una vez en la misma línea puede producir sentencias incorrectas.
`AND()`	Parte las condiciones actuales de la WHERE con un AND. Puede llamarse más de una vez, pero llamarlas más de una vez en la misma línea puede producir sentencias incorrectas. Dado que WHERE y HAVING concatenan automáticamente el AND, es muy infrecuente que sea necesario invocar a este método y se incluye realmente por completitud.
`GROUP_BY(String)` `GROUP_BY(String...)`	Añade una nueva clausula GROUP BY grupo, concatenada con una coma. Se le puede llamar más de una vez, lo cual hará que se concatenen nuevas condiciones separadas también por coma.
`HAVING(String)` `HAVING(String...)`	Añade una nueva clausula HAVING, concatenada con un AND. Se le puede llamar más de una vez, lo cual hará que se concatenen nuevas condiciones separadas también por AND. Usa OR() para dividirlas por OR.
`ORDER_BY(String)` `ORDER_BY(String...)`	Añade un Nuevo elemento a la clausula ORDER BY concatenado por coma. Se le puede llamar más de una vez, lo cual hará que se concatenen nuevas condiciones separadas también por coma.
`LIMIT(String)` `LIMIT(int)`	Appends a `LIMIT` clause. This method valid when use together with SELECT(), UPDATE() and DELETE(). And this method is designed to use together with OFFSET() when use SELECT(). (Available since 3.5.2)
`OFFSET(String)` `OFFSET(long)`	Appends a `OFFSET` clause. This method valid when use together with SELECT(). And this method is designed to use together with LIMIT(). (Available since 3.5.2)
`OFFSET_ROWS(String)` `OFFSET_ROWS(long)`	Appends a `OFFSET n ROWS` clause. This method valid when use together with SELECT(). And this method is designed to use together with FETCH_FIRST_ROWS_ONLY(). (Available since 3.5.2)
`FETCH_FIRST_ROWS_ONLY(String)` `FETCH_FIRST_ROWS_ONLY(int)`	Appends a `FETCH FIRST n ROWS ONLY` clause. This method valid when use together with SELECT(). And this method is designed to use together with OFFSET_ROWS(). (Available since 3.5.2)
`DELETE_FROM(String)`	Comienza una sentencia delete y especifica la tabla donde borrar. Generalmente suele ir seguida de una clausula WHERE!
`INSERT_INTO(String)`	Comienza una sentencia insert y especifica al tabla en la que insertar. Suele ir seguida de una o más llamadas a VALUES() o INTO_COLUMNS() y INTO_VALUES().
`SET(String)` `SET(String...)`	Añade a la lista “set” de una update.
`UPDATE(String)`	Comienza una sentencia update y especifica la tabla que modificar Suele ir seguida de una o más llamadas a SET() y normalmente de una llamada a WHERE().
`VALUES(String, String)`	Añade a una sentencia insert. El primer parámetro es el nombre de columna y el Segundo el valor(es).
`INTO_COLUMNS(String...)`	Appends columns phrase to an insert statement. This should be call INTO_VALUES() with together.
`INTO_VALUES(String...)`	Appends values phrase to an insert statement. This should be call INTO_COLUMNS() with together.
`ADD_ROW()`	Add new row for bulk insert. (Available since 3.5.2)

NOTE It is important to note that SQL class writes LIMIT, OFFSET, OFFSET n ROWS and FETCH FIRST n ROWS ONLY clauses into the generated statement as is. In other words, the library does not attempt to normalize those values for databases that don’t support these clauses directly. Therefore, it is very important for users to understand whether or not the target database supports these clauses. If the target database does not support these clauses, then it is likely that using this support will create SQL that has runtime errors.

Since version 3.4.2, you can use variable-length arguments as follows:

public String selectPersonSql() {
  return new SQL()
    .SELECT("P.ID", "A.USERNAME", "A.PASSWORD", "P.FULL_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME")
    .FROM("PERSON P", "ACCOUNT A")
    .INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID", "COMPANY C on D.COMPANY_ID = C.ID")
    .WHERE("P.ID = A.ID", "P.FULL_NAME like #{name}")
    .ORDER_BY("P.ID", "P.FULL_NAME")
    .toString();
}

public String insertPersonSql() {
  return new SQL()
    .INSERT_INTO("PERSON")
    .INTO_COLUMNS("ID", "FULL_NAME")
    .INTO_VALUES("#{id}", "#{fullName}")
    .toString();
}

public String updatePersonSql() {
  return new SQL()
    .UPDATE("PERSON")
    .SET("FULL_NAME = #{fullName}", "DATE_OF_BIRTH = #{dateOfBirth}")
    .WHERE("ID = #{id}")
    .toString();
}

Since version 3.5.2, you can create insert statement for bulk insert as follow:

public String insertPersonsSql() {
  // INSERT INTO PERSON (ID, FULL_NAME)
  //     VALUES (#{mainPerson.id}, #{mainPerson.fullName}) , (#{subPerson.id}, #{subPerson.fullName})
  return new SQL()
    .INSERT_INTO("PERSON")
    .INTO_COLUMNS("ID", "FULL_NAME")
    .INTO_VALUES("#{mainPerson.id}", "#{mainPerson.fullName}")
    .ADD_ROW()
    .INTO_VALUES("#{subPerson.id}", "#{subPerson.fullName}")
    .toString();
}

Since version 3.5.2, you can create select statement for limiting search result rows clause as follow:

public String selectPersonsWithOffsetLimitSql() {
  // SELECT id, name FROM PERSON
  //     LIMIT #{limit} OFFSET #{offset}
  return new SQL()
    .SELECT("id", "name")
    .FROM("PERSON")
    .LIMIT("#{limit}")
    .OFFSET("#{offset}")
    .toString();
}

public String selectPersonsWithFetchFirstSql() {
  // SELECT id, name FROM PERSON
  //     OFFSET #{offset} ROWS FETCH FIRST #{limit} ROWS ONLY
  return new SQL()
    .SELECT("id", "name")
    .FROM("PERSON")
    .OFFSET_ROWS("#{offset}")
    .FETCH_FIRST_ROWS_ONLY("#{limit}")
    .toString();
}

SqlBuilder y SelectBuilder (DEPRECADAS)

En versiones anteriores a la 3.2 optamos por una solución distinta, usando una variable ThreadLocal para resolver algunas limitaciones de las que hacen los DSLs Java algo incomodos. Sin embargo, esta solución está ahora desprecada porque los frameworks actuales están mas orientados a usar patrones builder-type y clases anónimas interas para este tipo de cosas. Por lo tanto las clases SelectBuilder y SqlBuilder están ahora deprecadas.

Los siguientes métodos aplican solo a las clases deprecadas SqlBuilder y SelectBuilder.

Método	Descripción
`BEGIN()` / `RESET()`	Estos métodos limpian estado guardad en el ThreadLocal de la clase SelectBuilder, y la preparan para construir una nueva sentencia. BEGIN() se lee mejor cuando se está creando una sentencia. RESET() se lee mejor cuando se está borrando lo hecho anteriormente en medio de una ejecución (quizá porque la lógica necesita una sentencia completamente distinta según las condiciones).
`SQL()`	Devuelve la SQL generada y restablece el estado del SelectBuilder (como si se hubiera llamado a un BEGIN() o a un RESET()). Por tanto este método solo se puede llamar una vez!

La clase SelectBuilder no es mágica, pero es importante que conozcas cómo funcionan. SelectBuilder y SqlBuilder usan una combinación de imports estáticos y una variable ThreadLocal para permitir una sintaxis más limpia más fácilmente usabe con condicionales. Para usarlas debes importar estáticamente métodos de las siguientes clases (uno u otro, no ambos):

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

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

De esta forma podrás crear métodos como estos:

/* DEPRECATED */
public String selectBlogsSql() {
  BEGIN(); // Clears ThreadLocal variable
  SELECT("*");
  FROM("BLOG");
  return SQL();
}

/* DEPRECATED */
private String selectPersonSql() {
  BEGIN(); // Clears ThreadLocal variable
  SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
  SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
  FROM("PERSON P");
  FROM("ACCOUNT A");
  INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
  INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
  WHERE("P.ID = A.ID");
  WHERE("P.FIRST_NAME like ?");
  OR();
  WHERE("P.LAST_NAME like ?");
  GROUP_BY("P.ID");
  HAVING("P.LAST_NAME like ?");
  OR();
  HAVING("P.FIRST_NAME like ?");
  ORDER_BY("P.ID");
  ORDER_BY("P.FULL_NAME");
  return SQL();
}