Skip to content

Instantly share code, notes, and snippets.

@agentgt
Created September 6, 2012 02:38
Show Gist options
  • Save agentgt/3650165 to your computer and use it in GitHub Desktop.
Save agentgt/3650165 to your computer and use it in GitHub Desktop.
MyBatis SqlBuilder done right!
/*
* Copyright 2009-2012 The MyBatis Team
*
* 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.
*/
package com.snaphop.sql.builder;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public abstract class AbstractSqlBuilder<T> {
private static final String AND = ") \nAND (";
private static final String OR = ") \nOR (";
public abstract T getSelf();
public T UPDATE(String table) {
sql().statementType = SQL.StatementType.UPDATE;
sql().tables.add(table);
return getSelf();
}
public T SET(String sets) {
sql().sets.add(sets);
return getSelf();
}
public T INSERT_INTO(String tableName) {
sql().statementType = SQL.StatementType.INSERT;
sql().tables.add(tableName);
return getSelf();
}
public T VALUES(String columns, String values) {
sql().columns.add(columns);
sql().values.add(values);
return getSelf();
}
public T SELECT(String columns) {
sql().statementType = SQL.StatementType.SELECT;
sql().select.add(columns);
return getSelf();
}
public T SELECT_DISTINCT(String columns) {
sql().distinct = true;
SELECT(columns);
return getSelf();
}
public T DELETE_FROM(String table) {
sql().statementType = SQL.StatementType.DELETE;
sql().tables.add(table);
return getSelf();
}
public T FROM(String table) {
sql().tables.add(table);
return getSelf();
}
public T JOIN(String join) {
sql().join.add(join);
return getSelf();
}
public T INNER_JOIN(String join) {
sql().innerJoin.add(join);
return getSelf();
}
public T LEFT_OUTER_JOIN(String join) {
sql().leftOuterJoin.add(join);
return getSelf();
}
public T RIGHT_OUTER_JOIN(String join) {
sql().rightOuterJoin.add(join);
return getSelf();
}
public T OUTER_JOIN(String join) {
sql().outerJoin.add(join);
return getSelf();
}
public T WHERE(String conditions) {
sql().where.add(conditions);
sql().lastList = sql().where;
return getSelf();
}
public T OR() {
sql().lastList.add(OR);
return getSelf();
}
public T AND() {
sql().lastList.add(AND);
return getSelf();
}
public T GROUP_BY(String columns) {
sql().groupBy.add(columns);
return getSelf();
}
public T HAVING(String conditions) {
sql().having.add(conditions);
sql().lastList = sql().having;
return getSelf();
}
public T ORDER_BY(String columns) {
sql().orderBy.add(columns);
return getSelf();
}
private SQL sql = new SQL();
private SQL sql() {
return sql;
}
public <A extends Appendable> A SQL(A a) {
sql().sql(a);
return a;
}
public String SQL() {
StringBuilder sb = new StringBuilder();
sql().sql(sb);
return sb.toString();
}
@Override
public String toString() {
return SQL();
}
private static class SafeAppendable {
private final Appendable a;
private boolean empty = true;
public SafeAppendable(Appendable a) {
super();
this.a = a;
}
public SafeAppendable append(CharSequence s) {
try {
if (empty && s.length() > 0) empty = false;
a.append(s);
} catch (IOException e) {
throw new RuntimeException(e);
}
return this;
}
public boolean isEmpty() {
return empty;
}
}
private static class SQL {
public enum StatementType {
DELETE, INSERT, SELECT, UPDATE
}
StatementType statementType;
List<String> sets = new ArrayList<String>();
List<String> select = new ArrayList<String>();
List<String> tables = new ArrayList<String>();
List<String> join = new ArrayList<String>();
List<String> innerJoin = new ArrayList<String>();
List<String> outerJoin = new ArrayList<String>();
List<String> leftOuterJoin = new ArrayList<String>();
List<String> rightOuterJoin = new ArrayList<String>();
List<String> where = new ArrayList<String>();
List<String> having = new ArrayList<String>();
List<String> groupBy = new ArrayList<String>();
List<String> orderBy = new ArrayList<String>();
List<String> lastList = new ArrayList<String>();
List<String> columns = new ArrayList<String>();
List<String> values = new ArrayList<String>();
boolean distinct;
private void sqlClause(SafeAppendable builder, String keyword, List<String> parts, String open, String close,
String conjunction) {
if (!parts.isEmpty()) {
if ( ! builder.isEmpty() )
builder.append("\n");
builder.append(keyword);
builder.append(" ");
builder.append(open);
String last = "________";
for (int i = 0, n = parts.size(); i < n; i++) {
String part = parts.get(i);
if (i > 0 && !part.equals(AND) && !part.equals(OR) && !last.equals(AND) && !last.equals(OR)) {
builder.append(conjunction);
}
builder.append(part);
last = part;
}
builder.append(close);
}
}
private String selectSQL(SafeAppendable builder) {
if (distinct) {
sqlClause(builder, "SELECT DISTINCT", select, "", "", ", ");
}
else {
sqlClause(builder, "SELECT", select, "", "", ", ");
}
sqlClause(builder, "FROM", tables, "", "", ", ");
sqlClause(builder, "JOIN", join, "", "", "\nJOIN ");
sqlClause(builder, "INNER JOIN", innerJoin, "", "", "\nINNER JOIN ");
sqlClause(builder, "OUTER JOIN", outerJoin, "", "", "\nOUTER JOIN ");
sqlClause(builder, "LEFT OUTER JOIN", leftOuterJoin, "", "", "\nLEFT OUTER JOIN ");
sqlClause(builder, "RIGHT OUTER JOIN", rightOuterJoin, "", "", "\nRIGHT OUTER JOIN ");
sqlClause(builder, "WHERE", where, "(", ")", " AND ");
sqlClause(builder, "GROUP BY", groupBy, "", "", ", ");
sqlClause(builder, "HAVING", having, "(", ")", " AND ");
sqlClause(builder, "ORDER BY", orderBy, "", "", ", ");
return builder.toString();
}
private String insertSQL(SafeAppendable builder) {
sqlClause(builder, "INSERT INTO", tables, "", "", "");
sqlClause(builder, "", columns, "(", ")", ", ");
sqlClause(builder, "VALUES", values, "(", ")", ", ");
return builder.toString();
}
private String deleteSQL(SafeAppendable builder) {
sqlClause(builder, "DELETE FROM", tables, "", "", "");
sqlClause(builder, "WHERE", where, "(", ")", " AND ");
return builder.toString();
}
private String updateSQL(SafeAppendable builder) {
sqlClause(builder, "UPDATE", tables, "", "", "");
sqlClause(builder, "SET", sets, "", "", ", ");
sqlClause(builder, "WHERE", where, "(", ")", " AND ");
return builder.toString();
}
public String sql(Appendable a) {
SafeAppendable builder = new SafeAppendable(a);
if (statementType == null) {
return null;
}
String answer;
switch (statementType) {
case DELETE:
answer = deleteSQL(builder);
break;
case INSERT:
answer = insertSQL(builder);
break;
case SELECT:
answer = selectSQL(builder);
break;
case UPDATE:
answer = updateSQL(builder);
break;
default:
answer = null;
}
return answer;
}
}
}
package com.snaphop.sql.builder;
public class SqlBuilder extends AbstractSqlBuilder<SqlBuilder>{
@Override
public SqlBuilder getSelf() {
return this;
}
}
package com.snaphop.sql.builder;
import static org.junit.Assert.*;
import org.junit.Before;
import org.junit.Test;
public class SqlBuilderTest {
@Before
public void setUp() throws Exception {}
@Test
public void test() {
//Fluent Style
String sql = new SqlBuilder()
.SELECT("id, name").FROM("PERSON A")
.WHERE("name like ?")
.WHERE("id = ?").toString();
assertEquals("" +
"SELECT id, name\n" +
"FROM PERSON A\n" +
"WHERE (name like ? AND id = ?)", sql);
//Mixed
sql = new SqlBuilder() {{
SELECT("id, name");
FROM("PERSON A");
WHERE("name like ?").WHERE("id = ?");
}}.toString();
assertEquals("" +
"SELECT id, name\n" +
"FROM PERSON A\n" +
"WHERE (name like ? AND id = ?)", sql);
//You can pass in your own StringBuilder
StringBuilder sb = new StringBuilder();
//From the tutorial
sql = new SqlBuilder() {{
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");
}}.SQL(sb).toString();
assertEquals("SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON\n" +
"FROM PERSON P, ACCOUNT A\n" +
"INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID\n" +
"INNER JOIN COMPANY C on D.COMPANY_ID = C.ID\n" +
"WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) \n" +
"OR (P.LAST_NAME like ?)\n" +
"GROUP BY P.ID\n" +
"HAVING (P.LAST_NAME like ?) \n" +
"OR (P.FIRST_NAME like ?)\n" +
"ORDER BY P.ID, P.FULL_NAME", sql);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment