SQL building for more dynamic JDBC operations in Spring JdbcTemplates.

ComplexTableDescription.java

package me.woemler.sqlbuilder;

import org.springframework.util.Assert;

import java.util.Arrays;
import java.util.List;

/**
 * Based on com.nurkiewicz.jdbcrepository.TableDescription, with a number of modifications.
 * 
 * @author woemler 
 */
public class ComplexTableDescription {
	
	private String tableName;
	private List<String> idColumns;
	private String selectClause;
	private String fromClause;
	private String groupByClause;

	public ComplexTableDescription(String tableName, List<String> idColumns,
			String selectClause, String fromClause, String groupByClause) {

		Assert.notNull(tableName);
		Assert.notNull(idColumns);
		Assert.notNull(selectClause);
		Assert.notNull(fromClause);
		Assert.notNull(groupByClause);
		
		this.tableName = tableName;
		this.idColumns = idColumns;
		this.selectClause = selectClause;
		this.fromClause = fromClause;
		this.groupByClause = groupByClause;

	}

	public ComplexTableDescription(String tableName, List<String> idColumns,
			String selectClause, String fromClause) {
		this(tableName, idColumns, selectClause, fromClause, "");
	}

	public ComplexTableDescription(String tableName, List<String> idColumns,
			String selectClause) {
		this(tableName, idColumns, selectClause, tableName, "");
	}

	public ComplexTableDescription(String tableName, List<String> idColumns) {
		this(tableName, idColumns, tableName+".*", tableName, "");
	}

	public ComplexTableDescription(String tableName) {
		this(tableName, Arrays.asList(new String[]{tableName+".id"}), tableName+".*", tableName, "");
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public List<String> getIdColumns() {
		return idColumns;
	}

	public void setIdColumns(List<String> idColumns) {
		this.idColumns = idColumns;
	}

	public String getSelectClause() {
		return selectClause;
	}

	public void setSelectClause(String selectClause) {
		this.selectClause = selectClause;
	}

	public String getFromClause() {
		return fromClause;
	}

	public void setFromClause(String fromClause) {
		this.fromClause = fromClause;
	}

	public String getGroupByClause() {
		return groupByClause;
	}

	public void setGroupByClause(String groupByClause) {
		this.groupByClause = groupByClause;
	}
}

Condition.java

package me.woemler.sqlbuilder;

import me.woemler.sqlbuilder.Evaluation;

/**
 * @author woemler 
 */
public class Condition {
	private String clause;
	private Object value;

	public Condition(String column, Object value, Evaluation evalutation){
		StringBuilder builder = new StringBuilder(" " + column);
		switch (evalutation) {
			case EQUALS:
				builder.append(" = ? ");
				break;
			case NOT_EQUALS:
				builder.append(" != ? ");
				break;
			case IS_NULL:
				builder.append(" is null ");
				break;
			case NOT_NULL:
				builder.append(" is not null ");
				break;
			case IN:
				builder.append(" in (?) ");
				break;
			case NOT_IN:
				builder.append(" not in (?) ");
				break;
			default:
				builder.append(" = ? ");
		}
		this.clause = builder.toString();
		this.value = value;
	}

	public String getClause() {
		return clause;
	}

	public Object getValue() {
		return value;
	}

}

Conditions.java

package me.woemler.sqlbuilder;

import java.util.ArrayList;
import java.util.List;

/**
 * Wraps a series of conditions and joins them with AND/OR operators
 * 
 * @author woemler 
 */
public class Conditions {
	
	public static enum Operation { AND, OR }
	
	private Operation operation;
	private String sql;
	private List<Object> values;
	
	public Conditions(Operation operation, Condition... conditions){
		
		this.operation = operation;
		this.values = new ArrayList<>();

		StringBuilder builder = new StringBuilder();
		String separator = " AND ";
		if (operation.equals(Operation.OR)){
			separator = " OR ";
		}
		boolean flag = false;
		for (Condition condition: conditions){
			if (flag){
				builder.append(separator);
			}
			flag = true;
			builder.append(condition.getClause());
			if (condition.getValue() != null){
				values.add(condition.getValue());
			}
		}
		
		this.sql = builder.toString();
		
	}
	
	public Conditions(Operation operation, Conditions... conditions){
		
		this.operation = operation;
		this.values = new ArrayList<>();

		StringBuilder builder = new StringBuilder();
		String separator = " AND ";
		if (operation.equals(Operation.OR)){
			separator = " OR ";
		}
		boolean flag = false;
		
		for (Conditions c: conditions){
			if (flag) {
				builder.append(separator);
			}
			flag = true;
			builder.append(" ( " + c.getSql() + " ) ");
			this.values.addAll(c.getValues());
		}
		
		this.sql = builder.toString();
		
	}
	
	public String getSql(){
		return sql;
	}
	
	public List<Object> getValues(){
		return values;
	}
	
