MyBatis | Dynamically Generating SQL in a Program | How to Use Each Method

SELECT

new SQL() {{
    SELECT("foo");
    SELECT("bar");
    SELECT("fizz, buzz");
    FROM("foo_table");
}};

SELECT foo, bar, fizz, buzz FROM foo_table

When written consecutively, each item is separated automatically with commas.
You can also pass an explicitly comma-separated string.
If there is an unnecessary comma at the end, it is not removed.

FROM

new SQL() {{
    SELECT("*");
    FROM("foo_table");
    FROM("bar_table");
}};

SELECT * FROM foo_table, bar_table

When FROM is written consecutively, the FROM clauses are listed with comma separation.

WHERE

new SQL() {{
    SELECT("*");
    FROM("foo_table");
    WHERE("foo = ?");
    WHERE("bar = ?");
    AND();
    WHERE("fizz = ?");
    WHERE("buzz = ?");
    OR();
    WHERE("hoge = ?");
    WHERE("fuga = ?");
}};

SELECT *
  FROM foo_table
 WHERE (
       foo = ?
       AND bar = ?
 ) AND (
       fizz = ?
       AND buzz = ?
 )  OR (
       hoge = ?
       AND fuga = ?
 )

You can create a WHERE clause with the WHERE() method.
If you write WHERE() methods consecutively, they are connected with AND conditions.
Use the AND() and OR() methods to connect conditions with AND and OR.

INNER_JOIN

new SQL() {{
    SELECT("*");
    FROM("foo_table");
    INNER_JOIN("bar_table on bar_table.id = foo_table.bar_id");
    INNER_JOIN("fizz_table on fizz_table.id = bar_table.fizz_id");
}};

    SELECT *
      FROM foo_table
INNER JOIN bar_table
        on bar_table.id = foo_table.bar_id
INNER JOIN fizz_table
        on fizz_table.id = bar_table.fizz_id

You can add an INNER JOIN clause with the INNER_JOIN() method.
You must write the join condition string, such as on~.

ORDER_BY

new SQL() {{
    SELECT("*");
    FROM("foo_table");
    ORDER_BY("foo");
    ORDER_BY("bar desc");
}};

SELECT * FROM foo_table ORDER BY foo, bar desc

You can create an ORDER BY clause with the ORDER_BY() method.
When written consecutively, entries are automatically separated with commas.

GROUP_BY

new SQL() {{
    SELECT("foo, bar");
    FROM("foo_table");
    GROUP_BY("foo");
    GROUP_BY("bar");
}};

SELECT foo, bar FROM foo_table GROUP BY foo, bar

You can create a GROUP BY clause with the GROUP_BY() method.
When written consecutively, entries are automatically separated with commas.

HAVING

new SQL() {{
    SELECT("foo, count(*)");
    FROM("foo_table");
    GROUP_BY("foo");
    HAVING("0 < count(*)");
    HAVING("count(*) < 100");
}};

  SELECT foo
        ,count(*)
    FROM foo_table
GROUP BY foo
  HAVING (
         0 < count(foo)
         AND count(foo) < 100
  )

You can create a HAVING clause with the HAVING() method.
It behaves the same as WHERE(); consecutive calls are connected with AND.

INSERT_INTO, VALUES

new SQL() {{
    INSERT_INTO("foo_table");
    VALUES("foo_value", "#{foo_value}");
    VALUES("fizz_value, buzz_value", "#{fizz_value}, #{buzz_value}");
}};

INSERT INTO foo_table (foo_value, fizz_value, buzz_value) VALUES (?, ?, ?)

You can create an INSERT statement with the INSERT_INTO() method.
You can define items to insert with the VALUES() method.
- Write the item name to set in the first argument.
- Pass the value specified in the VALUES clause as the second argument.
- When multiple items are described, they are automatically separated with commas.

UPDATE, SET

new SQL() {{
    UPDATE("foo_table");
    SET("foo_value = #{foo_value}");
    SET("bar_value = #{bar_value}");
    WHERE("id = #{id}");
}};

UPDATE foo_table SET foo_value = ?, bar_value = ? WHERE (id = ?)

You can create an UPDATE statement with the UPDATE() method.
You can create a SET clause with the SET() method.
When written consecutively, entries are automatically separated with commas.

DELETE_FROM

new SQL() {{
    DELETE_FROM("foo_table");
    WHERE("id = #{id}");
}};

DELETE FROM foo_table WHERE (id = ?)

You can create a DELETE statement with the DELETE_FROM() method.