Java tutorial
/** * e3db Fast database library for Grails * Copyright (C) 2009-2010 Collegeman.net, LLC * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License along * with this program; if not, write to the Free Software Foundation, Inc., * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. */ package net.collegeman.grails.e3db; import java.util.*; import java.sql.*; import javax.sql.*; import groovy.lang.*; import org.springframework.context.ApplicationContext; import org.springframework.util.StringUtils; import org.springframework.beans.factory.NoSuchBeanDefinitionException; import org.springframework.jdbc.core.simple.*; import org.springframework.jdbc.datasource.*; public class SqlBuffer extends GroovyObjectSupport { public static final String LIST_PARAM_MODE = "list"; public static final String MAP_PARAM_MODE = "map"; private String paramMode; protected String conjunction = " "; protected List<String> sql = new ArrayList<String>(); protected List<Object> queryParamList = new ArrayList<Object>(); protected Map<String, Object> queryParamMap = new HashMap<String, Object>(); protected Integer maxResults = null; protected Integer firstResult = null; public SqlBuffer() { } public SqlBuffer(String conjunction) { this.conjunction = " " + conjunction.trim() + " "; } public final void sql(String sql, Object... args) { this.sql.add(sql); if (args != null && args.length > 0) { if (args.getClass().isArray() && args[0] instanceof Map) { if (paramMode != MAP_PARAM_MODE) { if (paramMode != null && queryParamList.size() > 0) { throw new IllegalStateException( "Cannot add a named parameter to this query: it is already in unnamed parameter mode: " + queryParamList); } else { paramMode = MAP_PARAM_MODE; } } Map mapArg = (Map) args[0]; for (Object key : mapArg.keySet()) { queryParamMap.put(String.valueOf(key), mapArg.get(key)); } } else { if (paramMode != LIST_PARAM_MODE) { if (paramMode != null && queryParamMap.size() > 0) { throw new IllegalStateException( "Cannot add an unnamed parameter to this query: it is already in named parameter mode: " + queryParamMap); } else { paramMode = LIST_PARAM_MODE; } } // if args[0] is a List, convert the list to an array args = args[0] instanceof List ? ((List) args[0]).toArray() : args; for (Object arg : args) { queryParamList.add(arg); } } } } public final void maxResults(Object maxResults) { if (maxResults != null) this.maxResults = Integer.parseInt(String.valueOf(maxResults)); } public final Integer getMaxResults() { return maxResults; } public final void firstResult(Object firstResult) { if (firstResult != null) this.firstResult = Integer.parseInt(String.valueOf(firstResult)); } public final void page(Object page) { if (maxResults == null) throw new IllegalStateException("When using page(), you must first define maxResults()"); if (page != null) { firstResult((Integer.parseInt(String.valueOf(page)) - 1) * getMaxResults()); } } public final Integer getFirstResult() { return firstResult; } public final Object getQueryParams() { return (paramMode == MAP_PARAM_MODE) ? queryParamMap : queryParamList; } public final String getSql() { return StringUtils.collectionToDelimitedString(sql, conjunction); } public final void select(Closure closure) { SqlBuffer buf = new SqlBuffer(); closure.setDelegate(buf); closure.setResolveStrategy(Closure.DELEGATE_FIRST); closure.call(); String select = buf.getSql(); if (hasLength(select)) { sql("SELECT " + select, buf.getQueryParams()); } // inherit maxResults and firstResult properties if (buf.getMaxResults() != null) maxResults(buf.getMaxResults()); if (buf.getFirstResult() != null) firstResult(buf.getFirstResult()); } public final void where(Closure closure) { SqlBuffer buf = new SqlBuffer("AND"); closure.setDelegate(buf); closure.setResolveStrategy(Closure.DELEGATE_FIRST); closure.call(); String where = buf.getSql(); if (hasLength(where)) { sql("WHERE " + where, buf.getQueryParams()); } } public final void exists(Closure closure) { SqlBuffer buf = new SqlBuffer(); closure.setDelegate(buf); closure.setResolveStrategy(Closure.DELEGATE_FIRST); closure.call(); String exists = buf.getSql(); if (hasLength(exists)) { sql("EXISTS ( " + exists + ")", buf.getQueryParams()); } } public final void add(Closure closure) { injunction("AND", closure); } public final void or(Closure closure) { injunction("OR", closure); } public final void format(String string, Object... args) { try { sql(String.format(string, args)); } catch (IllegalFormatConversionException e) { throw new IllegalArgumentException("Failed to format [" + string + "] with arguments [" + args + "]"); } } private void injunction(String type, Closure closure) { SqlBuffer buf = new SqlBuffer(type); closure.setDelegate(buf); closure.setResolveStrategy(Closure.DELEGATE_FIRST); closure.call(); String injunction = buf.getSql(); if (hasLength(injunction)) { sql(buf.getSql(), buf.getQueryParams()); } } protected static final boolean hasLength(Object o) { if (o == null) return false; else return StringUtils.hasLength(String.valueOf(o)); } public final void prepend(String sql) { this.sql.add(0, sql); } public final boolean hasSql() { return (sql.size() > 0); } }