	public Operation getOperation(){
		return operation;
	}
	
}

Evaluation.java

package me.woemler.sqlbuilder;

/**
 * @author woemler
 */
public enum Evaluation {
	EQUALS, 
	IN,
	NOT_EQUALS,
	NOT_IN,
	LIKE, 
	GREATER_THAN, 
	LESS_THAN,
	GREATER_THAN_EQUALS,
	LESS_THAN_EQUALS,
	IS_NULL, 
	NOT_NULL, 
	IS_TRUE, 
	IS_FALSE
	;
	
}

SqlBuilder.java

package me.woemler.sqlbuilder;

import me.woemler.sqlbuilder.Evaluation;
import me.woemler.sqlbuilder.ComplexTableDescription;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;

import javax.swing.*;
import java.util.*;

/**
 * @author woemler
 * 
 * Example usage:
 * 
 * SqlBuilder sqlBuilder = new SqlBuilder(tableDescription);
 * String sql = sqlBuilder.select("*")
 *   .from("users")
 *   .where(
 *     and(
 *       eq("category", "active"),
 *       notEq("name", "Joe")
 *     )
 *   )
 *   .groupBy("name", "address")
 *   .orderBy(
 *     new Sort(
 *       new Sort.Order(Sort.Direction.ASC, "name"), 
 *       new Sort.Order(Sort.Direction.DESC, "address")
 *     )
 *   )
 *   .limit(10, 50)
 *   .toSql();
 *   
 *  Should produce the string:
 *  
 *   SELECT * FROM users 
 *   WHERE category = ? AND name != ? 
 *   GROUP BY name, address
 *   ORDER BY name ASC, address DESC
 *   LIMIT 10, 50
 *   
 *  List<Object> parameters = sqlBuilder.getQueryParameterValues;
 * 
 *  Should return a list with { 'active', 'Joe' }
 * 
 */
public class SqlBuilder {
	
	private String tableName;
	private String selectClause = "*";
	private String fromClause = "";
	private String whereClause = "";
	private String groupByClause = "";
	private String orderByClause = "";
	private String limitClause = "";
	private String insertClause = "";
	private String updateClause = "";
	private enum Mode { SELECT, INSERT, UPDATE, DELETE };
	private Mode mode = Mode.SELECT;
	
	private List<String> idColumns;
	private LinkedHashMap<String, SortOrder> sorts;
	private List<Object> queryParameterValues;
	
	public SqlBuilder(ComplexTableDescription tableDescription){
		
		this.tableName = tableDescription.getTableName();
		this.selectClause = tableDescription.getSelectClause();
		this.fromClause = tableDescription.getFromClause();
		this.groupByClause = tableDescription.getGroupByClause();
		this.idColumns = tableDescription.getIdColumns();
		this.queryParameterValues = new ArrayList<>();
		
		this.sorts = new LinkedHashMap<>();
		
	}
	
	public SqlBuilder(String tableName){
		this.tableName = tableName;
		this.fromClause = tableName;
	}
	
	public SqlBuilder(){ }
	
	//// SELECT

	public String getSelectClause() {
		return "SELECT " + selectClause;
	}
	
	public void setSelectClause(String selectClause){
		this.selectClause = selectClause;
	}

	public SqlBuilder select(String clause){
		this.selectClause = clause;
		return this;
	}

	//// INSERT

	public SqlBuilder insert(Map<String,Object> parameters){
		StringBuilder columnString = new StringBuilder(" (");
		StringBuilder valueString = new StringBuilder(" VALUES (");
		boolean flag = false;
		for (Map.Entry param: parameters.entrySet()){
			if (flag){
				columnString.append(",");
				valueString.append(",");
			}
			flag = true;
			columnString.append(param.getKey());
			valueString.append("?");
			queryParameterValues.add(param.getValue());
		}
		columnString.append(") ");
		valueString.append(") ");
		insertClause = tableName + columnString.toString() + valueString.toString(); 
		mode = Mode.INSERT;
		return this;
	}
	
	public String getInsertClause(){
		return "INSERT INTO " + insertClause;
	}
	
	//// UPDATE

	public SqlBuilder update(Map<String,Object> parameters){
		StringBuilder stringBuilder = new StringBuilder();
		boolean flag = false;
		for (Map.Entry param: parameters.entrySet()){
			if (flag){
				stringBuilder.append(", ");
			}
			flag = true;
			stringBuilder.append(param.getKey() + " = ?");
			queryParameterValues.add(param.getValue());
		}
		updateClause = tableName + " SET " + stringBuilder.toString();
		mode = Mode.UPDATE;
		return this;
	}
	
	public String getUpdateClause(){
		return "UPDATE " + updateClause;
	}

	//// DELETE

	public SqlBuilder delete(){
		fromClause = " " + tableName + " ";
		mode = Mode.DELETE;
		return this;
	}
	
	//// FROM

	public String getFromClause() {
		return " FROM " + fromClause;
	}
	
	public void setFromClause(String fromClause){
		this.fromClause = fromClause;
	}

	public SqlBuilder from(String clause) {
		this.fromClause =  clause;
		return this;
	}
	
	//// WHERE
	
	public String getWhereClause(){
		return whereClause.equals("") ? whereClause : " WHERE " + whereClause;
	}
	
