May 16, 2021 MyBatis
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";
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.
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();
}
Method | Describe |
---|---|
SELECT(String)
|
Start a new or append to an
SELECT
clause. C
an be called multiple times, and parameters are
SELECT
clause.
Arguments typically use comma-separated lists of column names and aliases, but they can also be arbitrary parameters accepted by the database driver.
|
SELECT_DISTINCT(String)
|
Start a new or append to an
SELECT
clause and add
DISTINCT
keyword to the generated query. C
an be called multiple times, and parameters are
SELECT
clause.
Arguments typically use comma-separated lists of column names and aliases, but they can also be arbitrary parameters accepted by the database driver.
|
FROM(String)
|
Start a new or append to an
FROM
clause. C
an be called multiple times, and arguments are
FROM
the FROM clause.
A parameter is usually a table name or alias, or it can be any parameter accepted by the database driver.
|
JOIN(String),INNER_JOIN(String),LEFT_OUTER_JOIN(String),RIGHT_OUTER_JOIN(String)
|
Add a new join clause of the appropriate type based
JOIN
called method.
Arguments can contain a standard connection consisting of columns and connection conditions.
|
WHERE(String)
|
Insert a
WHERE
clause condition and stitch
AND
AND. C
an be called multiple times, and FOR stitching is used for each new condition
AND
by the call.
To use
OR
separation, use
OR()
|
OR()
|
Use
OR
to separate the
WHERE
clause conditions.
You can be called multiple times, but multiple calls in a row can produce the
SQL
|
AND()
|
Use
AND
to separate the
WHERE
clause conditions. Y
ou can be called multiple times, but multiple calls in a row can produce the
SQL
Because
WHERE
and
HAVING
automatically use
AND
stitching, this method is not commonly used and is defined only for integrity.
|
GROUP_BY(String)
|
Append
GROUP BY
clause, stitching with a comma.
Can be called multiple times, and each call stitches up the new condition with a comma.
|
HAVING(String)
|
Append
HAVING
HAVING clause. U
se
AND
C
an be called multiple times, and each call uses
AND
to stitch the new condition.
To use
OR
separation, use
OR()
|
ORDER_BY(String)
|
Append
ORDER BY
clause and stitch it with a comma.
You can be called multiple times, and each call stitches the new condition with a comma.
|
LIMIT(String)``LIMIT(int)
|
Append
LIMIT
clause. V
alid
SELECT()
UPDATE()
DELETE()
W
hen used
SELECT()
it should be used
OFFSET()
(introduced in 3.5.2)
|
OFFSET(String)``OFFSET(long)
|
Append
OFFSET
clause. V
alid
SELECT()
W
hen used
SELECT()
it should be used in
LIMIT()
(introduced in 3.5.2)
|
OFFSET_ROWS(String)``OFFSET_ROWS(long)
|
Append
OFFSET n ROWS
clause. V
alid
SELECT()
T
his method should be
FETCH_FIRST_ROWS_ONLY()
method.
(Joined at 3.5.2)
|
FETCH_FIRST_ROWS_ONLY(String)``FETCH_FIRST_ROWS_ONLY(int)
|
Append
FETCH FIRST n ROWS ONLY
clause. V
alid
SELECT()
T
his method should be
OFFSET_ROWS()
method.
(Joined at 3.5.2)
|
DELETE_FROM(String)
|
Start a new
delete
statement and specify the table name of the deleted table.
Usually it is followed by
WHERE
clause!
|
INSERT_INTO(String)
|
Start a new
insert
statement and specify the name of the table that is inserted into the data table.
This should be followed by one
VALUES()
INTO_COLUMNS()
INTO_VALUES()
calls.
|
SET(String)
|
Append a list of
"set"
to the
update
statement
|
UPDATE(String)
|
Start a new
update
statement and specify the table name of the update table.
This is followed by one
SET()
calls, and usually one
WHERE()
call.
|
VALUES(String, String)
|
Append the data value
insert
statement.
The first argument is the column name of the data insert, and the second argument is the data value.
|
INTO_COLUMNS(String...)
|
Append the insert column clause
insert
statement.
Should be
INTO_VALUES()
|
INTO_VALUES(String...)
|
Append the insert value clause into
insert
statement.
Should be INTO_COLUMNS with the
INTO_COLUMNS()
|
ADD_ROW()
|
Add a new row of data to perform bulk inserts. (introduced in 3.5.2) |
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();
}
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();
}
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
Method | Describe |
---|---|
BEGIN()
/
RESET()
|
These methods empty
SelectBuilder
state of
ThreadLocal
class and prepare a new build statement. W
HEN you start a new statement,
BEGIN()
reads best.
For some reason (in some cases, perhaps logic requires a completely different statement), it is best to clean
RESET()
read in execution.
|
SQL()
|
Returns the
SQL()
and
SelectBuilder
state
BEGIN()
RESET()
were called).
Therefore, the method can only be called once!
|
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();
}