001package com.hfg.sql.table;
002
003import java.sql.Connection;
004import java.sql.ResultSet;
005import java.sql.SQLException;
006import java.util.ArrayList;
007import java.util.Collection;
008import java.util.Collections;
009import java.util.List;
010import java.util.function.Function;
011
012import com.hfg.exception.ProgrammingException;
013import com.hfg.sql.SQLClause;
014import com.hfg.sql.SQLQuery;
015import com.hfg.sql.SQLUtil;
016import com.hfg.sql.WhereClause;
017import com.hfg.sql.jdbc.JDBCException;
018import com.hfg.sql.jdbc.RDBMS;
019import com.hfg.sql.jdbc.SQLStatementOptions;
020import com.hfg.sql.jdbc.Schema;
021import com.hfg.util.CompareUtil;
022import com.hfg.util.StringBuilderPlus;
023import com.hfg.util.collection.CollectionUtil;
024import com.hfg.util.StringUtil;
025
026//------------------------------------------------------------------------------
027/**
028 Database table object.
029 <div>
030 @author J. Alex Taylor, hairyfatguy.com
031 </div>
032 */
033//------------------------------------------------------------------------------
034// com.hfg XML/HTML Coding Library
035//
036// This library is free software; you can redistribute it and/or
037// modify it under the terms of the GNU Lesser General Public
038// License as published by the Free Software Foundation; either
039// version 2.1 of the License, or (at your option) any later version.
040//
041// This library is distributed in the hope that it will be useful,
042// but WITHOUT ANY WARRANTY; without even the implied warranty of
043// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
044// Lesser General Public License for more details.
045//
046// You should have received a copy of the GNU Lesser General Public
047// License along with this library; if not, write to the Free Software
048// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
049//
050// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
051// jataylor@hairyfatguy.com
052//------------------------------------------------------------------------------
053
054
055public class DatabaseTable<T extends DatabaseRow> implements Cloneable, Comparable<DatabaseTable<T>>
056{
057   private String mName;
058   private String mAlias;
059   private Schema mSchema;
060   private RDBMS  mRDBMS = sDefaultRDBMS;
061   private List<DatabaseCol> mCols;
062   private Function<ResultSet, T> mResultSetConstructorFn;
063
064
065   private String mCachedQualifiedName;
066
067   private static RDBMS sDefaultRDBMS = RDBMS.PostgreSQL;
068
069   //###########################################################################
070   // CONSTRUCTORS
071   //###########################################################################
072
073   //---------------------------------------------------------------------------
074   public DatabaseTable(String inName, Function<ResultSet, T> inResultSetConstructorFn)
075   {
076      mName = inName;
077      mResultSetConstructorFn = inResultSetConstructorFn;
078   }
079
080   //###########################################################################
081   // PUBLIC METHODS
082   //###########################################################################
083
084   //---------------------------------------------------------------------------
085   public static void setDefaultRDBMS(RDBMS inValue)
086   {
087      if (null == inValue)
088      {
089         throw new JDBCException("The default RDBMS cannot be set to null!");
090      }
091
092      sDefaultRDBMS = inValue;
093   }
094
095   //---------------------------------------------------------------------------
096   public static RDBMS getDefaultRDBMS()
097   {
098      return sDefaultRDBMS;
099   }
100
101   //---------------------------------------------------------------------------
102   public DatabaseTable<T> setRDBMS(RDBMS inValue)
103   {
104      mRDBMS = inValue;
105      return this;
106   }
107
108   //---------------------------------------------------------------------------
109   public RDBMS getRDBMS()
110   {
111      return mRDBMS;
112   }
113
114
115   //---------------------------------------------------------------------------
116   @Override
117   public int compareTo(DatabaseTable<T> inObj2)
118   {
119      int result = 1;
120
121      if (inObj2 != null)
122      {
123         result = CompareUtil.compare(getQualifiedName(), inObj2.getQualifiedName());
124      }
125
126      return result;
127   }
128
129   //---------------------------------------------------------------------------
130   @Override
131   public boolean equals(Object inObj2)
132   {
133      return (inObj2 != null
134              && inObj2 instanceof DatabaseTable
135              && 0 == compareTo((DatabaseTable) inObj2));
136   }
137
138   //---------------------------------------------------------------------------
139   @Override
140   public int hashCode()
141   {
142      return getQualifiedName().hashCode();
143   }
144
145
146   //---------------------------------------------------------------------------
147   public DatabaseTable<T> setName(String inValue)
148   {
149      mName = inValue;
150      mCachedQualifiedName = null;
151      return this;
152   }
153
154   //---------------------------------------------------------------------------
155   public String name()
156   {
157      return mName;
158   }
159
160   //---------------------------------------------------------------------------
161   public String getQualifiedName()
162   {
163      if (null == mCachedQualifiedName)
164      {
165         mCachedQualifiedName = (mSchema != null ? mSchema.name() + "." : "") + name();
166      }
167
168      return mCachedQualifiedName;
169   }
170
171   //---------------------------------------------------------------------------
172   @Override
173   public String toString()
174   {
175      return getQualifiedName();
176   }
177
178   //---------------------------------------------------------------------------
179   public DatabaseTable<T> clone()
180   {
181      DatabaseTable<T> clone;
182      try
183      {
184         clone = (DatabaseTable<T>) super.clone();
185      }
186      catch (CloneNotSupportedException e)
187      {
188         throw new ProgrammingException(e);
189      }
190
191      for (DatabaseCol col : getCols())
192      {
193         clone.addCol(col.clone());
194      }
195
196      return clone;
197   }
198
199   //---------------------------------------------------------------------------
200   public DatabaseTable setAlias(String inValue)
201   {
202      mAlias = inValue;
203      return this;
204   }
205
206   //---------------------------------------------------------------------------
207   public String getAlias()
208   {
209      return mAlias;
210   }
211
212   //---------------------------------------------------------------------------
213   public DatabaseTable setSchema(Schema inValue)
214   {
215      mSchema = inValue;
216      mCachedQualifiedName = null;
217      return this;
218   }
219
220   //---------------------------------------------------------------------------
221   public Schema getSchema()
222   {
223      return mSchema;
224   }
225
226   //---------------------------------------------------------------------------
227   public List<DatabaseCol> getCols()
228   {
229      return Collections.unmodifiableList(mCols);
230   }
231
232   //---------------------------------------------------------------------------
233   public DatabaseCol getCol(String inColName)
234   {
235      DatabaseCol requestedCol = null;
236
237      if (mCols != null)
238      {
239         for (DatabaseCol col : mCols)
240         {
241            if (col.name().equalsIgnoreCase(inColName))
242            {
243               requestedCol = col;
244               break;
245            }
246         }
247      }
248
249      return requestedCol;
250   }
251
252   //---------------------------------------------------------------------------
253   public Collection<DatabaseCol> getTaggedCols(String inTag)
254   {
255      Collection<DatabaseCol> taggedCols = null;
256
257      if (mCols != null)
258      {
259         for (DatabaseCol col : mCols)
260         {
261            if (col.tagged(inTag))
262            {
263               if (null == taggedCols)
264               {
265                  taggedCols = new ArrayList<>(4);
266               }
267
268               taggedCols.add(col);
269            }
270         }
271      }
272
273      return taggedCols;
274   }
275
276   //---------------------------------------------------------------------------
277   public void addCol(DatabaseCol inCol)
278   {
279      if (null == mCols)
280      {
281         mCols = new ArrayList<>(50);
282      }
283
284      if (! mCols.contains(inCol))
285      {
286         mCols.add(inCol);
287      }
288
289      inCol.setTable(this);
290   }
291
292   //---------------------------------------------------------------------------
293   public DatabaseCol getIdCol()
294   {
295      DatabaseCol idCol = null;
296
297      if (mCols != null)
298      {
299         for (DatabaseCol col : mCols)
300         {
301            if (col.isId())
302            {
303               idCol = col;
304               break;
305            }
306         }
307      }
308
309      return idCol;
310   }
311
312   //---------------------------------------------------------------------------
313   public T getRow(Connection inConn, SQLQuery inQuery)
314         throws JDBCException
315   {
316      T requestedRow = null;
317
318      List<T> rows = getRows(inConn, inQuery);
319
320      if (CollectionUtil.hasValues(rows))
321      {
322         if (rows.size() > 1)
323         {
324            throw new JDBCException(rows.size() + " " + name() + " rows found with query " + StringUtil.singleQuote(inQuery) + "! Expected only one.");
325         }
326
327         requestedRow = rows.get(0);
328      }
329
330      return requestedRow;
331   }
332
333   //---------------------------------------------------------------------------
334   public T getRow(Connection inConn, List<SQLClause> inSQLClauses)
335         throws JDBCException
336   {
337      SQLQuery query = getBaseQuery();
338
339      if (CollectionUtil.hasValues(inSQLClauses))
340      {
341         for (SQLClause clause : inSQLClauses)
342         {
343            query.addClause(clause);
344         }
345      }
346
347      return getRow(inConn, query);
348   }
349
350   //---------------------------------------------------------------------------
351   public T getRow(Connection inConn, SQLClause inClause)
352         throws JDBCException
353   {
354      SQLQuery query = getBaseQuery().addClause(inClause);
355
356      return getRow(inConn, query);
357   }
358
359   //---------------------------------------------------------------------------
360   public T getRowById(Connection inConn, Long inId)
361         throws JDBCException
362   {
363      return getRow(inConn, new WhereClause(this.getIdCol() + " = " + inId));
364   }
365
366   //---------------------------------------------------------------------------
367   public T getRowById(Connection inConn, Integer inId)
368         throws JDBCException
369   {
370      return getRow(inConn, new WhereClause(this.getIdCol() + " = " + inId));
371   }
372
373   //---------------------------------------------------------------------------
374   public List<T> getRows(Connection inConn)
375         throws JDBCException
376   {
377      return getRows(inConn, (List<SQLClause>) null);
378   }
379
380   //---------------------------------------------------------------------------
381   public List<T> getRows(Connection inConn, List<SQLClause> inSQLClauses)
382         throws JDBCException
383   {
384      SQLQuery query = getBaseQuery();
385
386      if (CollectionUtil.hasValues(inSQLClauses))
387      {
388         for (SQLClause clause : inSQLClauses)
389         {
390            query.addClause(clause);
391         }
392      }
393
394      return getRows(inConn, query);
395   }
396
397   //---------------------------------------------------------------------------
398   public List<T> getRows(Connection inConn, SQLClause inClause)
399         throws JDBCException
400   {
401      SQLQuery query = getBaseQuery().addClause(inClause);
402
403      return getRows(inConn, query);
404   }
405
406   //---------------------------------------------------------------------------
407   public List<T> getRows(Connection inConn, SQLClause... inClauses)
408         throws JDBCException
409   {
410      SQLQuery query = getBaseQuery();
411      for (SQLClause clause : inClauses)
412      {
413         query.addClause(clause);
414      }
415
416      return getRows(inConn, query);
417   }
418
419   //---------------------------------------------------------------------------
420   public List<T> getRows(Connection inConn, SQLQuery inQuery)
421         throws JDBCException
422   {
423      return getRows(inConn, inQuery, null);
424   }
425
426   //---------------------------------------------------------------------------
427   public List<T> getRows(Connection inConn, SQLQuery inQuery, SQLStatementOptions inOptions)
428         throws JDBCException
429   {
430      ArrayList<T> rows = null;
431
432      ResultSet rs = null;
433      try
434      {
435         rs = inQuery.execute(inConn, inOptions);
436
437         while (rs.next())
438         {
439            if (null == rows)
440             {
441               rows = new ArrayList<T>();
442            }
443
444            T row = mResultSetConstructorFn.apply(rs);
445            // Initially blessing the row should no longer be necessary.
446 //           row.bless(); // It's fresh from the database
447
448            rows.add(row);
449         }
450
451         if (rows != null)
452         {
453            // Don't waste extra capacity in the ArrayList
454            rows.trimToSize();
455         }
456      }
457      catch (SQLException e)
458      {
459         throw new JDBCException("Problem getting rows from " + name() + "!", e);
460      }
461      finally
462      {
463         SQLUtil.closeResultSetAndStatement(rs);
464      }
465
466      return rows;
467   }
468
469
470
471   //---------------------------------------------------------------------------
472   public int getRowCount(Connection inConn)
473         throws SQLException
474   {
475      return getRowCount(inConn, (Collection<SQLClause>) null);
476   }
477 
478   //---------------------------------------------------------------------------
479   public int getRowCount(Connection inConn, SQLClause inClause)
480         throws SQLException
481   {
482      List<SQLClause> clauses = new ArrayList<>(1);
483      clauses.add(inClause);
484
485      return getRowCount(inConn, clauses);
486   }
487
488   //---------------------------------------------------------------------------
489   public int getRowCount(Connection inConn, Collection<SQLClause> inSQLClauses)
490         throws SQLException
491   {
492      return SQLUtil.getRowCount(inConn, this, inSQLClauses);
493   }
494
495
496   //---------------------------------------------------------------------------
497   public SQLQuery getBaseQuery()
498   {
499      SQLQuery query = new SQLQuery();
500
501      if (containsColumnsNotRetrievedByDefault())
502      {
503         StringBuilderPlus colList = new StringBuilderPlus().setDelimiter(", ");
504         for (DatabaseCol col : getCols())
505         {
506            if (col.getRetrievedByDefault())
507            {
508               colList.delimitedAppend(col.name());
509            }
510         }
511
512         query.addSelect(colList.toString());
513      }
514      else
515      {
516         query.addSelect((StringUtil.isSet(getAlias()) ? getAlias() + "." : "") + "*");
517      }
518
519      query.addFrom(this);
520
521      return query;
522   }
523
524   //---------------------------------------------------------------------------
525   public boolean exists(Connection inConn)
526         throws SQLException
527   {
528      return getRDBMS().tableExists(inConn, getSchema(), name());
529   }
530
531   //---------------------------------------------------------------------------
532   public void drop(Connection inConn)
533         throws SQLException
534   {
535      SQLUtil.execute(inConn, "DROP TABLE " + getQualifiedName());
536   }
537
538   //---------------------------------------------------------------------------
539   public void truncate(Connection inConn)
540         throws SQLException
541   {
542      SQLUtil.execute(inConn, "TRUNCATE TABLE " + getQualifiedName());
543   }
544
545   //---------------------------------------------------------------------------
546   public void renameTo(Connection inConn, String inNewName)
547         throws SQLException
548   {
549      SQLUtil.execute(inConn, "ALTER TABLE " + getQualifiedName() + " RENAME TO " + inNewName);
550   }
551
552   //###########################################################################
553   // PRIVATE METHODS
554   //###########################################################################
555
556   //---------------------------------------------------------------------------
557   private boolean containsColumnsNotRetrievedByDefault()
558   {
559      boolean result = false;
560
561      for (DatabaseCol col : getCols())
562      {
563         if (! col.getRetrievedByDefault())
564         {
565            result = true;
566            break;
567         }
568      }
569
570      return result;
571   }
572
573}