Skip to content

Instantly share code, notes, and snippets.

@woemler

woemler/ComplexTableDescription.java

Created May 12, 2015 14:37
Show Gist options
  • Save woemler/c6f901bc1ecc3c12eb2e to your computer and use it in GitHub Desktop.
Save woemler/c6f901bc1ecc3c12eb2e to your computer and use it in GitHub Desktop.
Download ZIP
SQL building for more dynamic JDBC operations in Spring JdbcTemplates.
Raw
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;
}
}
Raw
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;
}
}
Raw
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;
}
}
Raw
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
;
}
Raw
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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment