001package com.hfg.sql.table;
002
003import java.lang.reflect.Constructor;
004import java.sql.*;
005import java.util.*;
006
007import com.hfg.exception.ProgrammingException;
008import com.hfg.sql.*;
009import com.hfg.sql.jdbc.JDBCException;
010import com.hfg.sql.table.field.*;
011import com.hfg.util.StringBuilderPlus;
012import com.hfg.util.StringUtil;
013import com.hfg.util.collection.DirtyMap;
014import com.hfg.util.collection.OrderedSet;
015import com.hfg.xml.XMLTag;
016
017//------------------------------------------------------------------------------
018/**
019 Database row object.
020 <div>
021 @author J. Alex Taylor, hairyfatguy.com
022 </div>
023 */
024//------------------------------------------------------------------------------
025// com.hfg XML/HTML Coding Library
026//
027// This library is free software; you can redistribute it and/or
028// modify it under the terms of the GNU Lesser General Public
029// License as published by the Free Software Foundation; either
030// version 2.1 of the License, or (at your option) any later version.
031//
032// This library is distributed in the hope that it will be useful,
033// but WITHOUT ANY WARRANTY; without even the implied warranty of
034// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
035// Lesser General Public License for more details.
036//
037// You should have received a copy of the GNU Lesser General Public
038// License along with this library; if not, write to the Free Software
039// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
040//
041// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
042// jataylor@hairyfatguy.com
043//------------------------------------------------------------------------------
044
045
046public abstract class DatabaseRow implements Cloneable
047{
048   private Set<DatabaseField> mFields;
049   private Map<String, DatabaseField> mFieldMap;
050   private DatabaseCol mIdCol;
051
052
053   //###########################################################################
054   // CONSTRUCTORS
055   //###########################################################################
056
057   //---------------------------------------------------------------------------
058   public DatabaseRow(DatabaseTable<? extends DatabaseRow> inTable)
059   {
060      init(inTable, null);
061   }
062
063   //---------------------------------------------------------------------------
064   public DatabaseRow(DatabaseTable<? extends DatabaseRow> inTable, ResultSet inResultSet)
065   {
066//      this(inTable);
067//      bindResultSet(inResultSet);
068      init(inTable, inResultSet);
069   }
070
071   //---------------------------------------------------------------------------
072   public DatabaseRow(XMLTag inXMLTag)
073   {
074      init(getDatabaseTable(), null);
075      inXMLTag.verifyTagName(DatabaseXML.ROW);
076
077      List<XMLTag> fieldTags = inXMLTag.getSubtagsByName(DatabaseXML.FIELD);
078      for (XMLTag fieldTag : fieldTags)
079      {
080         addField(DatabaseField.instantiate(fieldTag, getDatabaseTable()));
081      }
082   }
083
084   //---------------------------------------------------------------------------
085   private void init(DatabaseTable<? extends DatabaseRow> inTable, ResultSet inResultSet)
086   {
087      // A large number of row objects could get created and since we know
088      // exactly how many columns there are, try to be memory efficient in
089      // allocating the field map.
090      List<DatabaseCol> cols = inTable.getCols();
091      mFields = new OrderedSet<>(cols.size());
092      mFieldMap = new DirtyMap<>(cols.size(), 1.0f);
093      for (DatabaseCol col : cols)
094      {
095         addField(allocateField(col, inResultSet));
096      }
097   }
098
099   //###########################################################################
100   // PUBLIC METHODS
101   //###########################################################################
102
103   //---------------------------------------------------------------------------
104   @SuppressWarnings("unchecked")
105   public static DatabaseRow instantiate(XMLTag inXMLTag)
106   {
107      DatabaseRow row;
108      try
109      {
110         Class clazz = Class.forName(inXMLTag.getAttributeValue(DatabaseXML.CLASS_ATT));
111         Constructor constructor = clazz.getConstructor(XMLTag.class);
112         row = (DatabaseRow) constructor.newInstance(inXMLTag);
113      }
114      catch (Exception e)
115      {
116         throw new JDBCException(e);
117      }
118
119      return row;
120   }
121
122/*
123   //---------------------------------------------------------------------------
124   protected List<? extends DatabaseRow> internalGetRows(Connection inConn, SQLQuery inQuery)
125      throws JDBCException
126   {
127      List<DatabaseRow> rows = null;
128
129      ResultSet rs = null;
130      try
131      {
132         rs = inQuery.execute(inConn);
133
134         while (rs.next())
135         {
136            if (null == rows)
137            {
138               rows = new ArrayList<DatabaseRow>();
139            }
140
141            rows.add(createRowFromResultSet(rs));
142         }
143      }
144      catch (SQLException e)
145      {
146         throw new JDBCException("Problem getting rows from " + getTable().name() + "!", e);
147      }
148      finally
149      {
150         SQLUtil.closeResultSetAndStatement(rs);
151      }
152
153      return rows;
154   }
155*/
156
157
158   //---------------------------------------------------------------------------
159   @SuppressWarnings("unchecked")
160   @Override
161   public DatabaseRow clone()
162   {
163      DatabaseRow cloneObj;
164      try
165      {
166         cloneObj = (DatabaseRow) super.clone();
167      }
168      catch (CloneNotSupportedException e)
169      {
170         throw new ProgrammingException(e);
171      }
172
173      cloneObj.mFields = new OrderedSet<>(mFieldMap.size());
174      cloneObj.mFieldMap = new HashMap<>(mFieldMap.size());
175      for (DatabaseField origField : getFields())
176      {
177         DatabaseField field = origField.clone();
178
179         if (field.getCol().isId())
180         {
181            // Clear the primary key
182            field.setValue(null);
183         }
184
185         cloneObj.addField(field);
186      }
187
188      return cloneObj;
189   }
190
191   //---------------------------------------------------------------------------
192   public XMLTag toXMLTag()
193   {
194      XMLTag tag = new XMLTag(DatabaseXML.ROW);
195      tag.setAttribute(DatabaseXML.CLASS_ATT, getClass().getName());
196
197      if (mFieldMap != null)
198      {
199         for (DatabaseField field : mFieldMap.values())
200         {
201            if (! field.isNull())
202            {
203               tag.addSubtag(field.toXMLTag());
204            }
205         }
206      }
207
208      return tag;
209   }
210
211   //---------------------------------------------------------------------------
212   public DatabaseField getField(DatabaseCol inCol)
213   {
214      return mFieldMap.get(inCol.name());
215   }
216
217   //---------------------------------------------------------------------------
218   public Collection<DatabaseField> getFields()
219   {
220      return mFields;
221   }
222
223   //---------------------------------------------------------------------------
224   public abstract DatabaseTable<? extends DatabaseRow> getDatabaseTable();
225
226   //---------------------------------------------------------------------------
227   /**
228    Invokes insert() or update() depending on whether there is an id column and
229    if it already has a value.
230    * @param inConn the connection to use
231    * @return the result of the insert() or update()
232    * @throws SQLException exception thrown if the insert/update fails
233    */
234   public boolean save(Connection inConn)
235         throws SQLException
236   {
237      boolean result;
238
239      DatabaseCol idCol = getIdCol();
240      if (idCol != null
241            && getField(idCol).getValue() != null)
242      {
243         result = update(inConn);
244      }
245      else
246      {
247         result = insert(inConn);
248      }
249
250      return result;
251   }
252
253   //---------------------------------------------------------------------------
254   @SuppressWarnings("unchecked")
255   public boolean insert(Connection inConn)
256      throws SQLException
257   {
258      boolean result = false;
259
260      boolean initialState = inConn.getAutoCommit();
261      try
262      {
263         if (initialState)
264         {
265            inConn.setAutoCommit(false);
266         }
267
268         preInsert(inConn);
269
270         if (! containsBinaryFields())
271         {
272            SQLInsert sql = new SQLInsert().setTable(getDatabaseTable()).setFields(mFieldMap.values());
273            result = sql.execute(inConn);
274         }
275         else
276         {
277            // Because there is a binary field, we should insert via a prepared statement
278            StringBuilderPlus sql = new StringBuilderPlus("INSERT INTO " + getDatabaseTable() + " (");
279            List<DatabaseCol> insertCols = new ArrayList<>(getFields().size());
280            StringBuilderPlus colList = new StringBuilderPlus().setDelimiter(", ");
281            StringBuilderPlus values = new StringBuilderPlus().setDelimiter(",");
282            for (DatabaseField field : getFields())
283            {
284               if (! getIdCol().equals(field.getCol())
285                     && ! field.isNull())
286               {
287                  insertCols.add(field.getCol());
288                  colList.delimitedAppend(field.getCol().name());
289                  values.delimitedAppend("?");
290               }
291            }
292
293            sql.append(colList.toString());
294            sql.append(") VALUES (");
295            sql.append(values.toString());
296            sql.append(")");
297
298            PreparedStatement ps = inConn.prepareStatement(sql.toString());
299            int i = 1;
300            for (DatabaseCol col : insertCols)
301            {
302               DatabaseField field = getField(col);
303               try
304               {
305                  field.setValueInPreparedStatement(ps, i++);
306               }
307               catch (Exception e)
308               {
309                  throw new SQLException("Problem setting value (" + field.getValue() + ") for " + col + "!", e);
310               }
311            }
312
313            SQLUtil.executeUpdate(ps);
314            ps.close();
315         }
316
317
318         DatabaseCol idCol = getIdCol();
319         if (idCol != null)
320         {
321            DatabaseField idField = getField(idCol);
322            if (idField.isNull())
323            {
324               // Save the new row id back to the row object's id field
325               Long newIdValue = getDatabaseTable().getRDBMS().getLastGeneratedId(inConn, idCol);
326
327               if (idField.getCol().getType() == Types.BIGINT)
328               {
329                  idField.setValue(newIdValue);
330               }
331               else if (idField.getCol().getType() == Types.INTEGER)
332               {
333                  idField.setValue(newIdValue.intValue());
334               }
335               else if (idField.getCol().getType() == Types.SMALLINT)
336               {
337                  idField.setValue(newIdValue.intValue());
338               }
339            }
340         }
341
342         postInsert(inConn);
343
344         if (initialState)
345         {
346            inConn.commit();
347         }
348
349         bless();
350      }
351      catch (Exception e)
352      {
353         if (initialState)
354         {
355            inConn.rollback();
356         }
357
358         throw e;
359      }
360      finally
361      {
362         if (initialState != inConn.getAutoCommit())
363         {
364            inConn.setAutoCommit(initialState);
365         }
366      }
367
368      return result;
369   }
370
371   //---------------------------------------------------------------------------
372   public synchronized boolean update(Connection inConn)
373      throws SQLException
374   {
375      if (null == getIdCol())
376      {
377         throw new SQLException("update() cannot be used for a row without an id column!");
378      }
379
380      boolean result = false;
381
382      boolean initialState = inConn.getAutoCommit();
383      try
384      {
385         if (initialState)
386         {
387            inConn.setAutoCommit(false);
388         }
389
390         preUpdate(inConn);
391
392         if (isDirty())
393         {
394            if (! containsBinaryFields())
395            {
396               SQLUpdate sql = new SQLUpdate(this);
397               if (sql.hasSetFields()) // We should always have set fields if we are dirty, but just in case...
398               {
399                  int rowsUpdated = sql.execute(inConn);
400                  result = (1 == rowsUpdated);
401               }
402            }
403            else
404            {
405               // Because there is a binary field, we should update via a prepared statement
406               StringBuilderPlus sql = new StringBuilderPlus("UPDATE " + getDatabaseTable() + " SET");
407               List<DatabaseField> updateFields = new ArrayList<>(getFields().size());
408               for (DatabaseField field : getFields())
409               {
410                  if (!getIdCol().equals(field.getCol()) // Skip the id column
411                        && field.isDirty())
412                  {
413                     sql.append((updateFields.size() > 0 ? "," : "") + " " + field.getCol().name() + " = ?");
414                     updateFields.add(field);
415                  }
416               }
417
418               sql.append(" WHERE " + getIdCol().name() + " = " + getField(getIdCol()).getSQLValue());
419
420
421               PreparedStatement ps = inConn.prepareStatement(sql.toString());
422               int i = 1;
423               for (DatabaseField field : updateFields)
424               {
425                  field.setValueInPreparedStatement(ps, i++);
426               }
427
428               SQLUtil.executeUpdate(ps);
429               ps.close();
430            }
431         }
432
433         postUpdate(inConn);
434
435         if (initialState)
436         {
437            inConn.commit();
438         }
439
440         bless();
441      }
442      catch (Exception e)
443      {
444         if (initialState)
445         {
446            inConn.rollback();
447         }
448
449         throw e;
450      }
451      finally
452      {
453         if (initialState != inConn.getAutoCommit())
454         {
455            inConn.setAutoCommit(initialState);
456         }
457      }
458
459      return result;
460   }
461
462   //---------------------------------------------------------------------------
463   public boolean delete(Connection inConn)
464      throws SQLException
465   {
466      boolean result;
467
468      boolean initialState = inConn.getAutoCommit();
469      try
470      {
471         if (initialState)
472         {
473            inConn.setAutoCommit(false);
474         }
475
476         preDelete(inConn);
477
478         SQLDelete sql = new SQLDelete(this);
479
480         int rowsUpdated = sql.execute(inConn);
481         
482         postDelete(inConn);
483
484         if (initialState)
485         {
486            inConn.commit();
487         }
488         
489         result = (1 == rowsUpdated);
490      }
491      catch (Exception e)
492      {
493         if (initialState)
494         {
495            inConn.rollback();
496         }
497
498         throw e;
499      }
500      finally
501      {
502         if (initialState != inConn.getAutoCommit())
503         {
504            inConn.setAutoCommit(initialState);
505         }
506      }
507
508      return result;
509   }
510
511   //---------------------------------------------------------------------------
512   public DatabaseCol getIdCol()
513   {
514      if (null == mIdCol)
515      {
516         mIdCol = getDatabaseTable().getIdCol();
517      }
518
519      return mIdCol;
520   }
521
522   //---------------------------------------------------------------------------
523   public boolean isDirty()
524   {
525      boolean isDirty = false;
526      for (DatabaseField field : mFieldMap.values())
527      {
528         if (field.isDirty())
529         {
530            isDirty = true;
531            break;
532         }
533      }
534
535      return isDirty;
536   }
537
538   //---------------------------------------------------------------------------
539   /**
540    Indicates whether the specified field is dirty or not.
541    @param inColumn the DatabaseCol corresponding to the field
542    @return whether or not the specified field is dirty
543    */
544   public boolean isDirty(DatabaseCol inColumn)
545   {
546      DatabaseField field = mFieldMap.get(inColumn.name());
547      return (field != null
548              && field.isDirty());
549   }
550
551   //---------------------------------------------------------------------------
552   /**
553    Sets the dirty flag on all fields to false.
554    */
555   public void bless()
556   {
557      for (DatabaseField field : mFieldMap.values())
558      {
559         field.setIsDirty(false);
560      }
561   }
562
563   //###########################################################################
564   // PROTECTED METHODS
565   //###########################################################################
566
567   //---------------------------------------------------------------------------
568   protected DatabaseField allocateField(DatabaseCol inCol)
569   {
570      return allocateField(inCol, (Object) null);
571   }
572
573   //---------------------------------------------------------------------------
574   protected DatabaseField allocateField(DatabaseCol inCol, Object inValue)
575   {
576      DatabaseField field;
577      switch (inCol.getType())
578      {
579         case Types.VARCHAR:
580            field = new DatabaseStringField(inCol, inValue);
581            break;
582         case Types.CHAR:
583            field = new DatabaseCharField(inCol, inValue);
584            break;
585         case Types.DATE:
586         case Types.TIMESTAMP:
587         case Types.TIMESTAMP_WITH_TIMEZONE:
588            field = new DatabaseDateField(inCol, inValue);
589            break;
590         case Types.BIGINT:
591            field = new DatabaseLongField(inCol, inValue);
592            break;
593         case Types.INTEGER:
594            field = new DatabaseIntField(inCol, inValue);
595            break;
596         case Types.SMALLINT:
597            field = new DatabaseShortField(inCol, inValue);
598            break;
599         case Types.FLOAT:
600            field = new DatabaseFloatField(inCol, inValue);
601            break;
602         case Types.REAL:
603         case Types.DOUBLE:
604            field = new DatabaseDoubleField(inCol, inValue);
605            break;
606         case Types.BOOLEAN:
607            field = new DatabaseBooleanField(inCol, inValue);
608            break;
609         case Types.BINARY:
610            field = new DatabaseBinaryField(inCol, inValue);
611            break;
612         case Types.ARRAY:
613            field = new DatabaseArrayField(inCol, inValue);
614            break;
615         default:
616            throw new JDBCException(StringUtil.singleQuote(inCol.getType()) + " is not a currently supported data type!");
617      }
618
619      return field;
620   }
621
622   //---------------------------------------------------------------------------
623   protected DatabaseField allocateField(DatabaseCol inCol, ResultSet inResultSet)
624   {
625      DatabaseField field;
626      switch (inCol.getType())
627      {
628         case Types.VARCHAR:
629            field = new DatabaseStringField(inCol, inResultSet);
630            break;
631         case Types.CHAR:
632            field = new DatabaseCharField(inCol, inResultSet);
633            break;
634         case Types.DATE:
635         case Types.TIMESTAMP:
636         case Types.TIMESTAMP_WITH_TIMEZONE:
637            field = new DatabaseDateField(inCol, inResultSet);
638            break;
639         case Types.SMALLINT:
640            field = new DatabaseShortField(inCol, inResultSet);
641            break;
642         case Types.BIGINT:
643            field = new DatabaseLongField(inCol, inResultSet);
644            break;
645         case Types.INTEGER:
646            field = new DatabaseIntField(inCol, inResultSet);
647            break;
648         case Types.FLOAT:
649            field = new DatabaseFloatField(inCol, inResultSet);
650            break;
651         case Types.REAL:
652         case Types.DOUBLE:
653            field = new DatabaseDoubleField(inCol, inResultSet);
654            break;
655         case Types.BOOLEAN:
656            field = new DatabaseBooleanField(inCol, inResultSet);
657            break;
658         case Types.BINARY:
659            field = new DatabaseBinaryField(inCol, inResultSet);
660            break;
661         case Types.ARRAY:
662            field = new DatabaseArrayField(inCol, inResultSet);
663            break;
664         default:
665            throw new JDBCException(StringUtil.singleQuote(inCol.getType()) + " is not a currently supported data type!");
666      }
667
668      return field;
669   }
670
671   //---------------------------------------------------------------------------
672   protected void addField(DatabaseField inField)
673   {
674      mFields.add(inField);
675      mFieldMap.put(inField.getCol().name(), inField);
676   }
677
678   //---------------------------------------------------------------------------
679   protected void preInsert(Connection inConn)
680      throws SQLException
681   {
682
683   }
684
685   //---------------------------------------------------------------------------
686   protected void postInsert(Connection inConn)
687      throws SQLException
688   {
689
690   }
691
692   //---------------------------------------------------------------------------
693   protected void preUpdate(Connection inConn)
694      throws SQLException
695   {
696
697   }
698
699   //---------------------------------------------------------------------------
700   protected void postUpdate(Connection inConn)
701      throws SQLException
702   {
703
704   }
705
706   //---------------------------------------------------------------------------
707   protected void preDelete(Connection inConn)
708      throws SQLException
709   {
710
711   }
712
713   //---------------------------------------------------------------------------
714   protected void postDelete(Connection inConn)
715      throws SQLException
716   {
717
718   }
719
720   //###########################################################################
721   // PRIVATE METHODS
722   //###########################################################################
723/*
724   //---------------------------------------------------------------------------
725   private void bindResultSet(ResultSet inResultSet)
726   {
727      for (DatabaseField field : mFieldMap.values())
728      {
729         field.setValueFromResultSet(inResultSet);
730
731         field.setIsDirty(false); // Fresh from the database!
732      }
733   }
734
735   //---------------------------------------------------------------------------
736   private boolean resultSetContainsCol(ResultSet inResultSet, DatabaseCol inColumn)
737   {
738      boolean result = true;
739      try
740      {
741         inResultSet.findColumn(inColumn.name());
742      }
743      catch (SQLException e)
744      {
745         result = false;
746      }
747
748      return result;
749   }
750*/
751   //---------------------------------------------------------------------------
752   private boolean containsBinaryFields()
753   {
754      boolean result = false;
755      for (DatabaseField field : getFields())
756      {
757         if (field instanceof DatabaseBinaryField)
758         {
759            result = true;
760            break;
761         }
762      }
763
764      return result;
765   }
766}