MyBatis SQL Statement Builder

SQL Statement Builder

Problem

One of the most painful things Java programmers face is embedding SQL statements in Java code. T his is usually done because SQL statements need to be generated dynamically - otherwise they can be placed in external files or stored in a process. A s you've seen, MyBatis has a powerful dynamic SQL generation scheme in its XML mapping features. S ometimes, however, it is necessary to create SQL statements within Java code. A t this point, MyBatis has another feature that can help you, before reducing typical plus marks, quotation marks, new lines, formatting issues, and embedding conditions to handle extra commas or AND connections. I n fact, generating SQL code dynamically in Java code can be a nightmare. For example:

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

Solution

MyBatis 3 provides a handy tool class to help resolve the issue. U sing SQL classes, simply create an instance to call the method to generate sql statements. The problem in the example above is like rewriting a SQL class:

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

What's so special about this example? W hen you look closely, don't worry about the occasional recurring "AND" keyword, or the choice between "WHERE" and "AND" or nothing. The SQL class pays great attention to where "WHERE" should appear, where "AND" should be used, and all string links.

SQL class

Here are some examples:

    // 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();
    }

Tip Note that the SQL class inserts the LIMIT OFFSET OFFSET n ROWS FETCH FIRST n ROWS ONLY as is. I n other words, the class library does not perform any transformations for databases that do not support these clauses. T herefore, users should know if the target database supports these clauses. If the target database does not support these clauses, the resulting SQL may cause a run error.

Starting with version 3.4.2, you can use variable length parameters like this:

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

• Starting with version 3.5.2, you can build bulk insert statements like this:

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

Starting with version 3.5.2, you can build SELECT statements that limit the number of results returned, like this:

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 and SelectBuilder (abandoned)

Prior to version 3.2, we used a little different approach to mask some of the language limitations that Java DSL by implementing the ThreadLocal variable. B ut this approach has been abandoned, and modern frameworks welcome the idea of using builder types and anonymous internal classes. As a result, SelectBuilder SqlBuilder are abandoned.

The following method applies only to the SqlBuilder SelectBuilder

SelectBuilder SqlBuilder are not magical, but it's also important to know how they work. SelectBuilder SqlBuilder ThreadLocal to turn on neat syntax that can easily interlee conditions. Using them, the method of importing classes statically is just like this (one or the other, not both):

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

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

This allows you to create methods like this:

    /* 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();
    }