Announcement Announcement Module
Collapse
No announcement yet.
SQLTemplate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLTemplate

    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.
Working...
X