MyBatis SqlBuilder done right!

AbstractSqlBuilder.java

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

SqlBuilder.java

package com.snaphop.sql.builder;


public class SqlBuilder extends AbstractSqlBuilder<SqlBuilder>{
    
    @Override
    public SqlBuilder getSelf() {
        return this;
    }
}

SqlBuilderTest.java

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

    }

}