	public void setWhereClause(String whereClause){
		this.whereClause = whereClause;
	}
	
	public SqlBuilder where(Conditions conditions){
		whereClause = conditions.getSql();
		queryParameterValues.addAll(conditions.getValues());
		return this;
	}
	
	public SqlBuilder where(Condition condition){
		whereClause = condition.getClause();
		queryParameterValues.add(condition.getValue());
		return this;
	}
	
	public static Conditions and(Condition... conditions){
		return new Conditions(Conditions.Operation.AND, conditions);
	}

	public static Conditions and(Conditions... conditions){
		return new Conditions(Conditions.Operation.AND, conditions);
	}
	
	public static Conditions or(Condition... conditions){
		return new Conditions(Conditions.Operation.OR, conditions);
	}

	public static Conditions or(Conditions... conditions){
		return new Conditions(Conditions.Operation.OR, conditions);
	}
	
	public static Condition equal(String column, Object value){
		return new Condition(column, value, Evaluation.EQUALS);
	}
	
	public static Condition notEqual(String column, Object value){
		return new Condition(column, value, Evaluation.NOT_EQUALS);
	}

	public static Condition in(String column, Object[] value){
		return new Condition(column, value, Evaluation.IN);
	}

	public static Condition notIn(String column, Object[] value){
		return new Condition(column, value, Evaluation.NOT_IN);
	}

	public static Condition isNull(String column){
		return new Condition(column, null, Evaluation.IS_NULL);
	}
	
	public static Condition notNull(String column){
		return new Condition(column, null, Evaluation.NOT_NULL);
	}
	
	//// GROUP BY
	
	public String getGroupByClause(){
		return groupByClause.equals("") ? groupByClause : " GROUP BY " + groupByClause;
	}
	
	public void setGroupByClause(String groupByClause){
		this.groupByClause = groupByClause;
	}
	
	public SqlBuilder groupBy(String... columns){
		StringBuilder builder = new StringBuilder();
		boolean flag = false;
		for (String column: columns){
			if (flag){
				builder.append(", ");
			}
			flag = true;
			builder.append(column);
		}
		groupByClause = builder.toString();
		return this;
	}

	//// ORDER BY
	
	public String getOrderByClause(){
		return orderByClause.equals("") ? orderByClause : " ORDER BY " + orderByClause;
	}
	
	public void setOrderByClause(String orderByClause){
		this.orderByClause = orderByClause;
	}
	
	public SqlBuilder orderBy(Sort sort){
		StringBuilder builder = new StringBuilder();
		boolean flag = false;
		Iterator<Sort.Order> orders = sort.iterator();
		while (orders.hasNext()){
			Sort.Order order = orders.next();
			if (flag){
				builder.append(", ");
			}
			flag = true;
			builder.append(order.getProperty() + " " + order.getDirection().toString());
		}
		orderByClause = builder.toString();
		return this;
	}
	
	public SqlBuilder orderBy(Sort.Order... orders){
		StringBuilder builder = new StringBuilder();
		boolean flag = false;
		for (Sort.Order order: orders){
			if (flag){
				builder.append(", ");
			}
			flag = true;
			builder.append(order.getProperty() + " " + order.getDirection().toString());
		}
		orderByClause = builder.toString();
		return this;
	}
	
	public SqlBuilder orderBy(String column, Sort.Direction direction){
		orderByClause = column + " " + direction.toString();
		return this;
	}

	public SqlBuilder orderBy(String column){
		orderByClause = column + " ASC";
		return this;
	}
	
	//// LIMIT
	
	public String getLimitClause(){
		return limitClause.equals("") ? limitClause : " LIMIT " + limitClause;
	}
	
	public void setLimitClause(String limitClause){
		this.limitClause = limitClause;
	}
	
	public SqlBuilder limit(Integer offset, Integer count){
		limitClause = offset.toString() + "," + count.toString();
		return this;
	}
	
	public SqlBuilder limit(Integer count){
		limitClause = count.toString();
		return this;
	}
	
	public SqlBuilder limit(Pageable pageable){
		limitClause = String.valueOf(pageable.getOffset()) + "," + String.valueOf(pageable.getPageSize());
		return this;
	}
	
	public String getDeleteClause(){
		return "DELETE ";
	}
	
	//// Output
	
	public String toSql(){
		String sql;
		switch (mode){
			case SELECT:
				sql = getSelectClause() + getFromClause() + getWhereClause() + getGroupByClause() + getOrderByClause() + getLimitClause();
				break;
			case INSERT:
				sql = getInsertClause();
				break;
			case UPDATE:
				sql = getUpdateClause() + getWhereClause();
				break;
			case DELETE:
				sql = getDeleteClause() + getFromClause() + getWhereClause();
				break;
			default:
				sql = getSelectClause() + getFromClause() + getWhereClause() + getGroupByClause() + getOrderByClause() + getLimitClause();
		}
		System.out.println(sql);
		return sql;
	}
	
	public List<Object> getQueryParameterValues(){
		return queryParameterValues;
	}
	
}