001/**
002 * Copyright 2014 Tampere University of Technology, Pori Department
003 * 
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 * 
008 *   http://www.apache.org/licenses/LICENSE-2.0
009 * 
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016package core.tut.pori.dao;
017
018import java.util.ArrayList;
019import java.util.Iterator;
020
021import org.springframework.jdbc.core.JdbcTemplate;
022
023import core.tut.pori.dao.clause.SQLClause;
024import core.tut.pori.dao.clause.WhereClause;
025import core.tut.pori.http.parameters.Limits;
026
027
028/**
029 * Helper class, which can be used to build an SQL DELETE clause.
030 * 
031 * Note that the class has no implementations of its own of the SQLClause, 
032 * but uses the same clauses as defined by SQLSelectBuilder (SQL WHERE).
033 */
034public class SQLDeleteBuilder {
035  private Limits _limits = null;
036  private String _tableName = null;
037  private ArrayList<SQLClause> _whereClauses = null;
038  
039  /**
040   * 
041   * @param table
042   */
043  public SQLDeleteBuilder(String table){
044    _tableName = table;
045  }
046  
047  
048  /**
049   * clear the currently set list of where clauses
050   */
051  public void clearWhereClauses(){
052    _whereClauses = null;
053  }
054  
055  /**
056   * 
057   * @return number of added where clauses
058   */
059  public int getWhereClauseCount() {
060    return (_whereClauses == null ? 0 : _whereClauses.size());
061  }
062  
063  /**
064   * @param startItem the startItem to set, negative value disables limit
065   */
066  public void setStartItem(int startItem) {
067    if(_limits == null){
068      _limits = new Limits(startItem, Limits.DEFAULT_MAX_ITEMS);
069    }else{
070      _limits.setTypeLimits(startItem, _limits.getEndItem(), null);
071    }
072  }
073
074  /**
075   * @param maxItems the maxItems to set, negative value disables limit
076   */
077  public void setMaxItems(int maxItems) {
078    if(_limits == null){
079      _limits = new Limits(0, maxItems-1);
080    }else{
081      _limits.setTypeLimits(_limits.getStartItem(), maxItems-1, null);
082    }
083  }
084  
085  /**
086   * 
087   * @param limits if null, this is a no-op
088   */
089  public void setLimits(Limits limits){
090    _limits = limits;
091  }
092  
093  /**
094   * 
095   * @param clause
096   */
097  public void addWhereClause(WhereClause clause){
098    if(_whereClauses == null){
099      _whereClauses = new ArrayList<>();
100    }
101    _whereClauses.add(clause);
102  }
103  
104  /**
105   * execute the statement on the given template
106   * @param template
107   * @return number of rows affected
108   */
109  public int execute(JdbcTemplate template){
110    return template.update(toSQLString(), getValues(), getValueTypes());
111  }
112  
113  /**
114   * execute the statement on the given template
115   * @param template
116   * @param type
117   * @return number of rows affected
118   */
119  public int execute(JdbcTemplate template, String type){
120    return template.update(toSQLString(type), getValues(), getValueTypes());
121  }
122  
123  /**
124   * 
125   * @return array of parameter values
126   */
127  public Object[] getValues(){
128    return SQLSelectBuilder.getValues(_whereClauses);
129  }
130  
131  /**
132   * 
133   * @return array of parameter types
134   */
135  public int[] getValueTypes(){
136    return SQLSelectBuilder.getValueTypes(_whereClauses);
137  }
138  
139  /**
140   * 
141   * @return the query as an SQL string
142   */
143  public String toSQLString(){
144    return toSQLString(null);
145  }
146
147  /**
148   * 
149   * @param type
150   * @return the query as an SQL string using the given type of typed parameters (e.g. Limits). See {@link core.tut.pori.http.parameters.Limits}
151   */
152  public String toSQLString(String type){
153    StringBuilder sql = new StringBuilder("DELETE FROM ");
154    sql.append(_tableName);
155
156    /* create where clauses */
157    if(_whereClauses != null){
158      sql.append(" WHERE ");
159      Iterator<SQLClause> iter = _whereClauses.iterator();
160      iter.next().toSQLString(sql);
161      while(iter.hasNext()){
162        WhereClause clause = (WhereClause) iter.next();
163        sql.append(clause.getClauseType().toClauseString());
164        clause.toSQLString(sql);
165      }
166    }
167    
168    /* create limit */
169    if(_limits != null){
170      sql.append(" LIMIT ");
171      sql.append(_limits.getStartItem(type));
172      sql.append(',');
173      sql.append(_limits.getMaxItems());
174    }
175    
176    return sql.toString();
177  }
178}