001package com.hfg.sql;
002
003import com.hfg.sql.jdbc.JDBCException;
004import com.hfg.sql.jdbc.SQLStatementOptions;
005import com.hfg.sql.table.DatabaseCol;
006import com.hfg.sql.table.DatabaseTable;
007import com.hfg.sql.table.field.DatabaseField;
008import com.hfg.util.collection.CollectionUtil;
009import com.hfg.util.StringBuilderPlus;
010import com.hfg.util.StringUtil;
011import com.hfg.util.collection.OrderedSet;
012
013import java.sql.Connection;
014import java.sql.ResultSet;
015import java.sql.SQLException;
016import java.util.ArrayList;
017import java.util.Collection;
018import java.util.List;
019
020//------------------------------------------------------------------------------
021/**
022 Container for building a SQL query.
023 <div>
024 @author J. Alex Taylor, hairyfatguy.com
025 </div>
026 */
027//------------------------------------------------------------------------------
028// com.hfg XML/HTML Coding Library
029//
030// This library is free software; you can redistribute it and/or
031// modify it under the terms of the GNU Lesser General Public
032// License as published by the Free Software Foundation; either
033// version 2.1 of the License, or (at your option) any later version.
034//
035// This library is distributed in the hope that it will be useful,
036// but WITHOUT ANY WARRANTY; without even the implied warranty of
037// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
038// Lesser General Public License for more details.
039//
040// You should have received a copy of the GNU Lesser General Public
041// License along with this library; if not, write to the Free Software
042// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
043//
044// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
045// jataylor@hairyfatguy.com
046//------------------------------------------------------------------------------
047
048public class SQLQuery extends SQLCmd
049{
050   private List<String> mSelectList;
051   private OrderedSet<String> mFromClauses;
052   private List<JoinClause> mJoinClauses;
053   private List<String> mGroupByList;
054
055   private SQLClause mOffsetClause;
056   private SQLClause mLimtClause;
057
058   //###########################################################################
059   // PUBLIC METHODS
060   //###########################################################################
061
062   //---------------------------------------------------------------------------
063   public ResultSet execute(Connection inConn)
064      throws SQLException
065   {
066      return SQLUtil.executeQuery(inConn, toSQL());
067   }
068
069   //---------------------------------------------------------------------------
070   public ResultSet execute(Connection inConn, SQLStatementOptions inOptions)
071      throws SQLException
072   {
073      return SQLUtil.executeQuery(inConn, toSQL(), inOptions);
074   }
075
076   //---------------------------------------------------------------------------
077   public String toSQL()
078   {
079      StringBuilderPlus sql = new StringBuilderPlus("SELECT ");
080      sql.appendln(StringUtil.join(mSelectList, ", "));
081
082      if (CollectionUtil.hasValues(mFromClauses))
083      {
084         sql.append("  FROM ");
085         sql.appendln(StringUtil.join(mFromClauses, ", "));
086      }
087
088      if (CollectionUtil.hasValues(mJoinClauses))
089      {
090         for (JoinClause joinClause : mJoinClauses)
091         {
092            sql.append("  " + joinClause.getJoinType() + " ")
093               .append(joinClause.toSQL());
094         }
095      }
096
097      sql.append(generateWhereClause());
098      sql.append(generateGroupByClause());
099      sql.append(generateOrderByClause());
100      sql.append(generateOffsetClause());
101      sql.append(generateLimitClause());
102
103      return sql.toString();
104   }
105
106   //---------------------------------------------------------------------------
107   @Override
108   public String toString()
109   {
110      return toSQL();
111   }
112
113   //---------------------------------------------------------------------------
114   @Override
115   public SQLQuery addClauses(Collection<SQLClause> inClauses)
116   {
117      return (SQLQuery) super.addClauses(inClauses);
118   }
119
120   //---------------------------------------------------------------------------
121   @Override
122   public SQLQuery addClause(SQLClause inValue)
123      throws JDBCException
124   {
125      switch (inValue.getType())
126      {
127         // Handle query-specific clause types
128         case FROM:
129            mFromClauses.add(inValue.toSQL());
130            break;
131
132         case JOIN:
133            if (null == mJoinClauses)
134            {
135               mJoinClauses = new ArrayList<>(2);
136            }
137
138            JoinClause joinClause;
139            if (inValue instanceof JoinClause)
140            {
141               joinClause = (JoinClause) inValue;
142            }
143            else
144            {
145               joinClause = new JoinClause(inValue.toSQL());
146            }
147
148            mJoinClauses.add(joinClause);
149            break;
150
151         case GROUP_BY:
152            addGroupBy(inValue.toSQL());
153            break;
154
155         case OFFSET:
156            mOffsetClause = inValue;
157            break;
158
159         case LIMIT:
160            mLimtClause = inValue;
161            break;
162
163         // Handle general clause types
164         default:
165            super.addClause(inValue);
166      }
167
168      return this;
169   }
170
171   //---------------------------------------------------------------------------
172   public SQLQuery addSelect(DatabaseCol inColumn)
173   {
174      addSelect(inColumn.getQualifiedName());
175
176      return this;
177   }
178
179   //---------------------------------------------------------------------------
180   public SQLQuery addSelect(String inValue)
181   {
182      if (null == mSelectList)
183      {
184         mSelectList = new ArrayList<>(50);
185      }
186
187      mSelectList.add(inValue);
188
189      return this;
190   }
191
192   //---------------------------------------------------------------------------
193   public SQLQuery addFrom(DatabaseTable inValue)
194   {
195      return addFrom(inValue, inValue.getAlias());
196   }
197
198   //---------------------------------------------------------------------------
199   public SQLQuery addFrom(DatabaseTable inValue, String inAlias)
200   {
201      addFrom(inValue.getQualifiedName(), inAlias);
202
203      return this;
204   }
205
206   //---------------------------------------------------------------------------
207   public SQLQuery addFrom(String inValue, String inAlias)
208   {
209      if (null == mFromClauses)
210      {
211         mFromClauses = new OrderedSet<>(10);
212      }
213
214      mFromClauses.add(inValue + (StringUtil.isSet(inAlias) ? " " + inAlias : ""));
215
216      return this;
217   }
218
219   //---------------------------------------------------------------------------
220   public SQLQuery addFrom(String inValue)
221   {
222      if (null == mFromClauses)
223      {
224         mFromClauses = new OrderedSet<>(10);
225      }
226
227      mFromClauses.add(inValue);
228
229      return this;
230   }
231
232   //---------------------------------------------------------------------------
233   public SQLQuery addJoin(JoinClause inValue)
234   {
235      if (null == mJoinClauses)
236      {
237         mJoinClauses = new OrderedSet<>(5);
238      }
239
240      mJoinClauses.add(inValue);
241
242      return this;
243   }
244
245   //---------------------------------------------------------------------------
246   public SQLQuery addJoin(String inValue)
247   {
248      if (null == mJoinClauses)
249      {
250         mJoinClauses = new OrderedSet<>(5);
251      }
252
253      mJoinClauses.add(new JoinClause(inValue));
254
255      return this;
256   }
257
258   //---------------------------------------------------------------------------
259   public SQLQuery addWhereClause(String inValue)
260   {
261      super.addClause(new WhereClause(inValue));
262
263      return this;
264   }
265
266   //---------------------------------------------------------------------------
267   public SQLQuery addWhereClause(DatabaseField inValue)
268   {
269      super.addClause(new WhereClause(inValue));
270
271      return this;
272   }
273
274   //---------------------------------------------------------------------------
275   public SQLQuery addWhereClauseGroup(WhereClauseGroup inValue)
276   {
277      return (SQLQuery) super.addWhereClauseGroup(inValue);
278   }
279
280   //---------------------------------------------------------------------------
281   public SQLQuery addGroupBy(DatabaseCol inValue)
282   {
283      addGroupBy(inValue.getQualifiedName());
284
285      return this;
286   }
287
288   //---------------------------------------------------------------------------
289   public SQLQuery addGroupBy(String inValue)
290   {
291      if (null == mGroupByList)
292      {
293         mGroupByList = new ArrayList<>(50);
294      }
295
296      mGroupByList.add(inValue);
297
298      return this;
299   }
300
301   //---------------------------------------------------------------------------
302   public SQLQuery addOrderBy(String inValue)
303   {
304      addClause(new OrderByClause(inValue));
305
306      return this;
307   }
308
309   //---------------------------------------------------------------------------
310   public SQLQuery setLimit(int inValue)
311   {
312      addClause(new LimitClause(inValue));
313
314      return this;
315   }
316
317   //###########################################################################
318   // PRIVATE METHODS
319   //###########################################################################
320
321   //---------------------------------------------------------------------------
322   private String generateGroupByClause()
323   {
324      StringBuilderPlus sql = new StringBuilderPlus();
325      if (CollectionUtil.hasValues(mGroupByList))
326      {
327         sql.append("GROUP BY ");
328         sql.appendln(StringUtil.join(mGroupByList, ", "));
329      }
330
331      return sql.toString();
332   }
333
334   //---------------------------------------------------------------------------
335   private String generateOffsetClause()
336   {
337      StringBuilderPlus sql = new StringBuilderPlus();
338      if (mOffsetClause != null)
339      {
340         sql.append("OFFSET ");
341         sql.appendln(mOffsetClause.toString());
342      }
343
344      return sql.toString();
345   }
346
347   //---------------------------------------------------------------------------
348   private String generateLimitClause()
349   {
350      StringBuilderPlus sql = new StringBuilderPlus();
351      if (mLimtClause != null)
352      {
353         sql.append("LIMIT ");
354         sql.appendln(mLimtClause.toString());
355      }
356
357      return sql.toString();
358   }
359}