【0】README
【1】在Java代码中来动态生成SQL代码就是一场噩梦
1)problem+solutions:
1.1)problem:Java程序员面对的最痛苦的事情之一就是在Java代码中嵌入SQL语句。这么来做通常是由于SQL语句需要动态来生成-否则可以将它们放到外部文件或者存储过程中。正如你已经看到的那样,MyBatis在它的XML映射特性中有一个强大的动态SQL生成方案。但有时在Java代码内部创建SQL语句也是必要的。(干货——事实上,在Java代码中来动态生成SQL代码就是一场噩梦)<pre name="code" class="java">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";
1.2)solutions)MyBatis 3提供了方便的工具类来帮助解决该问题。使用SQL类,简单地创建一个实例来调用方法生成SQL语句。上面示例中的问题就像重写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(); }
【2】SQL类
1)这里给出一些示例:
// 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();
}