Hi!

I have developed a small template system for SQL complex seach queries. It is based on ANDed OR groups.

SQLTemplate.java:

Code:
package at.gv.bmf.tradaba.template;

import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import at.gv.bmf.tradaba.commons.util.StringUtil;

public abstract class SQLTemplate {

	protected Map<String, String> joinTable;
	protected Map<String, String> joinCond;
	protected Map<String, String> andCond;
	protected Map<String, String> orGroupCond;
	protected Map<String, InfixSQLTemplate> infixCond;
	protected String orderBy;

	public SQLTemplate() {
		joinTable = new HashMap<String, String>();
		joinCond = new HashMap<String, String>();
		andCond = new HashMap<String, String>();
		orGroupCond = new HashMap<String, String>();
		infixCond = new HashMap<String, InfixSQLTemplate>();
	}
	
	public abstract String getOrderBy();

	public abstract String getBasicWhereCond();

	public abstract String getSelectFromTable(String schema);

	public void addJoinTable(String key, String value)
			throws SQLTemplateException {
				if (! StringUtil.isEmpty(key) && ! StringUtil.isEmpty(value)) {
					joinTable.put(key, value);
				} else {
					throw new SQLTemplateException();
				}
			}

	public void addJoinCond(String key, String value)
			throws SQLTemplateException {
				if (! StringUtil.isEmpty(key) && ! StringUtil.isEmpty(value)) {
					joinCond.put(key, value);
				} else {
					throw new SQLTemplateException();
				}
			}

	public void addAndCond(String key, String value)
			throws SQLTemplateException {
				if (! StringUtil.isEmpty(key) && ! StringUtil.isEmpty(value)) {
					// Protect against SQL-Injection
					value = value.replaceAll(";", "");
					andCond.put(key, value);
				} else {
					throw new SQLTemplateException();
				}
			}

	public void addOrGroupCond(String key, String value)
			throws SQLTemplateException {
				if (! StringUtil.isEmpty(key) && ! StringUtil.isEmpty(value)) {
					// Protect against SQL-Injection
					value = value.replaceAll(";", "");
					if (orGroupCond.containsKey(key)) {
						orGroupCond.put(key, orGroupCond.get(key) + " or " + value);
					} else {
						orGroupCond.put(key, value);
					}
				} else {
					throw new SQLTemplateException();
				}
			}

	public void addInfixOrGroupCond(String key, InfixSQLTemplate template)
			throws SQLTemplateException {
				if (! StringUtil.isEmpty(key) && template != null) {
					if (template.getClass() != null) {
						if (infixCond.containsKey(key)) {
							infixCond.put(key, infixCond.get(key).addOrCond(template));
						} else {
							infixCond.put(key, new InfixSQLTemplate().addOrCond(template));
						}
					}
				} else {
					throw new SQLTemplateException();
				}
			}

	public Map<String, InfixSQLTemplate> getInfixCond() {
		return infixCond;
	}

	public void setOrderBy(String orderBy) {
		this.orderBy = orderBy;
	}

	public String render(String schema) {
		StringBuffer sql = new StringBuffer();
		sql.append(getSelectFromTable(schema)).append(" ");
		for (Entry<String, String> entry : joinTable.entrySet()) {
		    sql.append(", ").append(schema).append(".").append(entry.getValue()).append(" ");;
		}
		sql.append(getBasicWhereCond()).append(" ");
		for (Entry<String, String> entry : joinCond.entrySet()) {
		    sql.append("and ").append(entry.getValue()).append(" ");
		}
		for (Entry<String, String> entry : andCond.entrySet()) {
		    sql.append("and ").append(entry.getValue()).append(" ");;
		}
		for (Entry<String, String> entry : orGroupCond.entrySet()) {
		    sql.append("and ( ").append(entry.getValue()).append(") ");;
		}
		sql.append(getOrderBy());
		return sql.toString();
	}

}
InfixSQLTemplate.java:

Code:
package at.gv.bmf.tradaba.template;

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

import at.gv.bmf.tradaba.commons.util.StringUtil;

public class InfixSQLTemplate {

	private String cond;
	private List<InfixSQLTemplate> andGroupCond;
	private List<InfixSQLTemplate> orGroupCond;
	
	public InfixSQLTemplate() {
		andGroupCond = new ArrayList<InfixSQLTemplate>();
		orGroupCond = new ArrayList<InfixSQLTemplate>();
	}
	
	public InfixSQLTemplate(String cond) throws SQLTemplateException {
		if (! StringUtil.isEmpty(cond)) {
			// Protect against SQL-Injection
			cond = cond.replaceAll(";", "");
			this.cond = cond;
		} else {
			throw new SQLTemplateException();
		}
	}
	
	public String getCond() {
		return cond;
	}
	
	public InfixSQLTemplate addAndCond(InfixSQLTemplate template) throws SQLTemplateException {
		if (template != null) {
			andGroupCond.add(template);
			return this;
		} else {
			throw new SQLTemplateException();
		}
	}
	
	public InfixSQLTemplate addOrCond(InfixSQLTemplate template) throws SQLTemplateException {
		if (template != null) {
			orGroupCond.add(template);
			return this;
		} else {
			throw new SQLTemplateException();
		}
	}
	
	public StringBuffer render() {
		StringBuffer sb = new StringBuffer();
		if (andGroupCond != null && andGroupCond.size() > 0) {
			// group condition, AND
			sb.append(" ( ");
			int i = 0;
			for (InfixSQLTemplate template : andGroupCond) {
				sb.append(template.render());
				i++;
				if (i < andGroupCond.size()) {
					sb.append(" and ");
				}
			}
			sb.append(" ) ");
		} else if (orGroupCond != null && orGroupCond.size() > 0) {
			// group condition, OR
			sb.append(" ( ");
			int i = 0;
			for (InfixSQLTemplate template : orGroupCond) {
				sb.append(template.render());
				i++;
				if (i < orGroupCond.size()) {
					sb.append(" or ");
				}
			}
			sb.append(" ) ");
		} else if (cond != null) {
			sb.append(" ");
			sb.append(cond);
			sb.append(" ");
		}
		return sb;
	}
	
}
Usage:

extend SQLTemplate and fill in the abstract methods.

call
addJoinTable
addJoinCond
addAndCond
addOrGroupCond
(addInfixOrGroupCond)

at the end:

call
render

Enjoy!

Waiting for change requests, ideas and opinions.

GV.