<?xml version="1.0" encoding="UTF-8"?>
<!--

       Copyright ${license.git.copyrightYears} the original author or authors.

       Licensed under the Apache License, Version 2.0 (the "License");
       you may not use this file except in compliance with the License.
       You may obtain a copy of the License at

          http://www.apache.org/licenses/LICENSE-2.0

       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.

-->
<document xmlns="http://maven.apache.org/XDOC/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/XDOC/2.0 http://maven.apache.org/xsd/xdoc-2.0.xsd">

  <properties>
    <title>MyBatis 3 | The SQL BUilder Class</title>
    <author email="clinton.begin@gmail.com">Clinton Begin</author>
  </properties>

  <body>
    <section name="The SQL Builder Class">
      <subsection name="The Problem">
        <p>One of the nastiest things a Java developer will ever have to do is embed SQL in Java code. Usually this is
          done because the SQL has to be dynamically generated - otherwise you could externalize it in a file or a
          stored proc. As you've already seen, MyBatis has a powerful answer for dynamic SQL generation in its
          XML mapping features. However, sometimes it becomes necessary to build a SQL statement string inside of Java
          code. In that case, MyBatis has one more feature to help you out, before reducing yourself to the typical mess
          of plus signs, quotes, newlines, formatting problems and nested conditionals to deal with extra commas or
          AND conjunctions. Indeed, dynamically generating SQL code in Java can be a real nightmare.  For example:
        </p>

        <source><![CDATA[
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";
]]></source>
      </subsection>
      <subsection name="The Solution">
      <p>MyBatis 3 offers a convenient utility class to help with the problem.
        With the SQL class, you simply create an instance that lets you call methods against it to build a SQL statement
        one step at a time.  The example problem above would look like this when rewritten with the SQL class:
      </p>

      <source><![CDATA[
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();
}
]]></source>

      <p>What is so special about that example? Well, if you look closely, it doesn't have to worry about
          accidentally duplicating "AND" keywords, or choosing between "WHERE"
          and "AND" or none at all. The SQL class takes care of understanding where "WHERE"
          needs to go, where an "AND" should be used and all of the String concatenation.
        </p>

      </subsection>

      <subsection name="The SQL Class">

        <p>Here are some examples:</p>

        <source><![CDATA[
// 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();
}
]]></source>

        <table>
          <thead>
            <tr>
              <th>Method</th>
              <th>Description</th>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>SELECT(String)</code>
                  </li>
                  <li>
                    <code>SELECT(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Starts or appends to a
                <code>SELECT</code>
                clause. Can be called more than once, and parameters will be appended to
                the
                <code>SELECT</code>
                clause. The parameters are usually a comma separated list of columns and aliases, but can be
                anything acceptable to the driver.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>SELECT_DISTINCT(String)</code>
                  </li>
                  <li>
                    <code>SELECT_DISTINCT(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Starts or appends to a
                <code>SELECT</code>
                clause, also adds the
                <code>DISTINCT</code>
                keyword to the generated query.
                Can be called more than once, and parameters will be appended to the
                <code>SELECT</code>
                clause. The parameters
                are usually a comma separated list of columns and aliases, but can be anything acceptable to the driver.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>FROM(String)</code>
                  </li>
                  <li>
                    <code>FROM(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Starts or appends to a
                <code>FROM</code>
                clause. Can be called more than once, and parameters will be appended to the
                <code>FROM</code>
                clause. Parameters are usually a table name and an alias, or anything acceptable to the driver.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>JOIN(String)</code>
                  </li>
                  <li>
                    <code>JOIN(String...)</code>
                  </li>
                  <li>
                    <code>INNER_JOIN(String)</code>
                  </li>
                  <li>
                    <code>INNER_JOIN(String...)</code>
                  </li>
                  <li>
                    <code>LEFT_OUTER_JOIN(String)</code>
                  </li>
                  <li>
                    <code>LEFT_OUTER_JOIN(String...)</code>
                  </li>
                  <li>
                    <code>RIGHT_OUTER_JOIN(String)</code>
                  </li>
                  <li>
                    <code>RIGHT_OUTER_JOIN(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Adds a new
                <code>JOIN</code>
                clause of the appropriate type, depending on the method called. The parameter can include
                a standard join consisting of the columns and the conditions to join on.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>WHERE(String)</code>
                  </li>
                  <li>
                    <code>WHERE(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Appends a new
                <code>WHERE</code>
                clause condition, concatenated by<code>AND</code>. Can be called multiple times, which causes it
                to concatenate the new conditions each time with<code>AND</code>. Use
                <code>OR()</code>
                to split with an<code>OR</code>.
              </td>
            </tr>
            <tr>
              <td>
                <code>OR()</code>
              </td>
              <td>Splits the current
                <code>WHERE</code>
                clause conditions with an<code>OR</code>. Can be called more than once, but calling more
                than once in a row will generate erratic<code>SQL</code>.
              </td>
            </tr>
            <tr>
              <td>
                <code>AND()</code>
              </td>
              <td>Splits the current
                <code>WHERE</code>
                clause conditions with an<code>AND</code>. Can be called more than once, but calling more
                than once in a row will generate erratic<code>SQL</code>. Because
                <code>WHERE</code>
                and
                <code>HAVING</code>
                both automatically concatenate with
                <code>AND</code>, this is a very uncommon method to use and is only really included for completeness.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>GROUP_BY(String)</code>
                  </li>
                  <li>
                    <code>GROUP_BY(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Appends a new
                <code>GROUP BY</code>
                clause elements, concatenated by a comma. Can be called multiple times, which
                causes it to concatenate the new conditions each time with a comma.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>HAVING(String)</code>
                  </li>
                  <li>
                    <code>HAVING(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Appends a new
                <code>HAVING</code>
                clause condition, concatenated by AND. Can be called multiple times, which causes it
                to concatenate the new conditions each time with<code>AND</code>. Use
                <code>OR()</code>
                to split with an<code>OR</code>.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>ORDER_BY(String)</code>
                  </li>
                  <li>
                    <code>ORDER_BY(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Appends a new
                <code>ORDER BY</code>
                clause elements, concatenated by a comma. Can be called multiple times, which
                causes it to concatenate the new conditions each time with a comma.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>LIMIT(String)</code>
                  </li>
                  <li>
                    <code>LIMIT(int)</code>
                  </li>
                </ul>
              </td>
              <td>
                Appends a <code>LIMIT</code> 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)
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>OFFSET(String)</code>
                  </li>
                  <li>
                    <code>OFFSET(long)</code>
                  </li>
                </ul>
              </td>
              <td>
                Appends a <code>OFFSET</code> clause.
                This method valid when use together with SELECT().
                And this method is designed to use together with LIMIT(). (Available since 3.5.2)
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>OFFSET_ROWS(String)</code>
                  </li>
                  <li>
                    <code>OFFSET_ROWS(long)</code>
                  </li>
                </ul>
              </td>
              <td>
                Appends a <code>OFFSET n ROWS</code> 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)
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>FETCH_FIRST_ROWS_ONLY(String)</code>
                  </li>
                  <li>
                    <code>FETCH_FIRST_ROWS_ONLY(int)</code>
                  </li>
                </ul>
              </td>
              <td>
                Appends a <code>FETCH FIRST n ROWS ONLY</code> 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)
              </td>
            </tr>
            <tr>
              <td>
                <code>DELETE_FROM(String)</code>
              </td>
              <td>Starts a delete statement and specifies the table to delete from. Generally this should be followed by
                a
                WHERE statement!
              </td>
            </tr>
            <tr>
              <td>
                <code>INSERT_INTO(String)</code>
              </td>
              <td>Starts an insert statement and specifies the table to insert into. This should be followed by one or
                more VALUES() or INTO_COLUMNS() and INTO_VALUES() calls.
              </td>
            </tr>
            <tr>
              <td>
                <ul>
                  <li>
                    <code>SET(String)</code>
                  </li>
                  <li>
                    <code>SET(String...)</code>
                  </li>
                </ul>
              </td>
              <td>Appends to the "set" list for an update statement.</td>
            </tr>
            <tr>
              <td>
                <code>UPDATE(String)</code>
              </td>
              <td>Starts an update statement and specifies the table to update. This should be followed by one or more
                SET() calls, and usually a WHERE() call.
              </td>
            </tr>
            <tr>
              <td>
                <code>VALUES(String, String)</code>
              </td>
              <td>Appends to an insert statement. The first parameter is the column(s) to insert, the second parameter
                is
                the value(s).
              </td>
            </tr>
            <tr>
              <td>
                <code>INTO_COLUMNS(String...)</code>
              </td>
              <td>
                Appends columns phrase to an insert statement.
                This should be call INTO_VALUES() with together.
              </td>
            </tr>
            <tr>
              <td>
                <code>INTO_VALUES(String...)</code>
              </td>
              <td>
                Appends values phrase to an insert statement.
                This should be call INTO_COLUMNS() with together.
              </td>
            </tr>
            <tr>
              <td>
                <code>ADD_ROW()</code>
              </td>
              <td>
                Add new row for bulk insert. (Available since 3.5.2)
              </td>
            </tr>
          </tbody>
        </table>

        <p>
          <span class="label important">NOTE</span>
          It is important to note that SQL class writes <code>LIMIT</code>, <code>OFFSET</code>, <code>OFFSET n ROWS</code> and <code>FETCH FIRST n ROWS ONLY</code> 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.
        </p>

        <p>Since version 3.4.2, you can use variable-length arguments as follows:</p>

<source><![CDATA[
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();
}
]]></source>

        <p>Since version 3.5.2, you can create insert statement for bulk insert as follow:</p>

        <source><![CDATA[
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();
}
]]></source>

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

        <source><![CDATA[
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();
}
]]></source>

      </subsection>

      <subsection name="SqlBuilder and SelectBuilder (DEPRECATED)">
        <p>
          Before version 3.2 we took a bit of a different approach, by utilizing a ThreadLocal variable to mask some of the
          language limitations that make Java DSLs a bit cumbersome. However, this approach is now deprecated, as modern
          frameworks have warmed people to the idea of using builder-type patterns and anonymous inner classes for such
          things. Therefore the SelectBuilder and SqlBuilder classes have been deprecated.
        </p>
        <p>
          The following methods apply to only the deprecated SqlBuilder and SelectBuilder classes.
        </p>
        <table>
        <thead>
          <tr>
            <th>Method</th>
            <th>Description</th>
          </tr>
        </thead>
        <tbody>
        <tr>
          <td>
            <code>BEGIN()</code>
            /
            <code>RESET()</code>
          </td>
          <td>These methods clear the ThreadLocal state of the SelectBuilder class, and prepare it for a new
            statement to be
            built.
            <code>BEGIN()</code>
            reads best when starting a new statement.
            <code>RESET()</code>
            reads best when clearing a
            statement in the middle of execution for some reason (perhaps if the logic demands a completely
            different
            statement under some conditions).
          </td>
        </tr>
        <tr>
          <td>
            <code>SQL()</code>
          </td>
          <td>This returns the generated
            <code>SQL()</code>
            and resets the
            <code>SelectBuilder</code>
            state (as if
            <code>BEGIN()</code>
            or
            <code>RESET()</code>
            were called).
            Thus, this method can only be called ONCE!
          </td>
        </tr>
        </tbody>
        </table>

        <p>
          The SelectBuilder and SqlBuilder classes are not magical, but it's important to know how they work.
          SelectBuilder and SqlBuilder use a combination of
          Static Imports and a ThreadLocal variable to enable a clean syntax that can be easily interlaced with
          conditionals.  To use them, you statically import the methods from the classes like this (one or the other,
          not both):
        </p>

        <source>import static org.apache.ibatis.jdbc.SelectBuilder.*;</source>
        <source>import static org.apache.ibatis.jdbc.SqlBuilder.*;</source>

        <p>This allows us to create methods like these:</p>

        <source><![CDATA[
/* DEPRECATED */
public String selectBlogsSql() {
  BEGIN(); // Clears ThreadLocal variable
  SELECT("*");
  FROM("BLOG");
  return SQL();
}
        ]]></source>

        <source><![CDATA[
/* 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();
}
        ]]></source>

      </subsection>


    </section>
  </body>

</document>
