001package com.hfg.sql;
002
003import java.sql.Connection;
004import java.sql.SQLException;
005import java.util.ArrayList;
006import java.util.Collection;
007
008import com.hfg.sql.jdbc.JDBCException;
009import com.hfg.sql.table.DatabaseRow;
010import com.hfg.sql.table.DatabaseTable;
011import com.hfg.sql.table.field.DatabaseField;
012import com.hfg.util.StringBuilderPlus;
013import com.hfg.util.StringUtil;
014import com.hfg.util.collection.CollectionUtil;
015
016//------------------------------------------------------------------------------
017/**
018 Object for doing SQL updates.
019 <div>
020 @author J. Alex Taylor, hairyfatguy.com
021 </div>
022 */
023//------------------------------------------------------------------------------
024// com.hfg XML/HTML Coding Library
025//
026// This library is free software; you can redistribute it and/or
027// modify it under the terms of the GNU Lesser General Public
028// License as published by the Free Software Foundation; either
029// version 2.1 of the License, or (at your option) any later version.
030//
031// This library is distributed in the hope that it will be useful,
032// but WITHOUT ANY WARRANTY; without even the implied warranty of
033// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
034// Lesser General Public License for more details.
035//
036// You should have received a copy of the GNU Lesser General Public
037// License along with this library; if not, write to the Free Software
038// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
039//
040// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
041// jataylor@hairyfatguy.com
042//------------------------------------------------------------------------------
043
044public class SQLUpdate extends SQLCmd
045{
046   private DatabaseTable mTable;
047   private Collection<DatabaseField> mFieldList;
048
049   //###########################################################################
050   // CONSTRUCTORS
051   //###########################################################################
052
053   //---------------------------------------------------------------------------
054   public SQLUpdate()
055   {
056   }
057
058   //---------------------------------------------------------------------------
059   public SQLUpdate(DatabaseRow inRow)
060   {
061      setTable(inRow.getDatabaseTable());
062      // We only want to update dirty fields
063      for (DatabaseField field : inRow.getFields())
064      {
065         if (field.getCol().isId())
066         {
067            addClause(new WhereClause(field.getCol().name() + " = " + field.getSQLValue()));
068         }
069         else if (field.isDirty())
070         {
071            addSetField(field);
072         }
073      }
074   }
075
076   //###########################################################################
077   // PUBLIC METHODS
078   //###########################################################################
079
080   //---------------------------------------------------------------------------
081   public SQLUpdate setTable(DatabaseTable inValue)
082   {
083      mTable = inValue;
084
085      return this;
086   }
087
088   //---------------------------------------------------------------------------
089   public SQLUpdate addSetField(DatabaseField inValue)
090   {
091      if (null == mFieldList)
092      {
093         mFieldList = new ArrayList<>(50);
094      }
095
096      mFieldList.add(inValue.setIsDirty(true));
097
098      return this;
099   }
100
101   //---------------------------------------------------------------------------
102   public boolean hasSetFields()
103   {
104      return CollectionUtil.hasValues(mFieldList);
105   }
106
107   //---------------------------------------------------------------------------
108   public Collection<DatabaseField> getSetFields()
109   {
110      return mFieldList;
111   }
112
113   //---------------------------------------------------------------------------
114   public SQLUpdate setFields(Collection<DatabaseField> inValue)
115   {
116      if (CollectionUtil.hasValues(inValue))
117      {
118         for (DatabaseField field : inValue)
119         {
120            addSetField(field);
121         }
122      }
123
124      return this;
125   }
126
127   //---------------------------------------------------------------------------
128   @Override
129   public SQLUpdate addClause(SQLClause inValue)
130   {
131      super.addClause(inValue);
132
133      return this;
134   }
135
136   //---------------------------------------------------------------------------
137   public SQLUpdate addWhereClause(String inValue)
138   {
139      super.addClause(new WhereClause(inValue));
140
141      return this;
142   }
143
144   //---------------------------------------------------------------------------
145   public SQLUpdate addWhereClause(DatabaseField inValue)
146   {
147      super.addClause(new WhereClause(inValue));
148
149      return this;
150   }
151
152   //---------------------------------------------------------------------------
153   public SQLUpdate addWhereClauseGroup(WhereClauseGroup inValue)
154   {
155      return (SQLUpdate) super.addWhereClauseGroup(inValue);
156   }
157
158   //---------------------------------------------------------------------------
159   public int execute(Connection inConn)
160         throws SQLException
161   {
162      int result = SQLUtil.executeUpdate(inConn, toSQL());
163
164      if (result > 0
165          && mFieldList != null)
166      {
167         // Since the fields have been updated, clear the dirty flags.
168         for (DatabaseField field : mFieldList)
169         {
170            field.setIsDirty(false);
171         }
172      }
173
174      return result;
175   }
176
177   //---------------------------------------------------------------------------
178   public String toSQL()
179   {
180      if (null == mTable)
181      {
182         throw new JDBCException("No table has been specified for update!");
183      }
184      else if (null == mFieldList)
185      {
186         throw new JDBCException("No fields have been specified for update!");
187      }
188
189      StringBuilderPlus sql = new StringBuilderPlus("UPDATE ");
190      sql.appendln(mTable.getQualifiedName() + (StringUtil.isSet(mTable.getAlias()) ? " " + mTable.getAlias() : ""));
191
192      StringBuilderPlus setClauses = new StringBuilderPlus().setDelimiter(", ");
193      for (DatabaseField field : mFieldList)
194      {
195         if (field.isDirty())
196         {
197            setClauses.delimitedAppend(field.getCol().name() + " = " + field.getSQLValue());
198         }
199      }
200      sql.appendln("SET " + setClauses.toString());
201
202      sql.appendln(generateWhereClause());
203
204      return sql.toString();
205   }
206
207   //---------------------------------------------------------------------------
208   @Override
209   public String toString()
210   {
211      return toSQL();
212   }
213}